Teradata中用窗口函数统计行数：排除重复STATUS='07'的情况

现有数据（Have） #

Id    MONTH            ORDERNO       STATUS
  101   2022-01-31        105           00
  101   2022-02-28        105           00
  101   2022-03-31        106           00
  101   2022-04-30        106           07
  101   2022-05-31        106           07
  102   2022-01-01        105           00
  102   2022-02-28        105           00
  102   2022-03-31        105           07
  102   2022-04-28        105           07

期望结果（Want） #

Id     TENURE
  101      4
  102      3

需求说明 #

统计每个Id的有效月份数，规则为：排除STATUS='07'的第二次及以后出现的记录。

当前代码及问题 #

当前使用的窗口函数未添加过滤逻辑，导致统计了所有记录：

SELECT id,
      COUNT(*) OVER (PARTITION BY id ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND 
  UNBOUNDED FOLLOWING) AS TENURE FROM Have;

得到的结果不符合预期：

Id     TENURE
  101      5
  102      4

解决方案 #

可以通过子查询标记STATUS='07'的出现顺序，再筛选出有效记录后统计行数。以下是修改后的SQL：

SELECT id, COUNT(*) AS TENURE
FROM (
    SELECT *,
           -- 按Id、ORDERNO、STATUS分组，给同组记录按月份排序生成行号
           ROW_NUMBER() OVER (PARTITION BY id, ORDERNO, STATUS ORDER BY MONTH) AS status_rn
    FROM Have
) sub
-- 保留非07状态的所有记录，以及07状态的第一次出现记录
WHERE (STATUS = '07' AND status_rn = 1) OR STATUS != '07'
GROUP BY id;

逻辑说明 #

1. 子查询中用ROW_NUMBER()窗口函数，针对每个Id、ORDERNO和STATUS的组合，按MONTH升序生成行号，标记同组记录的出现顺序。
2. 外层查询筛选出：所有STATUS!='07'的记录，以及STATUS='07'但行号为1的记录（即该状态的第一次出现）。
3. 最后按Id分组统计行数，得到符合要求的TENURE。

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