经典sql题（六）查找用户每月累积访问次数
使用聚合开窗查找用户每月累积访问次数，首先介绍一下使用 GROUP BY和开窗的区别
GROUP BY
- 行数变化：使用
GROUP BY后，原始数据会按指定列进行分组，结果中每组只保留一行，因此行数通常减少。 - 作用：适用于需要对数据进行汇总的场景，如计算总和、平均值等。
- 示例：如果有多个用户的访问记录，通过
GROUP BY可以将每个用户每月的访问次数汇总为一行。
窗口函数
- 行数变化：窗口函数在计算时不改变原始数据的行数，即每条原始记录依然保留。
- 作用：适用于需要在保留详细数据的同时进行累积计算、排名等操作。
- 示例：在按月汇总用户访问数据的同时，通过窗口函数增加一列显示累计访问次数，每月的详细记录依然存在。
示例数据
假设 test 表有如下数据：
| user_id | visit_date | visit_count |
|---|---|---|
| 1 | 2023-01-05 10:30:00 | 10 |
| 1 | 2023-01-15 15:45:00 | 20 |
| 1 | 2023-02-10 12:00:00 | 15 |
| 2 | 2023-01-07 09:00:00 | 5 |
| 2 | 2023-02-11 14:30:00 | 10 |
| 3 | 2023-01-20 11:00:00 | 8 |
| 3 | 2023-02-05 16:30:00 | 12 |
| 4 | 2023-01-25 14:00:00 | 7 |
| 4 | 2023-02-15 09:15:00 | 9 |
| 5 | 2023-01-10 13:30:00 | 6 |
| 5 | 2023-02-20 16:45:00 | 11 |
第一步：提取年月并计算每月访问次数
我们首先提取出每条记录的年月，并计算每个用户每月的访问次数：
SELECTuser_id,DATE_FORMAT(visit_date, '%Y-%m') AS month_id,SUM(visit_count) AS visit_cnt_lm
FROMtest
GROUP BYuser_id,month_id;
结果（子查询结果）
| user_id | month_id | visit_cnt_lm |
|---|---|---|
| 1 | 2023-01 | 30 |
| 1 | 2023-02 | 15 |
| 2 | 2023-01 | 5 |
| 2 | 2023-02 | 10 |
| 3 | 2023-01 | 8 |
| 3 | 2023-02 | 12 |
| 4 | 2023-01 | 7 |
| 4 | 2023-02 | 9 |
| 5 | 2023-01 | 6 |
| 5 | 2023-02 | 11 |
第二步：计算访问次数的累计值
然后，我们使用窗口函数为每个用户计算访问次数的累计值：
SELECTuser_id,month_id,visit_cnt_lm,SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id) AS visit_cnt_td
FROM (SELECTuser_id,DATE_FORMAT(visit_date, '%Y-%m') AS month_id,SUM(visit_count) AS visit_cnt_lmFROMtestGROUP BYuser_id,month_id
) AS t2;
结果（最终结果）
| user_id | month_id | visit_cnt_lm | visit_cnt_td |
|---|---|---|---|
| 1 | 2023-01 | 30 | 30 |
| 1 | 2023-02 | 15 | 45 |
| 2 | 2023-01 | 5 | 5 |
| 2 | 2023-02 | 10 | 15 |
| 3 | 2023-01 | 8 | 8 |
| 3 | 2023-02 | 12 | 20 |
| 4 | 2023-01 | 7 | 7 |
| 4 | 2023-02 | 9 | 16 |
| 5 | 2023-01 | 6 | 6 |
| 5 | 2023-02 | 11 | 17 |
步骤
-
提取年月并计算访问次数：
- 查询：使用
DATE_FORMAT提取年月，并汇总每个用户每月的访问次数。 - 结果表：显示用户、月份及其访问次数。
- 查询：使用
-
计算访问次数的累计值：
- 查询：使用窗口函数计算每个用户的访问次数累计值。
- 最终结果：展示每个用户每月的访问次数及其累计值。
解析
-
提取年月：使用
DATE_FORMAT方法从完整日期中提取年月。 -
汇总访问次数：利用
SUM(visit_count)按用户和月份分组汇总数据。 -
计算累计值：通过窗口函数
SUM() OVER按用户分区、按月份排序，计算每个用户的访问次数累计值。 -
SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id)计算每个用户按照月份的累计访问次数。 -
PARTITION BY user_id按用户分区，ORDER BY month_id按月份排序。