Snowflake中表转置（Pivot）SQL实现技术求助

你的核心需求是把原表中STATE字段的ON/OFF状态转为独立列，对应展示每个TAG的状态变更时间。先指出你现有SQL的几个问题，再给出修正后的实现：

现有SQL的问题 #

1. 子查询里写的"'ON'","'OFF'"是固定字符串常量，不是Pivot生成的时间列，只会输出'ON'和'OFF'文本，而非对应的时间值。
2. 内层子查询的order by datetime asc完全多余，Pivot用的MAX(DATETIME)是聚合函数，行顺序不影响聚合结果。
3. TOP 100的位置不合理，应该放到外层查询控制返回行数。

修正后的SQL语句 #

SELECT TAG, PRIORITY, "ON", "OFF"
FROM (
    -- 只取需要的字段，减少冗余
    SELECT DATETIME, TAG, PRIORITY, STATE
    FROM tab
) AS source_data
PIVOT (
    -- 取每个状态的最新变更时间，若要保留所有时间可改用ARRAY_AGG(DATETIME)
    MAX(DATETIME) 
    FOR STATE IN ('ON' AS "ON", 'OFF' AS "OFF")
) AS pivoted_result
-- 按ON状态的时间排序
ORDER BY "ON" ASC
-- 限制返回前100条
LIMIT 100;

关键说明 #

• PIVOT里的MAX(DATETIME)：假设每个TAG+PRIORITY+STATE组合有多条时间记录，取最新的那条作为状态变更时间。如果需要保留该状态的所有历史时间，可把MAX(DATETIME)改成ARRAY_AGG(DATETIME ORDER BY DATETIME ASC)，对应列会返回时间数组。
• 列名"ON"和"OFF"需要用双引号括起来，因为它们和SQL关键字同名，Snowflake要求用双引号区分特殊名称。
• Snowflake中用LIMIT替代TOP更符合通用SQL规范，不过TOP 100也可以直接放在SELECT后使用。

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