大数据面试SQL044-统计每个用户累计访问次数

数据仓库晨曦

发布于 2024-03-25 15:51:19

940

发布于 2024-03-25 15:51:19

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目

有如下用户访问数据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 语句。

select
    user_id,
    substr(visit_date, 0, 7) as month,
    sum(visit_cnt)           as month_visit
from t_visit_stat_044
group by user_id, substr(visit_date, 0, 7)
order by 1,2

查询结果

2)对上述结果，再次使用聚合函数sum()但是对其进行开窗按照用户分组，按照月份排序。

select
    user_id,
    month,
    month_visit,
    sum(month_visit)over(partition by user_id order by month asc) as total_count
from (select
          user_id,
          substr(visit_date, 0, 7) as month,
          sum(visit_cnt)           as month_visit
      from t_visit_stat_044
      group 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 DELIMITED
FIELDS 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);

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-03-10，如有侵权请联系 cloudcommunity@tencent.com 删除

数据