经典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

第一步：提取年月并计算每月访问次数

我们首先提取出每条记录的年月，并计算每个用户每月的访问次数：

SELECT
    user_id,
    DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
    SUM(visit_count) AS visit_cnt_lm
FROM
    test
GROUP BY
    user_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

第二步：计算访问次数的累计值

然后，我们使用窗口函数为每个用户计算访问次数的累计值：

SELECT
    user_id,
    month_id,
    visit_cnt_lm,
    SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id) AS visit_cnt_td
FROM (
    SELECT
        user_id,
        DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
        SUM(visit_count) AS visit_cnt_lm
    FROM
        test
    GROUP BY
        user_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

步骤

1. 提取年月并计算访问次数：

   • 查询：使用 DATE_FORMAT 提取年月，并汇总每个用户每月的访问次数。
   • 结果表：显示用户、月份及其访问次数。

2. 计算访问次数的累计值：

   • 查询：使用窗口函数计算每个用户的访问次数累计值。
   • 最终结果：展示每个用户每月的访问次数及其累计值。

解析

• 提取年月：使用 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 按月份排序。