如何用SQL窗口函数获取站点跨年一年以上的前序测量值？

普通的LAG()窗口函数仅能按行偏移取值，无法基于日期差≥1年的条件过滤，因此需要针对每条记录，在同站点范围内查询满足日期要求的最近测量值。以下是不同数据库环境下的实现方案：

方案1：使用横向关联（LATERAL JOIN / APPLY） #

这是最直观且高效的方式，适用于PostgreSQL、BigQuery、MySQL 8.0+、SQL Server等支持横向关联的数据库。

PostgreSQL / BigQuery 示例 #

SELECT 
    t.site,
    t.date,
    t.measurement,
    prev.measurement AS previous_measurement
FROM your_table t
LEFT JOIN LATERAL (
    -- 同站点下，早于当前日期1年以上的最近记录
    SELECT measurement
    FROM your_table
    WHERE site = t.site
      AND date < t.date - INTERVAL '1 year'
    ORDER BY date DESC
    LIMIT 1
) prev ON true
ORDER BY t.site, t.date DESC;

MySQL 8.0+ 示例 #

SELECT 
    t.site,
    t.date,
    t.measurement,
    prev.measurement AS previous_measurement
FROM your_table t
LEFT JOIN LATERAL (
    SELECT measurement
    FROM your_table
    WHERE site = t.site
      AND date < DATE_SUB(t.date, INTERVAL 1 YEAR)
    ORDER BY date DESC
    LIMIT 1
) prev ON 1=1
ORDER BY t.site, t.date DESC;

SQL Server 示例 #

SELECT 
    t.site,
    t.date,
    t.measurement,
    prev.measurement AS previous_measurement
FROM your_table t
OUTER APPLY (
    SELECT TOP 1 measurement
    FROM your_table
    WHERE site = t.site
      AND date < DATEADD(YEAR, -1, t.date)
    ORDER BY date DESC
) prev
ORDER BY t.site, t.date DESC;

方案说明 #

• 主表每条记录通过横向关联，查询同站点内满足日期 < 当前日期 - 1年的记录，并按日期倒序取第一条，即为符合要求的最近测量值。
• 无符合条件的记录时，previous_measurement自动返回NULL，与预期结果一致。

验证结果 #

以你提供的样本数据为例，执行上述SQL后将得到：

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