hiveSQL面试题24__N指标--累计去重

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