基于日期使用Teradata对客户记录分组统计的技术问询

需求说明 #

基于包含MINDate、MAXDATE、CLOSEDATE字段的客户数据，将客户账户分为3组，统计2022年每个月各分组的活跃客户数量：

• Grp1：客户在对应月份整月活跃（或全年活跃的所有月份）
• Grp2：客户仅在对应月份的部分日期活跃（非关闭月份）
• Grp3：客户在对应月份关闭（有CLOSEDATE且为该月份）

现有数据 #

custID         MINDate     MAXDATE         CLOSEDATE
  10001          1/1/2022   12/31/2022
  20001          7/6/2022   12/31/2022
  30001          4/5/2022  6/10/2022         6/10/2022
  40001          1/1/2022   12/31/2022 

客户分组逻辑说明 #

• 10001：全年所有月份归入Grp1
• 20001：7月归入Grp2，8-12月归入Grp1
• 30001：4月归入Grp2，5月归入Grp1，6月归入Grp3
• 40001：全年所有月份归入Grp1

期望结果 #

jan22 Feb22 Mar22 Apr22 May22 Jun22 Jul22 Aug22 Sep22 Oct22 Nov22 Dec22       
Grp1         2    2     2      2     3      2    2      3    3     3    3     3   
Grp2                           1                 1    
Grp3                                        1

尝试的SQL（未完成） #

WITH cte1 AS (
  SELECT custID,
    MINDate,
    MAXDATE,
    ADD_MONTHS(TRUNC(MINDate, 'MM'), ROW_NUMBER() OVER (PARTITION BY custID ORDER BY MINDate) - 1) AS Month
  FROM Have
  QUALIFY EXTRACT(MONTH FROM MINDate) = EXTRACT(MONTH FROM ADD_MONTHS(TRUNC(MINDate, 'MM'), ROW_NUMBER() OVER (PARTITION BY custID ORDER BY MINDate) - 1))
), cte2 AS (
  SELECT custID,
    Month,
    CASE WHEN Month = ADD_MONTHS(TRUNC(MAXDATE, 'MM'), 1) THEN LAST_DAY(MAXDATE) - MAXDATE + 1 ELSE 1 END AS Days
  FROM (
    SELECT custID,
      MIN(Month) AS Month,
      MAX(MAXDATE) AS MAXDATE
    FROM cte1
    GROUP BY custID
  ) t1
  INNER JOIN cte1 t2 ON t1.custID = t2.custID AND t1.Month <= t2.Month AND t2.Month < ADD_MONTHS(TRUNC(MAXDATE, 'MM'), 1)
), 
  SELECT Month, SUM(Days) AS Days
  FROM cte2
  GROUP BY Month

正确的Teradata SQL实现 #

思路 #

1. 生成2022年所有月份的日期序列作为基础维度表
2. 关联每个客户与他们活跃范围内的所有月份
3. 根据规则判断每个客户-月份所属分组
4. 按分组和月份统计客户数量，最后转置为宽表格式匹配期望结果

代码实现 #

WITH all_months AS (
  -- 生成2022年12个月份的起始日期
  SELECT ADD_MONTHS(DATE '2022-01-01', m - 1) AS month_start
  FROM (SELECT ROW_NUMBER() OVER () AS m FROM sys_calendar.calendar WHERE calendar_date BETWEEN DATE '2022-01-01' AND DATE '2022-12-31' QUALIFY m <=12) AS months
),
customer_months AS (
  -- 关联每个客户与所有其活跃的月份，标记关键属性
  SELECT 
    h.custID,
    am.month_start,
    -- 判断是否为关闭月份
    CASE WHEN h.CLOSEDATE IS NOT NULL AND TRUNC(h.CLOSEDATE, 'MM') = am.month_start THEN 1 ELSE 0 END AS is_close_month,
    -- 判断是否为起始月份且非整月活跃
    CASE WHEN TRUNC(h.MINDate, 'MM') = am.month_start AND h.MINDate <> am.month_start THEN 1 ELSE 0 END AS is_partial_start,
    -- 判断是否为非关闭的结束月份且非整月活跃
    CASE WHEN h.CLOSEDATE IS NULL AND TRUNC(h.MAXDATE, 'MM') = am.month_start AND h.MAXDATE <> LAST_DAY(am.month_start) THEN 1 ELSE 0 END AS is_partial_end
  FROM Have h
  JOIN all_months am 
    ON am.month_start BETWEEN TRUNC(h.MINDate, 'MM') AND TRUNC(COALESCE(h.CLOSEDATE, h.MAXDATE), 'MM')
),
group_assign AS (
  -- 为每个客户-月份分配分组
  SELECT 
    custID,
    month_start,
    CASE 
      WHEN is_close_month = 1 THEN 'Grp3'
      WHEN is_partial_start = 1 OR is_partial_end = 1 THEN 'Grp2'
      ELSE 'Grp1'
    END AS group_name
  FROM customer_months
),
monthly_counts AS (
  -- 按分组和月份统计客户数
  SELECT 
    group_name,
    TO_CHAR(month_start, 'monyy') AS month_label,
    COUNT(DISTINCT custID) AS cust_count
  FROM group_assign
  GROUP BY group_name, month_start, TO_CHAR(month_start, 'monyy')
)
-- 转置为宽表格式
SELECT 
  group_name,
  MAX(CASE WHEN month_label = 'jan22' THEN cust_count ELSE 0 END) AS jan22,
  MAX(CASE WHEN month_label = 'feb22' THEN cust_count ELSE 0 END) AS feb22,
  MAX(CASE WHEN month_label = 'mar22' THEN cust_count ELSE 0 END) AS mar22,
  MAX(CASE WHEN month_label = 'apr22' THEN cust_count ELSE 0 END) AS apr22,
  MAX(CASE WHEN month_label = 'may22' THEN cust_count ELSE 0 END) AS may22,
  MAX(CASE WHEN month_label = 'jun22' THEN cust_count ELSE 0 END) AS jun22,
  MAX(CASE WHEN month_label = 'jul22' THEN cust_count ELSE 0 END) AS jul22,
  MAX(CASE WHEN month_label = 'aug22' THEN cust_count ELSE 0 END) AS aug22,
  MAX(CASE WHEN month_label = 'sep22' THEN cust_count ELSE 0 END) AS sep22,
  MAX(CASE WHEN month_label = 'oct22' THEN cust_count ELSE 0 END) AS oct22,
  MAX(CASE WHEN month_label = 'nov22' THEN cust_count ELSE 0 END) AS nov22,
  MAX(CASE WHEN month_label = 'dec22' THEN cust_count ELSE 0 END) AS dec22
FROM monthly_counts
GROUP BY group_name
ORDER BY 
  CASE group_name WHEN 'Grp1' THEN 1 WHEN 'Grp2' THEN 2 WHEN 'Grp3' THEN 3 END;

代码说明 #

• all_months：利用Teradata系统日历表生成2022年所有月份的起始日期，作为统计的时间维度
• customer_months：关联客户与活跃月份，同时标记关闭月份、部分活跃的起始/结束月份
• group_assign：根据标记字段分配对应的分组
• monthly_counts：统计每个分组在每个月份的活跃客户数
• 最后通过CASE语句转置为宽表，匹配期望的列展示格式

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