PostgreSQL中如何获取每条记录去年同期的amount值？

针对日期存在±1天差异的去年同期匹配需求，以下是几种实用的SQL解决方案：

方法1：自连接+日期范围匹配 #

适用于所有支持基础日期函数的SQL数据库，核心是通过划定“去年同期窗口”（当前日期减364至366天）来关联记录，若存在多条匹配则取日期最接近的结果。

PostgreSQL示例 #

SELECT
    t1.date,
    t1.amount,
    COALESCE(t2.amount, 0) AS year_ago_amount
FROM your_table t1
LEFT JOIN your_table t2
    ON t2.date BETWEEN (t1.date - INTERVAL '366 days') AND (t1.date - INTERVAL '364 days')
ORDER BY ABS(t1.date - t2.date)
FETCH FIRST 1 ROW ONLY;

MySQL示例 #

SELECT
    t1.date,
    t1.amount,
    (SELECT t2.amount
     FROM your_table t2
     WHERE t2.date BETWEEN DATE_SUB(t1.date, INTERVAL 366 DAY) AND DATE_SUB(t1.date, INTERVAL 364 DAY)
     ORDER BY ABS(DATEDIFF(t1.date, t2.date))
     LIMIT 1) AS year_ago_amount
FROM your_table t1;

方法2：LATERAL JOIN（PostgreSQL）/CROSS APPLY（SQL Server） #

通过横向关联子查询，精准定位每条记录对应的最近去年同期记录，灵活性更强。

PostgreSQL版本 #

SELECT
    t1.date,
    t1.amount,
    COALESCE(t2.amount, 0) AS year_ago_amount
FROM your_table t1
LEFT JOIN LATERAL (
    SELECT amount
    FROM your_table t2
    WHERE t2.date >= t1.date - INTERVAL '366 days'
      AND t2.date <= t1.date - INTERVAL '364 days'
    ORDER BY ABS(t1.date - t2.date)
    LIMIT 1
) t2 ON true;

SQL Server版本 #

SELECT
    t1.date,
    t1.amount,
    ISNULL(t2.amount, 0) AS year_ago_amount
FROM your_table t1
OUTER APPLY (
    SELECT TOP 1 amount
    FROM your_table t2
    WHERE t2.date >= DATEADD(DAY, -366, t1.date)
      AND t2.date <= DATEADD(DAY, -364, t1.date)
    ORDER BY ABS(DATEDIFF(DAY, t1.date, t2.date))
) t2;

方法3：处理闰年特殊场景 #

针对2月29日这类闰年日期，先标准化日期（将2月29日转为2月28日），再进行匹配，避免因闰年导致的日期偏差：

WITH normalized_dates AS (
    SELECT
        date,
        amount,
        CASE
            WHEN EXTRACT(MONTH FROM date) = 2 AND EXTRACT(DAY FROM date) = 29
            THEN date - INTERVAL '1 day'
            ELSE date
        END AS norm_date
    FROM your_table
)
SELECT
    t1.date,
    t1.amount,
    COALESCE(t2.amount, 0) AS year_ago_amount
FROM normalized_dates t1
LEFT JOIN normalized_dates t2
    ON t2.norm_date = t1.norm_date - INTERVAL '1 year'
    OR ABS(t1.norm_date - (t2.norm_date + INTERVAL '1 year')) <= INTERVAL '1 day';

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