如何使用 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中文网其他相关文章！