Snowflake环境下SQL查询构建求助：状态与所有者字段关联处理

场景说明 #

基于无时间戳的审计数据，将每个状态值与对应的所有者（审计值）进行匹配，纯SQL实现，无需PL/SQL。

方案一：所有者字段仅变更时非空，其余为NULL #

假设输入表audit_table包含字段：object_id（对象ID）、status（状态值，每行非空）、owner（所有者，仅变更行有值，其余为NULL），且表中行顺序对应变更先后顺序。

WITH numbered_records AS (
    -- 为每个对象的记录分配顺序号，模拟变更时序（若有实际排序字段，替换CURRENT_TIMESTAMP）
    SELECT 
        object_id,
        status,
        owner,
        ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY CURRENT_TIMESTAMP) AS change_seq
    FROM audit_table
),
matched_owner AS (
    -- 用最近的非空所有者值填充每行状态对应的所有者
    SELECT 
        object_id,
        status,
        LAST_VALUE(owner IGNORE NULLS) OVER (
            PARTITION BY object_id 
            ORDER BY change_seq 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS associated_owner
    FROM numbered_records
)
-- 输出最终匹配结果
SELECT object_id, status, associated_owner
FROM matched_owner
ORDER BY object_id, change_seq;

方案二：状态变更与所有者变更为独立行 #

假设输入表audit_table包含字段：object_id（对象ID）、change_type（变更类型：'STATUS'或'OWNER'）、change_value（对应状态值或所有者值），行顺序对应变更先后顺序。

WITH numbered_changes AS (
    -- 为每个对象的变更记录分配顺序号
    SELECT 
        object_id,
        change_type,
        change_value,
        ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY CURRENT_TIMESTAMP) AS change_seq
    FROM audit_table
),
status_list AS (
    -- 提取所有状态变更记录
    SELECT object_id, change_value AS status, change_seq
    FROM numbered_changes
    WHERE change_type = 'STATUS'
),
owner_list AS (
    -- 提取所有所有者变更记录
    SELECT object_id, change_value AS owner, change_seq
    FROM numbered_changes
    WHERE change_type = 'OWNER'
)
-- 为每个状态匹配最近的所有者变更
SELECT 
    sl.object_id,
    sl.status,
    (SELECT MAX(ol.owner) FROM owner_list ol WHERE ol.object_id = sl.object_id AND ol.change_seq <= sl.change_seq) AS associated_owner
FROM status_list sl
ORDER BY sl.object_id, sl.change_seq;

注意事项 #

• 若表中有隐含的变更顺序字段（如插入时间戳、自增ID），请将ORDER BY CURRENT_TIMESTAMP替换为该字段，确保顺序准确性。
• 以上方案基于输入数据的行顺序代表变更时序的假设，若实际顺序无法保证，需补充其他排序依据。

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