如何移除数据库中间隔非恰好一年的日期观测值

核心思路 #

要提取间隔恰好一年的观测值，核心是基于日期/时间字段的差值计算，通过两种常见方式实现：

1. 自关联表，匹配时间差为1年的记录对
2. 使用窗口函数，查找每条记录前后间隔1年的观测

具体实现（按数据库类型） #

1. MySQL/MariaDB #

假设你的表名为observations，日期字段为record_date，唯一标识字段为id。

方式1：自关联找所有间隔1年的记录对

SELECT 
    o1.id AS id_1, o1.record_date AS date_1,
    o2.id AS id_2, o2.record_date AS date_2
FROM 
    observations o1
JOIN 
    observations o2 
ON 
    o1.id <> o2.id  -- 排除自身匹配
    AND YEAR(o2.record_date) = YEAR(o1.record_date) + 1
    AND MONTH(o2.record_date) = MONTH(o1.record_date)
    AND DAY(o2.record_date) = DAY(o1.record_date);

注：如果需要忽略闰年2月29日的特殊情况，可以用TIMESTAMPDIFF(YEAR, o1.record_date, o2.record_date) = 1代替日期部分的匹配，但要注意该函数是按整年跨度计算（比如2023-03-01到2024-02-28会被判定为1年），如果需要精确到日的恰好一年，优先用日期各部分匹配。

方式2：窗口函数找每条记录的下一年对应观测

SELECT 
    id, record_date,
    LEAD(id) OVER (PARTITION BY DATE_FORMAT(record_date, '%m-%d') ORDER BY record_date) AS next_year_id,
    LEAD(record_date) OVER (PARTITION BY DATE_FORMAT(record_date, '%m-%d') ORDER BY record_date) AS next_year_date
FROM 
    observations
HAVING 
    TIMESTAMPDIFF(YEAR, record_date, next_year_date) = 1;

这里按“月-日”分组，用LEAD函数获取同月份日期的下一条记录，再筛选时间差恰好为1年的结果。

2. PostgreSQL #

PostgreSQL支持更灵活的日期运算，假设表和字段同上：

方式1：自关联精确匹配年差

SELECT 
    o1.id, o1.record_date, o2.id, o2.record_date
FROM 
    observations o1
INNER JOIN 
    observations o2 
ON 
    o1.id != o2.id
    AND o2.record_date = o1.record_date + INTERVAL '1 year';

注：PostgreSQL会自动处理闰年，比如2020-02-29加1年会变成2021-02-28，如果需要严格匹配日份，可以额外加EXTRACT(DOY FROM o1.record_date) = EXTRACT(DOY FROM o2.record_date)（DOY是一年中的第几天）。

方式2：窗口函数筛选年度对应记录

WITH ranked_obs AS (
    SELECT 
        id, record_date,
        ROW_NUMBER() OVER (PARTITION BY TO_CHAR(record_date, 'MM-DD') ORDER BY record_date) AS rn
    FROM observations
)
SELECT 
    r1.id, r1.record_date, r2.id, r2.record_date
FROM ranked_obs r1
JOIN ranked_obs r2 
ON 
    r1.rn = r2.rn - 1
    AND r1.record_date + INTERVAL '1 year' = r2.record_date;

3. SQL Server #

SQL Server的日期函数略有不同，示例如下：

方式1：自关联找精确年差记录

SELECT 
    o1.id, o1.record_date, o2.id, o2.record_date
FROM 
    observations o1
JOIN 
    observations o2 
ON 
    o1.id <> o2.id
    AND DATEADD(YEAR, 1, o1.record_date) = o2.record_date;

方式2：窗口函数获取年度匹配项

SELECT 
    id, record_date, next_year_id, next_year_date
FROM (
    SELECT 
        id, record_date,
        LEAD(id) OVER (PARTITION BY FORMAT(record_date, 'MM-dd') ORDER BY record_date) AS next_year_id,
        LEAD(record_date) OVER (PARTITION BY FORMAT(record_date, 'MM-dd') ORDER BY record_date) AS next_year_date
    FROM observations
) AS obs
WHERE 
    DATEADD(YEAR, 1, record_date) = next_year_date;

特殊场景处理 #

• 如果你的表有分组维度（比如每个用户的观测记录），只需在PARTITION BY或关联条件中加入分组字段（比如user_id），确保只匹配同一用户的年度记录。
• 若存在多条同年同月同日的记录，可通过额外字段（如timestamp）进一步筛选，或用DISTINCT去重。

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