DAU, WAU, MAU를 SQL로 구하기-02 관련 질문드립니다.

with

temp_00 as (

select generate_series('2016-08-02'::date, '2016-11-01'::date, '1 days'::interval)::date as current_date

)

select b.current_date, count(distinct user_id) as dau

from ga_sess a

cross join temp_00 b

where visit_stime >= (b.current_date - interval '1 days') and visit_stime < b.current_date

group by b.current_date

위 SQL문에서

where visit_stime >= (b.current_date - interval '1 days') and visit_stime < b.current_date

이 부분이 이해가 가지 않습니다.

current_date가 2016-08-02일 경우

visit_stime이 2016-08-02보다는 작아야 하고,

2016-08-01보다는 크거나 같다는 말인데..

그럼 즉, 2016-08-01 00:00:00 ~2016-08-01 23:59:59 라는 건데요..

그럼 이게 2016-08-01의 DAU가 아닌가요?..

select 절에서는 current_date로 group by를 해서

추출된 데이터는 2016-08-02의 DAU로 추출이 되더라구요..

실제 csv파일로 받아서 보아도,

2016-08-01의 dau가 1569로 확인이 되는거 같기두 하구요..

with

temp_00 as (

select generate_series('2016-08-01'::date, '2016-11-01'::date, '1 days'::interval)::date as current_date

)

select b.current_date, count(distinct user_id) as dau

from ga_sess a

cross join temp_00 b

where visit_stime >= b.current_date and visit_stime < (b.current_date + interval '1 days')

group by b.current_date

with

temp_00 as (

select generate_series('2016-08-01'::date, '2016-11-01'::date, '1 days'::interval)::date as current_date

)

select b.current_date, count(distinct user_id) as wau

from ga_sess a

cross join temp_00 b

where visit_stime >= (b.current_date - interval '6 days') and visit_stime < (b.current_date + interval '1 days')

group by b.current_date;

with

temp_00 as (

select generate_series('2016-08-01'::date, '2016-11-01'::date, '1 days'::interval)::date as current_date

)

select b.current_date, count(distinct user_id) as mau

from ga_sess a

cross join temp_00 b

where visit_stime >= (b.current_date - interval '29 days') and visit_stime < (b.current_date + interval '1 days')

group by b.current_date;

이렇게 구하는게 맞는거 아닌지 문의드립니다 !