如何在PostgreSQL中将24小时数据转换为独立列？

我有一张名为net_score的表，数据示例如下：

date            |       ip        | up_score 
----------------------------+-----------------+----------
 2022-09-09 07:30:04.485979 | 12.22.19.0      |       51
 2022-09-09 07:30:04.485979 | 10.22.39.1      |       95
 2022-09-09 07:30:04.485979 | 14.260.13.1     |      100
 2022-09-09 07:30:04.485979 | 252.229.219.43  |       97
 2022-09-09 07:30:04.485979 | 10.551.343.10   |       97
 2022-09-09 08:30:04.485979 | 12.22.19.0      |       11
 2022-09-09 08:30:04.485979 | 10.22.39.1      |       54
 2022-09-09 08:30:04.485979 | 14.260.13.1     |       89
 2022-09-09 08:30:04.485979 | 252.229.219.43  |       37
 2022-09-09 08:30:04.485979 | 10.551.343.10   |       11
 2022-09-09 09:30:04.485979 | 12.22.19.0      |       54
 2022-09-09 09:30:04.485979 | 10.22.39.1      |       15
 2022-09-09 09:30:04.485979 | 14.260.13.1     |       90
 2022-09-09 09:30:04.485979 | 252.229.219.43  |       17
 2022-09-09 09:30:04.485979 | 10.551.343.10   |       50

该表包含date、ip和up_score列，数据按小时统计。我需要将24小时的小时级数据按ip分组转换为24个独立列，若某小时无数据则填充-1。

我已能通过以下语句获取小时级数据：

select date_trunc('hour', date) as hourly, ip, up_score 
from net_score 
where date between '2022-09-09 05:30:00' and '2022-09-10 05:30:00' 
group by ip, hourly, up_score 
order by ip, hourly

但期望得到如下格式的结果（缺失小时值填-1）：

ip       | hour_0  |  hour_1  |  hour_2  |  ..
--------------------------+--------- +----------+------
12.22.19.0      |   51    |    11    |     54   |  ..
10.22.39.1      |   95    |    54    |     15   |  ..
14.260.13.1     |   100   |    89    |     90   |  ..
252.229.219.43  |   97    |    37    |     17   |  ..
10.551.343.10   |   97    |    11    |     50   |  ..

说明：
采用此方式的原因是，原查询会返回大量行，新增一个ip就会增加24行，后续用python处理效率较低。而转为24列后，每个ip仅对应一行，列数固定为24，可提升处理效率。若此方法有问题或可优化，请指正。

核心思路 #

先生成目标时间范围内的完整24小时序列，再与所有IP做全组合，确保每个IP都覆盖24小时；通过左连接关联原表数据，缺失值填充-1；最后用条件聚合将每个小时的up_score转为独立列。

具体SQL实现 #

WITH hourly_range AS (
    -- 生成24个连续小时点，覆盖目标时间段
    SELECT generate_series(
        '2022-09-09 05:30:00'::timestamp,
        '2022-09-10 04:30:00'::timestamp,
        '1 hour'::interval
    ) AS hour_point
),
ip_list AS (
    -- 提取所有唯一IP
    SELECT DISTINCT ip FROM net_score
),
full_data AS (
    -- 生成IP与小时的全量组合，左连接原表填充缺失值
    SELECT 
        i.ip,
        h.hour_point,
        COALESCE(n.up_score, -1) AS up_score
    FROM ip_list i
    CROSS JOIN hourly_range h
    LEFT JOIN net_score n 
        ON i.ip = n.ip 
        AND date_trunc('hour', n.date) = h.hour_point
)
-- 按IP分组，将每个小时的up_score转为单独列
SELECT 
    ip,
    MAX(up_score) FILTER (WHERE hour_point = '2022-09-09 05:30:00') AS hour_0,
    MAX(up_score) FILTER (WHERE hour_point = '2022-09-09 06:30:00') AS hour_1,
    MAX(up_score) FILTER (WHERE hour_point = '2022-09-09 07:30:00') AS hour_2,
    -- 依次添加剩余21个小时对应的列，直到hour_23
    MAX(up_score) FILTER (WHERE hour_point = '2022-09-10 04:30:00') AS hour_23
FROM full_data
GROUP BY ip
ORDER BY ip;

代码说明 #

1. hourly_range CTE：用generate_series生成连续小时点，确保没有遗漏的时间维度。
2. ip_list CTE：获取所有唯一IP，保证每个IP都能生成24小时的完整记录。
3. full_data CTE：通过CROSS JOIN生成IP与小时的全组合，LEFT JOIN关联原表后用COALESCE将缺失值替换为-1。
4. 最终聚合：使用FILTER子句筛选每个小时的数据并聚合，也可以用CASE WHEN替代FILTER，示例：

   MAX(CASE WHEN hour_point = '2022-09-09 05:30:00' THEN up_score ELSE -1 END) AS hour_0
   

优化建议 #

• 若需适配不同时间范围，可将起止时间设为参数，避免硬编码。
• 给net_score表建立(ip, date)复合索引，提升关联查询效率。
• 你选择的行转列方式是合理的：减少行数量能显著降低Python处理时的数据传输量和内存占用，IP数量越多，效率提升越明显。

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