sql - PostgreSQL - 计算使用演讲厅的类(class)数量

Question

我在一所大学中有一组不同的演讲厅，具有 ID(例如 ABC1)和名称(例如 Theater A)。我想计算使用每个演讲厅的不同类(class)的数量，并根据类(class)数量num(大多数类(class)，排名 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
;

我试图通过添加 count(c.id) 来计算 num:

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

但是，这似乎返回特定类(class)使用任何房间的次数，而不是特定演讲厅的次数。我不确定我是否提供了足够的信息来解决这个问题，但如果有遗漏请发表评论!

Answer 1

这没有经过测试，我不确定窗口函数内的聚合是否按照我认为的那样进行。如果这不起作用，删除 RANK 函数，用另一个 SELECT 包围该语句(因此将其用作子选择)并将 RANK 添加到再次选择外部列表。如果两个房间的类(class)数相同，此函数将为您提供相同的值。

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