[sql] DAU2

이전 포스팅에서는 DAU를 정적으로 구하는 방법을 알아보았고, 이번에는 DAU를 동적으로 구하는 방법을 알아본다. DAU를 동적으로 계산하는 방법은 아래 2가지가 있었는데, 이번 포스팅에서는반복문과 변수를 활용하는 방법에 대해서 쓰겠다

① 반복문과 변수를 활용해서 insert into
② cross join

 

목적은 90일치 각각의 DAU, WAU, MAU 를 계산해서 테이블화 하는 것이다. 각 지표들은 각 기준날짜로부터 계산된 DAU, WAU, MAU 이어야한다. 각 일자마다 세 지표가 다르게 계산되어야 하는 것이다. 이는 아래 4단계를 거쳐 완성해보도록 한다.

 

① 테이블 생성

② 변수선언

③ for loop 생성

④ transaction 처리

 

① 먼저 일자와 각 지표들을 담을 테이블은 만들어준다. 테이블은 만든뒤, insert into를 반복실행해서 90일치의 지표들을 기입해줄 것이다.

create table if not exists daily_acquistions
(d_day date,
dau integer,
wau integer,
mau integer
);

 

② 다음으로는 변수를 선언해준다. 기존 session 테이블의 최신일자가 '2016-10-31' 이므로, 최신일자부터 시작해서 지표를 계산하기위해 current_date_input 변수에 해당일자를 담아준다. 그리고 정수형 타입의 i 변수를 선언해주었는데, 이는 날짜를 하나씩 차감하면서 계산하는 용도로 사용할 것이다.

DECLARE
    current_date_input DATE := '2016-10-31';
    i INT;

 

③ 변수를 지정해줬으면, 지정한 변수를 이용해서 for loop를 만들어준다. FOR i IN 0..89 LOOP 라는 문법으로 루프를 형성해주면 i 라는 변수는 0부터 89까지 반복되면서 루프내의 쿼리를 실행한다. 

FOR i IN 0..89 LOOP
	..
END LOOP;

 

우리는 '2016-10-31' 일자부터 시작해서 과거 90일의 DAU, WAU, MAU를 구해야한다. 이는 loop내에서 위에서 선언한 current_date_input 변수에서 날짜를 0부터 89까지 차감하면서 각 지표를 구해준다. '2016-10-31'의 (DAU, WAU, MAU)를 구한뒤, '2016-10-30'의 (DAU, WAU, MAU)를 구하고, '2016-10-31' - '89일'의 (DAU, WAU, MAU) 까지 구해주는 것이다. 반영한 쿼리는 아래와 같다.

FOR i IN 0..89 LOOP
	INSERT INTO daily_acquisitions (d_day, dau, wau, mau)
	SELECT
	    current_date_input - i AS d_day,
	    (SELECT COUNT(DISTINCT user_id)
	     FROM ga_sess
	     WHERE visit_stime >= (current_date_input - i - INTERVAL '1 days')
	       AND visit_stime < (current_date_input - i)) AS dau,
	    (SELECT COUNT(DISTINCT user_id)
	     FROM ga_sess
	     WHERE visit_stime >= (current_date_input - i - INTERVAL '7 days')
	       AND visit_stime < (current_date_input - i)) AS wau,
	    (SELECT COUNT(DISTINCT user_id)
	     FROM ga_sess
	     WHERE visit_stime >= (current_date_input - i - INTERVAL '30 days')
	       AND visit_stime < (current_date_input - i)) AS mau;
END LOOP;

 

④ 마지막으로 DO $$ .. END $$; 로 쿼리를 감싸주어, 쿼리가 트랜잭션으로 처리되도록 해준다. 데이터 정합성을 위해서 반복문내의 쿼리를 한덩이로 묶어서 도중에 실패한다면 쿼리문 전체가 실패하도록 처리한다. 

DO $$
DECLARE
    current_date_input DATE := '2016-10-31';
    i INT;
BEGIN
    FOR i IN 0..89 LOOP
        INSERT INTO daily_acquisitions (d_day, dau, wau, mau)
        SELECT
            current_date_input - i AS d_day,
            (SELECT COUNT(DISTINCT user_id)
             FROM ga_sess
             WHERE visit_stime >= (current_date_input - i - INTERVAL '1 days')
               AND visit_stime < (current_date_input - i)) AS dau,
            (SELECT COUNT(DISTINCT user_id)
             FROM ga_sess
             WHERE visit_stime >= (current_date_input - i - INTERVAL '7 days')
               AND visit_stime < (current_date_input - i)) AS wau,
            (SELECT COUNT(DISTINCT user_id)
             FROM ga_sess
             WHERE visit_stime >= (current_date_input - i - INTERVAL '30 days')
               AND visit_stime < (current_date_input - i)) AS mau;
    END LOOP;
END $$;

 

그러나 이 쿼리는 시간이 오래걸린다는 단점이 있다. 테이블에 하루치의 데이터를 넣기위해서는 3번의 조회가 필요하며, 이를 90번동안 반복해야 하기때문이다. 3 X 90 = 270, 총 270번의 쿼리를 날려야 90일간의 DAU, WAU, MAU를 구할수 있다. 

 

이 문제를 해결하기 위해서 다음 포스팅에서는 cross join을 이용해서 좀더 효율적으로 지표를 도출하는 방법에 대해 알아본다.