加入後保留數組元素的順序

我有一個返回 CTE 的查詢，看起來像

+-----------+-------------+
|   node_id | ancestors   |
|-----------+-------------|
|         1 | []          |
|         2 | []          |
|         3 | [1]         |
|         4 | [2]         |
|         5 | [4, 2]      |
+-----------+-------------+

我想要做的是加入nodes表格並將列中的 ID 替換為表格ancestors上的另一列nodes。到目前為止，這是我的查詢：

WITH RECURSIVE tree AS (
 -- snip --
)
SELECT node.entity_id AS id,
      array_remove(array_agg(parent_nodes.entity_id), NULL) AS ancestors
FROM tree
JOIN entity.nodes AS node ON node.id = tree.node_id
LEFT OUTER JOIN entity.nodes AS parent_nodes ON parent_nodes.id = ANY(tree.ancestors)
GROUP BY node.id;

該查詢的問題是失去了原始ancestors數組的順序。有沒有辦法在array_agg函式期間保持原始順序的同時執行連接？

您的查詢的問題是連接條件id = ANY(ancestors)。它不僅不保留原始順序，還消除了數組中的重複元素。（Anid可以匹配 10 個元素ancestors，它仍然只會被選擇一次。）不確定您的查詢邏輯是否允許重複元素，但如果允許，我很確定您想要保留所有實例 - 您想要保留 “畢竟原始訂單”。

假設目前 Postgres **9.4+**缺乏資訊，我建議採用完全不同的方法：

SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
  SELECT ARRAY (
     SELECT p.entity_id
     FROM   unnest(t.ancestors) WITH ORDINALITY a(id, ord)
     JOIN   entity.nodes p USING (id)
     ORDER  BY ord
     ) AS ancestors
  ) p ON true;

如果nodes.id定義為主鍵並且nodes.entity_id也是唯一的，您的查詢只會按預期工作。問題中缺少資訊。

通常，這個沒有顯式的簡單查詢ORDER BY也可以工作，但不能保證（Postgres 9.3+）……

SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
  SELECT ARRAY (
     SELECT p.entity_id
     FROM   unnest(t.ancestors) id
     JOIN   entity.nodes p USING (id)
     ) AS ancestors
  ) p ON true;

您也可以確保安全。詳細解釋：

• 帶有元素編號的 PostgreSQL unnest()

Postgres 9.3 的SQL Fiddle 展示。

可選優化

你加入了entity.nodes兩次——代替node_id和ancestors類似的。另一種方法是將兩者折疊成一個數組或一組並只加入一次。可能更快，但你必須測試。

對於這些替代方案，我們無論如何都需要：ORDER BY

在我們取消嵌套之前添加node_id到ancestors數組中……

SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
  SELECT ARRAY (
     SELECT p.entity_id
     FROM   unnest(t.node_id || t.ancestors) WITH ORDINALITY a(id, ord)
     JOIN   entity.nodes p USING (id)
     ORDER  BY ord
     ) AS arr
  ) p ON true;

或者添加node_id到我們加入之前的未嵌套元素ancestors…

SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
  SELECT ARRAY (
     SELECT p.entity_id
     FROM  (
        SELECT t.node_id AS id, 0 AS ord
        UNION ALL
        SELECT * FROM unnest(t.ancestors) WITH ORDINALITY
        ) x
     JOIN   entity.nodes p USING (id)
     ORDER  BY ord
     ) AS arr
  ) p ON true;

您沒有展示我們的 CTE，這可能會進一步優化…

引用自：https://dba.stackexchange.com/questions/119698