如何基于不同过滤条件从同列生成多列客户购买统计结果？

原语句问题分析 #

1. CTE语法错误：多个CTE定义只需一个WITH开头，后续CTE用逗号分隔，无需重复使用WITH。
2. 连接方式错误：INNER JOIN会过滤掉无对应时段购买记录的活跃客户，无法展示这类客户数据。
3. 字段名不一致：数据表日期字段为LastPurchaseDate，但原语句用了PurchaseDate，需统一。
4. NULL值未处理：无对应购买记录时返回NULL，未转换为期望的0或NA。

方案一：修正CTE+LEFT JOIN方式 #

WITH Purchases40Days AS (
    SELECT ClientNumber,
           COUNT(*) AS PurchasesCount40Days
    FROM Purchases 
    WHERE LastPurchaseDate > DATEADD('day', -40, CURRENT_DATE)
    GROUP BY ClientNumber
),
Purchases20Days AS (
    SELECT ClientNumber,
           COUNT(*) AS PurchasesCount20Days
    FROM Purchases 
    WHERE LastPurchaseDate > DATEADD('day', -20, CURRENT_DATE)
    GROUP BY ClientNumber
),
PurchasesPrior40Days AS (
    SELECT ClientNumber,
           COUNT(*) AS PurchasesCountPrior40Days
    FROM Purchases 
    WHERE LastPurchaseDate <= DATEADD('day', -40, CURRENT_DATE)
    GROUP BY ClientNumber
)

SELECT 
    c.ClientNumber,
    COALESCE(p4.PurchasesCount40Days, 0) AS "Purchases on last 40 Days",
    COALESCE(p2.PurchasesCount20Days, 0) AS "Purchases on last 20 days",
    COALESCE(pp4.PurchasesCountPrior40Days, 0) AS "Purchases prior 40 days"
FROM Client c
LEFT JOIN Purchases40Days p4 ON p4.ClientNumber = c.ClientNumber
LEFT JOIN Purchases20Days p2 ON p2.ClientNumber = c.ClientNumber
LEFT JOIN PurchasesPrior40Days pp4 ON pp4.ClientNumber = c.ClientNumber
WHERE c.Status = 'Active'
ORDER BY c.ClientNumber;

修改说明 #

• 修正CTE语法，用逗号分隔多个CTE定义。
• 将INNER JOIN替换为LEFT JOIN，保留所有活跃客户，即使某时段无购买记录。
• 用COALESCE把NULL值转换为0（若需显示NA，可替换为'NA'，注意字段类型匹配）。
• 统一日期字段名为LastPurchaseDate，调整PurchasesPrior40Days的条件为<=，避免遗漏刚好40天前的记录。

方案二：高效的条件聚合方式（推荐） #

无需多CTE，一次扫描数据表即可完成统计，性能更优：

SELECT 
    c.ClientNumber,
    SUM(CASE WHEN p.LastPurchaseDate > DATEADD('day', -40, CURRENT_DATE) THEN 1 ELSE 0 END) AS "Purchases on last 40 Days",
    SUM(CASE WHEN p.LastPurchaseDate > DATEADD('day', -20, CURRENT_DATE) THEN 1 ELSE 0 END) AS "Purchases on last 20 days",
    SUM(CASE WHEN p.LastPurchaseDate <= DATEADD('day', -40, CURRENT_DATE) THEN 1 ELSE 0 END) AS "Purchases prior 40 days"
FROM Client c
LEFT JOIN Purchases p ON c.ClientNumber = p.ClientNumber
WHERE c.Status = 'Active'
GROUP BY c.ClientNumber
ORDER BY c.ClientNumber;

说明 #

• 用CASE结合SUM实现条件计数，每个时段的购买次数通过条件判断累加。
• LEFT JOIN确保所有活跃客户都被包含，无购买记录时对应列显示0。
• 仅扫描一次Purchases表，比多CTE+多次扫描的方式更高效。

若需将0替换为NA，可调整语句为：

SELECT 
    c.ClientNumber,
    COALESCE(CAST(SUM(CASE WHEN p.LastPurchaseDate > DATEADD('day', -40, CURRENT_DATE) THEN 1 ELSE NULL END) AS VARCHAR), 'NA') AS "Purchases on last 40 Days",
    COALESCE(CAST(SUM(CASE WHEN p.LastPurchaseDate > DATEADD('day', -20, CURRENT_DATE) THEN 1 ELSE NULL END) AS VARCHAR), 'NA') AS "Purchases on last 20 days",
    COALESCE(CAST(SUM(CASE WHEN p.LastPurchaseDate <= DATEADD('day', -40, CURRENT_DATE) THEN 1 ELSE NULL END) AS VARCHAR), 'NA') AS "Purchases prior 40 days"
FROM Client c
LEFT JOIN Purchases p ON c.ClientNumber = p.ClientNumber
WHERE c.Status = 'Active'
GROUP BY c.ClientNumber
ORDER BY c.ClientNumber;

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