PostgreSQL：有效地将数组列聚合为组的一部分

我们希望对表执行 GROUP BY 操作。原始表包含一个 ARRAY 列。在一个组内，这些数组的内容应转换为具有唯一元素的单个数组。这些元素不需要排序。包含 最新的 PostgreSQL 版本可用。

原始表示例：

  id | fruit  | flavors               
---: | :----- | :---------------------
     | apple  | {sweet,sour,delicious}
     | apple  | {sweet,tasty}         
     | banana | {sweet,delicious}    

期望结果示例：

count_total | aggregated_flavors          
----------: | :---------------------------
          1 | {delicious,sweet} 
          2 | {sour,tasty,delicious,sweet}

创建原始表的 SQL 玩具代码：

CREATE TABLE example(id int, fruit text, flavors text ARRAY);
INSERT INTO example (fruit, flavors)
VALUES ('apple', ARRAY [ 'sweet','sour', 'delicious']),
       ('apple', ARRAY [ 'sweet','tasty' ]),
       ('banana', ARRAY [ 'sweet', 'delicious']);

我们提出了一个解决方案，需要将数组转换为 s

SELECT COUNT(*) AS count_total,
       array
  (SELECT DISTINCT unnest(string_to_array(replace(replace(string_agg(flavors::text, ','), '{', ''), '}', ''), ','))) AS aggregated_flavors
FROM example
GROUP BY fruit

然而我们认为这不是最佳的，并且可能会有问题，因为我们假设字符串既不包含“{”、“}”也不包含“,”。感觉必须有函数可以按照我们需要的方式组合数组，但我们找不到它们。

非常感谢大家！

假设每条记录都包含一个唯一的id value:

SELECT
    fruit,
    array_agg(DISTINCT flavor),      -- 2
    COUNT(DISTINCT id)               -- 3
FROM
    example,
    unnest(flavors) AS flavor        -- 1
GROUP BY fruit

1. unnest()数组元素
2. 通过...分组fruit value: array_agg()对于不同的flavors
3. 通过...分组fruit value: COUNT()对于不同的id与每个fruit group.

if the id列确实是空的，您可以生成id值例如row_number()窗函数 https://www.postgresql.org/docs/current/tutorial-window.html:

SELECT
    * 
FROM (
    SELECT
        *, row_number() OVER () as id
    FROM example
) s,
unnest(flavors) AS flavor