如何从两个不同CTE查询结果中取值计算无营收新增客户数量

实现方案 #

你可以将两个统计逻辑分别封装为独立CTE，按年、月字段关联后直接对应列做差值计算即可，查询中加入ISNULL处理避免当月无消费客户时计算结果为空的问题，完整语句如下：

USE dwh01;
WITH 
-- 统计全量新增客户数
cte_total_new AS (
    SELECT 
         d.[Year],
         d.[month],
         COUNT(DISTINCT CASE WHEN c.branchid = '1080' THEN c.customerid END) AS total_1080,
         COUNT(DISTINCT CASE WHEN c.branchid = '1081' THEN c.customerid END) AS total_1081
    FROM [dwh01].[live].[DimCustomer] c
    INNER JOIN live.DimDate d 
        ON d.DayDate = c.Createdate
    WHERE d.DayDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0) 
      AND d.DayDate <= DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) 
      AND c.IsActive = 'Y'
    GROUP BY d.[Year], d.[month]
),
-- 统计有消费记录的新增客户数
cte_has_revenue AS (
    SELECT 
         d.[Year],
         d.[month],
         COUNT(DISTINCT CASE WHEN c.branchid = '1080' THEN c.customerid END) AS revenue_1080,
         COUNT(DISTINCT CASE WHEN c.branchid = '1081' THEN c.customerid END) AS revenue_1081
    FROM live.FactSales fs
    INNER JOIN [live].[DimCustomer] c 
        ON fs.customerkey = c.CustomerKey
    INNER JOIN live.DimDate d 
        ON d.DayDate = c.Createdate
    WHERE d.DayDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0) 
      AND d.DayDate <= DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) 
      AND c.IsActive = 'Y'
    GROUP BY d.[Year], d.[month]
)
-- 计算无营收新增客户数
SELECT 
    t.[Year],
    t.[month],
    t.total_1080 - ISNULL(r.revenue_1080, 0) AS no_revenue_1080,
    t.total_1081 - ISNULL(r.revenue_1081, 0) AS no_revenue_1081
FROM cte_total_new t
LEFT JOIN cte_has_revenue r
    ON t.[Year] = r.[Year] AND t.[month] = r.[month]
ORDER BY t.[Year] ASC, t.[month] ASC;

你之前查询得到的全量新增客户结果如下：

执行上述语句后即可得到你预期的无营收新增客户结果：

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