PostgreSQL实现按日期分组按小时分桶适配Grafana热力图

需要生成适配Grafana热力图的结果集，要求以日期为行、各小时时段为列展示对应小时的交易数量，示例格式如下：

现有数据表trades，包含字段：id、closed_at（交易结束时间）、asset。当前已写出按日期分组统计交易总数的查询，但需要进一步按小时分桶并将小时转为列展示，已知需用到generate_series和interval函数，但未实现最终查询。

现有基础查询：

SELECT 
    closed_at::DATE,
    COUNT(id)
FROM trades
GROUP BY closed_at
ORDER BY closed_at

解决方案（PostgreSQL） #

以下查询会生成所有日期+24小时的完整矩阵，没有交易的时段会填充0，完美适配Grafana热力图的格式要求：

WITH hourly_buckets AS (
    -- 生成0-23的小时序列，格式化为"HH:00"的列名格式
    SELECT to_char(generate_series(0,23), 'HH24:00') AS hour_slot
),
date_hour_trades AS (
    -- 按日期+小时分组统计交易数
    SELECT
        closed_at::DATE AS trade_date,
        to_char(closed_at, 'HH24:00') AS hour_slot,
        COUNT(id) AS trade_count
    FROM trades
    GROUP BY trade_date, hour_slot
),
all_date_hour_combinations AS (
    -- 生成所有日期与所有小时的笛卡尔积，确保每个日期的24小时都有记录
    SELECT
        d.trade_date,
        h.hour_slot
    FROM (SELECT DISTINCT closed_at::DATE AS trade_date FROM trades) d
    CROSS JOIN hourly_buckets h
)
-- 透视结果：将小时转为列，填充交易数，无交易则为0
SELECT
    trade_date AS date,
    MAX(CASE WHEN hour_slot = '00:00' THEN trade_count ELSE 0 END) AS "00:00",
    MAX(CASE WHEN hour_slot = '01:00' THEN trade_count ELSE 0 END) AS "01:00",
    MAX(CASE WHEN hour_slot = '02:00' THEN trade_count ELSE 0 END) AS "02:00",
    MAX(CASE WHEN hour_slot = '03:00' THEN trade_count ELSE 0 END) AS "03:00",
    MAX(CASE WHEN hour_slot = '04:00' THEN trade_count ELSE 0 END) AS "04:00",
    MAX(CASE WHEN hour_slot = '05:00' THEN trade_count ELSE 0 END) AS "05:00",
    MAX(CASE WHEN hour_slot = '06:00' THEN trade_count ELSE 0 END) AS "06:00",
    MAX(CASE WHEN hour_slot = '07:00' THEN trade_count ELSE 0 END) AS "07:00",
    MAX(CASE WHEN hour_slot = '08:00' THEN trade_count ELSE 0 END) AS "08:00",
    MAX(CASE WHEN hour_slot = '09:00' THEN trade_count ELSE 0 END) AS "09:00",
    MAX(CASE WHEN hour_slot = '10:00' THEN trade_count ELSE 0 END) AS "10:00",
    MAX(CASE WHEN hour_slot = '11:00' THEN trade_count ELSE 0 END) AS "11:00",
    MAX(CASE WHEN hour_slot = '12:00' THEN trade_count ELSE 0 END) AS "12:00",
    MAX(CASE WHEN hour_slot = '13:00' THEN trade_count ELSE 0 END) AS "13:00",
    MAX(CASE WHEN hour_slot = '14:00' THEN trade_count ELSE 0 END) AS "14:00",
    MAX(CASE WHEN hour_slot = '15:00' THEN trade_count ELSE 0 END) AS "15:00",
    MAX(CASE WHEN hour_slot = '16:00' THEN trade_count ELSE 0 END) AS "16:00",
    MAX(CASE WHEN hour_slot = '17:00' THEN trade_count ELSE 0 END) AS "17:00",
    MAX(CASE WHEN hour_slot = '18:00' THEN trade_count ELSE 0 END) AS "18:00",
    MAX(CASE WHEN hour_slot = '19:00' THEN trade_count ELSE 0 END) AS "19:00",
    MAX(CASE WHEN hour_slot = '20:00' THEN trade_count ELSE 0 END) AS "20:00",
    MAX(CASE WHEN hour_slot = '21:00' THEN trade_count ELSE 0 END) AS "21:00",
    MAX(CASE WHEN hour_slot = '22:00' THEN trade_count ELSE 0 END) AS "22:00",
    MAX(CASE WHEN hour_slot = '23:00' THEN trade_count ELSE 0 END) AS "23:00"
FROM all_date_hour_combinations
LEFT JOIN date_hour_trades USING (trade_date, hour_slot)
GROUP BY trade_date
ORDER BY trade_count;

关键部分说明 #

1. hourly_buckets：生成0到23点的所有小时槽，格式化为Grafana友好的HH:00格式。
2. date_hour_trades：按日期+小时分组，统计每个时段的交易数量。
3. all_date_hour_combinations：通过笛卡尔积生成所有日期与所有小时的组合，避免缺失某些时段的记录（比如某小时无交易时仍保留列并填0）。
4. 透视转换：用CASE语句将行转列，把每个小时的交易数映射到对应的列，无交易的时段用0填充，MAX函数用于聚合确保每个日期+小时只返回一个值。

适配Grafana的注意事项 #

• 若closed_at是带时区的时间类型，可在to_char函数中添加时区参数调整小时显示。
• 如需筛选特定资产，可在date_hour_trades的FROM trades后添加WHERE asset = '目标资产'。
• 若要指定日期范围，可在all_date_hour_combinations的子查询中添加日期过滤，比如WHERE closed_at::DATE BETWEEN '2023-01-01' AND '2023-01-31'。

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