如何在不失去屬性的情況下有效地使用 GROUP BY？

我正在使用 PostgreSQL。

我的想法是將在table-a中出現超過 3 次的玩家分組。

table-a = < player_id, year_world, c, d, e >

通過第一個子查詢，我得到player_id和該球員參加世界杯的次數。並且隨著NATURAL JOIN我恢復播放器出現的元組中的年份或其他屬性。

以下查詢有效，但我想對其進行優化：

SELECT player_id , year_world
FROM    (SELECT player_id , count(player_id ) player_id_count
       FROM <table-a> 
       GROUP BY player_id 
       HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

我想知道是否可以不使用NATURAL JOIN來獲取表的其他屬性。因為當我將要投影的其餘屬性放入SELECT其中時，GROUP BY我得到一個空表。

請記住，添加到SELECT的子查詢的GROUP BY屬性必須由我們正在分組的屬性確定。也就是說，對於每個player_id值，我總是可以有相同的新屬性值，如果對於相同的player_id值，我有不同的新屬性值，這是不確定的。添加與我使用的組NATURAL JOIN或其他解決方案不相關的屬性。

以下查詢不NATURAL JOIN適合我：

SELECT player_id, count(player_id ) player_id_count, year_world, c, d, e
FROM <table-a> 
GROUP BY player_id, year_world, c, d, e
HAVING count(player_id ) > 3

DML 中最有效的方法是什麼？

SELECT您可以簡單地在主查詢中添加更多列，而根本不更改子查詢：

SELECT player_id , year_world, c, d, e                   -- all columns
FROM    (SELECT player_id , count(player_id ) player_id_count
       FROM <table-a> 
       GROUP BY player_id 
       HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

無論您是進行NATURAL連接還是連接JOIN .. ON都對效率無關緊要（但使用LATERAL連接可能很重要）。

另一種常用方法是在子查詢或 CTE 中使用視窗函式，然後WHERE在主查詢中使用 a 限制結果——這正是 Lennart 的答案。

無論如何，這個問題需要某種子查詢來解決。

為了提高效率，索引(player_id)很可能會有所幫助，無論您使用哪種方法編寫查詢。work_mem還應檢查記憶體設置，例如，它會影響性能。最佳設置和效率取決於很多因素（表大小、數據分佈、整個伺服器的 RAM 大小、連接數、Postgres 版本等）

與使用視窗函式的解決方案相比，連接GROUP BY結果（或LATERAL變體）的解決方案很可能更有效。這在很大程度上取決於您的數據分佈和可用的索引。您必須比較所有方法的執行計劃。

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