在Redshift中查询近1周内各ID的首个数据缺失日期

问题背景 #

需要编写Redshift SQL查询，确定近1周回溯窗口内每个ID的首个数据缺失日期（注：这里的“首个缺失”指从当日往回追溯遇到的第一个缺失日期，即离当日最近的缺失日期）。数据按ID每日记录，date字段类型为datetimestamp。

示例数据 #

原数据表（假设表名为daily_records）：

+------+---------------------+
| id   | date                |
+------+---------------------+
|    1 | 2023-05-04 00:00:00 |
|    1 | 2023-05-05 00:00:00 |
|    1 | 2023-05-06 00:00:00 |
|    2 | 2023-05-04 00:00:00 |
|    2 | 2023-05-05 00:00:00 |
|    2 | 2023-05-11 00:00:00 |
+------+---------------------+

假设当日为2023-05-11，期望返回结果：

+------+------------+
| id   | first_gap  |
+------+------------+
|    1 | 2023-05-11 |
|    2 | 2023-05-10 |
+------+------------+

解决方案SQL #

以下查询通过生成日期范围、匹配预期记录、筛选缺失日期三个步骤实现需求：

WITH date_range AS (
    -- 生成近1周的日期范围（当日往前推6天，共7天）
    SELECT DATE '2023-05-11' AS dt
    UNION ALL
    SELECT dt - INTERVAL '1 day'
    FROM date_range
    WHERE dt - INTERVAL '1 day' >= DATE '2023-05-11' - INTERVAL '6 days'
),
all_ids AS (
    -- 获取所有唯一ID
    SELECT DISTINCT id FROM daily_records
),
all_id_dates AS (
    -- 生成每个ID在近1周内的所有预期日期（笛卡尔积）
    SELECT ai.id, dr.dt
    FROM all_ids ai
    CROSS JOIN date_range dr
),
missing_dates AS (
    -- 筛选出原表中缺失的日期记录
    SELECT aid.id, aid.dt AS missing_date
    FROM all_id_dates aid
    LEFT JOIN daily_records t 
        ON aid.id = t.id 
        AND DATE_TRUNC('day', t.date) = aid.dt  -- 匹配日期部分（忽略时间戳）
    WHERE t.id IS NULL
)
-- 按ID分组，取离当日最近的缺失日期
SELECT id, MAX(missing_date) AS first_gap
FROM missing_dates
GROUP BY id
ORDER BY id;

关键说明 #

1. 日期范围生成：使用递归CTEdate_range生成近7天的日期，若需动态使用当前日期，将DATE '2023-05-11'替换为CURRENT_DATE即可。
2. 匹配日期部分：由于date字段是datetimestamp类型，用DATE_TRUNC('day', t.date)截取日期部分与生成的纯日期匹配。
3. 处理全量ID：通过all_idsCTE确保所有存在的ID都能出现在结果中，避免因无缺失日期被过滤。

如果需要对无缺失日期的ID返回特定标识（如“无缺失”），可以修改最后一步查询：

SELECT ai.id, COALESCE(MAX(md.missing_date), '无缺失日期') AS first_gap
FROM all_ids ai
LEFT JOIN missing_dates md ON ai.id = md.id
GROUP BY ai.id
ORDER BY ai.id;

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