如何正确使用 PostgreSQL 的 DISTINCT ON 和不同的 ORDER BY 子句？

理解 PostgreSQL 的 DISTINCT ON 和 ORDER BY 交互

PostgreSQL 的 DISTINCT ON 子句旨在从指定表达式中具有相同值的每组行中选择第一行。 关键点是“第一”行的选择完全取决于 ORDER BY 子句。 它们必须对齐。

一个常见的错误是将 DISTINCT ON 子句与不包含 ORDER BY 表达式的 DISTINCT ON 子句一起使用。这会导致不可预测的结果，因为数据库对“第一”行的选择变得任意。

使用 DISTINCT ON 纠正订单问题

当 DISTINCT ON 中的字段与 ORDER BY 中的前导字段不匹配时，就会出现错误。要解决此问题，请确保 ORDER BY 子句以与 DISTINCT ON 相同的表达式开头。这保证了每组中第一行的选择是一致且可预测的。

“每组最大 N”问题的替代方法

如果目标是查找每个 address_id 的最新购买（按购买日期排序），这是一个经典的“每组最大 N”查询。 这里有两个有效的解决方案：

通用 SQL 解决方案：

此方法使用子查询来查找每个 purchased_at 的最大值 address_id，然后将其连接回原始表以检索完整的行。

SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC

PostgreSQL 特定优化：

PostgreSQL 使用嵌套 DISTINCT ON 查询提供了更简洁且可能更快的解决方案：

SELECT * FROM (
  SELECT DISTINCT ON (address_id) *
  FROM purchases 
  WHERE product_id = 1
  ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC

在处理“每组最大 N”场景时，与仅依赖 DISTINCT ON 相比，这些替代方案提供了更清洁、更高效的解决方案。 它们避免了不必要的排序并提高了查询性能。

以上是如何正确使用 PostgreSQL 的 DISTINCT ON 和不同的 ORDER BY 子句？的详细内容。更多信息请关注PHP中文网其他相关文章！