計算每組最大的行數和附加條件

我有下表：

horse_main_id | 種族ID | 馬名 | 評分100 | 比賽結果
---------------+---------+-------------------+-----------+-------------
23 | 159 | 天使學 | 126 | 1
24 | 159 | 阿沃克 | 118 | 
25 | 159 | 馬洛人 | 118 | 
26 | 159 | 哈克納爾 | 113 | 
27 | 159 | 令人興奮 | 110 | 2
28 | 159 | 蒙地卡羅 | 107 | 
29 | 159 | 克羅斯康斯坦茨 | 103 | 
30 | 160 | 奧爾德伯恩斯 | 119 | 
31 | 160 | 鑽石吉姆 | 117 | 
33 | 160 | 塗裝 | 115 | 
34 | 160 | 帕拉吉 | 115 | 
35 | 160 | 我會'ava'alf | 110 | 2
36 | 160 | 傑克和國王 | 108 | 
32 | 160 | 座機島 | 115 | 1

我想對 的 組進行操作race_id，找到所有組有 和max(rating100)的組race_result = 1，併計算所有發生這種情況的時間。

所以這個例子應該只返回 1，對於 race_id 159 組，126 的 max rating100 也有 race_result = 1，但是對於 race_id 160 組，max rating100 是 119，但沒有race_result = 1。

這是我得到的最接近的（被告知我應該使用視窗函式）：

選擇 * 從 (
選擇 horse_name、race_id、race_result
, max(rating100) OVER (按race_id劃分) AS max_rating
FROM horse_main) t
其中race_result = 1；

由於在任何一種情況下都需要一個子查詢，我會在子查詢中使用一個普通的聚合（可能更便宜）：

SELECT count(*)
FROM  (SELECT race_id, max(rating100) AS rating100
      FROM   horse_main
      GROUP  BY 1) x
JOIN   horse_main h USING (race_id, rating100)
WHERE  h.race_result = 1;

如果每個有很多race_id行，則使用以下技術之一獲得分組最大值會更快：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

---

或者使用視窗函式**rank()**- 這就是你有效計算的：所有最大rating100的 perrace_idwithrace_result = 1：

SELECT count(*)
FROM  (SELECT race_result, rank() OVER (PARTITION BY race_id ORDER BY rating100 DESC) AS rnk
      FROM   horse_main) x
WHERE  rnk = 1
AND    race_result = 1;

上的索引(race_id, rating100 DESC, race_result DESC)應該有助於大表的性能。

假設rating100已定義NOT NULL，否則您需要添加NULLS LAST查詢和索引。

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