[SQL] PostgreSQL의에서 테이블을 피벗 해제하는 방법

PostgreSQL의에서 테이블을 피벗 해제하는 방법

내가 익숙하지 않은 이대로는 포스트 그레스의 함수를 작성 어려움을 겪고 있어요. 나는이 형식과 포스트 그레스로 가져올 수있는 여러 테이블을 가지고 :

id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
 1    23     45     87     99
 2    12     31    ...

나는이 형식으로 변환해야한다 :

id | year | value
_________________
 1   1960    23
 1   1961    45
 1   1962    87
 ...
 2   1960    12
 2   1961    31
 ...

나는 이런 식으로 읽어도 기능을 상상할 것입니다 :

SELECT all-years FROM imported_table;
CREATE a new_table;
FROM min-year TO max-year LOOP
     EXECUTE "INSERT INTO new_table (id, year, value) VALUES (id, year, value)";
END LOOP;

그러나, 나는 진짜 문제이의 핵심적 견실 한 세부 사항을 작성하는 데 문제가 있습니다. 나 PHP에서 그렇게하기가 쉬울 것,하지만 난 그것의 청소기는 포스트 그레스 기능에서 직접 수행 할 것을 확신합니다.

년 (시작과 끝) 테이블에서 테이블마다 다릅니다. 그리고 가끔, 난 매 다섯 번째 년 정도 세를 가질 수 ...

해결법

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

   1.완전히 동적 버전은 동적 SQL이 필요합니다. EXECUTE와 plpgsql 기능을 사용 :

   완전히 동적 버전은 동적 SQL이 필요합니다. EXECUTE와 plpgsql 기능을 사용 :

   포스트 그레스 9.2 또는 그 이상을 위해 (측면이 구현되기 전에) :

   CREATE OR REPLACE FUNCTION f_unpivot_years92(_tbl regclass, VARIADIC _years int[])
     RETURNS TABLE(id int, year int, value int) AS
   $func$
   BEGIN
      RETURN QUERY EXECUTE '
      SELECT id
           , unnest($1) AS year
           , unnest(ARRAY["'|| array_to_string(_years, '","') || '"]) AS val
      FROM   ' || _tbl || '
      ORDER  BY 1, 2'
      USING _years;
   END
   $func$  LANGUAGE plpgsql;
   

   이상 (옆에) 포스트 그레스 9.3 이상의 경우 :

   CREATE OR REPLACE FUNCTION f_unpivot_years(_tbl regclass, VARIADIC _years int[])
     RETURNS TABLE(id int, year int, value int) AS
   $func$
   BEGIN
      RETURN QUERY EXECUTE (SELECT
        'SELECT t.id, u.year, u.val
         FROM  ' || _tbl || ' t
         LEFT   JOIN LATERAL (
            VALUES ' || string_agg(format('(%s, t.%I)', y, y), ', ')
        || ') u(year, val) ON true
         ORDER  BY 1, 2'
         FROM   unnest(_years) y
         );
   END
   $func$  LANGUAGE plpgsql;
   

   VARIADIC 소개 :

   임의 년 동안 전화 :

   SELECT * FROM f_unpivot_years('tbl', 1961, 1964, 1963);
   

   같은, 실제 배열을 전달 :

   SELECT * FROM f_unpivot_years('tbl', VARIADIC '{1960,1961,1962,1963}'::int[]);
   

   순차적 년의 긴 목록 :

   SELECT * 
   FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2014)));
   

   정기적으로 (매 5 년마다 예를 들어)와 긴 목록 :

   SELECT *
   FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));
   

   출력은 요청했다.

   이 함수는합니다 : 1. 유효한 테이블 이름 -이 ( ''낙타 ''와 같은) 다른 불법 경우 이중 인용했다. 어설 션 정확성에 객체 식별자 유형 regclass 형을 사용하고 SQL 인젝션 방어. 당신은 ( '공공. "낙타"'처럼) 모호 할 스키마 자격을 이야기 이름을 할 수 있습니다. 더:

   2. (큰 따옴표) 열 이름에 해당하는 숫자의 목록입니다. 또는 실제 배열, 키워드 VARIADIC로 시작.

   열 배열은 어떤 방식으로 정렬 할 필요는 없지만, 테이블과 열이 존재해야합니다 또는 예외가 발생합니다.

   출력은 ID와 (정수) 년으로 정렬됩니다. 당신이 년이 입력 배열의 정렬 순서에 따라 정렬 할 경우 1. 정렬 순서가 엄격하게 현재 구현에서,하지만 작동이 보장되지는 배열에 따라 BY 단지 ORDER합니다. 그것에 대해 더 많은 :

   또한 NULL 값에 대해 작동합니다.

   예제와 함께 모두 SQL 바이올린.

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

   2.PostgreSQL의 새로운 기능을 정의하거나 열 번호를 모르고하는 등의 작업에 사용할 수있는 깔끔한 JSON 기능과 같은 9.3 이벤트.

   PostgreSQL의 새로운 기능을 정의하거나 열 번호를 모르고하는 등의 작업에 사용할 수있는 깔끔한 JSON 기능과 같은 9.3 이벤트.

   SELECT id, (k).key as year, (k).value as value FROM
     (SELECT j->>'id' as id, json_each_text(j) as k
       FROM (
          SELECT row_to_json(tbl) as j FROM tbl) 
       as q)
       as r
   WHERE (k).key <> 'id';
   

   http://sqlfiddle.com/#!15/1714b/13

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

   3.병렬 unnesting는 쉬울 수 있습니다

   병렬 unnesting는 쉬울 수 있습니다

   select
       id,
       unnest(array[1960, 1961, 1962]) as year,
       unnest(array["1960", "1961", "1962"]) as value
   from (values
       (1,23,45,87), (2,12,31,53)
   ) s(id, "1960", "1961", "1962")
   ;
    id | year | value 
   ----+------+-------
     1 | 1960 |    23
     1 | 1961 |    45
     1 | 1962 |    87
     2 | 1960 |    12
     2 | 1961 |    31
     2 | 1962 |    53
   

4. ==============================

   4.가장 간단한 방법은 모든 노동 조합이다 :

   가장 간단한 방법은 모든 노동 조합이다 :

   select id, 1960 as year, "1960" as value
   from table t
   union all
   select id, '1960', "1961"
   from table t
   . . .;
   

   다소 더 정교한 방법은 다음과 같습니다

   select t.id, s.yr,
          (case when s.yr = 1960 then "1960"
                when s.yr = 1961 then "1961"
                . . .
           end) as value
   from table t cross join
        generate_series(1960, 1980) s(yr);
   

   당신은 삽입을 사용하여 다른 테이블에이를 넣어 또는으로 테이블을 만들 수 있습니다.

from https://stackoverflow.com/questions/25625342/how-to-unpivot-a-table-in-postgresql by cc-by-sa and MIT license