Oracle audit表查询：获取删除记录对应的最近创建时间

示例数据 #

先创建模拟的profile_audit表并插入测试数据：

CREATE TABLE profile_audit (
    audit_id NUMBER PRIMARY KEY,
    user_id VARCHAR2(50),
    profile_name VARCHAR2(100),
    role VARCHAR2(50),
    action_type VARCHAR2(10), -- 'CREATE' 或 'DELETE'
    action_date DATE
);

INSERT INTO profile_audit VALUES (1, 'U001', 'PROF_ADMIN', 'ADMIN', 'CREATE', TO_DATE('2024-01-10', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (2, 'U001', 'PROF_ADMIN', 'ADMIN', 'CREATE', TO_DATE('2024-02-15', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (3, 'U001', 'PROF_ADMIN', 'ADMIN', 'DELETE', TO_DATE('2024-03-20', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (4, 'U002', 'PROF_USER', 'USER', 'CREATE', TO_DATE('2024-01-05', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (5, 'U002', 'PROF_USER', 'USER', 'DELETE', TO_DATE('2024-02-10', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (6, 'U001', 'PROF_GUEST', 'GUEST', 'CREATE', TO_DATE('2024-03-01', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (7, 'U001', 'PROF_GUEST', 'GUEST', 'CREATE', TO_DATE('2024-03-10', 'YYYY-MM-DD'));
INSERT INTO profile_audit VALUES (8, 'U001', 'PROF_GUEST', 'GUEST', 'DELETE', TO_DATE('2024-03-15', 'YYYY-MM-DD'));

目标SQL语句 #

使用窗口函数ROW_NUMBER()筛选每个删除记录对应的最近创建记录：

WITH delete_records AS (
    SELECT 
        user_id,
        profile_name,
        role,
        action_date AS delete_date
    FROM profile_audit
    WHERE action_type = 'DELETE'
),
create_records_ranked AS (
    SELECT 
        user_id,
        profile_name,
        role,
        action_date AS create_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, profile_name, role 
            ORDER BY action_date DESC
        ) AS rn
    FROM profile_audit
    WHERE action_type = 'CREATE'
)
SELECT 
    dr.profile_name,
    dr.delete_date,
    cr.create_date AS last_create_date_before_delete
FROM delete_records dr
JOIN create_records_ranked cr 
    ON dr.user_id = cr.user_id
    AND dr.profile_name = cr.profile_name
    AND dr.role = cr.role
    AND cr.create_date < dr.delete_date
WHERE cr.rn = 1
ORDER BY dr.delete_date DESC;

期望输出 #

逻辑说明 #

1. delete_records CTE提取所有删除操作记录，获取核心字段与删除日期。
2. create_records_ranked CTE对创建操作按user_id、profile_name、role分组，按创建日期倒序排名，每组内最新的创建记录排名为1。
3. 关联两个CTE，筛选出创建日期早于删除日期且排名为1的记录，即可得到每个删除profile对应的最近创建日期。

若需兼容无对应创建记录的删除操作（比如删除不存在的profile），可将JOIN改为LEFT JOIN，此时last_create_date_before_delete会返回NULL，按需调整即可。

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