postgresql - 用for循环替换多个左联接

我有一个表，每个点都有一个时间属性（“hour”列），位于不同的沙砾方格中（由“grid”列标记）。
对于每一个正方形，我想得到按小时分组的点的数量，从而得到一个具有24列的表+与现有GRIDID的一样多行。
到目前为止，我用了24个左连接（见下图）。有没有一种简单循环的方法来简化查询？

SELECT * from (select gridid, count(id)  as "00" from points where hour = 0 GROUP BY gridid ORDER BY "00" DESC)t00

left join
(select gridid, count(id)  as "01" from points where hour = 1 GROUP BY gridid)t01
on t01.gridid = t00.gridid

left join
(select gridid, count(id)  as "02" from points where hour = 2 GROUP BY gridid)t02
on t02.gridid = t00.gridid

...

left join
(select gridid, count(id)  as "24" from points where hour = 24 GROUP BY gridid)t02
on t02.gridid = t24.gridid

最佳答案

FOR循环在SQL中通常是一个坏主意，它可以更好地与基于集的操作符一起工作。相反，可以尝试以下方法：

select grid_id,
       sum(case hour when 0 then 1 end) as "00",
       sum(case hour when 1 then 1 end) as "01",
       sum(case hour when 2 then 1 end) as "02",
...

       sum(case hour when 24 then 1 end) as "24"
from points
group by grid_id
order by 2 desc

（顺便说一下，第00列和第24列不太可能同时填充，除非您使用的是25小时的一天。）