如何使用 PostgreSQL 的 DISTINCT ON 子句來找出每個群組中的第一行？

使用PostgreSQL的DISTINCT ON子句查找每個分組中的第一行

簡介

處理分組數據時，通常需要提取每個分組中的第一行。這項任務可以使用DISTINCT ON子句來完成，這是一個PostgreSQL特有的擴展功能。

查詢結構

<code class="language-sql">SELECT DISTINCT ON (grouping_expression)
       selected_columns
FROM   table_name
ORDER  BY grouping_expression, additional_ordering_expression(s)</code>

grouping_expression: 用於定義分組的表達式。此子句中的前導表達式必須與後續的ORDER BY子句中的表達式匹配。

selected_columns: 要在輸出中檢索的列。

additional_ordering_expression(s): 用於確定每個分組中行順序的可選表達式。這些表達式必須在ORDER BY子句中跟在分組表達式之後。

示例

考慮具有以下模式的purchases表：

<code class="language-sql">CREATE TABLE purchases (
    id SERIAL PRIMARY KEY,
    customer VARCHAR(50) NOT NULL,
    total NUMERIC(10, 2) NOT NULL
);</code>

以及以下數據：

<code class="language-sql">INSERT INTO purchases (customer, total) VALUES
('Joe', 5),
('Sally', 3),
('Joe', 2),
('Sally', 1);</code>

要檢索總購買額最高的客戶，請使用以下查詢：

<code class="language-sql">SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;</code>

輸出:

<code>id | customer | total
-----------------------------
1  | Joe      | 5
2  | Sally    | 3</code>

NULL值

如果分組值可能包含NULL值，建議在ORDER BY子句中添加NULLS LAST，以確保優先處理具有非NULL值的行：

<code class="language-sql">ORDER  BY customer, total DESC NULLS LAST, id;</code>

選擇其他列

SELECT列表不限於分組表達式。可以包含任何其他列，這使得此技術可用於替換複雜的子查詢或聚合/窗口函數。

性能注意事項

DISTINCT ON查詢的效率取決於數據的分佈和每個分組的行數。對於小型分組，它通常效率很高。但是，對於大型分組，以下鏈接中描述的替代技術可能會提供更好的性能：

• PostgreSQL中SELECT DISTINCT比預期慢
• 優化GROUP BY查詢以檢索每個用戶的最新行
• 查詢每行的最後N個相關行

以上是如何使用 PostgreSQL 的 DISTINCT ON 子句來找出每個群組中的第一行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！