PostgreSQL按产品和类别分区实现年度间隔LAG数据查询

你的问题核心在于不能依赖行偏移函数（如LAG()）——因为快照任务可能失败，行的顺序和日期的年度对应关系不固定。正确的思路是基于report_date的日期逻辑匹配，找到同产品、同分类下，去年同一天的库存数据。

下面提供两种通用的实现方案，适配不同数据库：

方法1：自连接（通用SQL语法） #

通过将表与自身关联，匹配product、category相同，且report_date恰好相差一年的记录。这种方法兼容性最好，几乎支持所有SQL数据库。

SELECT
    t1.product,
    t1.units,
    t1.category,
    t1.report_date,
    t2.units AS prev_year_units_atreportdate
FROM
    inventory_snapshots t1
LEFT JOIN
    inventory_snapshots t2
ON
    t1.product = t2.product
    AND t1.category = t2.category
    -- 日期计算需根据你的数据库调整
    AND t2.report_date = DATEADD(year, -1, t1.report_date)
ORDER BY
    t1.product, t1.category, t1.report_date;

不同数据库的日期函数替换： #

• MySQL/MariaDB：DATE_SUB(t1.report_date, INTERVAL 1 YEAR)
• PostgreSQL：t1.report_date - INTERVAL '1 year'
• SQL Server：DATEADD(YEAR, -1, t1.report_date)
• Oracle：ADD_MONTHS(t1.report_date, -12)

方法2：LATERAL JOIN/OUTER APPLY（精准单条匹配） #

如果存在同一天多次快照的场景（比如重复捕获），这种方法可以更灵活地指定取哪一条数据（比如最新的、最大库存值等），性能也更优（尤其是在有复合索引的情况下）。

PostgreSQL 示例： #

SELECT
    t1.product,
    t1.units,
    t1.category,
    t1.report_date,
    t2.units AS prev_year_units_atreportdate
FROM
    inventory_snapshots t1
LEFT JOIN LATERAL (
    -- 若当日有多个快照，可替换为MAX(units)、AVG(units)等聚合逻辑
    SELECT units
    FROM inventory_snapshots
    WHERE product = t1.product
      AND category = t1.category
      AND report_date = t1.report_date - INTERVAL '1 year'
    LIMIT 1
) t2 ON true
ORDER BY
    t1.product, t1.category, t1.report_date;

SQL Server 示例： #

SELECT
    t1.product,
    t1.units,
    t1.category,
    t1.report_date,
    t2.units AS prev_year_units_atreportdate
FROM
    inventory_snapshots t1
OUTER APPLY (
    SELECT TOP 1 units
    FROM inventory_snapshots
    WHERE product = t1.product
      AND category = t1.category
      AND report_date = DATEADD(YEAR, -1, t1.report_date)
) t2
ORDER BY
    t1.product, t1.category, t1.report_date;

关键注意事项 #

1. 若去年同一天无快照（任务失败），prev_year_units_atreportdate会返回NULL，完全符合你的需求。
2. 为提升查询性能，建议在(product, category, report_date)上创建复合索引，这会大幅加速关联匹配的过程。
3. 若存在同一天多快照的情况，可在子查询中加入聚合函数（如MAX(units)）或排序逻辑（如ORDER BY report_time DESC）来获取符合业务需求的数值。

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