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。