PostgreSQL时间序列转日期列（求和聚合）实现求助

需求描述 #

我不是数据库专家，现在需要重构PostgreSQL表：现有大量包含evse_id的充电桩数据行，每条数据有带时间的created_at字段和固定为15分钟的duration字段。希望将2023年2月1日至28日的日期作为列，evse_id作为行，填充每日充电时长的求和结果。

原始数据示例 #

期望输出格式 #

解决方案 #

因为目标日期范围固定（2023-02-01至2023-02-28），使用条件聚合是PostgreSQL中最直接易懂的实现方式，适合非数据库专家操作。

完整SQL语句 #

SELECT
    evse_id,
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-01' THEN duration ELSE 0 END), 0) AS "2023-02-01",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-02' THEN duration ELSE 0 END), 0) AS "2023-02-02",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-03' THEN duration ELSE 0 END), 0) AS "2023-02-03",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-04' THEN duration ELSE 0 END), 0) AS "2023-02-04",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-05' THEN duration ELSE 0 END), 0) AS "2023-02-05",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-06' THEN duration ELSE 0 END), 0) AS "2023-02-06",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-07' THEN duration ELSE 0 END), 0) AS "2023-02-07",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-08' THEN duration ELSE 0 END), 0) AS "2023-02-08",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-09' THEN duration ELSE 0 END), 0) AS "2023-02-09",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-10' THEN duration ELSE 0 END), 0) AS "2023-02-10",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-11' THEN duration ELSE 0 END), 0) AS "2023-02-11",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-12' THEN duration ELSE 0 END), 0) AS "2023-02-12",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-13' THEN duration ELSE 0 END), 0) AS "2023-02-13",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-14' THEN duration ELSE 0 END), 0) AS "2023-02-14",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-15' THEN duration ELSE 0 END), 0) AS "2023-02-15",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-16' THEN duration ELSE 0 END), 0) AS "2023-02-16",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-17' THEN duration ELSE 0 END), 0) AS "2023-02-17",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-18' THEN duration ELSE 0 END), 0) AS "2023-02-18",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-19' THEN duration ELSE 0 END), 0) AS "2023-02-19",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-20' THEN duration ELSE 0 END), 0) AS "2023-02-20",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-21' THEN duration ELSE 0 END), 0) AS "2023-02-21",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-22' THEN duration ELSE 0 END), 0) AS "2023-02-22",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-23' THEN duration ELSE 0 END), 0) AS "2023-02-23",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-24' THEN duration ELSE 0 END), 0) AS "2023-02-24",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-25' THEN duration ELSE 0 END), 0) AS "2023-02-25",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-26' THEN duration ELSE 0 END), 0) AS "2023-02-26",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-27' THEN duration ELSE 0 END), 0) AS "2023-02-27",
    COALESCE(SUM(CASE WHEN DATE(created_at) = '2023-02-28' THEN duration ELSE 0 END), 0) AS "2023-02-28"
FROM
    your_table_name  -- 替换成你的实际表名
WHERE
    DATE(created_at) BETWEEN '2023-02-01' AND '2023-02-28'
GROUP BY
    evse_id
ORDER BY
    evse_id;

关键说明 #

• DATE(created_at)：将带时间戳的created_at转换为纯日期格式，方便按天进行匹配判断
• CASE WHEN：判断当前数据行的日期是否等于目标日期，匹配则取该行的duration值，否则取0
• SUM()：对每个evse_id在对应日期的所有duration值求和
• COALESCE()：确保当某个evse_id在某天没有充电记录时，返回0而非NULL
• WHERE子句：仅过滤2023年2月的数据，减少查询计算量，提升效率

使用提示 #

将SQL语句中的your_table_name替换为你实际存储充电桩数据的表名，即可直接执行得到期望的行列转换结果。

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