Snowflake中查询EMAIL_AUDIT表非5天间隔的email_sent_date数据

实现逻辑说明 #

• 首先需要将DD-MM-YYYY格式的字符串类型email_sent_date字段转换为日期类型，才能正确计算日期间隔
• 使用窗口函数LAG()按日期升序排序后，取每一行对应的上一行日期，计算两个日期间的差值
• 筛选出日期间隔不等于5天的记录即为异常数据（首条记录无上一行日期，自动排除）

完整SQL代码 #

WITH date_convert AS (
    -- 转换字符串为日期类型，同时保留原始字符串格式用于输出
    SELECT 
        email_sent_date AS original_date,
        TO_DATE(email_sent_date, 'DD-MM-YYYY') AS sent_date
    FROM EMAIL_AUDIT
),
date_diff_calc AS (
    -- 计算当前行和上一行的日期间隔
    SELECT 
        original_date,
        sent_date,
        DATEDIFF(day, LAG(sent_date) OVER (ORDER BY sent_date ASC), sent_date) AS day_gap
    FROM date_convert
)
-- 筛选间隔不符合5天规则的异常日期
SELECT original_date AS abnormal_date
FROM date_diff_calc
WHERE day_gap != 5;

补充说明 #

1. 如果email_sent_date本身已经是日期类型，可直接省略第一个date_convert CTE，直接在date_diff_calc中使用原字段计算即可
2. 如果你的EMAIL_AUDIT表需要按业务维度分组计算（比如按收件人域、邮件类型分别校验间隔），可以在LAG()函数的窗口中添加PARTITION BY 分组字段即可，示例：

LAG(sent_date) OVER (PARTITION BY email_type ORDER BY sent_date ASC)

执行上述SQL后，输出结果会和预期一致：01-06-2021和01-08-2021。

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