Postgres：如何让generate_series返回含空数据区间的时间戳序列？

要实现返回10分钟区间的时间戳（而非数字分区），同时保留无数据的区间，需要通过生成基础时间序列 + 交叉连接worker列表 + 左连接聚合的方式实现，具体SQL如下：

WITH time_intervals AS (
    -- 生成所有10分钟区间的起始和结束时间，采用左闭右开区间[start, end)逻辑
    SELECT 
        start_time AS interval_start,
        start_time + INTERVAL '10 minutes' AS interval_end
    FROM generate_series(
        TIMESTAMP '2022-11-19 19:00:00+00',
        TIMESTAMP '2022-11-19 19:50:00+00',
        '10 minutes'
    ) AS start_time
),
workers AS (
    -- 获取查询时间范围内所有存在的worker
    SELECT DISTINCT worker 
    FROM minerstats
    WHERE created BETWEEN '2022-11-19 19:00:00+00' AND '2022-11-19 20:00:00+00'
)
SELECT
    ti.interval_start, -- 可替换为ti.interval_end，按需显示区间起始/结束时间戳
    w.worker,
    AVG(m.hashrate) AS hashrate,
    AVG(m.sharespersecond) AS sharespersecond,
    COUNT(m.created) AS record_count -- 无数据时返回0
FROM time_intervals ti
-- 交叉连接生成所有时间区间+worker的组合，确保空区间不丢失
CROSS JOIN workers w
-- 左连接匹配对应区间和worker的数据
LEFT JOIN minerstats m
    ON m.created >= ti.interval_start 
    AND m.created < ti.interval_end
    AND m.worker = w.worker
GROUP BY ti.interval_start, w.worker
ORDER BY ti.interval_start, w.worker;

关键说明： #

1. time_intervals CTE：直接生成每个10分钟区间的起始/结束时间戳，左闭右开的区间逻辑可以避免时间点重复统计。
2. workers CTE：提取目标时间范围内的所有worker，确保每个时间区间与每个worker的组合都被覆盖。
3. CROSS JOIN：生成时间区间和worker的笛卡尔积，保证即使某个worker在某区间无数据，该组合仍会出现在结果中。
4. LEFT JOIN：关联业务数据时保留所有基础组合，无数据的字段会返回NULL，COUNT(m.created)会返回0（统计非空记录数）。

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