Teradata SQL：如何合并激活/停用记录生成完整服务活跃时段？

一、关联激活与停用记录 #

要解决无停用记录时的空值丢失问题，核心是用**左连接（LEFT JOIN）**或窗口函数保留激活记录，同时匹配对应的停用记录。以下是两种可行方法：

方法1：LEFT JOIN + 最小停用日期匹配 #

针对每个用户（LastName+FirstName+AccountNumber）的同一服务码（ServiceCode），匹配激活日期之后最早的停用记录，无停用时自动返回空值：

SELECT
  a.LastName AS "Last Name",
  a.FirstName AS "First Name",
  a.AccountNumber AS "Account Number",
  a.ServiceCode,
  a.ActivityDate AS ActivateDate,
  MIN(d.ActivityDate) AS DeactivateDate
FROM
  your_table a
LEFT JOIN
  your_table d
ON
  a.LastName = d.LastName
  AND a.FirstName = d.FirstName
  AND a.AccountNumber = d.AccountNumber
  AND a.ServiceCode = d.ServiceCode
  AND d.ActivityCode = 'D'
  AND d.ActivityDate > a.ActivityDate
WHERE
  a.ActivityCode = 'A'
GROUP BY
  a.LastName, a.FirstName, a.AccountNumber, a.ServiceCode, a.ActivityDate
ORDER BY
  a.LastName, a.FirstName, a.AccountNumber, a.ServiceCode;

方法2：窗口函数（高效适配大数据量） #

用LEAD()窗口函数按用户、服务码分组排序，直接取当前激活记录之后的第一条停用记录，无停用时返回NULL：

WITH activity_cte AS (
  SELECT
    LastName,
    FirstName,
    AccountNumber,
    ServiceCode,
    ActivityCode,
    ActivityDate,
    LEAD(CASE WHEN ActivityCode = 'D' THEN ActivityDate END) OVER (
      PARTITION BY LastName, FirstName, AccountNumber, ServiceCode
      ORDER BY ActivityDate
    ) AS DeactivateDate
  FROM
    your_table
)
SELECT
  LastName AS "Last Name",
  FirstName AS "First Name",
  AccountNumber AS "Account Number",
  ServiceCode,
  ActivityDate AS ActivateDate,
  DeactivateDate
FROM
  activity_cte
WHERE
  ActivityCode = 'A';

二、统计每月各ServiceCode的活跃数量 #

基于上述关联结果，通过日期维度表匹配判断每个月的活跃状态，无停用记录视为持续活跃至当前日期：

WITH month_dim AS (
  -- 生成月度起止日期，这里取2020-2024年的月份，可按需调整范围
  SELECT
    ADD_MONTHS(DATE '2020-01-01', x.n) AS month_start,
    LAST_DAY(ADD_MONTHS(DATE '2020-01-01', x.n)) AS month_end
  FROM
    (SELECT ROW_NUMBER() OVER () - 1 AS n FROM sys_calendar.calendar LIMIT 60) x
),
active_services AS (
  -- 复用窗口函数的关联结果
  WITH activity_cte AS (
    SELECT
      LastName,
      FirstName,
      AccountNumber,
      ServiceCode,
      ActivityCode,
      ActivityDate,
      LEAD(CASE WHEN ActivityCode = 'D' THEN ActivityDate END) OVER (
        PARTITION BY LastName, FirstName, AccountNumber, ServiceCode
        ORDER BY ActivityDate
      ) AS DeactivateDate
    FROM
      your_table
  )
  SELECT
    LastName,
    FirstName,
    AccountNumber,
    ServiceCode,
    ActivityDate AS ActivateDate,
    COALESCE(DeactivateDate, CURRENT_DATE) AS DeactivateDate
  FROM
    activity_cte
  WHERE
    ActivityCode = 'A'
)
SELECT
  m.month_start AS stat_month,
  a.ServiceCode,
  COUNT(DISTINCT CONCAT(a.LastName, a.FirstName, a.AccountNumber)) AS active_user_count
FROM
  month_dim m
LEFT JOIN
  active_services a
ON
  a.ActivateDate <= m.month_end
  AND a.DeactivateDate >= m.month_start
GROUP BY
  m.month_start, a.ServiceCode
ORDER BY
  m.month_start, a.ServiceCode;

关键注意事项 #

• 确认LastName+FirstName+AccountNumber的组合唯一性，避免匹配错误
• 窗口函数方法比自连接更高效，适合百万级以上数据量
• 无停用记录时用COALESCE替换为当前日期，确保活跃状态判断准确

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