时间戳

首页 > 解决方案 > PostgreSQL 练习：GROUP BY 子句或在聚合函数中使用

sql - PostgreSQL 练习：GROUP BY 子句或在聚合函数中使用

问题描述

我在做 PostgreSQL 练习 - 按价值分类设施时遇到了一个问题。这是问题链接：https ://pgexercises.com/questions/aggregates/classify.html

我试过了：

select name,
  (case when rank <= max(rank)/3 then 'high'
        when rank > max(rank)/3 and rank <= max(rank)*2/3 then 'average'
        else 'low' end) as revenue 
from
 (select name, rank() over (order by sum(
         case when boo.memid=0 then slots*guestcost
              when boo.memid!=0 then slots*membercost end) desc) as rank
  from cd.bookings boo join cd.facilities fac on boo.facid=fac.facid
  group by name) subq

我也试过：

with subq as 
 (select name, rank() over (order by sum(
         case when boo.memid=0 then slots*guestcost
              when boo.memid!=0 then slots*membercost end) desc) as rank
  from cd.bookings boo join cd.facilities fac on boo.facid=fac.facid
  group by name)
select name,
   (case when rank <= max(rank)/3 then 'high'
         when rank > max(rank)/3 and rank <= max(rank)*2/3 then 'average'
         else 'low' end) as revenue from subq

两者都出现错误：列“sub.name”必须出现在 GROUP BY 子句中或在聚合函数中使用

我很困惑，不知道我的代码有什么问题。有谁能够帮我？

标签： sqlpostgresql

解决方案

您必须按名称和排名对结果表进行分组。

只需将其附加到您的语句中，它就会运行而不会出现错误：

group by name, rank

推荐阅读