Snowflake中拆分多行updates列数据的技术方案咨询

针对你遇到的Snowflake中多行格式的updates列拆分需求，可通过以下SQL实现目标格式：

WITH split_updates AS (
    SELECT 
        company_id,
        TRIM(value) AS line_content,
        CASE WHEN RIGHT(TRIM(value), 1) = ':' THEN 1 ELSE 0 END AS is_field,
        ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY seq) AS row_num
    FROM company_updates,
    LATERAL SPLIT_TO_TABLE(updates, '\n')
    WHERE TRIM(value) != ''
),
field_assigned AS (
    SELECT 
        company_id,
        line_content,
        TRIM(REPLACE(LAST_VALUE(CASE WHEN is_field = 1 THEN line_content END IGNORE NULLS) OVER (PARTITION BY company_id ORDER BY row_num), ':')) AS field_name,
        CASE WHEN is_field = 0 THEN ROW_NUMBER() OVER (PARTITION BY company_id, field_name ORDER BY row_num) END AS value_order
    FROM split_updates
    WHERE is_field = 0
)
SELECT 
    company_id AS "Company ID",
    field_name AS "Field",
    MAX(CASE WHEN value_order = 1 THEN TRIM(REPLACE(line_content, '-', '')) END) AS "Old Value",
    MAX(CASE WHEN value_order = 2 THEN TRIM(REPLACE(line_content, '-', '')) END) AS "New Value"
FROM field_assigned
GROUP BY company_id, field_name
ORDER BY company_id, field_name;

逻辑说明 #

1. 拆分多行内容：使用SPLIT_TO_TABLE将updates列按换行符\n拆分为单行记录，过滤空行后标记出以冒号结尾的字段行。
2. 关联字段与值：通过LAST_VALUE窗口函数，让每个值行（以-开头的行）关联最近的字段名，同时给每个字段下的值行编号（第1个为旧值，第2个为新值）。
3. 聚合生成目标列：用条件聚合将每个字段对应的两个值分别映射到Old Value和New Value列，最终按公司ID和字段分组得到结果。

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