PostgreSQL行转列：实现指定设备近两周监测类型值的日均值统计

核心逻辑拆解 #

• 先完成时间范围过滤：仅保留当前时间往前14天的设备读数
• 按【自然日期、设备ID、指标类型】分组，提前计算每个分组下的读数日平均值
• 将行级存储的指标类型值旋转为结果集列，无对应记录的位置填充为0，最终按日期倒序输出

实现方式 #

方式1：固定指标类型场景（无需额外扩展） #

适合指标类型枚举固定的场景，直接用条件聚合实现，无需依赖PostgreSQL扩展，可直接执行：

WITH daily_avg AS (
    -- 预计算每天每个指标的平均值
    SELECT
        DATE(created_at) AS stat_date,
        device,
        type,
        AVG(value) AS daily_value
    FROM readings
    WHERE
        device = :input_device_id -- 替换为需要查询的设备ID参数
        AND created_at >= NOW() - INTERVAL '14 days' -- 过去两周时间范围
    GROUP BY DATE(created_at), device, type
)
-- 行转列输出
SELECT
    stat_date AS date,
    device,
    COALESCE(MAX(CASE WHEN type = 'pressure' THEN daily_value END), 0) AS pressure,
    COALESCE(MAX(CASE WHEN type = 'flow' THEN daily_value END), 0) AS flow,
    COALESCE(MAX(CASE WHEN type = 'temperature' THEN daily_value END), 0) AS temperature
FROM daily_avg
GROUP BY stat_date, device
ORDER BY stat_date DESC;

对照样例数据执行后，输出结果和预期格式完全匹配：

• 2021-03-28 设备123456789：pressure列取当天唯一的65，flow列取4条flow记录的均值24.5
• 2021-03-24 设备123456789：pressure列取当天2条记录的均值78，flow列无对应记录填充为0

方式2：动态适配所有指标类型的通用实现 #

如果后续会新增指标类型、不想手动维护列分支，可以通过自定义函数自动查询指定设备下的所有唯一指标值，动态拼接收列表，无需手动调整逻辑：

CREATE OR REPLACE FUNCTION get_daily_device_metrics(target_device varchar(25))
RETURNS SETOF json AS $$
DECLARE
    type_columns text;
BEGIN
    -- 自动获取设备下所有唯一指标类型，生成聚合列片段
    SELECT string_agg(
        DISTINCT format(
            'COALESCE(MAX(CASE WHEN type = %L THEN daily_value END), 0) AS %I',
            type,
            type
        ),
        ', '
    ) INTO type_columns
    FROM readings
    WHERE device = target_device;

    -- 执行动态拼接的查询逻辑
    RETURN QUERY EXECUTE format('
        WITH daily_avg AS (
            SELECT
                DATE(created_at) AS stat_date,
                device,
                type,
                AVG(value) AS daily_value
            FROM readings
            WHERE
                device = %L
                AND created_at >= NOW() - INTERVAL ''14 days''
            GROUP BY DATE(created_at), device, type
        )
        SELECT json_build_object(
            ''date'', stat_date,
            ''device'', device,
            %s
        )
        FROM daily_avg
        GROUP BY stat_date, device
        ORDER BY stat_date DESC
    ', target_device, type_columns);
END;
$$ LANGUAGE plpgsql;

调用方式：传入需要查询的设备ID即可，比如SELECT * FROM get_daily_device_metrics('123456789'); #

关键实现说明 #

• 日期提取使用DATE(created_at)，直接截除时分秒部分，符合输出仅保留自然日期的要求
• 使用COALESCE(..., 0)填充无对应指标的日期值，和预期输出的0值逻辑一致
• 因为已经提前按日期、指标类型做了分组，每个分组仅对应一个平均值，外层用MAX()取值是条件聚合场景下的通用简化写法
• 动态函数的返回结构会自动适配设备下所有存在的指标类型，无需提前知道枚举值

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