Schnittmenge mehrerer arrays in PostgreSQL

Ist die nächste Sache zu einem array Kreuzung, die ich denken kann, ist dies:

select array_agg(e)
from (
    select unnest(a1)
    intersect
    select unnest(a2)
) as dt(e)

Dies setzt Voraus, dass a1 und a2 sind single-dimension-arrays mit der gleichen Art von Elementen. Sie wickeln konnte, dass in einer Funktion etwas wie dieses:

create function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
    ret int[];
begin
    -- The reason for the kludgy NULL handling comes later.
    if a1 is null then
        return a2;
    elseif a2 is null then
        return a1;
    end if;
    select array_agg(e) into ret
    from (
        select unnest(a1)
        intersect
        select unnest(a2)
    ) as dt(e);
    return ret;
end;
$$ language plpgsql;

Dann könnten Sie tun, Dinge wie diese:

=> select array_intersect(ARRAY[2,4,6,8,10], ARRAY[1,2,3,4,5,6,7,8,9,10]);
 array_intersect 
-----------------
 {6,2,4,10,8}
(1 row)

Beachten Sie, dass dies nicht die Garantie einer bestimmten Reihenfolge im array zurückgegeben, aber Sie können das Problem beheben, dass, wenn Sie darum kümmern. Dann könnte man erstellen Sie Ihre eigene Aggregatfunktion:

-- Pre-9.1
create aggregate array_intersect_agg(
    sfunc    = array_intersect,
    basetype = int[],
    stype    = int[],
    initcond = NULL
);

-- 9.1+ (AFAIK, I don't have 9.1 handy at the moment
-- see the comments below.
create aggregate array_intersect_agg(int[]) (
    sfunc = array_intersect,
    stype = int[]
);

Und jetzt sehen wir, warum wir array_intersect macht lustig und etwas kludgey Dinge, mit Null. Wir brauchen einen anfänglichen Wert für die aggregation verhält sich wie die universal gesetzt und wir können mit NULL für, die (ja, das riecht ein bisschen off, aber ich kann nicht an nichts denken besser aus der Spitze von meinem Kopf).

Einmal alle in diesem Ort, die Sie tun können, Dinge wie diese:

> select * from stuff;
    a    
---------
 {1,2,3}
 {1,2,3}
 {3,4,5}
(3 rows)

> select array_intersect_agg(a) from stuff;
 array_intersect_agg 
---------------------
 {3}
(1 row)

Nicht ganz einfach oder effizient sein, aber vielleicht ein vernünftiger Ausgangspunkt und besser als gar nichts.

Nützliche Hinweise:

• array_agg
• create aggregate
• erstellen-Funktion
• PL/pgSQL
• unnest

Es ist etwas spät um diese Frage zu beantworten, aber vielleicht wird jemand es brauchen, also habe ich beschlossen, zu teilen etwas, was ich schrieb, Ursache nicht gefunden keine fertige Lösung für die Kreuzung eine beliebige Anzahl von arrays. So ist es hier. Diese Funktion empfängt array von arrays, wenn es nur eine einzige array-Funktion liefert erste Reihe, wenn es 2 arrays, die Funktion schneidet 2 arrays und gibt das Ergebnis, wenn es mehr ist, dass 2 arrays, die Funktion nimmt Schnittpunkt der ersten 2 arrays speichert, es in eine variable und durchläuft alle anderen arrays, schneiden sich jeweils nächste array mit gespeicherten Ergebnisse und speichert das Ergebnis in variable. wenn Ergebnis ist null, es existiert null. In die-und die-variable, die speichert array mit interagierten die Daten von der Funktion zurückgegeben.

CREATE OR REPLACE FUNCTION array_intersected(iarray bigint[][])
  RETURNS bigint[] AS
$BODY$
    declare out_arr bigint[]; set1 bigint[]; set2 bigint[];
    BEGIN
        --RAISE NOTICE '%', array_length(iarray, 1);
        if array_length(iarray, 1) = 1 then
            SELECT ARRAY(SELECT unnest(iarray[1:1])) into out_arr;
        elseif array_length( iarray, 1) = 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
        elseif array_length(iarray, 1) > 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            --exit if no common numbers exists int 2 first arrays
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
            if out_arr = NULL then
                EXIT;
                END IF;
            FOR i IN 3 .. array_upper(iarray, 1)
            LOOP
               set1 := iarray[i:i];
               SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(out_arr))into out_arr;
               if out_arr = NULL then
                EXIT;
                   END IF;
            END LOOP;
        end if;

    return out_arr;

    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE;

Hier ist der code für die Validierung funktioniert es.

select array_intersected(array[[1, 2]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3], [2, 4]]::bigint[][]);

select array_intersected(array[[1, 2, 3, 4],[null, null, 4, 3], [3, 1, 4, null]]::bigint[][]);