使用 array_agg() 選擇特定的數組位置？

有沒有辦法根據其他列的資訊在數組內的特定位置設置值？（Postgres 9.3 或更高版本。）

例如，我想從下表中選擇一個項目及其庫存資訊：

表item：

CREATE TABLE item (
 id integer NOT NULL
);

INSERT INTO item VALUES
(1), (2), (3), (4);

表格item_stock（包含特定商店的資訊，如庫存和價格）：

CREATE TABLE item_stock (
   item_id integer NOT NULL,
   shop_id integer NOT NULL,
   stock integer,
   cost numeric(19,3),
);

INSERT INTO items_stock VALUES
 (1, 1, 2, 10),
 (1, 2, 0, 9),
 (2, 2, 0, 9),
 (3, 1, 3, 22);

SQLFiddle

查找查詢以產生以下結果，其中列中的數組stock包含特定商店的庫存資訊。在範例中，數組位置 1 是stockfor shop_id=1，數組位置 2 是stockfor shop_id=2。0而不是NULL找不到數據的地方：

id | stock
---+-------
1  | {2, 0}
2  | {0, 0}
3  | {3, 0}
4  | {0, 0}

您的回答基本上可以完成工作：

SELECT b.id, array_agg(b.stock) AS stock
FROM  (
  SELECT i.id, COALESCE(i_s.stock, 0) AS stock
  FROM   item i
  CROSS  JOIN unnest('{1,2}'::int[]) n
  LEFT   JOIN item_stock i_s ON i.id = i_s.item_id AND n.n = i_s.shop_id
  ORDER  BY i.id, n.n
  ) b
GROUP  BY b.id;

兩個顯著的變化：

1. ORDER BY如果沒有在子查詢中或作為附加子句array_aggregate()（通常更昂貴） ，則無法保證順序。這就是你問題的核心要素。
2. unnest('{1,2}'::int[])而不是generate_series(1,2)要求的商店 ID 幾乎不會一直是連續的。

我還將 set-returning 函式從SELECT列表中移到了一個單獨的表表達式中，並附有CROSS JOIN. 標準 SQL 形式，但這只是清晰和品味的問題，不是必需的。至少在 Postgres 10 或更高版本中。看：

• SELECT 子句中多個集合返回函式的預期行為是什麼？

對 ARRAY 建構子執行相同LEFT JOIN LATERAL操作可能會更快一些，因為我們不需要外部GROUP BY建構子，並且 ARRAY 建構子通常也更快：

SELECT i.id, s.stock
FROM   item i
CROSS  JOIN LATERAL (
  SELECT ARRAY(
     SELECT COALESCE(i_s.stock, 0)
     FROM   unnest('{1,2}'::int[]) n
     LEFT   JOIN item_stock i_s ON i_s.shop_id = n.n
                               AND i_s.item_id = i.id
     ORDER  BY n.n
     ) AS stock
  ) s;

有關的：

• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？
• 如何結合聚合函式應用 ORDER BY 和 LIMIT？

如果你有不止兩個商店，嵌套crosstab()應該提供最佳性能：

SELECT i.id, COALESCE(stock, '{0,0}') AS stock
FROM   item i
LEFT   JOIN (
  SELECT id, ARRAY[COALESCE(shop1, 0), COALESCE(shop2, 0)] AS stock
  FROM   crosstab(
    $$SELECT item_id, shop_id, stock
      FROM   item_stock
      WHERE  shop_id = ANY ('{1,2}'::int[])
      ORDER  BY 1,2$$

    , $$SELECT unnest('{1,2}'::int[])$$
     ) AS ct (id int, shop1 int, shop2 int)
  ) i_s USING (id);

需要在更多地方進行適配，以適應不同的店鋪ID。

有關的：

• PostgreSQL 交叉表查詢

db<>在這裡擺弄

指數

確保您至少有一個索引item_stock (shop_id, item_id)- 通常由PRIMARY KEY這些列上的 a 提供。對於交叉表查詢，它也很重要shop_id。看：

• 複合索引是否也適用於第一個欄位的查詢？

添加stock為另一個索引表達式可能允許更快的僅索引掃描。在 Postgres 11 或更高版本INCLUDE中，像這樣考慮 PK 的項目：

PRIMARY KEY (shop_id, item_id) INCLUDE (stock)

但前提是你需要它很多，因為它會使索引更大一點，並且可能更容易因更新而膨脹。

• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？
• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？

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