如何在PostgreSQL中为每个ID筛选符合条件的连续7天数据

需求说明 #

现有数据集包含96个唯一ID，每个ID对应365天的日度记录。需要针对每个ID：

• 找出所有data列不为0且不为null的日期（触发日期）
• 提取每个触发日期及后续连续7天的所有行（例如ID1的2022-01-06触发，需提取1月6日至12日的记录；ID1的2022-05-01触发，需提取5月1日至7日的记录）

解决方案 #

以下是PostgreSQL查询语句，假设你的表名为your_table，且date列是字符串格式（若为date类型可去掉to_date转换）：

WITH trigger_ranges AS (
    -- 筛选所有触发日期，并计算对应的7天区间（当天+后6天）
    SELECT
        ids,
        to_date(date, 'DD.MM.YYYY') AS start_date,
        to_date(date, 'DD.MM.YYYY') + INTERVAL '6 days' AS end_date
    FROM your_table
    WHERE data IS NOT NULL AND data != 0
)
-- 关联原表，提取所有落在触发区间内的记录
SELECT
    t.date,
    t.ids,
    t.data
FROM your_table t
JOIN trigger_ranges tr
    ON t.ids = tr.ids
    AND to_date(t.date, 'DD.MM.YYYY') BETWEEN tr.start_date AND tr.end_date
ORDER BY t.ids, to_date(t.date, 'DD.MM.YYYY');

关键细节说明 #

1. 日期转换优化：如果你的date列本身就是PostgreSQL的date类型，直接用列名替换to_date(date, 'DD.MM.YYYY')即可，避免额外转换开销。
2. 去重处理：如果不同触发区间存在重叠（比如某ID在1月6日和1月8日都有非零data），同一行记录会被多次匹配返回。若需确保每个日期的记录只出现一次，在SELECT后添加DISTINCT即可：

SELECT DISTINCT
    t.date,
    t.ids,
    t.data
-- 后续逻辑同上

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