自连接结果异常：统计月度快照账户表状态上月I变当月A的行数实现方法

• 日期关联逻辑颠倒：原自连接条件a1.mnth_end_dt = LAST_DAY(ADD_MONTHS(a2.mnth_end_dt,-1))的实际含义是a1的日期等于a2的日期往前推1个月的月末，也就是a2比a1晚1个月，完全违背了“a2是a1上一个月快照”的设计，才会出现1月的月份对应2月的Prior_Mth的异常。
• 异常关联导致行丢失：按错误的关联逻辑，只有当a2的日期比a1晚1个月时才会匹配成功。你设置a2的日期范围截止到2021-02，仅a1为2021-01时能匹配到a2=2021-02的记录，a1为2021-02、2021-03时对应的a2需要是2021-03、2021-04，均超出a2的范围被过滤，因此最终只返回1行结果。
• 分组维度冗余：同一统计月份Mth对应的上月日期是固定值，没必要把Prior_Mth作为分组键，反而会增加不必要的计算开销。

调整关联逻辑为正向匹配，直接通过日期函数计算上月值，不需要将Prior_Mth作为分组维度：

SELECT
  a1.mnth_end_dt AS Mth,
  LAST_DAY(ADD_MONTHS(a1.mnth_end_dt, -1)) AS Prior_Mth,
  COUNT(CASE WHEN a1.status_cd = 'A' AND a2.status_cd = 'I' THEN a1.acct_id END) AS Stat_Chg
FROM acct_tbl a1
INNER JOIN acct_tbl a2
  ON a1.acct_id = a2.acct_id
  -- 正确关联：a2的日期为a1的上一个月末
  AND a2.mnth_end_dt = LAST_DAY(ADD_MONTHS(a1.mnth_end_dt, -1))
WHERE a1.mnth_end_dt BETWEEN DATE '2021-01-01' AND DATE '2021-03-31'
GROUP BY a1.mnth_end_dt
ORDER BY a1.mnth_end_dt;

你的表是按月分区的快照表，窗口函数仅需单次扫描目标分区，相比自连接两次扫表的IO开销更低，尤其适合多年度统计场景：

WITH acct_status_lag AS (
  SELECT
    mnth_end_dt AS Mth,
    acct_id,
    status_cd,
    -- 按账户维度分区，取上一个月的状态值
    LAG(status_cd, 1) OVER (
      PARTITION BY acct_id 
      ORDER BY mnth_end_dt
    ) AS prior_status_cd
  FROM acct_tbl
  -- 多往前扫描一个月的分区，获取统计起始月的上月状态
  WHERE mnth_end_dt BETWEEN DATE '2020-12-01' AND DATE '2021-03-31'
)
SELECT
  Mth,
  LAST_DAY(ADD_MONTHS(Mth, -1)) AS Prior_Mth,
  COUNT(CASE WHEN status_cd = 'A' AND prior_status_cd = 'I' THEN acct_id END) AS Stat_Chg
FROM acct_status_lag
-- 过滤掉没有上月状态的首行记录
WHERE prior_status_cd IS NOT NULL
  AND Mth BETWEEN DATE '2021-01-01' AND DATE '2021-03-31'
GROUP BY Mth
ORDER BY Mth;

该方案可通过分区裁剪快速定位需要扫描的月度分区，不需要手动维护复杂的日期关联逻辑，后续扩展其他状态变更的统计规则时，仅需调整CASE判断条件即可。

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