如何結合聚合函式應用 ORDER BY 和 LIMIT？

January 29, 2019

我的問題可以在https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685上找到。
我有一個簡單的表格佈局：
class
person: belongs to a class
我想選擇所有類，對於每個類，我想要按降序排列的所屬人員的前兩個人員標識符。
我通過以下查詢解決了這個問題：
select     c.identifier, array_agg(p.identifier order by p.name desc) as persons
from       class as c
left join lateral (
            select   p.identifier, p.name
            from     person as p
            where    p.class_identifier = c.identifier
            order by p.name desc
            limit    2
          ) as p
on         true
group by   c.identifier
order by   c.identifier
注意：我本可以在SELECT子句中使用相關子查詢，但作為學習過程的一部分，我試圖避免這種情況。
如您所見，我order by p.name desc在兩個地方申請：
在子查詢中
在聚合函式中
有沒有辦法避免這種情況？我的火車：
首先，顯然我不能刪除order by子查詢中的，因為這會給出一個不符合我上述要求的查詢。
其次，我認為order by聚合函式中的不能省略，因為子查詢的行順序不一定保留在聚合函式中？
我應該重寫查詢嗎？

我order by p.name desc在兩個地方申請……有沒有辦法避免這種情況？
是的。直接在橫向子查詢中與ARRAY 建構子聚合：
SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
  SELECT ARRAY (
     SELECT identifier
     FROM   person
     WHERE  class_identifier = c.identifier
     ORDER  BY name DESC
     LIMIT  2
     ) AS persons
  ) p
ORDER  BY c.identifier;
你也不需要GROUP BY在外面SELECT這樣。更短，更清潔，更快。
我LEFT JOIN用一個普通的替換了，CROSS JOIN因為 ARRAY 建構子總是返回正好 1 行。（就像你在評論中指出的那樣。）
db<>在這裡擺弄。
有關的：
加入後保留數組元素的順序
子查詢中的行順序
要解決您的評論：
我了解到子查詢中的行順序永遠不能保證在外部查詢中保留。
嗯，不。雖然 SQL 標準不提供任何保證，但Postgres中的保證是有限的。手冊：
預設情況下未指定此順序，但可以通過ORDER BY在聚合呼叫中編寫子句來控制，如第 4.2.7 節所示。或者，從排序的子查詢中提供輸入值通常會起作用。例如：
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
請注意，如果外部查詢級別包含附加處理（例如連接），則此方法可能會失敗，因為這可能會導致子查詢的輸出在計算聚合之前重新排序。
如果您在下一級所做的只是聚合行，則可以肯定地保證順序。是的，我們提供給 ARRAY 建構子的也是一個子查詢。那不是重點。它也適用於array_agg()：
SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
  SELECT array_agg(identifier) AS persons
  FROM  (
     SELECT identifier
     FROM   person
     WHERE  class_identifier = c.identifier
     ORDER  BY name DESC
     LIMIT  2
     ) sub
  ) p
ORDER  BY c.identifier;
但我希望 ARRAY 建構子在這種情況下更快。看：
為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？
將多對多連接的右側轉換為數組