Teradata中计算指定日期区间内门店/商品组合的最长连续无销售天数

我来帮你解决这个Teradata里的连续无销售天数计算问题，你的原SQL确实漏掉了不少关键情况——比如区间首尾的无销售时段、连续0销量的日期，还有原表缺失记录的无销售场景，咱们一步步来修正：

核心思路 #

要准确计算连续无销售天数，首先得补全所有门店/商品组合在指定日期区间内的每一天记录，这样才能统一处理「无记录」和「Volume=0」两种无销售情况，再通过窗口函数识别连续的无销售区间，最后统计最长的区间长度。

具体实现SQL #

-- 步骤1：生成指定日期区间内的所有日期（递归CTE兼容大多数Teradata版本）
WITH date_dim AS (
    SELECT DATE '2023-12-31' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1' DAY
    FROM date_dim
    WHERE dt < DATE '2024-03-30'
),
-- 步骤2：获取所有有效的门店/商品组合（去重）
store_item AS (
    SELECT DISTINCT store, item
    FROM sales
    WHERE dt BETWEEN '2023-12-31' AND '2024-03-30'
    -- 若有单独的门店/商品维度表，用维度表更准确
),
-- 步骤3：生成所有门店/商品组合的全量日期记录
full_date_set AS (
    SELECT 
        si.store,
        si.item,
        dd.dt
    FROM store_item si
    CROSS JOIN date_dim dd
),
-- 步骤4：关联原销售表，标记无销售状态
sales_status AS (
    SELECT 
        fds.store,
        fds.item,
        fds.dt,
        -- 标记：无销售=1，有销售=0（覆盖两种无销售场景）
        CASE 
            WHEN s.volume IS NULL OR s.volume = 0 THEN 1
            ELSE 0
        END AS no_sales_flag
    FROM full_date_set fds
    LEFT JOIN sales s
        ON fds.store = s.store
        AND fds.item = s.item
        AND fds.dt = s.dt
),
-- 步骤5：识别连续的无销售分组
consec_groups AS (
    SELECT 
        store,
        item,
        dt,
        no_sales_flag,
        -- 遇到有销售时重置分组ID，连续无销售日期会归为同一组
        SUM(1 - no_sales_flag) OVER (
            PARTITION BY store, item 
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM sales_status
),
-- 步骤6：计算每个无销售分组的连续天数
consec_days AS (
    SELECT 
        store,
        item,
        group_id,
        COUNT(*) AS consecutive_days
    FROM consec_groups
    WHERE no_sales_flag = 1 -- 仅统计无销售的分组
    GROUP BY store, item, group_id
)
-- 最终取每个门店/商品组合的最长连续无销售天数
SELECT 
    store,
    item,
    MAX(consecutive_days) AS max_consec_no_sales_days
FROM consec_days
GROUP BY store, item;

关键细节说明 #

• 日期维度生成：用递归CTE生成区间内的所有日期，确保不遗漏任何一天；如果你的Teradata版本支持GENERATE_SERIES，可以用SELECT GENERATE_SERIES(DATE '2023-12-31', DATE '2024-03-30', INTERVAL '1' DAY) AS dt替代递归CTE，写法更简洁。
• 全量日期补全：通过CROSS JOIN关联门店/商品组合与所有日期，解决原表缺失无销售日期的问题。
• 无销售标记：统一处理「原表无记录」（LEFT JOIN后Volume为NULL）和「Volume=0」两种无销售场景，避免遗漏。
• 连续分组识别：通过累加有销售标记（1 - no_sales_flag）生成分组ID，每次遇到有销售的日期，分组ID就会递增，这样连续的无销售日期会被归为同一个分组。

你的原SQL问题分析 #

你的原SQL只过滤了Volume > 0的记录，直接忽略了Volume=0的无销售情况；而且用LAG(DT)计算的是有销售日期之间的间隔，完全没考虑：

• 日期区间开头到第一个有销售日期之间的无销售天数
• 最后一个有销售日期到区间结尾之间的无销售天数
• 连续出现Volume=0的无销售日期段

这些都是导致结果不准确的核心原因，上面的SQL完美覆盖了所有场景。

备注：内容来源于stack exchange，提问作者feonyte