當我需要前 n 個查詢時，ORDER BY 子句和 ROW_NUMBER 是否重複功能

我有一張桌子可以跟踪violations學生。我想計算違規次數並從每個班級中選擇前 2 名違規者。查詢看起來像這樣

SELECT *
FROM
 ( SELECT "people"."id", "name", "class",
   ROW_NUMBER() OVER (PARTITION BY "class" ORDER BY COUNT("violation") DESC) AS "v"
   FROM "people"
   INNER JOIN "discipline" on ("discipline"."people_id" = "people"."id")
   GROUP BY "people"."id", "name", "class"
 ) AS "v_table"
WHERE v < 3

該查詢似乎效率低下，因為它首先對計數進行排序ORDER BY，然後分配一個ROW_NUMBER. 如果我已經對分區進行了排序，我如何跳過 ROW_NUMBER 分配並獲得前 2 名。

更新： 添加數據表和SQL 小提琴（實際上違規列是多餘的，只是紀律表中的一個條目意味著存在違規。如果我刪除它，是否應該Count完成"discipline"."people_id"？像這樣）

Discipline                     People       
-------------------------      --------------
id  people_id  violation       id  name  class                    
1   1          True            1   Rob   A                 
2   1          True            2   Jen   B                 
3   2          True            3   Tom   C                
4   3          True            4   Ted   A                 
5   4          True            5   Tim   A            
6   1          True            ...                               
7   4          True         
...                                   

使用 PostgreSQL 9.3

首先，要回答評論中隱含的問題，使用ROW_NUMBER()聚合分配行號似乎效率低下，因為我們已經有了COUNT(violation)數字：

這是必需的，因為每個分區（類）的 COUNT 數字可能不同。由於我們想要前 2 個數字（每個類別），因此我們找不到有用的條件。使用行號，我們可以使用WHERE v < 3它為我們提供前 2 個。

在 9.3 版本中，在 Postgres 中添加了LATERAL連接，類似於 SQL-Server 的CROSS和OUTER APPLY。使用這種新的連接，您可以編寫一個查詢，該查詢使用每個分區的COUNT數字和 a TOP 2。無論效率高低，您都可以測試：

WITH classes AS
 ( SELECT DISTINCT class
   FROM people
 ) 
                            -- if you have a "classes" table, skip the above lines
SELECT 
   v.id, v.name, c.class,
   v.violations
FROM
   classes AS c,
 LATERAL 
   ( SELECT p.id, p.name,
            COUNT(d.violation) AS violations
     FROM people AS p
       INNER JOIN discipline AS d
         ON d.people_id = p.id
     WHERE p.class = c.class 
     GROUP BY p.id, p.name
     ORDER BY violations DESC
     LIMIT 2
 ) AS v
ORDER BY
   c.class, v.violations DESC ;

在**SQL-Fiddle測試**

引用自：https://dba.stackexchange.com/questions/71299