PostgreSQL -使用演讲剧院的类数

Question

我在一所大学里有一套独特的演讲厅，上面有身份证(如ABC1)和名字(如A剧院)。我想要统计使用每个演讲剧院的不同班级的数量，并根据课堂数量(大多数班级，排名1等)对演讲厅进行排名。这是清楚还是我需要澄清？

id   |   name    | num | rank 
-----------------------------
ABC1 | Theatre A | 42  |  1
ABC5 | Theatre E | 37  |  2
ABC7 | Theatre G | 25  |  3
ABC2 | Theatre B | 25  |  4
ABC3 | Theatre C | 10  |  5
ABC4 | Theatre D | 9   |  6
ABC6 | Theatre F | 0   |  7

到目前为止，我已经得到以下信息：

id   |   name    
------------------
ABC1 | Theatre A 
ABC2 | Theatre B 
ABC3 | Theatre C 
ABC4 | Theatre D 
ABC5 | Theatre E 
ABC6 | Theatre F 
ABC7 | Theatre G 

使用以下代码：

create or replace view Lecture_theatres
AS
SELECT distinct r.id, r.name
FROM Rooms r
     JOIN Room_types t ON (r.rtype=t.id)
     FULL JOIN Classes c ON (c.room = r.id)
WHERE
t.description='Lecture Theatre'
GROUP BY
r.id,r.name
;

我试图通过在以下内容中添加计数(c.id)来计算num：

SELECT distinct r.id, r.name, count(c.id)

然而，这似乎返回的次数，一个特定的班级使用任何房间，而不是特定的演讲剧院。我不确定我是否提供了足够的信息来解决这一问题，但如果有什么遗漏，请评论！

Answer 1

这是没有测试的，我也不确定窗口函数中的聚合是否像我认为的那样。如果这不起作用，请删除RANK函数，用另一个SELECT包围语句(因此使用它作为子选择)，并再次将RANK添加到外部选择列表中。如果两个房间的类数相同，则此函数将给出相同的值。

SELECT r.id, r.name, COUNT(c.id) AS num, RANK() OVER (ORDER BY COUNT(c.id) DESC) AS rank
FROM rooms r
INNER JOIN room_types t ON t.id = r.rtype
LEFT JOIN classes c ON c.room = r.id
WHERE t.description='Lecture Theatre'
GROUP BY r.id --if this is a PK, this should be enough; otherwise add r.name as well
ORDER BY num DESC