PostgreSQL DISTINCT ON与不同的ORDER BY

Question

PostgreSQL DISTINCT ON与不同的ORDER BY

我想运行此查询：

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

但我得到这个错误：

PG ::错误：错误：SELECT DISTINCT ON表达式必须与初始ORDER BY表达式匹配

添加address_id为第一个ORDER BY表达式会使错误无效，但我真的不想添加排序address_id。是否可以不通过订购address_id？

Answer 1

文件说：DISTINCT ON（expression [，...]）仅保留给定表达式求值的每组行的第一行。[...]请注意，除非使用ORDER BY确保首先显示所需的行，否则每个集合的“第一行”都是不可预测的。[...] DISTINCT ON表达式必须与最左边的ORDER BY表达式匹配。官方文件因此，您必须将address_id订单添加到订单中。或者，如果您正在寻找包含最新购买的产品的完整行，address_id并且该结果按purchased_at那时排序，则您尝试解决每组最大的N问题，可以通过以下方法解决：应该适用于大多数DBMS的一般解决方案：SELECT t1.* FROM purchases t1JOIN (     SELECT address_id, max(purchased_at) max_purchased_at    FROM purchases    WHERE product_id = 1     GROUP BY address_id) t2ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_atORDER BY t1.purchased_at DESC一个更基于PostgreSQL的解决方案基于@hkf的答案：SELECT * FROM (   SELECT DISTINCT ON (address_id) *   FROM purchases    WHERE product_id = 1   ORDER BY address_id, purchased_at DESC) tORDER BY purchased_at DESC

Answer 2

您可以在子查询中通过address_id进行排序，然后在外部查询中按所需顺序排序。SELECT * FROM      (SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*      FROM "purchases"      WHERE "purchases"."product_id" = 1 ORDER BY address_id DESC ) ORDER BY purchased_at DESC

Answer 3

一个子查询可以解决这个问题：SELECT *FROM  (     SELECT DISTINCT ON (address_id) *     FROM   purchases    WHERE  product_id = 1     ) pORDER  BY purchased_at DESC;领先的表达式ORDER BY必须与列中的列一致DISTINCT ON，因此您不能在同一列中按不同的顺序排序SELECT。ORDER BY如果要从每个集合中选择特定行，则仅在子查询中使用其他行：SELECT *FROM  (     SELECT DISTINCT ON (address_id) *     FROM   purchases    WHERE  product_id = 1     ORDER  BY address_id, purchased_at DESC  -- get "latest" row per address_id     ) pORDER  BY purchased_at DESC;如果purchased_at可以NULL，请考虑DESC NULLS LAST。