当前位置：首页>东北>大数据面试SQL045-每个用户每月访问次数占比及累积占比

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

2025-11-11 20:10:09

接上一道题目大数据面试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_countfrom (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_countfrom (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_perfrom (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 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);

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:www.dwsql.com
同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。

本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。

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

一、题目

二、分析

三、SQL

四、建表语句和数据插入

最新文章

热门文章

随机文章

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

一、题目

二、分析

三、SQL

四、建表语句和数据插入

【万众招聘】​新乡平原航空器材有限公司诚聘质量体系专员

教资|教资面试中小学试讲互动金句

最新文章

热门文章

随机文章

【万众招聘】新乡平原航空器材有限公司诚聘质量体系专员