如何解决 PostgreSQL 中的'DISTINCT ON 表达式必须匹配初始 ORDER BY 表达式”？

PostgreSQL 的 DISTINCT ON 子句简化了检索集合中每个唯一值的第一行。然而，当 DISTINCT ON 表达式与初始 ORDER BY 表达式不一致时，就会出现一个常见的陷阱。

DISTINCT ON 表达式不匹配错误

经常出现此错误：

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

结果：

<code>PG::Error: ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions</code>

解决方案：对齐DISTINCT ON和ORDER BY

PostgreSQL 文档要求 DISTINCT ON 表达式必须镜像最左边的 ORDER BY 表达式。 解决方案很简单：重新排序您的 ORDER BY 子句：

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

替代方法：绕过address_id订购

如果您需要避免在address_id之前订购，请考虑以下替代方案：

方法 1：“每组最大 N”方法

此方法可以有效地找到每个address_id的最近购买：

SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) as 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

方法2：PostGreSQL的嵌套查询

此方法使用子查询来实现所需的结果，同时保持 purchased_at 顺序：

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

当您不想在最终输出排序中优先考虑 address_id 时，这些替代方案可以提供灵活性。 他们有效地解决了“每个地址最近购买”的问题，而没有直接违反 DISTINCT ON 约束。

以上是如何解决 PostgreSQL 中的'DISTINCT ON 表达式必须匹配初始 ORDER BY 表达式”？的详细内容。更多信息请关注PHP中文网其他相关文章！