按组聚合的数组交集作为聚合函数

Question

我有以下表格:

CREATE TABLE person
AS
  SELECT name, preferences
  FROM ( VALUES
    ( 'John', ARRAY['pizza', 'meat'] ),
    ( 'John', ARRAY['pizza', 'spaghetti'] ),
    ( 'Bill', ARRAY['lettuce', 'pizza'] ),
    ( 'Bill', ARRAY['tomatoes'] )
  ) AS t(name, preferences);

我希望使用intersect(preferences)作为聚合函数，按照person分组。因此，我需要如下输出：

person | preferences
-------------------------------
John   | ['pizza']
Bill   | []

这个在SQL中应该怎么做呢？我猜我需要像下面一样做些什么，但是 X 函数是什么样子的呢？

SELECT    person.name, array_agg(X)
FROM      person
LEFT JOIN unnest(preferences) preferences
ON        true
GROUP BY  name

- Katrine Bers

---

@VaoTsun 我认为这是个好主意，但我该如何与那个连接进行交集操作（然后应用 array_agg）？ - Katrine Bers

数组中有重复值的可能性吗？ - Oto Shavadze

@OtoShavadze 不，该数组不包含重复项和null。 - Katrine Bers

没错 :) 我还在琢磨这个。 - Katrine Bers

您请求的输出是否正确？如果按人员分组，则我认为Bill的array_agg(preferences)应该是['西红柿']。如果在选择中没有group by，则array_agg(preferences) = []。 - Bampfer

Answer 1

您可以创建自己的聚合函数：

CREATE OR REPLACE FUNCTION arr_sec_agg_f(anyarray, anyarray) RETURNS anyarray
   LANGUAGE sql IMMUTABLE AS
   'SELECT CASE
              WHEN $1 IS NULL
              THEN $2
              WHEN $2 IS NULL
              THEN $1
              ELSE array_agg(x)
           END
    FROM (SELECT x FROM unnest($1) a(x)
          INTERSECT
          SELECT x FROM unnest($2) a(x)
         ) q';

CREATE AGGREGATE arr_sec_agg(anyarray) (
   SFUNC = arr_sec_agg_f(anyarray, anyarray),
   STYPE = anyarray
);

SELECT name, arr_sec_agg(preferences)
FROM person
GROUP BY name;

┌──────┬─────────────┐
│ name │ arr_sec_agg │
├──────┼─────────────┤
│ John │ {pizza}     │
│ Bill │             │
└──────┴─────────────┘
(2 rows)

- Laurenz Albe

---

很好，我不知道那是可能的。由于我目前无法更改模式，我将继续搜索查询。在这种情况下我该怎么办？接受这个问题并再次提出问题，注明我不能创建自己的函数，因此正在寻找一个简单的查询？ - Katrine Bers

你已经得到了另外两个答案，其中一个被你接受为正确答案，那么为什么不采用这些答案呢？ - Laurenz Albe

Answer 2

使用 ARRAY_AGG 和 FILTER

SELECT name, array_agg(pref) FILTER (WHERE namepref = total)
FROM (
  SELECT name, pref, t1.count AS total, count(*) AS namepref
  FROM (
    SELECT name, preferences, count(*) OVER (PARTITION BY name)
    FROM person
  ) AS t1
  CROSS JOIN LATERAL unnest(preferences) AS pref
  GROUP BY name, total, pref
) AS t2
GROUP BY name;

以下是一种使用ARRAY构造函数和DISTINCT的方法。

WITH t AS (
  SELECT name, pref, t1.count AS total, count(*) AS namepref
  FROM (
    SELECT name, preferences, count(*) OVER (PARTITION BY name)
    FROM person
  ) AS t1
  CROSS JOIN LATERAL unnest(preferences) AS pref
  GROUP BY name, total, pref
)
SELECT DISTINCT
  name,
  ARRAY(SELECT pref FROM t AS t2 WHERE total=namepref AND t.name = t2.name)
FROM t;

- Evan Carroll

---

这将不会计算数组的交集，而是生成一个包含所有出现多次的偏好的数组。尝试使用这三个记录：('Paul', ARRAY['pizza', 'meat'])、('Paul', ARRAY['pizza', 'salad'])和('Paul', ARRAY['salad', 'beer'])。结果应该为空，但您的查询将生成{pizza,salad}。 - Laurenz Albe

@LaurenzAlbe 已修复。 - Evan Carroll

除非存在包含相同值的数组，否则这将起作用。 - Laurenz Albe

Answer 3

如果编写自定义聚合函数（例如@LaurenzAlbe提供的）不是您的选择，您通常可以将相同的逻辑纳入递归CTE中：

with recursive cte(name, pref_intersect, pref_prev, iteration) as (
    select   name,
             min(preferences),
             min(preferences),
             0
    from     your_table
    group by name
  union all
    select   name,
             array(select e from unnest(pref_intersect) e
                   intersect
                   select e from unnest(pref_next) e),
             pref_next,
             iteration + 1
    from     cte,
    lateral  (select   your_table.preferences pref_next
              from     your_table
              where    your_table.name        = cte.name
              and      your_table.preferences > cte.pref_prev
              order by your_table.preferences
              limit    1) n
)
select   distinct on (name) name, pref_intersect
from     cte
order by name, iteration desc

http://rextester.com/ZQMGW66052

这里的主要思想是找到一种顺序，可以“遍历”您的行。我使用了preferences数组的自然排序（因为没有显示太多的列）。理想情况下，这种排序应该发生在一个或多个唯一字段上（最好是主键），但是在这里，由于preferences列中的重复不会影响交集的结果，所以这已经足够好了。

- pozs