PostgreSQL统计按小时交易数据时sv.last_updated需在GROUP BY子句如何修复

报错的核心原因是你在ORDER BY子句中使用了sv.last_updated字段，这个字段既没有出现在GROUP BY分组列表中，也没有被聚合函数包裹，违反了PostgreSQL的分组查询语法规则。
另外你当前语句末尾加了limit 1，和你要统计全天每个小时数据的需求冲突，执行后只会返回1行结果，属于逻辑错误。

场景1：需要输出指定日期24个小时全量的交易统计（无数据的小时count和amount返回0） #

直接删除ORDER BY里的sv.last_updated desc和末尾的limit 1即可，修复后的语句：

select cast('00:00' as time) + g.h * interval '1 hour' as time,
       count(sv.id) as counts,
       sum(sv.amount) as amount
from generate_series(0, 23, 1) g(h) 
left join paymentvirtualization.summery_virtualizer sv 
    on extract(hour from sv.last_updated) = g.h
    and date_trunc('day', sv.last_updated) = '2021-09-28' 
    and sv.guid = '1aecb2ba5c3941fe9cdab0cbf0c64937' 
group by g.h 
order by g.h;

场景2：需要按每个小时的最新交易时间排序 #

把排序条件改为聚合后的最新时间即可，写法如下：

select cast('00:00' as time) + g.h * interval '1 hour' as time,
       count(sv.id) as counts,
       sum(sv.amount) as amount,
       max(sv.last_updated) as hour_last_updated
from generate_series(0, 23, 1) g(h) 
left join paymentvirtualization.summery_virtualizer sv 
    on extract(hour from sv.last_updated) = g.h
    and date_trunc('day', sv.last_updated) = '2021-09-28' 
    and sv.guid = '1aecb2ba5c3941fe9cdab0cbf0c64937' 
group by g.h 
order by g.h, hour_last_updated desc;

场景3：只需要取最新有交易的1个小时的统计结果 #

调整排序逻辑为按每个小时的最大更新时间倒序，过滤无数据的小时即可：

select cast('00:00' as time) + g.h * interval '1 hour' as time,
       count(sv.id) as counts,
       sum(sv.amount) as amount
from generate_series(0, 23, 1) g(h) 
left join paymentvirtualization.summery_virtualizer sv 
    on extract(hour from sv.last_updated) = g.h
    and date_trunc('day', sv.last_updated) = '2021-09-28' 
    and sv.guid = '1aecb2ba5c3941fe9cdab0cbf0c64937' 
group by g.h 
order by max(sv.last_updated) desc nulls last
limit 1;

内容的提问来源于stack exchange，提问作者D.Anush