SQL查询需求：统计连续三月Imputed后接Projected的Retailer ID数量

AI 小北

2026-7-11

SQL查询：统计符合条件的Retailer ID数量

假设你的数据表名为retailer_fsl_status，包含字段：RetailerID（零售商ID）、record_month（记录月份，需为可排序的格式，比如'2024-01'）、FSL_Status（状态字段）。以下是满足需求的查询语句：

WITH status_sequence AS (
    SELECT 
        RetailerID,
        record_month,
        FSL_Status,
        -- 标记连续相同状态的分组
        ROW_NUMBER() OVER (PARTITION BY RetailerID ORDER BY record_month) 
        - ROW_NUMBER() OVER (PARTITION BY RetailerID, FSL_Status ORDER BY record_month) AS status_group,
        -- 获取下一条记录的状态
        LEAD(FSL_Status) OVER (PARTITION BY RetailerID ORDER BY record_month) AS next_status,
        -- 统计当前分组的状态出现次数
        COUNT(*) OVER (PARTITION BY RetailerID, status_group) AS status_count
    FROM retailer_fsl_status
),
valid_imputed_groups AS (
    SELECT DISTINCT RetailerID
    FROM status_sequence
    WHERE 
        FSL_Status = 'Imputed' 
        AND status_count = 3
        -- 检查连续Imputed组的下一个状态是Projected
        AND next_status = 'Projected'
)
SELECT COUNT(DISTINCT RetailerID) AS qualified_retailer_count
FROM valid_imputed_groups;

逻辑说明：

status_sequence CTE：通过两个ROW_NUMBER()的差值标记连续相同状态的分组，同时用LEAD()获取下月状态，统计每个分组内的状态出现次数。
valid_imputed_groups CTE：筛选出连续3个月为"Imputed"、且后续状态为"Projected"的零售商ID。
最后统计符合条件的唯一零售商ID总数。