在BigQuery中无需存储过程/函数生成完整审计表

场景与需求 #

在BigQuery环境中，无法使用存储过程（stored procedure）或函数（function），需基于两张表生成完整审计表：

• 事务数据表：存储每个id的最新状态信息
• 变更数据表：仅记录事务数据创建后的变更序列，需将事务表的creation_date作为审计表的首个change_date
• 最终审计表要求：同一id+同一change_date的多行变更合并为单行，完整记录每个id从创建到历次变更的状态轨迹

示例表创建SQL #

1. 事务数据表（图A） #

CREATE OR REPLACE TABLE `project.dataset.transaction_table` AS
SELECT 1 AS id, 'active' AS status, DATE('2024-01-01') AS creation_date, 'user1' AS last_updated_by
UNION ALL
SELECT 2 AS id, 'inactive' AS status, DATE('2024-01-03') AS creation_date, 'user2' AS last_updated_by;

2. 变更数据表（图B） #

CREATE OR REPLACE TABLE `project.dataset.change_table` AS
SELECT 1 AS id, 'pending' AS status, DATE('2024-01-02') AS change_date, 'user3' AS updated_by
UNION ALL
SELECT 1 AS id, 'active' AS status, DATE('2024-01-02') AS change_date, 'user4' AS updated_by
UNION ALL
SELECT 2 AS id, 'active' AS status, DATE('2024-01-04') AS change_date, 'user5' AS updated_by;

解决方案SQL #

无需存储过程/函数，仅通过CTE和窗口函数实现：

WITH combined_data AS (
    -- 将事务表的初始状态作为首个变更记录
    SELECT
        id,
        status,
        creation_date AS change_date,
        last_updated_by AS updated_by
    FROM `project.dataset.transaction_table`
    UNION ALL
    -- 导入变更表的所有记录
    SELECT
        id,
        status,
        change_date,
        updated_by
    FROM `project.dataset.change_table`
),
deduplicate_daily_changes AS (
    -- 合并同一id、同一变更日期的多条记录，保留最新的状态与操作人
    SELECT
        id,
        status,
        change_date,
        updated_by,
        ROW_NUMBER() OVER (PARTITION BY id, change_date ORDER BY updated_by DESC) AS rn
    FROM combined_data
),
ordered_audit_trail AS (
    -- 按id和变更日期排序，生成完整审计序列
    SELECT
        id,
        status,
        change_date,
        updated_by,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_date) AS audit_seq
    FROM deduplicate_daily_changes
    WHERE rn = 1
)
SELECT * FROM ordered_audit_trail ORDER BY id, change_date;

逻辑说明 #

1. combined_data：合并事务表的初始状态与变更表的所有记录，统一字段格式
2. deduplicate_daily_changes：通过窗口函数对同一id+change_date的记录去重，保留最新的一条（可根据业务需求调整排序规则）
3. ordered_audit_trail：对每个id的变更记录按日期排序，生成审计序列，最终输出符合要求的完整审计表

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