[SQL] PostgreSQL의 여러 배열의 교차로

PostgreSQL의 여러 배열의 교차로

내가보기로 정의가 :

 CREATE VIEW View1 AS 
 SELECT Field1, Field2, array_agg(Field3) AS AggField 
 FROM Table1 
 GROUP BY Field1, Field2;

내가 뭘하고 싶은처럼 뭔가 AggField의 배열의 교회법을 얻을 수 있습니다 :

SELECT intersection(AggField) FROM View1 WHERE Field2 = 'SomeValue';

이 모든 가능한에, 아니면 내가 원하는 것을 달성하기 위해 더 나은 방법은 무엇입니까?

해결법

1. ==============================

   1.내가 생각할 수있는 배열 교차로에 가장 가까운 것은 이것이다 :

   내가 생각할 수있는 배열 교차로에 가장 가까운 것은 이것이다 :

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

   이것은 그 A1 및 A2지지 요소의 동일한 유형의 단일 차원 배열이다. 이 같은 기능 뭔가 그것을 마무리 수 :

   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;
   

   그럼 당신은 이런 일을 할 수 있습니다 :

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

   참고이 반환 된 배열에 특정 순서를 보장하지 않습니다하지만 당신은 당신이 그것에 대해 걱정하는 경우 있음을 해결할 수 있습니다. 그런 다음 당신은 당신의 자신의 집계 함수를 만들 수 있습니다 :

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

   그리고 지금 우리는 array_intersect가 null로 재미와 약간 kludgey 일을하는 이유를 참조하십시오. 우리는 전체 집합처럼 동작합니다 우리가 그것에 대해 NULL을 사용할 수는있는 통합에 대한 초기 값이 필요 (예,이 떨어져 조금 냄새를하지만 난 내 머리 위로 떨어져 더 나은 아무것도 생각할 수 없다).

   이 모든 장소에 있으면,이 같은 일을 할 수있다 :

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

   아니 정확히 단순 또는 효율적인하지만 어쩌면 합리적인 시작점과 전혀 아무것도보다 낫다.

   유용한 참조 :

2. ==============================

   2.허용 대답은 나를 위해 작동하지 않았다. 이것은 내가 그것을 해결하는 방법입니다.

   허용 대답은 나를 위해 작동하지 않았다. 이것은 내가 그것을 해결하는 방법입니다.

   create or replace function array_intersect(a1 int[], a2 int[]) returns int[] as $$
   declare
     ret int[];
   begin
     -- RAISE NOTICE 'a1 = %', a1;
     -- RAISE NOTICE 'a2 = %', a2;
     if a1 is null then
       -- RAISE NOTICE 'a1 is null';
       return a2;
     -- elseif a2 is null then
     --    RAISE NOTICE 'a2 is null';
     --    return a1;
     end if;
     if array_length(a1,1) = 0 then
       return '{}'::integer[];
     end if;
     select array_agg(e) into ret
     from (
       select unnest(a1)
       intersect
       select unnest(a2)
     ) as dt(e);
     if ret is null then
       return '{}'::integer[];
     end if;
     return ret;
   end;
   $$ language plpgsql;
   

3. ==============================

   3.그것은이 질문에 대한 답을 조금 늦게하지만 난 원인은 배열의 숫자의 교차로에 대한 모든 준비가 해결책을 발견하지 않았다 썼다 주 뭔가하기로 결정 그래서 아마 누군가가 필요합니다. 그래서 여기 있습니다. 더 그 2 개 배열 인 경우 2 개 어레이는 2 개 어레이 및 리턴 결과 교차가 존재 함수 인 경우 단지 하나의 배열, 함수 반환 제 배열 인 경우,이 함수는, 배열의 어레이를 수신하고, 함수는 점포를 2 개 제 배열 교차 소요 다른 모든 배열을 통해 어떤 변수 및 루프에 저장된 결과 다음의 각 배열을 교차 저장 변수 초래한다. 결과가 null 인 경우는 null를 존재한다. 앤드 상호 작용 된 데이터를 저장하는 배열 함수로부터 반환되는 변수.

   그것은이 질문에 대한 답을 조금 늦게하지만 난 원인은 배열의 숫자의 교차로에 대한 모든 준비가 해결책을 발견하지 않았다 썼다 주 뭔가하기로 결정 그래서 아마 누군가가 필요합니다. 그래서 여기 있습니다. 더 그 2 개 배열 인 경우 2 개 어레이는 2 개 어레이 및 리턴 결과 교차가 존재 함수 인 경우 단지 하나의 배열, 함수 반환 제 배열 인 경우,이 함수는, 배열의 어레이를 수신하고, 함수는 점포를 2 개 제 배열 교차 소요 다른 모든 배열을 통해 어떤 변수 및 루프에 저장된 결과 다음의 각 배열을 교차 저장 변수 초래한다. 결과가 null 인 경우는 null를 존재한다. 앤드 상호 작용 된 데이터를 저장하는 배열 함수로부터 반환되는 변수.

   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;
   

   여기에 작동의 유효성을 검사하는 코드입니다.

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

from https://stackoverflow.com/questions/7020264/intersection-of-multiple-arrays-in-postgresql by cc-by-sa and MIT license