Oracle SQL查询审计表变更记录的实现方案求助

表结构及现有数据 #

现有ANC_PER_ABS_ENTRIES历史表用于追踪INSERT、UPDATE、DELETE操作，表结构及数据如下：

需求规则 #

• 对于PER_AB_ENTRY_ID=15、person_number=101的记录：首次运行取INSERT行，后续运行仅取UPDATE行
• 对于PER_AB_ENTRY_ID=17、person_number=103的记录：DELETE行需在后续运行中被提取，之后的新INSERT行也需返回
• 使用LAG函数检查上一次运行的LAST_UPD_DT，若START_DATE、END_DATE有变更，或存在INSERT/UPDATE/DELETE操作，返回最新行；DELETE操作需返回对应行，同一PER_AB_ENTRY_ID的新行也需返回

期望最新运行输出 #

已尝试的查询（无输出） #

with anc as 
(
select person_number,
absence_type,
ABSENCE_STATUS,
approval_status_cd,
start_date,
end_date,
duration,
PER_AB_ENTRY_ID,
AUDIT_ACTION_TYPE_,
         row_number() over (order by PER_AB_ENTRY_ID, LAST_UPD_DT) rn

from ANC_PER_ABS_ENTRIES
)

select N1.person_number,
N1.absence_type,
N1.ABSENCE_STATUS,
N1.approval_status_cd,
N1.start_date,
N1.end_date,
N1.duration,
N1.PER_AB_ENTRY_ID,
N1.action_type 
from anc N1,
ANC N2
WHERE 
N1.PER_AB_ENTRY_ID = N2.PER_AB_ENTRY_ID
   and n1.rn + 1 = n2.rn

AND ( n1.start_date <> n2.start_date
    or n1.end_date <> n2.end_date
    or n1.DURATION <> n2.DURATION)

注：数据库不支持MATCH_RECOGNISE语法

解决方案 #

你的尝试查询存在几个问题：CTE中引用的absence_type、ABSENCE_STATUS、approval_status_cd、AUDIT_ACTION_TYPE_字段在原表中不存在，且关联逻辑只对比了相邻行的字段变化，未覆盖DELETE操作及后续INSERT的场景。

以下是符合需求的Oracle SQL查询，使用LAG函数实现规则：

WITH ranked_entries AS (
    SELECT 
        PER_AB_ENTRY_ID,
        person_number,
        action_type,
        duration,
        START_DATE,
        END_DATE,
        LAST_UPD_DT,
        -- 按PER_AB_ENTRY_ID分组，按更新时间排序
        ROW_NUMBER() OVER (PARTITION BY PER_AB_ENTRY_ID ORDER BY LAST_UPD_DT) AS rn,
        -- 获取上一条记录的操作类型、日期、时长
        LAG(action_type) OVER (PARTITION BY PER_AB_ENTRY_ID ORDER BY LAST_UPD_DT) AS prev_action,
        LAG(START_DATE) OVER (PARTITION BY PER_AB_ENTRY_ID ORDER BY LAST_UPD_DT) AS prev_start_date,
        LAG(END_DATE) OVER (PARTITION BY PER_AB_ENTRY_ID ORDER BY LAST_UPD_DT) AS prev_end_date,
        LAG(duration) OVER (PARTITION BY PER_AB_ENTRY_ID ORDER BY LAST_UPD_DT) AS prev_duration
    FROM ANC_PER_ABS_ENTRIES
)
SELECT 
    PER_AB_ENTRY_ID,
    person_number,
    action_type,
    duration,
    START_DATE,
    END_DATE,
    LAST_UPD_DT
FROM ranked_entries
WHERE 
    -- 首次运行取第一条记录（INSERT）
    rn = 1
    OR
    -- 后续记录满足以下任一条件则返回：
    (
        -- 操作类型变化（包括INSERT/UPDATE/DELETE的切换）
        action_type != prev_action
        -- 日期或时长发生变更
        OR START_DATE != prev_start_date
        OR END_DATE != prev_end_date
        OR duration != prev_duration
    )
-- 按PER_AB_ENTRY_ID和更新时间排序，匹配期望输出顺序
ORDER BY PER_AB_ENTRY_ID, LAST_UPD_DT;

逻辑说明 #

1. 分组排序：按PER_AB_ENTRY_ID分组，每条记录按LAST_UPD_DT生成序号，并通过LAG函数获取上一条记录的关键字段
2. 筛选条件：
   • 保留每组的第一条记录（对应首次运行的INSERT行）
   • 保留操作类型变化的记录（比如UPDATE、DELETE及后续的INSERT）
   • 保留日期或时长发生变更的记录
3. 排序输出：最终结果按PER_AB_ENTRY_ID和LAST_UPD_DT排序，与期望输出一致

这个查询可以覆盖所有需求规则，包括DELETE操作及后续INSERT行的提取，同时兼容首次和后续运行的场景。

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