SQL合并多审计日志表并去重：生成完整姓名变更审计日志

这个需求完全可以通过SQL实现，核心用到的关联类型是全外连接（FULL OUTER JOIN），再结合窗口函数完成历史姓名值的填充。

需求说明 #

现有两张审计日志表，分别记录用户名字（firstname_history）和姓氏（lastname_history）的变更历史，需要合并成完整的姓名变更轨迹日志：

• 保留所有变更时间点
• 同一时间点同时变更名字和姓氏时，仅保留一条记录
• 未发生变更的字段，沿用最近一次的历史值

测试数据准备 #

-- 创建名字变更历史表
create table firstname_history(userid int, name string, timestamp date);
insert into firstname_history values 
(0, 'joe', '2000-01-01'),
(0, 'mark', '2000-01-12'),
(0, 'adam', '2000-01-21');

-- 创建姓氏变更历史表
create table lastname_history(userid int, name string, timestamp date);
insert into lastname_history values 
(0, 'brown', '2000-01-03'),
(0, 'franklin', '2000-01-21'),
(0, 'hampton', '2000-01-27');

实现SQL语句 #

WITH all_timestamps AS (
    -- 收集所有变更时间点并去重
    SELECT userid, timestamp FROM firstname_history
    UNION
    SELECT userid, timestamp FROM lastname_history
),
full_history AS (
    -- 全外连接关联时间点与两张历史表
    SELECT 
        a.userid,
        a.timestamp,
        f.name AS first,
        l.name AS last
    FROM all_timestamps a
    FULL OUTER JOIN firstname_history f 
        ON a.userid = f.userid AND a.timestamp = f.timestamp
    FULL OUTER JOIN lastname_history l 
        ON a.userid = l.userid AND a.timestamp = l.timestamp
),
filled_history AS (
    -- 用窗口函数填充最近的非空姓名值
    SELECT 
        timestamp,
        FIRST_VALUE(first) OVER (
            PARTITION BY userid 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS first,
        FIRST_VALUE(last) OVER (
            PARTITION BY userid 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last
    FROM full_history
    WHERE timestamp IS NOT NULL
)
-- 去重并按时间排序输出
SELECT DISTINCT timestamp, first, last
FROM filled_history
ORDER BY timestamp;

执行结果 #

+------------+-------+----------+
| timestamp  | first | last     |
+------------+-------+----------+
| 2000-01-01 | joe   | NULL     |
| 2000-01-03 | joe   | brown    |
| 2000-01-12 | mark  | brown    |
| 2000-01-21 | adam  | franklin |
| 2000-01-27 | adam  | hampton  |
+------------+-------+----------+

关键逻辑说明 #

1. all_timestamps CTE：汇总两张表的所有变更时间点并去重，确保不遗漏任何一个变更时刻。
2. 全外连接：将时间点表与两张历史表关联，让每个时间点都能匹配到对应的名字/姓氏变更记录，未变更字段显示为NULL。
3. 窗口函数填充：通过FIRST_VALUE按时间顺序向前填充最近的非空姓名值，实现未变更字段沿用历史值的效果。
4. 去重排序：对结果去重（处理同一时间点的合并记录）并按时间排序，得到符合要求的完整审计日志。

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