如何用SQL窗口函数按日期计算30/60天数值变化？

输入数据：

期望输出：

计算逻辑：

• 每条记录需匹配当前日期往前30/60天范围内、最接近当前日期的历史记录
• 变化率公式：(当前值 - 历史值) / 历史值 * 100，结果取整后格式化为百分比，无匹配记录时留空

假设表名为your_table，Date列是日期类型（若为字符串需先转换，见下文说明），以下是通用SQL写法：

SELECT
    t1.Date,
    t1.Value,
    -- 计算30天变化率
    CASE
        WHEN t30.Value IS NOT NULL THEN
            CONCAT(ROUND((t1.Value - t30.Value) / t30.Value * 100), '%')
        ELSE ''
    END AS `Change in last 30 days`,
    -- 计算60天变化率
    CASE
        WHEN t60.Value IS NOT NULL THEN
            CONCAT(ROUND((t1.Value - t60.Value) / t60.Value * 100), '%')
        ELSE ''
    END AS `Change in last 60 days`
FROM your_table t1
-- 关联30天内的最近历史记录
LEFT JOIN your_table t30
    ON t30.Date >= DATE_SUB(t1.Date, INTERVAL 30 DAY)
    AND t30.Date < t1.Date
    AND NOT EXISTS (
        SELECT 1 FROM your_table t
        WHERE t.Date >= DATE_SUB(t1.Date, INTERVAL 30 DAY)
        AND t.Date < t1.Date
        AND t.Date > t30.Date
    )
-- 关联60天内的最近历史记录
LEFT JOIN your_table t60
    ON t60.Date >= DATE_SUB(t1.Date, INTERVAL 60 DAY)
    AND t60.Date < t1.Date
    AND NOT EXISTS (
        SELECT 1 FROM your_table t
        WHERE t.Date >= DATE_SUB(t1.Date, INTERVAL 60 DAY)
        AND t.Date < t1.Date
        AND t.Date > t60.Date
    )
ORDER BY t1.Date DESC;

关键逻辑说明 #

1. 日期范围匹配：用DATE_SUB生成当前日期往前推30/60天的边界，确保历史记录在范围内且早于当前日期
2. 取最近记录：NOT EXISTS子查询过滤掉范围内比当前匹配记录更晚的条目，保证拿到的是距离当前日期最近的历史数据
3. 格式化处理：CASE语句处理无匹配记录的空值情况，ROUND取整百分比，CONCAT添加百分号

适配不同SQL方言的调整 #

• 若Date是字符串类型（如示例格式），需先转为日期类型：
  • MySQL：用STR_TO_DATE(t1.Date, '%m/%d/%Y')替换所有t1.Date
  • SQL Server：用CONVERT(DATE, t1.Date, 101)替换所有t1.Date，并将DATE_SUB改为DATEADD(day, -30, t1.Date)
  • PostgreSQL：用t1.Date::DATE转换类型，DATE_SUB改为t1.Date - INTERVAL '30 days'

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