Array intersection as aggregate function for group by

debugcn 投稿 Dev

Katrine Bers

I have the following table:

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);

I want to group by person with intersect(preferences) as aggregate function. So I want the following output:

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

How should this be done in SQL? I guess I need to do something like the following, but what does the X function look like?

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

Evan Carroll

Using FILTER with ARRAY_AGG

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;

Here is one way to do it using the ARRAY constructor and 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;

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]