Oracle SQL获取指定月份角色变更记录及用户前一条历史记录

针对生成指定月份用户角色变更报表的需求，结合Oracle SQL窗口函数可高效实现目标，同时避免全表扫描USER_AUDIT的性能问题。以下是优化后的查询方案：

核心SQL语句 #

SELECT
    -- 当前变更记录信息
    curr.ROLE_NAME AS 当前角色名称,
    curr.USER_ID,
    curr.AUDIT_DATE AS 变更时间,
    curr.ACTION_TYPE AS 操作类型,
    -- 前一条历史记录信息（若存在）
    prev.ROLE_NAME AS 变更前角色名称,
    prev.AUDIT_DATE AS 上次变更时间
FROM (
    SELECT
        r.ROLE_NAME,
        ua.USER_ID,
        ua.AUDIT_DATE,
        ua.ACTION_TYPE,
        ua.ROLE_ID,
        -- 窗口函数获取当前用户的上一条记录角色ID，按操作时间排序取最近前置记录
        LAG(ua.ROLE_ID) OVER (PARTITION BY ua.USER_ID ORDER BY ua.AUDIT_DATE ASC) AS PREV_ROLE_ID
    FROM USER_AUDIT ua
    JOIN ROLE r ON ua.ROLE_ID = r.ROLE_ID
    -- 精准筛选指定月份，避免毫秒级记录遗漏
    WHERE ua.AUDIT_DATE >= TRUNC(TO_DATE('2023-04', 'YYYY-MM'))
      AND ua.AUDIT_DATE < TRUNC(TO_DATE('2023-04', 'YYYY-MM'), 'MM') + INTERVAL '1' MONTH
) curr
-- 关联角色表获取变更前的角色名称
LEFT JOIN ROLE prev ON curr.PREV_ROLE_ID = prev.ROLE_ID
ORDER BY curr.USER_ID ASC, curr.AUDIT_DATE ASC;

关键说明 #

• LAG窗口函数：通过PARTITION BY ua.USER_ID按用户分组，ORDER BY ua.AUDIT_DATE ASC按操作时间排序，精准获取每个用户当前记录的上一条历史角色ID，满足"明确角色变更来源"的需求。
• 日期过滤优化：用>=和<替代BETWEEN，结合TRUNC函数确保不会遗漏毫秒级变更记录，同时精准锁定目标月份数据。
• 性能保障：先通过WHERE条件筛选指定月份的USER_AUDIT数据，再执行窗口函数，避免全表扫描。建议为USER_AUDIT表创建(USER_ID, AUDIT_DATE, ROLE_ID)复合索引，进一步提升查询效率。

扩展调整 #

如果需要获取前一条记录的更多字段（如操作人、备注等），只需在子查询中添加对应的LAG(字段名)即可，例如：

LAG(ua.OPERATOR) OVER (PARTITION BY ua.USER_ID ORDER BY ua.AUDIT_DATE ASC) AS PREV_OPERATOR

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