带2个月重叠的3个月滚动日期聚合实现咨询

问题背景 #

给定原始数据集：

date    Value   
01-Jul-24   37    
01-Aug-24   76   
01-Sep-24   25    
01-Oct-24   85    
01-Nov-24   27    
01-Dec-24   28

需要按连续3个月、重叠2个月的规则聚合，输出仅包含Date_Period和Sum两列的结果：

Date_Period Sum   
Oct ~ Dec 2024  140    
Sep ~ Nov 2024  137    
Aug ~ Oct 2024  186

最初尝试窗口函数over(partition..preceding..)会生成多余列，需要精简输出。

解决方案 #

利用窗口函数计算滚动总和，再过滤出完整周期的行并格式化日期字符串即可。以下分两种主流数据库示例：

PostgreSQL 实现 #

WITH monthly_data AS (
    SELECT
        TO_DATE(date, 'DD-Mon-YY') AS date,
        Value
    FROM your_table
),
rolling_sums AS (
    SELECT
        date,
        -- 计算当前月+前两个月的总和
        SUM(Value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum,
        -- 提取周期起始月份
        TO_CHAR(date - INTERVAL '2 months', 'Mon') AS start_month,
        -- 提取周期结束月份和年份
        TO_CHAR(date, 'Mon YYYY') AS end_month_year
    FROM monthly_data
)
SELECT
    CONCAT(start_month, ' ~ ', end_month_year) AS Date_Period,
    rolling_sum AS Sum
FROM rolling_sums
-- 只保留有完整3个月数据的行（从第3个月份开始）
WHERE date >= (SELECT MIN(date) + INTERVAL '2 months' FROM monthly_data)
ORDER BY date DESC;

MySQL 实现 #

WITH monthly_data AS (
    SELECT
        STR_TO_DATE(date, '%d-%b-%y') AS date,
        Value
    FROM your_table
),
rolling_sums AS (
    SELECT
        date,
        -- 计算当前月+前两个月的总和
        SUM(Value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum,
        -- 提取周期起始月份
        DATE_FORMAT(DATE_SUB(date, INTERVAL 2 MONTH), '%b') AS start_month,
        -- 提取周期结束月份和年份
        DATE_FORMAT(date, '%b %Y') AS end_month_year
    FROM monthly_data
)
SELECT
    CONCAT(start_month, ' ~ ', end_month_year) AS Date_Period,
    rolling_sum AS Sum
FROM rolling_sums
-- 只保留有完整3个月数据的行（从第3个月份开始）
WHERE date >= (SELECT DATE_ADD(MIN(date), INTERVAL 2 MONTH) FROM monthly_data)
ORDER BY date DESC;

逻辑说明 #

1. 先将原始字符串日期转换为数据库可识别的日期类型，避免字符串排序出错。
2. 用窗口函数SUM(...) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)计算连续3个月的滚动总和，确保每个行对应自身及前两行的数值之和。
3. 通过日期函数生成周期的起始月份和结束月份+年份，拼接成Date_Period格式。
4. 过滤掉无法形成完整3个月周期的行（比如7月、8月的记录），只保留从第3个月份开始的行，最后按日期降序排列得到目标结果。

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