大数据面试SQL045-每个用户每月访问次数占比及累积占比

数据仓库晨曦

发布于 2024-03-25 15:52:56

840

发布于 2024-03-25 15:52:56

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

接上一道题目大数据面试SQL044-统计每个用户累计访问次数我们再进一步探查sum()聚合函数使用over()开窗后有order by和没有order by的区别。

一、题目

有如下用户访问数据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_per    |   total_visit_per    |
+----------+----------+----------------------+----------------------+
| u001     | 2024-01  | 0.5833333333333334   | 0.5833333333333334   |
| u001     | 2024-02  | 0.4166666666666667   | 1.0                  |
| u002     | 2024-01  | 0.35294117647058826  | 0.35294117647058826  |
| u002     | 2024-02  | 0.6470588235294118   | 1.0                  |
| u003     | 2024-01  | 0.5                  | 0.5                  |
| u003     | 2024-02  | 0.5                  | 1.0                  |
+----------+----------+----------------------+----------------------+

二、分析

上一个题目，我们统计出了每个月的访问次数，以及到当月的累积次数。

想要实现每个用户本月访问次数占总访问次数的比例,需要计算出每个用户的访问总次数；想要计算出累积访问次数占总访问次数比例，也需要计算出每个用户的访问总次数；但是这里有个问题，需要将sum()的结果放到每一行，所以我们依旧需要使用开窗。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1）上一篇中查询累积访问次数的查询语句和结果

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

查询结果

2）在上面结果上，我们增加一列数据，对每个用户所有月份的访问次数和： total_month_count 。这里需要注意聚合函数sum()后面over()内的写法差别，计算累积求和时有order by 语句，计算全部访问次数时没有。

select
    user_id,
    month,
    month_visit,
    sum(month_visit)over(partition by user_id order by month asc) as total_count,
    sum(month_visit)over(partition by user_id) as total_all_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

查询结果

查询最后结果，需要将month_visit/total_all_count即为每个用户每月访问次数占其总次数的比例，total_count/total_all_count即为累积访问次数占比

select
    user_id,
    month,
    month_visit/sum(month_visit)over(partition by user_id) as month_visit_per,
    sum(month_visit)over(partition by user_id order by month asc)/sum(month_visit)over(partition by user_id) as total_visit_per
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

查询结果

说明： 聚合函数包括 sum()、count()、avg() 等，均可以使用over() 对其开窗。其中在over()函数内，如果存在order by 语句，则是计算从分组内的第一个数据累积到当前行数据的聚合结果，如果没有order by语句，则计算整个分组内的聚合结果。

四、建表语句和数据插入

--建表语句
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-12，如有侵权请联系 cloudcommunity@tencent.com 删除

函数