经典sql题（六）查找用户每月累积访问次数

使用聚合开窗查找用户每月累积访问次数，首先介绍一下使用 GROUP BY和开窗的区别

GROUP BY

• 行数变化：使用 GROUP BY 后，原始数据会按指定列进行分组，结果中每组只保留一行，因此行数通常减少。
• 作用：适用于需要对数据进行汇总的场景，如计算总和、平均值等。
• 示例：如果有多个用户的访问记录，通过 GROUP BY 可以将每个用户每月的访问次数汇总为一行。

窗口函数

• 行数变化：窗口函数在计算时不改变原始数据的行数，即每条原始记录依然保留。
• 作用：适用于需要在保留详细数据的同时进行累积计算、排名等操作。
• 示例：在按月汇总用户访问数据的同时，通过窗口函数增加一列显示累计访问次数，每月的详细记录依然存在。

示例数据

假设 test 表有如下数据：

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

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

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;

结果（子查询结果）

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

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

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;

结果（最终结果）

步骤

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 按月份排序。