Snowflake SQL：计算同一STORE_ID下多订单日期的天数差

原始订单数据 #

需求说明 #

新增DAYS列：

• 按同一STORE_ID分组，将订单按ORDER_DATE降序排列
• 计算当前行日期与下一行（上一日期）的天数差
• 若门店仅1条订单或为分组内最后一行（无后续日期），DAYS填0

预期结果 #

原SQL问题排查 #

你提供的SQL返回空值，核心问题有两个：

1. 错误的country条件：子查询中T2.country > T1.country完全不符合逻辑——同一门店的COUNTRY必然相同，这个条件会导致找不到匹配的记录，NextDate始终为空。
2. 日期逻辑反向：需求需要找当前日期的前序更早日期（降序排列后的下一行），但你用MIN(order_date) WHERE T2.order_date > T1.order_date找的是比当前日期晚的最小日期，逻辑完全相反。

正确SQL方案 #

使用窗口函数LAG()可以高效实现需求，无需子查询嵌套：

SELECT 
    country,
    store_id,
    order_date,
    COALESCE(DATEDIFF(day, LAG(order_date) OVER (PARTITION BY store_id ORDER BY order_date DESC), order_date), 0) AS DAYS
FROM main
ORDER BY store_id, order_date DESC;

逻辑说明 #

• PARTITION BY store_id：按门店分组处理
• ORDER BY order_date DESC：组内按日期降序排列
• LAG(order_date)：获取当前行的上一行（降序后的下一行）的日期，若为组内第一行则返回null
• COALESCE(..., 0)：将null值替换为0，满足无后续日期时填0的需求
• DATEDIFF(day, LAG_date, order_date)：计算当前日期与前序日期的天数差

如果你的SQL环境不支持窗口函数（比如旧版MySQL），可以改用关联子查询实现：

SELECT 
    T1.country,
    T1.store_id,
    T1.order_date,
    COALESCE(DATEDIFF(day, T2.order_date, T1.order_date), 0) AS DAYS
FROM main T1
LEFT JOIN (
    SELECT 
        store_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY order_date DESC) AS rn
    FROM main
) T2 ON T1.store_id = T2.store_id 
    AND T2.rn = (SELECT ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY order_date DESC) FROM main WHERE country = T1.country AND store_id = T1.store_id AND order_date = T1.order_date) + 1
ORDER BY T1.store_id, T1.order_date DESC;

不过优先推荐窗口函数方案，代码更简洁高效。

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