如何按ID聚合表中各TYPE的最新变更记录并合并TYPE字段

基于你已经获取的每个ID+TYPE最新变更记录，可通过以下两种方式实现同一ID下的记录合并：

前提：已筛选出每个ID+TYPE的最新记录 #

先复用你提供的子查询作为基础数据集：

WITH latest_id_type_records AS (
    SELECT *
    FROM MY_TABLE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY TIMESTAMP_ DESC) = 1
)

方案1：合并为带关联标识的字符串字段 #

适合将TYPE、对应START/END拼接成字符串，输出单条记录：

WITH latest_id_type_records AS (
    SELECT *
    FROM MY_TABLE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY TIMESTAMP_ DESC) = 1
)
SELECT
    ID,
    -- 用下划线连接所有TYPE
    STRING_AGG(TYPE, '_') WITHIN GROUP (ORDER BY TYPE) AS COMBINED_TYPE,
    -- 拼接TYPE与对应START（格式如TYPE1:start1_TYPE2:start2）
    STRING_AGG(CONCAT(TYPE, ':', START), '_') WITHIN GROUP (ORDER BY TYPE) AS COMBINED_START,
    -- 拼接TYPE与对应END
    STRING_AGG(CONCAT(TYPE, ':', END), '_') WITHIN GROUP (ORDER BY TYPE) AS COMBINED_END,
    -- 取该ID下的最大时间戳
    MAX(TIMESTAMP_) AS MAX_TIMESTAMP
FROM latest_id_type_records
GROUP BY ID;

注：MySQL环境下需将STRING_AGG替换为GROUP_CONCAT(TYPE SEPARATOR '_')，拼接逻辑同理。

方案2：透视为独立字段存储 #

若需将不同TYPE的START/END拆分为独立列（如START_TYPE1、END_TYPE2），使用透视逻辑：

WITH latest_id_type_records AS (
    SELECT *
    FROM MY_TABLE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY TIMESTAMP_ DESC) = 1
)
SELECT
    ID,
    STRING_AGG(TYPE, '_') WITHIN GROUP (ORDER BY TYPE) AS COMBINED_TYPE,
    MAX(TIMESTAMP_) AS MAX_TIMESTAMP,
    -- 按TYPE提取对应START
    MAX(CASE WHEN TYPE = 'TYPE1' THEN START END) AS START_TYPE1,
    MAX(CASE WHEN TYPE = 'TYPE2' THEN START END) AS START_TYPE2,
    -- 按TYPE提取对应END
    MAX(CASE WHEN TYPE = 'TYPE1' THEN END END) AS END_TYPE1,
    MAX(CASE WHEN TYPE = 'TYPE2' THEN END END) AS END_TYPE2
FROM latest_id_type_records
GROUP BY ID;

注：需根据实际存在的TYPE值，逐个编写CASE语句；若TYPE值不固定，部分数据仓库支持动态透视语法（如Snowflake的PIVOT）。

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