如何在PostgreSQL函数的RIGHT JOIN中用FOR循环替代多个UNION ALL

嘿，这个问题我熟！原来写一堆重复的UNION ALL确实挺繁琐的，用PL/pgSQL的FOR循环重构绝对能让你的代码清爽不少。我给你两种方案，一种严格符合你要求的FOR循环实现，另一种是PostgreSQL更推荐的简洁写法，供你参考：

下面是重构后的完整函数，每一步都做了注释说明：

CREATE OR REPLACE FUNCTION ended_jobs()
RETURNS TABLE (hour_slot timestamp with time zone, ended_count integer) AS $$
DECLARE
    start_hour timestamp with time zone;
    current_slot timestamp with time zone;
    next_slot timestamp with time zone;
BEGIN
    -- 计算起始时间：当前时间往前推16小时，截断到整点
    start_hour := date_trunc('hour', CURRENT_TIMESTAMP - interval '16 hours');
    
    -- 循环遍历过去16个连续小时区间
    FOR i IN 0..15 LOOP
        -- 计算当前小时槽和下一个小时槽（左闭右开区间，避免边界重复统计）
        current_slot := start_hour + i * interval '1 hour';
        next_slot := current_slot + interval '1 hour';
        
        -- 收集当前区间的结束作业数，等价于原来的单个UNION ALL分支
        RETURN QUERY
        SELECT 
            current_slot AS hour_slot,
            COUNT(jser.id) AS ended_count  -- 用主键统计更准确，也可以用COUNT(*)
        FROM job_start_end_rollups jser
        WHERE jser.end_time >= current_slot 
          AND jser.end_time < next_slot;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

关键说明： #

• 用DECLARE块定义变量存储时间区间的起始、当前和结束点，逻辑更清晰；
• start_hour确保我们从16小时前的整点开始，覆盖连续的16个小时；
• 循环0..15刚好对应16个小时区间，每次循环生成一个时间槽的统计结果，通过RETURN QUERY追加到返回集，完美替代重复的UNION ALL；
• 用>=和<判断时间边界，避免两个相邻区间重复统计同一时刻结束的作业。

虽然你要求用FOR循环，但其实PostgreSQL自带的generate_series函数可以直接生成时间序列，代码更简洁且性能通常更好，给你作为参考：

CREATE OR REPLACE FUNCTION ended_jobs()
RETURNS TABLE (hour_slot timestamp with time zone, ended_count integer) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        hours.hour_slot,
        COUNT(jser.id) AS ended_count
    FROM generate_series(
        date_trunc('hour', CURRENT_TIMESTAMP - interval '16 hours'),
        date_trunc('hour', CURRENT_TIMESTAMP - interval '1 hour'),
        interval '1 hour'
    ) AS hours(hour_slot)
    LEFT JOIN job_start_end_rollups jser 
        ON jser.end_time >= hours.hour_slot 
        AND jser.end_time < hours.hour_slot + interval '1 hour'
    GROUP BY hours.hour_slot
    ORDER BY hours.hour_slot;
END;
$$ LANGUAGE plpgsql;

关键说明： #

• generate_series直接生成过去16个小时的整点时间序列，一步替代所有UNION ALL和循环；
• LEFT JOIN确保每个时间槽都能显示（哪怕该小时没有结束的作业，此时ended_count为0），和你原来的RIGHT JOIN效果完全一致；
• 最后按时间槽排序，结果更规整，维护成本极低（改小时数只需调整generate_series的参数）。

• 如果不需要时区支持，可以把timestamp with time zone改成timestamp，但建议保留时区避免时间歧义；
• 确保job_start_end_rollups.end_time字段上有索引，这样统计查询会更快，尤其是数据量较大的时候；
• 两种方案都支持动态调整覆盖的小时数，比原来硬写一堆UNION ALL灵活太多。

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