PostgreSQL中generate_series时间序列CTE左外连接未返回全量结果

你写的LEFT JOIN没有返回无交易小时行，核心问题有两个：

• 左表time_series仅生成了营业时间点序列，没有包含分馆维度。你预期的结果是「每个分馆对应每个营业时间点都有一行」，但当前左表只有时间数据，和交易表左连后，无交易的时间点所有交易表字段（包括branch_name）均为NULL，不会自动生成每个分馆对应的空统计行。
• JOIN条件仅匹配了时间字段，未关联分馆维度，且分组时未覆盖所有SELECT中非聚合字段，进一步导致无交易的空行被归为NULL分馆组，无法在预期的分馆结果中展示。

先单独提取所有需要统计的分馆列表，将时间序列与分馆列表做笛卡尔积，生成「分馆+营业时间」的全量基准表作为左表，再左连预处理后的交易明细做统计，无匹配交易时计数自然返回0。

WITH time_bound AS (
    -- 一次性取出交易时间范围，简化多层嵌套子查询
    SELECT
        min(transaction_gmt) AS min_ts,
        max(transaction_gmt) AS max_ts
    FROM sierra_view.circ_trans
    WHERE op_code = 'o'
),
time_series AS (
    SELECT
        date_trunc('hours', dd) AS open_hour,
        CASE extract(DOW FROM date_trunc('hours', dd))::INTEGER
            WHEN 0 THEN '0 Sunday'
            WHEN 1 THEN '1 Monday'
            WHEN 2 THEN '2 Tuesday'
            WHEN 3 THEN '3 Wednesday'
            WHEN 4 THEN '4 Thursday'
            WHEN 5 THEN '5 Friday'
            WHEN 6 THEN '6 Saturday'
        END AS transaction_day
    FROM time_bound,
    generate_series(
        date_trunc('hours', time_bound.min_ts),
        date_trunc('hours', time_bound.max_ts),
        '1 hour'::INTERVAL
    ) AS dd
    WHERE extract(HOUR FROM dd)::INTEGER BETWEEN 8 AND 22
),
all_branches AS (
    -- 取出所有统计范围内的分馆，与交易表关联的分馆范围保持一致
    SELECT DISTINCT bn."name" AS branch_name
    FROM sierra_view.statistic_group AS sg 
    INNER JOIN sierra_view.location AS l ON l.code = sg.location_code
    INNER JOIN sierra_view.branch AS b ON b.code_num = l.branch_code_num
    INNER JOIN sierra_view.branch_name AS bn ON bn.branch_id = b.id
),
-- 生成「分馆+营业时间」全量组合作为左连基准
base_dim AS (
    SELECT
        ab.branch_name,
        ts.open_hour AS open_hour_timestamp,
        ts.transaction_day,
        extract(HOUR FROM ts.open_hour)::INTEGER AS hour,
        date(ts.open_hour) AS open_date
    FROM time_series ts
    CROSS JOIN all_branches ab
),
trans_detail AS (
    -- 预处理交易明细，提前做关联和过滤
    SELECT
        ct1.item_record_id,
        ct1.patron_record_id,
        bn."name" AS branch_name,
        date_trunc('hours', ct1.transaction_gmt) AS transaction_hour
    FROM sierra_view.circ_trans AS ct1
    INNER JOIN sierra_view.statistic_group AS sg ON sg.code_num = ct1.stat_group_code_num
    INNER JOIN sierra_view.location AS l ON l.code = sg.location_code
    INNER JOIN sierra_view.branch AS b ON b.code_num = l.branch_code_num
    INNER JOIN sierra_view.branch_name AS bn ON bn.branch_id = b.id
    WHERE
        ct1.op_code = 'o'
        AND ct1.ptype_code::INTEGER < 196
)
SELECT
    bd.branch_name,
    bd.open_hour_timestamp,
    bd.transaction_day,
    bd.hour,
    bd.open_date,
    count(td.item_record_id) AS count_checkouts,
    count(DISTINCT td.patron_record_id) AS count_distinct_patrons
FROM base_dim bd
LEFT OUTER JOIN trans_detail td
    ON td.transaction_hour = bd.open_hour_timestamp
    AND td.branch_name = bd.branch_name
GROUP BY
    bd.branch_name,
    bd.open_hour_timestamp,
    bd.transaction_day,
    bd.hour,
    bd.open_date
ORDER BY bd.branch_name, bd.open_hour_timestamp;

• 新增all_branches CTE统一取出统计范围内的全部分馆，保证和交易数据覆盖的分馆范围完全一致。
• 新增base_dim基准表，通过CROSS JOIN生成「每个分馆对应每个8-22点营业时间」的全量组合，从表结构层面保证左表数据完整，不会丢失无交易的行。
• 关联交易表时同时匹配时间和分馆字段，避免不同分馆的交易数据错配。
• 所有维度字段均从基准表base_dim取值，避免左连后出现NULL的分馆名称；所有非聚合字段全量加入GROUP BY，避免语法兼容问题。
• 简化了原查询中取最小、最大时间戳的多层嵌套子查询，执行效率更高。

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