一、题目
有如下用户访问数据t_visit_stat_044,包含用户ID(user_id),访问日期(visit_date),当天访问次数(visit_cnt)
要求使用SQL统计出每个用户的累积访问次数。如下表所示
--样例数据+----------+-------------+------------+| user_id | visit_date | visit_cnt |+----------+-------------+------------+| u001 | 2024-01-01 | 3 || u001 | 2024-01-02 | 4 || u002 | 2024-01-03 | 6 || u003 | 2024-01-07 | 2 || u001 | 2024-02-04 | 5 || u002 | 2024-02-10 | 3 || u002 | 2024-02-13 | 1 || u002 | 2024-02-14 | 7 || u003 | 2024-02-11 | 2 |+----------+-------------+------------+--期望结果+----------+----------+--------------+--------------+| user_id | month | month_visit | total_count |+----------+----------+--------------+--------------+| u001 | 2024-01 | 7 | 7 || u001 | 2024-02 | 5 | 12 || u002 | 2024-01 | 6 | 6 || u002 | 2024-02 | 11 | 17 || u003 | 2024-01 | 2 | 2 || u003 | 2024-02 | 2 | 4 |+----------+----------+--------------+--------------+
二、分析
题目要考察的内容分为两部分，统计每个用户每月访问次数，这个分组使用聚合函数sum()即可完成，即题目中的小计部分。第二部分为计算每个用户截止到当前月份的累积访问次数。这是考察聚合函数经过over开窗后，使用order by 进行排序和不使用排序结果的差异。不使用order by计算的是整个分组下的聚合结果；使用order by 则是计算分组内截止到当前行的数据。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）因为题目给出的是每天的统计结果，目标结果为每个月数据。我们第一步使用聚合函数sum()计算每月每个用户的访问次数。这里为了结果方便查看，在最后增加了order by 语句。
selectuser_id,substr(visit_date, 0, 7) as month,sum(visit_cnt) as month_visitfrom t_visit_stat_044group by user_id, substr(visit_date, 0, 7)order by 1,2
查询结果
2)对上述结果，再次使用聚合函数sum()但是对其进行开窗按照用户分组，按照月份排序。
selectuser_id,month,month_visit,sum(month_visit)over(partition by user_id order by month asc) as total_countfrom (selectuser_id,substr(visit_date, 0, 7) as month,sum(visit_cnt) as month_visitfrom t_visit_stat_044group by user_id, substr(visit_date, 0, 7)) t
查询结果
四、建表语句和数据插入
--建表语句create table if not exists t_visit_stat_044(user_id string,visit_date string,visit_cnt bigint)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS orc;--数据插入语句insert into table t_visit_stat_044 values('u001','2024-01-01',3),('u001','2024-01-02',4),('u002','2024-01-03',6),('u003','2024-01-07',2),('u001','2024-02-04',5),('u002','2024-02-10',3),('u002','2024-02-13',1),('u002','2024-02-14',7),('u003','2024-02-11',2);
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:
同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，关注公众号，后台回复“加群”，添加好友加入；
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
下一篇：
数据分析01-如何进行数据分箱
请登录后发表评论
登录
全部评论
