PostgreSQL实现多值列数据透视 按日期时间合并的行列转换查询

实现逻辑 #

• 先逆透视原表的时间段宽字段，拆分为按小时维度的行数据，同时将日期与小时拼接为完整的日期时间列
• 再将STATUS字段的三类枚举值转为列头，展示对应时段各状态的数值

完整查询语句 #

-- 请将下方的activity_log替换为实际表名
WITH unpivot_time AS (
    SELECT
        id,
        -- 拼接日期与时段起始小时生成datetime，可按需修改输出格式
        date + (time_hour || ' hours')::interval AS datetime,
        status,
        time_value
    FROM activity_log
    -- 逆透视4个时间段字段，映射对应时段的起始小时
    CROSS JOIN LATERAL (
        VALUES
            (9, time09_10),
            (10, time10_11),
            (11, time11_12),
            (12, time12_13)
    ) AS t(time_hour, time_value)
)
SELECT
    id,
    datetime,
    -- 行转列取三类状态的数值
    MAX(time_value) FILTER (WHERE status = 'RUN') AS run,
    MAX(time_value) FILTER (WHERE status = 'WALK') AS walk,
    MAX(time_value) FILTER (WHERE status = 'STOP') AS stop
FROM unpivot_time
GROUP BY id, datetime
ORDER BY id, datetime;

补充说明 #

• 若需要字符串格式的datetime，可将datetime生成逻辑替换为to_char(date + (time_hour || ' hours')::interval, 'YYYY-MM-DD HH24:MI')，按需调整格式模板即可
• 若同一ID、同一时段同一状态存在多条记录，可根据业务需求将聚合函数MAX替换为SUM、AVG等

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