[sql] DAU3

이전시간에는 for문과 변수를 이용해서 90일치의 dau, wau, mau 지표를 계산하는 방법을 알아봤다. 이 방법은 시간이 오래걸린다는 단점이 있었다. for문을 이용해서 90일치의 각 지표를 모두 구하려면 총 270번의 쿼리가 필요했었기 때문이다. 그래서 이번시간에는 이 단점을 극복하고자 cross join을 이용해보고자한다.

 

순서는 아래와같이 진행한다.

 

① 일자 테이블 생성
② 생성한 일자테이블과 session 데이터 cross join 해서 dau, wau, mau 임시 테이블 생성
③ 생성된 세 지표테이블 join

 

① 먼저 일자 테이블을 하나 생성한다. postgres에서는 generate_series라는 함수를 사용해서 시작일과 종료일을 기입해주면 일자 배열형태의 칼럼을 쉽게 만들수 있다. generate_series 함수는 차례로 시작일, 종료일, 간격을 인자로 전달해주면된다.

 

우리는 매일 1일 간격의 dau, wau, mau를 구할것이므로 '1 day'::interval을 간격으로 기입해주면 되겠다. 이렇게 생성한 일자 테이블은 "기준 일자"를 나타내는 칼럼으로 사용할 것이다 (dau, wau, mau를 집계하는 기준일자).

create table
daily_date as 
select generate_series('2016-08-03'::date, '2016-10-31'::date, '1 day'::interval)::date as cur_date;

① 쿼리 결과

 

② 다음으로는 위에서 생성한 일자 테이블과, session 데이터를 cross join해준다. cross join은 키가 따로 없고, 두 테이블의 모든 경우의수를 합치는 join이다. 먼저 모든 경우의수를 합친뒤에 필요한 데이터만 남기는 방법을 사용하는 것이다. 

 

예를들어, dau를 집계한다고하면 각 기준일자(①에서 생성한 테이블의 cur_date)  하루전의 session 정보가 필요하다. 이를 얻기위해서 기준일자 테이블과 session 테이블의 모든 경우의수를 join 한뒤에, 각 기준일자마다 하루 전 session 데이터만 남도록 where절에서 필터한다. 그리고 기준일자로 집계해서 session id를 카운팅해주면 기준일자별 dau를 집계할 수 있다.

create table 
daily_dau as 
select b.cur_date as d_day, count(distinct sess_id) as dau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 1  
    and a.visit_stime < b.cur_date
group by 1
;

 

wau, mau 테이도 위에서 필터 조건만 달리해서 구해주면 된다.

create table 
daily_wau as 
select b.cur_date as d_day, count(distinct sess_id) as wau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 7  
    and a.visit_stime < b.cur_date
group by 1
;

create table 
daily_mau as 
select b.cur_date as d_day, count(distinct sess_id) as mau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 30  
    and a.visit_stime < b.cur_date
group by 1
;

 

③ 마지막으로, 위에서 구한 각 지표 테이블을 기준일자를 key값으로 join해준다. 위에서구한 dau, wau, mau 테이블을 한테이블로 합쳐주는 작업이다.

create table
daily_acquisitions as 
select d.d_day, d.dau, w.dau as wau, m.dau as mau
from daily_dau d
left join daily_wau w on d.d_day = w.d_day
left join daily_mau m on d.d_day = m.d_day
order by d.d_day desc
;

 

최종적으로 완성된 쿼리는 아래와같다.

-- daily_date 테이블 생성
create table 
daily_date as 
select generate_series('2016-08-03'::date, '2016-10-31'::date, '1 day'::interval)::date as cur_date;

-- daily_dau 테이블 생성
create table daily_dau as 
select b.cur_date as d_day, count(distinct sess_id) as dau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 1  
    and a.visit_stime < b.cur_date
group by 1;

-- daily_wau 테이블 생성
create table daily_wau as 
select b.cur_date as d_day, count(distinct sess_id) as wau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 7  
    and a.visit_stime < b.cur_date
group by 1;

-- daily_mau 테이블 생성
create table daily_mau as 
select b.cur_date as d_day, count(distinct sess_id) as mau
from ga_sess a 
cross join daily_date b
where a.visit_stime >= b.cur_date - 30  
    and a.visit_stime < b.cur_date
group by 1;

-- daily_acquisitions 테이블 생성
create table daily_acquisitions as 
select d_day, dau, wau, mau
from daily_dau d
left join daily_wau w using(d_day)
left join daily_mau m using(d_day)
order by d.d_day desc
;

 

출력되는 테이블의 형태는 아래와 같다.

 

사실 이번 글에서 사용했던 cross join도 모든 경우의 수를 합쳐줬다가 필터해서 필요한 데이터만 사용하는 것이기 때문에, 시간이 적게 소요되는것은 아니다.

 

그러나 추측보단 실험과 검증이 항상 중요하다. 이전 포스팅에서 사용했던 반복문을 사용해서 90번의 insert into를 반복하는 쿼리와 cross join을 활용하는 두 쿼리의 시간을 모두 측정해봤을 때, 거의 2배의 성능을 보임을 확인할수 있었다 (노란색: cross join 활용, 하늘색: 반복문 활용).

 

이처럼, 적시에 cross join을 잘만 활용한다면 복잡한 문제를 해결할수있을 뿐더러 쿼리의 성능도 꽤 많이 향상 시킬수 있다.