Teradata SQL需求：按C2和年月统计有效日期范围内的账户数量

现有数据集包含ACC_ID、C1、C2、EFF_START_DATE、EFF_END_DATE共5列，每个账户对应多行数据。需使用Teradata SQL按C2和年月分组，统计各月份内符合有效日期范围的账户数量，规则如下：

• 仅统计2018年及之后的月份，若EFF_START_DATE早于2018年，从2018年开始计入统计；
• 账户的有效日期范围覆盖的每个月份都需计入统计，例如：
  • EFF_START_DATE为2018-01-01、EFF_END_DATE为2499-12-31的账户，需计入2018年及之后所有月份；
  • EFF_START_DATE为2018-02-15、EFF_END_DATE为2018-03-15的账户，仅计入2018年2月和3月。

此前尝试通过提取年月并基于EFF_START_DATE统计，结果不符合预期。

样本数据 #

ACC_ID, C1 , C2, EFF_START_DATE, EFF_END_DATE
111 , 0 , A , 2018-01-01, 2499-12-31
222 , 0 , A , 2018-02-15 , 2018-03-15
222 , 0 , B , 2018-03-16, 2499-12-31
333 , 0, A, 2000-01-01, 2499-12-31

预期输出 #

MONTH, C2, COUNT
JAN-18, A, 2  -- 对应账户111、333
FEB-18, A , 3  -- 对应账户111、222、333
MAR-18, A, 3  -- 对应账户111、222、333
MAR-18, B, 1  -- 对应账户222

（注：原预期输出中MAR-18的A类COUNT值存在笔误，实际应为3）

要实现需求，核心是生成每个账户有效日期范围内的所有月份，再按C2和年月分组统计，具体SQL如下：

WITH date_range AS (
    -- 生成2018年1月至今的所有月份起始日期
    SELECT 
        ADD_MONTHS(DATE '2018-01-01', (n - 1)) AS month_start
    FROM 
        (SELECT ROW_NUMBER() OVER () AS n FROM sys_calendar.calendar LIMIT 1000) t
    WHERE 
        ADD_MONTHS(DATE '2018-01-01', (n - 1)) <= CURRENT_DATE
),
account_valid_months AS (
    SELECT 
        d.month_start,
        t.C2,
        t.ACC_ID
    FROM 
        your_table t
    JOIN 
        date_range d ON 
            -- 月份最后一天 >= 调整后的账户有效起始日期（早于2018年的统一设为2018-01-01）
            LAST_DAY(d.month_start) >= CASE WHEN t.EFF_START_DATE < DATE '2018-01-01' THEN DATE '2018-01-01' ELSE t.EFF_START_DATE END
            -- 月份起始日期 <= 账户有效结束日期
            AND d.month_start <= t.EFF_END_DATE
)
SELECT 
    TO_CHAR(month_start, 'MON-YY') AS MONTH,
    C2,
    COUNT(DISTINCT ACC_ID) AS COUNT
FROM 
    account_valid_months
GROUP BY 
    month_start, C2
ORDER BY 
    month_start, C2;

代码说明 #

1. date_range CTE：借助Teradata内置的sys_calendar.calendar生成2018年1月到当前日期的所有月份起始日期，LIMIT 1000可覆盖未来几十年的月份需求。
2. account_valid_months CTE：将账户数据与生成的月份表关联，筛选出账户有效范围覆盖的月份：
   • 对早于2018年的起始日期做调整，统一从2018年1月开始统计；
   • 通过月份的首尾日期与账户有效范围的比对，确保该月份被账户有效周期覆盖。
3. 最终统计：按月份和C2分组，用COUNT(DISTINCT ACC_ID)统计唯一账户数，避免同一账户在同一月份被重复计数。

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