[szerkesztettem parszor, sorry]

SAS-ban szenvedek valamivel, ami beszel SQL-ul is.
Igazabol a megoldas egyik resze megvan, csak nem ertek dolgokat. Adott hogy akarok nezni egy penetracio%-ot, amit ha egy lepesben csinalok (lasd 1. verzio), az harom oraig fut. Ha ket lepesben csinalom, aztan visszamergeolom (ezt a merge-t SAS-ban, nem SQL-ben, de asszem ez mindegy, mert SQL join se tartana semeddig), akkor ket perc alatt lefut. Probalom megerteni miert. Lasd lejjebb 1. es 2. verzio.
A &yearmonth. -t ignorald, az csak makró, annyit jelent per pill hogy "201201"

/* v1 - ez harom orat tart */

proc sql;
create table p1_dmn_p_m_&yearmonth. as
select t1.yearmonth,
t1.event,
t1.subcategorised_domain,
count(distinct t1.hhid) as cnt_hhids,
count(distinct t1.usersessionid) as cnt_sessions,
count(t1.url) as cnt_pis ,
count(distinct t1.hhid)/(select count(distinct t2.hhid)
from clife.Activity_merged_&yearmonth. as t2
where t2.subcategorised_domain ne "" and
t1.yearmonth = t2.yearmonth and
t1.event=t2.event and
t1.subcategorised_domain = t2.subcategorised_domain
group by t2.yearmonth) as penpct
from clife.Activity_merged_&yearmonth. as t1
where t1.subcategorised_domain ne ""
group by t1.yearmonth, t1.event, t1.subcategorised_domain
order by t1.yearmonth, t1.event;
quit;

/* v2 - ez kb ket perc alatt lefut. miert?? */

proc sql;
create table p1_dmn_p_m_&yearmonth. as
select t1.yearmonth,
t1.event,
t1.subcategorised_domain,
count(distinct t1.hhid) as cnt_hhids,
count(distinct t1.usersessionid) as cnt_sessions,
count(t1.url) as cnt_pis
from clife.Activity_merged_&yearmonth. as t1
where t1.subcategorised_domain ne ""
group by t1.yearmonth, t1.event, t1.subcategorised_domain
order by t1.yearmonth, t1.event;
quit;

proc sql;
create table p2_dmn_p_m_&yearmonth. as
select distinct yearmonth, event, count(distinct t2.hhid)
from clife.Activity_merged_&yearmonth. as t2
where t2.subcategorised_domain ne "" and
t2.yearmonth and
t2.event and
t2.subcategorised_domain
group by t2.yearmonth, event;
quit;
data kpi_dmn_p_m_&yearmonth. (drop = _temg001);
merge p1_dmn_p_m_&yearmonth. p2_dmn_p_m_&yearmonth.;
by yearmonth event ;
penpct = cnt_hhids/_temg001;
run;
proc sort data = kpi_dmn_p_m_&yearmonth.; by event descending penpct; run;

[ Szerkesztve ]

BackToTheUKBlog:[backtotheukblog.wordpress.com] 2019/09/05: Brexitgráf IV — őrültek és csirkék háza