假设表A为事件流水表,客户当天有一条记录则视为当天活跃。
表A:
time_id user_id
2018-01-0110:00:00 001
2018-01-0111:03:00 002
2018-01-0113:18:00 001
2018-01-0208:34:00 004
2018-01-0210:08:00 002
2018-01-0210:40:00 003
2018-01-0214:21:00 002
2018-01-0215:39:00 004
2018-01-0308:34:00 005
2018-01-0310:08:00 003
2018-01-0310:40:00 001
2018-01-0314:21:00 005
求累计去重:
输出结果如下所示:
日期 当日活跃人数 月累计活跃人数_截至当日
date_id user_cnt_act user_cnt_act_month
2018-01-01 2 2
2018-01-02 3 4
2018-01-03 3 5
实现如下:
select t4.time_id,t5.c2,t4.c from (
select t1.time_id1 time_id,count(distinct(t2.user_id))c from (
select substr(time_id,1,10) time_id1 from a group by substr(time_id,1,10)
)t1
left join
(
select substr(time_id,1,10) time_id1,user_id from a group by substr(time_id,1,10),user_id
)t2
on t1.time_id1>= t2.time_id1
group by t1.time_id1
)t4
left join
(
select substr(time_id,1,10) time_id1,count(distinct(user_id)) c2 from a group by substr(time_id,1,10)
)t5
on t4.time_id = t5.time_id1