Traversing an array as an aggregate function for a group
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 group by person
with intersect(preferences)
as an aggregated function. So I want to get the following result:
person | preferences
-------------------------------
John | ['pizza']
Bill | []
How do I do this in SQL? I guess I need to do something like the following, but what does the function look like X
?
SELECT person.name, array_agg(X)
FROM person
LEFT JOIN unnest(preferences) preferences
ON true
GROUP BY name
source to share
Using FILTER
withARRAY_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's one way to do it using the constructor ARRAY
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;
source to share
You can create your own aggregate function:
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)
source to share
If writing a custom aggregate (like @LaurenzAlbe) is not an option for you, you can usually register the same logic in a recursive 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
The main idea here is to find an order where you can "walk" through your lines. I used natural array order preferences
(because there aren't many of your columns showing). Ideally, this ordering should be done on (a) a unique field (preferably on the primary key), but here, since duplication in the column preferences
doesn't affect the intersection result, that's good enough.
source to share