Oracle SQL Developer提取用户ID首次出现记录的SQL求助

修改后的SQL代码 #

WITH combined_audit AS (
    -- 合并归档表和审计表，统一过滤条件
    SELECT auddte AS Upd_Date, usr_id AS Usr, exec_cmd AS Upd_Des
    FROM arc_sys_audit
    WHERE aud_typ = 'I'
      AND appl_id NOT IN ('LEXTEDIT', 'srvcmdopr', 'srvcmdmnt')
      AND (exec_cmd LIKE '%from all roles%' OR exec_cmd LIKE '%Added usr_id = % to role_id%')
      AND auddte >= SYSDATE - 365
    UNION ALL
    SELECT auddte AS Upd_Date, usr_id AS Usr, exec_cmd AS Upd_Des
    FROM sys_audit
    WHERE aud_typ = 'I'
      AND appl_id NOT IN ('LEXTEDIT', 'srvcmdopr', 'srvcmdmnt')
      AND (exec_cmd LIKE '%from all roles%' OR exec_cmd LIKE '%Added usr_id = % to role_id%')
      AND auddte >= SYSDATE - 365
),
user_operations AS (
    -- 提取目标用户ID并标记操作类型，添加行号用于筛选首次/唯一记录
    SELECT 
        Upd_Date,
        Usr,
        Upd_Des,
        -- 从描述中提取实际操作的终端用户ID
        CASE
            WHEN Upd_Des LIKE '%Added usr_id = %' THEN REGEXP_SUBSTR(Upd_Des, 'Added usr_id = ([^ ]+)', 1, 1, 'i', 1)
            WHEN Upd_Des LIKE '%from all roles%' THEN REGEXP_SUBSTR(Upd_Des, 'Removed user_id = ([^ ]+)', 1, 1, 'i', 1)
        END AS Target_User,
        -- 标记操作类型：添加/删除
        CASE
            WHEN Upd_Des LIKE '%Added usr_id = %' THEN 'ADD'
            WHEN Upd_Des LIKE '%from all roles%' THEN 'REMOVE'
        END AS Operation_Type,
        -- 按用户+操作类型分组，按日期降序、描述降序排序，取每组第一条
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE
                    WHEN Upd_Des LIKE '%Added usr_id = %' THEN REGEXP_SUBSTR(Upd_Des, 'Added usr_id = ([^ ]+)', 1, 1, 'i', 1)
                    WHEN Upd_Des LIKE '%from all roles%' THEN REGEXP_SUBSTR(Upd_Des, 'Removed user_id = ([^ ]+)', 1, 1, 'i', 1)
                END,
                CASE
                    WHEN Upd_Des LIKE '%Added usr_id = %' THEN 'ADD'
                    WHEN Upd_Des LIKE '%from all roles%' THEN 'REMOVE'
                END
            ORDER BY Upd_Date DESC, Upd_Des DESC
        ) AS rn
    FROM combined_audit
)
-- 筛选每组第一条记录，按日期降序输出
SELECT Upd_Date, Usr, Upd_Des
FROM user_operations
WHERE rn = 1
ORDER BY Upd_Date DESC;

关键步骤解释 #

• 合并表查询：用combined_auditCTE将归档表和审计表合并，一次性写过滤条件，减少代码冗余，同时只保留需要的添加/删除操作记录。
• 提取目标用户ID：通过REGEXP_SUBSTR正则函数从操作描述中提取实际被操作的终端用户ID（区分添加和删除记录的不同格式）。
• 标记操作类型：用CASE语句区分添加（ADD）和删除（REMOVE）操作。
• 窗口函数筛选唯一记录：使用ROW_NUMBER()窗口函数，按终端用户ID+操作类型分组，对每组内的记录按日期降序、描述降序排序，取每组的第一条记录（rn=1），这样就能得到每个用户的首次添加记录（如果同一天有多条则取最后一条，匹配你的期望结果），以及删除记录（通常每个用户只会有一条删除记录）。

补充说明 #

• 如果需要严格取最早的添加时间（而非同一天的最后一条），只需将窗口函数的ORDER BY Upd_Date DESC改为ORDER BY Upd_Date ASC即可。
• REGEXP_SUBSTR的'i'参数表示忽略大小写，确保匹配不同格式的描述文本。

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