0- 描述
描述:假设表A为事件流水表,客户当天有一条记录则视为当天活跃。
表A:
time_id user_id
2018-01-01 10:00:00 001
2018-01-01 11:03:00 002
2018-01-01 13:18:00 001
2018-01-02 08:34:00 004
2018-01-02 10:08:00 002
2018-01-02 10:40:00 003
2018-01-02 14:21:00 002
2018-01-02 15:39:00 004
2018-01-03 08:34:00 005
2018-01-03 10:08:00 003
2018-01-03 10:40:00 001
2018-01-03 14:21:00 005
假设客户活跃非常,一天产生的事件记录平均达千条。
1- 问题一
描述:累计去重
输出结果如下所示:
日期 当日活跃人数 月累计活跃人数_截至当日
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 tt1.date_id
,tt2.user_cnt_act
,tt1.user_cnt_act_month
FROM
( -- ④ 按照t.date_id分组求出user_cnt_act_month,得到tt1
SELECT t.date_id
,COUNT(user_id) AS user_cnt_act_month
FROM
( -- ③ 表a和表b进行笛卡尔积,按照a.date_id,b.user_id分组,保证截止到当日的用户唯一,得出表t。
SELECT a.date_id
,b.user_id
FROM
( -- ① 按照日期分组,取出date_id字段当主表的维度字段 得出表a
SELECT from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
FROM test.temp_tanhaidi_20211213_1
GROUP BY from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
) a
INNER JOIN
( -- ② 按照date_id、user_id分组,保证每天每个用户只有一条记录,得出表b
SELECT from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
,user_id
FROM test.temp_tanhaidi_20211213_1
GROUP BY from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
,user_id
) b
ON 1 = 1
WHERE a.date_id >= b.date_id
GROUP BY a.date_id
,b.user_id
) t
GROUP BY t.date_id
) tt1
LEFT JOIN
( -- ⑥ 按照date_id分组求出user_cnt_act,得到tt2
SELECT date_id
,COUNT(user_id) AS user_cnt_act
FROM
( -- ⑤ 按照日期分组,取出date_id字段当主表的维度字段 得出表a
SELECT from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
,user_id
FROM test.temp_tanhaidi_20211213_1
GROUP BY from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
,user_id
) a
GROUP BY date_id
) tt2
ON tt2.date_id = tt1.date_id