使用Snowflake SQL按日期统计不同市场细分组合的活跃客户数

核心思路 #

要实现按日期统计三类活跃客户，需要先覆盖所有统计日期，再逐个判断每个客户当日的活跃合同类型，最后按日期聚合分类计数。以下是可直接运行的SQL代码：

WITH date_series AS (
    -- 生成需要统计的所有日期范围（从最早合同开始日到最晚合同结束日）
    SELECT DATEADD(day, seq4(), (SELECT MIN(contract_start_date) FROM contracts)) AS date_day
    FROM TABLE(GENERATOR(ROWCOUNT => (SELECT DATEDIFF(day, MIN(contract_start_date), MAX(contract_end_date)) + 1 FROM contracts)))
),
customer_daily_active_segments AS (
    -- 标记每个客户每日是否持有活跃的Gas/Electricity合同
    SELECT
        ds.date_day,
        c.customer_id,
        MAX(CASE WHEN c.market_segment = 'Gas' AND c.contract_start_date <= ds.date_day AND c.contract_end_date >= ds.date_day THEN 1 ELSE 0 END) AS has_active_gas,
        MAX(CASE WHEN c.market_segment = 'Electricity' AND c.contract_start_date <= ds.date_day AND c.contract_end_date >= ds.date_day THEN 1 ELSE 0 END) AS has_active_elec
    FROM date_series ds
    CROSS JOIN contracts c
    WHERE ds.date_day BETWEEN c.contract_start_date AND c.contract_end_date
    GROUP BY ds.date_day, c.customer_id
),
customer_category_mapping AS (
    -- 对客户进行分类
    SELECT
        date_day,
        customer_id,
        CASE
            WHEN has_active_gas = 1 AND has_active_elec = 0 THEN 'only_gas'
            WHEN has_active_gas = 0 AND has_active_elec = 1 THEN 'only_elec'
            WHEN has_active_gas = 1 AND has_active_elec = 1 THEN 'combo_elec_gas'
        END AS customer_category
    FROM customer_daily_active_segments
)
-- 按日期聚合统计各类客户数量
SELECT
    date_day,
    COUNT(CASE WHEN customer_category = 'only_gas' THEN customer_id END) AS only_gas,
    COUNT(CASE WHEN customer_category = 'only_elec' THEN customer_id END) AS only_elec,
    COUNT(CASE WHEN customer_category = 'combo_elec_gas' THEN customer_id END) AS combo_elec_gas
FROM customer_category_mapping
GROUP BY date_day
ORDER BY date_day;

代码说明 #

1. date_series：通过GENERATOR函数生成连续日期序列，确保统计覆盖所有有合同活跃的日期，避免遗漏无合同变动的日期。
2. customer_daily_active_segments：关联日期序列与合同表，用MAX聚合判断客户当日是否存在活跃的Gas或Electricity合同（同一个客户可能有多份同类型合同，只要有一份活跃即标记为1）。
3. customer_category_mapping：根据活跃合同标记，将客户划分为三类。
4. 最终聚合：按日期分组，用条件计数统计每类客户的数量。

自定义调整 #

• 如果需要指定统计日期范围（比如最近一年），可修改date_series的起始和结束日期，例如：

  SELECT DATEADD(day, seq4(), DATEADD(year, -1, CURRENT_DATE())) AS date_day
  FROM TABLE(GENERATOR(ROWCOUNT => 366))
  

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