基于序列字段校验Snowflake中唯一标识符关联数据一致性

核心思路 #

1. 从唯一标识符中拆分出前缀（同一组记录的共同标识，即去掉序列段的部分）和序列值（区分主条目/子条目的两位编码）
2. 将子条目（序列02、03）与同前缀的主条目（序列01）关联，对比指定字段的一致性
3. 筛选出字段值与主条目不一致的违规记录

具体查询代码 #

WITH main_records AS (
    -- 提取所有主条目（序列01）的前缀和基准字段值
    SELECT
        SPLIT_PART(唯一标识符, '/', 1) || '/' || SPLIT_PART(唯一标识符, '/', 3) || '/' || SPLIT_PART(唯一标识符, '/', 4) AS 前缀,
        Column1 AS main_col1,
        Column2 AS main_col2,
        Column3 AS main_col3
    FROM your_table_name
    WHERE SPLIT_PART(唯一标识符, '/', 2) = '01'
),
child_records AS (
    -- 提取所有待校验的子条目（序列02、03）信息
    SELECT
        唯一标识符,
        SPLIT_PART(唯一标识符, '/', 1) || '/' || SPLIT_PART(唯一标识符, '/', 3) || '/' || SPLIT_PART(唯一标识符, '/', 4) AS 前缀,
        Column1,
        Column2,
        Column3,
        SPLIT_PART(唯一标识符, '/', 2) AS 序列值
    FROM your_table_name
    WHERE SPLIT_PART(唯一标识符, '/', 2) IN ('02', '03')
)
-- 关联主条目与子条目，筛选不一致的违规记录
SELECT
    cr.唯一标识符,
    cr.序列值,
    cr.Column1,
    mr.main_col1 AS 主条目Column1,
    cr.Column2,
    mr.main_col2 AS 主条目Column2,
    cr.Column3,
    mr.main_col3 AS 主条目Column3,
    -- 生成直观的违规说明
    CASE WHEN cr.Column1 != mr.main_col1 THEN 'Column1不一致' ELSE '' END ||
    CASE WHEN cr.Column2 != mr.main_col2 THEN '、Column2不一致' ELSE '' END ||
    CASE WHEN cr.Column3 != mr.main_col3 THEN '、Column3不一致' ELSE '' END AS 违规说明
FROM child_records cr
JOIN main_records mr ON cr.前缀 = mr.前缀
WHERE cr.Column1 != mr.main_col1 
   OR cr.Column2 != mr.main_col2 
   OR cr.Column3 != mr.main_col3
ORDER BY cr.前缀, cr.序列值;

代码说明 #

• SPLIT_PART(唯一标识符, '/', 2)：拆分出唯一标识符中的序列段，用来快速区分主/子条目
• CTE main_records：单独存储所有主条目的基准字段值，作为校验参照
• CTE child_records：提取所有需要校验的子条目信息
• 最终通过前缀匹配同组记录，筛选出字段值不一致的违规条目，并生成明确的违规说明

示例结果（基于提供的测试数据） #

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