为什么过滤掉where子句需要额外的子查询

Question

首先。抱歉标题含糊不清。我不知道如何构建它。通过示例代码，我希望你能理解我的疑虑。

任务是找出俱乐部收入排名前三的设施。我以为这是最后的代码。

SELECT x,y as rank
FROM tableA
where rank <=3;

但这说明等级栏并不存在。因此，我必须将其包含在另一个子查询中，以筛选前3名。

SELECT x,rank
FROM
(SELECT x,y as rank
FROM tableA
) as sub
where rank<=3;

为什么？我需要一个额外的子查询吗？为什么它不能在原始查询中使用where子句进行筛选？

设施名称代码及其收入排名：

select res.name,rank() over(order by total desc) as rank
from
(select fac.facid,fac.name,sum(slots*
case
when memid=0 then guestcost
else membercost
end) as total
from cd.bookings bks
inner join cd.facilities fac
on bks.facid=fac.facid
group by fac.facid
order by total desc) as res

我认为可行的方法：

select res.name,rank() over(order by total desc) as rank
from
(select fac.facid,fac.name,sum(slots*
case
when memid=0 then guestcost
else membercost
end) as total
from cd.bookings bks
inner join cd.facilities fac
on bks.facid=fac.facid
group by fac.facid
order by total desc) as res
where rank<=3;           
(why this don't work??)

工作原理：

select name, rank
from
(select res.name,rank() over(order by total desc) as rank
from
(select fac.facid,fac.name,sum(slots*
case
when memid=0 then guestcost
else membercost
end) as total
from cd.bookings bks
inner join cd.facilities fac
on bks.facid=fac.facid
group by fac.facid
order by total desc) as res) as sub
where rank <=3;

Answer 1

考虑您的第一个查询版本：

SELECT x, RANK() OVER (ORDER BY y) rnk
FROM tableA
WHERE rnk <= 3;

这是不合法的，因为不能在WHERE子句中使用在同一级别的SELECT中定义的别名。这里的问题是，执行的窗口函数通常在WHERE子句中进行筛选之前执行最后。相反，您需要计算子查询中的排名，然后对其进行过滤：

SELECT *
FROM
(
SELECT x, RANK() OVER (ORDER BY y) rnk
FROM tableA
) t
WHERE rnk <= 3;

请注意，SQL的某些版本确实允许在QUALIFY子句中使用窗口函数：

SELECT x, RANK() OVER (ORDER BY y) rnk
FROM tableA
QUALIFY RANK() OVER (ORDER BY y) <= 3;

但是Postgres不支持QUALIFY，所以您可能需要使用子查询选项。