如何在Snowflake中对非数值列透视并保留分组最新数据？

针对你的需求，我们可以分两步实现：先筛选每个ID和FIELD_NAME对应的最新记录，再将数据透视成宽表结构。以下是两种可行的Snowflake SQL写法：

方法一：用CASE WHEN+聚合函数实现透视 #

WITH latest_records AS (
    SELECT 
        ID,
        FIELD_NAME,
        FIELD_VALUE,
        -- 按ID和字段名分组，更新日期倒序排序，标记最新记录
        ROW_NUMBER() OVER (PARTITION BY ID, FIELD_NAME ORDER BY UPDATE_DATE DESC) AS row_rank
    FROM your_table_name -- 替换为你的实际表名
)
SELECT
    ID,
    MAX(CASE WHEN FIELD_NAME = 'Name' THEN FIELD_VALUE END) AS NAME,
    MAX(CASE WHEN FIELD_NAME = 'Age' THEN FIELD_VALUE END) AS AGE
FROM latest_records
WHERE row_rank = 1 -- 只保留每个组的最新记录
GROUP BY ID
ORDER BY ID;

方法二：用Snowflake原生PIVOT语法实现透视 #

WITH latest_records AS (
    SELECT 
        ID,
        FIELD_NAME,
        FIELD_VALUE,
        ROW_NUMBER() OVER (PARTITION BY ID, FIELD_NAME ORDER BY UPDATE_DATE DESC) AS row_rank
    FROM your_table_name -- 替换为你的实际表名
)
SELECT *
FROM latest_records
WHERE row_rank = 1
PIVOT (
    MAX(FIELD_VALUE) -- 聚合函数，这里用MAX是因为每个组只剩一条记录，不影响结果
    FOR FIELD_NAME IN ('Name' AS NAME, 'Age' AS AGE)
)
ORDER BY ID;

核心逻辑说明 #

1. 筛选最新记录：通过ROW_NUMBER()窗口函数，以ID和FIELD_NAME为分组键，按UPDATE_DATE降序排序，标记出每个组内的第一条记录（即最新记录）。
2. 透视转表：两种方法都是将行格式的字段名和值，转换为列格式的NAME和AGE字段，最终得到你需要的宽表结构。

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