如何查询AcctTypCD变更为HPLS或VPLS的记录及对应操作员工

前置说明 #

你现有存储最新状态的tableA快照表无法直接满足需求，缺少AcctTypCD的历史变更轨迹，你需要先确认有以下任意一种数据支撑：

• 已构建tableA的缓慢变化维（SCD2）历史表，存储账户每次变更的前后值、变更时间、操作人
• 数据库已开启CDC变更捕获或操作审计日志，可查询到tableA的所有更新操作记录

实现方案（以最常用的SCD2历史表为例） #

假设历史表命名为tableA_his，核心扩展字段包括：prev_accttypcd（变更前的AcctTypCD取值）、change_time（变更操作执行时间）、operate_emp（变更操作执行员工），查询SQL如下：

SELECT 
  t.acctnbr,
  t.prev_accttypcd, -- 变更前类型
  t.accttypcd AS current_accttypcd, -- 变更后类型
  t.change_time,
  t.operate_emp, -- 执行变更的员工
  t.contractdate,
  t.wrkstlct,
  t.wrkstrgn
FROM tableA_his t
WHERE 
  -- 变更后为指定类型
  t.accttypcd IN ('HPLS', 'VPLS')
  -- 变更前不是指定类型，排除初始化就是目标类型的记录
  AND t.prev_accttypcd NOT IN ('HPLS', 'VPLS')
  -- 按需调整时间范围，此处保留你原需求的当月范围
  AND t.change_time BETWEEN TRUNC(SYSDATE,'mm') AND SYSDATE
ORDER BY t.wrkstrgn, t.wrkstlct, t.operate_emp, t.change_time;

如果你的历史表仅存储每日全量快照，没有提前计算变更前值，可以用窗口函数取前一天的状态做对比，SQL如下：

WITH acct_daily_snap AS (
  SELECT 
    *,
    -- 取同一个账户前一天的AcctTypCD值
    LAG(accttypcd,1) OVER (PARTITION BY acctnbr ORDER BY snap_dt) AS prev_accttypcd,
    LAG(emp,1) OVER (PARTITION BY acctnbr ORDER BY snap_dt) AS prev_emp
  FROM tableA_daily_snap
  WHERE snap_dt BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) AND SYSDATE
)
SELECT 
  acctnbr,
  prev_accttypcd,
  accttypcd AS current_accttypcd,
  snap_dt AS change_dt,
  emp AS operate_emp,
  contractdate,
  wrkstlct,
  wrkstrgn
FROM acct_daily_snap
WHERE 
  accttypcd IN ('HPLS', 'VPLS')
  AND prev_accttypcd NOT IN ('HPLS', 'VPLS')
  AND snap_dt BETWEEN TRUNC(SYSDATE,'mm') AND SYSDATE
ORDER BY wrkstrgn, wrkstlct, operate_emp, change_dt;

方案优势 #

• 准确性高：可以完全过滤掉账户初始创建就是HPLS/VPLS的无效记录，只返回真实发生类型变更的条目
• 性能优异：基于带变更前值的历史表查询时，不需要额外关联计算，索引建好的情况下毫秒级返回结果
• 完全匹配业务需求：直接返回变更前后类型、操作员工、变更时间等所有要求的字段

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