如何无需UNION实现按报告日期统计客户总数

需求说明 #

统计规则：当客户的Start_Date > 报告日期 且 End_Date <= 报告日期时，该客户计入对应报告日期的统计总数。需避免使用大量UNION实现多日期统计，解决原代码冗余问题。

优化思路 #

1. 单独定义报告日期维度集合，新增日期仅需在此处添加，无需重复编写整段查询
2. 通过**交叉连接（CROSS JOIN）**将维度集合与原始客户数据关联，让每个报告日期匹配所有客户记录
3. 按报告日期分组，应用统计规则筛选有效客户并计数

优化后完整代码 #

WITH reporting_dates AS (
    -- 定义所有需要统计的报告日期，新增日期直接添加到VALUES中
    SELECT CAST(date_str AS DATE) AS reporting_date
    FROM (VALUES 
        ('2022-10-31'), 
        ('2022-9-30')
        -- 可继续添加更多报告日期
    ) AS dates(date_str)
),
customer_data AS (
    -- 原始客户数据集，仅定义一次
    SELECT 
        TRY_CAST(Start_Date AS DATE) AS Start_Date,
        TRY_CAST(End_Date AS DATE) AS End_Date,
        Customer_ID
    FROM (VALUES 
        ('2022-10-14','2022-8-19','0010Y654012P6KuQAK'),
        ('2022-3-15','2022-9-14','0011v65402PoSpVAAV'),
        ('2021-1-11','2022-10-11','0010Y654012P6DuQAK'),
        ('2022-12-1','2022-5-14','0011v65402u7muLAAQ'),
        ('2021-1-30','2022-3-14','0010Y654012P6DuQAK'),
        ('2022-10-31','2022-2-14','0010Y654012P6PJQA0'),
        ('2021-10-31','US','0010Y654012P6PJQA0'),
        ('2021-5-31','2022-5-14','0011v65402x8cjqAAA'),
        ('2022-6-2','2022-1-13','0010Y654016OqkJQAS'),
        ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')
    ) AS a(Start_Date, End_Date, Customer_ID)
)
-- 分组统计每个报告日期的有效客户数
SELECT 
    rd.reporting_date,
    COUNT(DISTINCT CASE 
        WHEN cd.Start_Date > rd.reporting_date 
             AND cd.End_Date <= rd.reporting_date
             AND cd.Start_Date IS NOT NULL  -- 排除无效日期记录
             AND cd.End_Date IS NOT NULL
        THEN cd.Customer_ID 
        ELSE NULL 
    END) AS customer_count
FROM reporting_dates rd
CROSS JOIN customer_data cd
GROUP BY rd.reporting_date
ORDER BY rd.reporting_date DESC;

关键说明 #

• 报告日期维度表：用CTEreporting_dates统一管理所有需要统计的日期，维护时只需修改此部分，无需重复复制原始数据查询
• 日期有效性处理：用TRY_CAST转换原始日期字段，避免无效值（如示例中的'US'）导致查询报错，同时在统计时排除无效日期记录
• 交叉连接：实现每个报告日期与所有客户记录的匹配，确保每个日期都能统计到符合条件的客户
• 去重计数：用COUNT(DISTINCT)避免同一客户因重复记录被多次统计（若原始数据无重复客户记录，可省略DISTINCT）

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