如何在关联查询中保留generate_series生成的所有行？

这是个非常常见的PostgreSQL查询陷阱，核心原因是你大概率用了内连接（INNER JOIN）——内连接只会保留两边表有匹配数据的行，当某个时间段没有对应设备的数据包时，这一行自然就被过滤掉了。要同时满足关联设备筛选+保留所有时间槽的需求，你只需要调整两个关键地方：

1. 把内连接改成左连接（LEFT JOIN） #

左连接的特性是保留左表（这里就是generate_series生成的时间序列）的所有行，即使右表（devices、packets等）没有匹配的数据，不匹配的字段会填充为NULL。这是保留所有时间槽的基础。

2. 将设备筛选条件移到JOIN的ON子句，而非WHERE子句 #

如果把设备的过滤条件写在WHERE里，左连接后生成的NULL行（无匹配设备/数据包）会被WHERE条件直接过滤掉，相当于又变回了内连接的效果。所以必须把筛选条件绑定到对应的JOIN操作上。

示例代码对比 #

假设你原来的查询大概是这样的（内连接+WHERE筛选）：

SELECT 
  gs.t AS time_slot, 
  COUNT(p.id) AS packet_count
FROM generate_series(
  '2024-05-01 00:00:00'::timestamp, 
  '2024-05-01 23:00:00'::timestamp, 
  '1 hour'
) gs(t)
JOIN devices d ON d.id = p.device_id
JOIN packets p ON p.received_at BETWEEN gs.t AND gs.t + '1 hour'
WHERE d.device_type = 'gateway' -- 这里的条件会过滤掉无匹配的时间行
GROUP BY gs.t
ORDER BY gs.t;

修改后的正确查询应该是：

SELECT 
  gs.t AS time_slot, 
  COALESCE(COUNT(p.id), 0) AS packet_count -- 用COALESCE把NULL转为0，更直观
FROM generate_series(
  '2024-05-01 00:00:00'::timestamp, 
  '2024-05-01 23:00:00'::timestamp, 
  '1 hour'
) gs(t)
-- 先左连接设备，把筛选条件放在ON里
LEFT JOIN devices d ON d.device_type = 'gateway'
-- 再左连接数据包，同时绑定时间范围条件
LEFT JOIN packets p ON p.device_id = d.id 
  AND p.received_at BETWEEN gs.t AND gs.t + '1 hour'
GROUP BY gs.t
ORDER BY gs.t;

额外注意事项 #

• 如果你的衍生查询里还有其他关联表，比如设备分组、区域表等，所有这些表都要使用左连接，并且它们的过滤条件都要放在对应的ON子句中。
• 聚合函数比如SUM(p.size)可能会返回NULL，同样可以用COALESCE(SUM(p.size), 0)把NULL转为0，让统计结果更统一。

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