tengo la siguiente tabla:
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); Quiero group by person con intersect(preferences) como función agregada. Así que quiero el siguiente resultado:
person | preferences ------------------------------- John | ['pizza'] Bill | [] ¿Cómo se debe hacer esto en SQL? Supongo que necesito hacer algo como lo siguiente, pero ¿cómo se ve la función X ?
SELECT person.name, array_agg(X) FROM person LEFT JOIN unnest(preferences) preferences ON true GROUP BY namePodrías crear tu propia función agregada:
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)Usando FILTER con 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; Aquí hay una forma de hacerlo usando el constructor ARRAY y 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;Si escribir un agregado personalizado (como el proporcionado por @LaurenzAlbe) no es una opción para usted, generalmente puede inscribir la misma lógica en un CTE recursivo :
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 deschttp://rextester.com/ZQMGW66052
La idea principal aquí es encontrar un orden en el que pueda "caminar" a través de sus filas. Usé el orden natural de la matriz de preferences (porque no se muestran muchas de sus columnas). Idealmente, este orden debería ocurrir en (a) campo(s) único(s) (preferiblemente en la clave principal), pero aquí, debido a que las duplicaciones en la columna de preferences no influyen en el resultado de la intersección, es suficiente.