使用T-SQL CTE统计跨期匹配、流失及新增销售额的实现问题

现有代码存在的问题 #

• 语法错误：CTE定义不完整，缺少GROUP BY子句聚合每个Item+Variant维度的销售额，列名声明不符合T-SQL规范，连接时两个表别名重复声明为ps会触发执行错误
• 逻辑局限：内连接INNER JOIN本身仅会返回两边匹配的行，天然无法获取仅出现在单张表的未匹配数据，不需要额外加UNION ALL，改用FULL OUTER JOIN即可实现需求

修正后可直接运行的实现代码 #

WITH PriorSalesCTE AS
(
    SELECT 
        Item,
        Variant,
        SUM(sales) AS TotalSales
    FROM 你的销售表名 -- 替换为实际的销售数据表名
    WHERE [Date] BETWEEN '2020-07-01' AND '2020-07-05'
    GROUP BY Item, Variant
),
CurrentSalesCTE AS
(
    SELECT 
        Item,
        Variant,
        SUM(sales) AS TotalSales
    FROM 你的销售表名 -- 替换为实际的销售数据表名
    WHERE [Date] BETWEEN '2021-07-01' AND '2021-07-05'
    GROUP BY Item, Variant
)
SELECT
    SUM(CASE WHEN ps.TotalSales IS NOT NULL AND cs.TotalSales IS NOT NULL THEN cs.TotalSales ELSE 0 END) AS MatchedSales,
    SUM(CASE WHEN ps.TotalSales IS NOT NULL AND cs.TotalSales IS NULL THEN ps.TotalSales ELSE 0 END) AS LostSales,
    SUM(CASE WHEN cs.TotalSales IS NOT NULL AND ps.TotalSales IS NULL THEN cs.TotalSales ELSE 0 END) AS NewSales
FROM PriorSalesCTE ps
FULL OUTER JOIN CurrentSalesCTE cs
    ON ps.Item = cs.Item
    AND ps.Variant = cs.Variant

逻辑说明 #

• 先修正两个CTE的语法，按Item+Variant分组聚合对应周期的销售额
• 用FULL OUTER JOIN保留两张CTE的所有行，匹配不上的另一侧字段返回NULL
• 通过CASE判断行的归属维度，分别聚合三个指标：
  • 两边都非NULL为匹配销售
  • 仅2020年非NULL为流失销售
  • 仅2021年非NULL为新增销售

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