MySQL无需自连接：按行/按天聚合近30天数据及独立用户统计

嘿，你的需求完全可以通过MySQL的窗口函数实现，而且比自连接更简洁高效——尤其是MySQL 8.0及以上版本已经支持范围型窗口，刚好适配时间滑动的场景。下面分场景给你详细说明：

1. 对每行数据聚合其过去30天的数据 #

不需要自连接，直接用RANGE类型的窗口函数就能搞定。注意MySQL 8.0.19及以上版本才支持窗口函数里的COUNT(DISTINCT)，如果你的版本符合要求，直接用下面的语句：

SELECT 
    SessionID,
    UserID,
    datetime,
    -- 统计当前行日期往前30天内的独立用户数
    COUNT(DISTINCT UserID) OVER (
        ORDER BY datetime 
        RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
    ) AS past_30d_unique_users
FROM UserSessions;

语句解释： #

• ORDER BY datetime 指定窗口的排序依据是会话时间
• RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW 定义了滑动窗口的范围：从当前行的日期往前推30天，一直到当前行的时间点
• COUNT(DISTINCT UserID) 会自动统计这个时间窗口内的所有独立用户，每行都会返回对应的聚合结果

2. 按天展示该日期过去30天内的独立用户数量 #

如果需要按日期维度汇总，我们可以先把每天的独立用户去重，再用窗口函数计算滑动窗口的聚合：

-- 先按天筛选出每天的独立用户
WITH daily_unique_users AS (
    SELECT 
        DATE(datetime) AS session_date,
        UserID
    FROM UserSessions
    GROUP BY DATE(datetime), UserID
)
SELECT 
    session_date,
    -- 统计当前日期往前30天内的独立用户总数
    COUNT(UserID) OVER (
        ORDER BY session_date 
        RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
    ) AS past_30d_unique_users
FROM daily_unique_users
GROUP BY session_date
ORDER BY session_date;

语句解释： #

• CTE daily_unique_users 先把同一天内同一用户的多次会话去重，避免重复统计
• 主查询里的窗口函数基于日期排序，滑动范围同样是过去30天，因为已经去重，直接用COUNT(UserID)就可以得到准确的独立用户数

3. 通用滑动窗口聚合的支持 #

MySQL的窗口函数完全支持你提到的两类滑动场景：

• 行范围滑动：比如统计当前行前后N行的聚合，可以用ROWS BETWEEN N PRECEDING AND CURRENT ROW（或其他范围），例如：

  SELECT 
      id,
      value,
      AVG(value) OVER (ORDER BY id ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS last_6_rows_avg
  FROM your_table;
  

• 时间范围滑动：像你说的“统计某日期前30天的橙子均价”这类需求，写法和前面的用户统计类似，只需要替换聚合函数和表结构：

  SELECT 
      sale_date,
      AVG(price) OVER (
          ORDER BY sale_date 
          RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
      ) AS past_30d_avg_orange_price
  FROM orange_sales;
  

这类窗口函数的性能通常比自连接好，因为数据库可以更高效地处理窗口内的聚合计算，不需要做笛卡尔积式的关联。

内容的提问来源于stack exchange，提问作者Ken Skywalker