[SQL] PostgreSQL를 9.3 : 동적 피벗 테이블

PostgreSQL를 9.3 : 동적 피벗 테이블

나는 즉 두 개의 열 콜라와 COLB 등을 포함 매트릭스라는 테이블이 아래 그림 :

테이블 : 매트릭스

create table matrix
(
cola varchar(10),
colb varchar(10)
);

행 삽입 :

insert into matrix values('A1','B1'),('A2','B2'),('A3','B3'),('A4','B4'),
             ('A5','B5'),('A6','B6'),('A7','B7'),('A8','B8'),
             ('A9','B9'),('A10','B10'),('A11','B11'),('A12','B12'),
             ('A13','B13'),('A14','B14'),('A15','B15'),('A16','B16'),
             ('A17','B17'),('A18','B18'),('A19','B19'),('A20','B20'),
             ('A21','B21'),('A22','B22'),('A23','B23'),('A24','B24'),
             ('A25','B25'),('A26','B26'),('A27','B27'),('A28','B28'),
             ('A29','B29'),('A30','B30');

참고 : 나는 행렬의 형태로 결과를 표시하고 서로에 속하는 열을 계산하고 각 열에 대한 행렬의 값을 할당합니다. 난 그냥이 단지 예를 들어 30 개 기록을 추가뿐만 아니라 수천 개의 레코드가있을 수 있습니다. 내가 필요 그래서 그위한 동적 피벗 테이블을 준비한다. 아래와 같이 예상 결과.

예상 결과:

      A1        A2      A3      A4      A5      A6 ................ A30
      ------------------------------------------------------------------
B1 |  1         0       0       0       0       0                    0  
   |    
B2 |  0         1       0       0       0       0                    0
   |
B3 |  0         0       1       0       0       0                    0 
   |
B4 |  0         0       0       1       0       0                    0
   | 
B5 |  0         0       0       0       1       0                    0 
   | 
B6 |  0         0       0       0       0       1                    0
.  |
.  |
.  |
.  |
B30|  0         0       0       0       0        0                   1 

해결법

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

   1.당신은 추가 모듈 tablefunc에서 크로스 탭 ()로이 작업을 수행 할 수 있습니다

   당신은 추가 모듈 tablefunc에서 크로스 탭 ()로이 작업을 수행 할 수 있습니다

   SELECT b
        , COALESCE(a1, 0) AS "A1"
        , COALESCE(a2, 0) AS "A2"
        , COALESCE(a3, 0) AS "A3"
        , ... -- all the way up to "A30"
   FROM   crosstab(
            'SELECT colb, cola, 1 AS val FROM matrix
             ORDER  BY 1,2'
           , $$SELECT 'A'::text || g FROM generate_series(1,30) g$$
          ) AS t (b text
                , a1  int, a2  int, a3  int, a4  int, a5  int, a6  int
                , a7  int, a8  int, a9  int, a10 int, a11 int, a12 int
                , a13 int, a14 int, a15 int, a16 int, a17 int, a18 int
                , a19 int, a20 int, a21 int, a22 int, a23 int, a24 int
                , a25 int, a26 int, a27 int, a28 int, a29 int, a30 int);
   

   NULL 대신 0 일 경우, 너무, 그냥 외부 쿼리에서 SELECT *가 될 수 있습니다. 상해:

   특별한 여기 "어려움"실제 "값". 그래서 마지막 열로 1 AS 발을 추가합니다.

   (알려지지 않은 결과 형)와 완전히 동적 쿼리는 하나의 검색어에 가능하지 않다. 당신은 두 개의 쿼리가 필요합니다. 먼저 빌드 동적으로 위의 같은 문, 다음을 실행합니다. 세부:

   당신이 열 (1600)의 최대 수를 초과하는 경우 그 결과는 개별 열로 표현 될 수 없기 때문에, 고전적인 크로스 탭이 불가능하다. (또한, 인간의 눈은 거의 많은 열이있는 테이블을 읽을 수 없을 것입니다)

   hstore 또는 jsonb 같은 배열 또는 문서 유형은 대안이다. 다음은 배열을 가진 솔루션입니다 :

   SELECT colb, array_agg(cola) AS colas
   FROM  (
      SELECT colb, right(colb, -1)::int AS sortb
           , CASE WHEN m.cola IS NULL THEN 0 ELSE 1 END AS cola
      FROM        (SELECT DISTINCT colb FROM matrix) b
      CROSS  JOIN (SELECT DISTINCT cola FROM matrix) a
      LEFT   JOIN matrix m USING (colb, cola)
      ORDER  BY sortb, right(cola, -1)::int 
      ) sub
   GROUP  BY 1, sortb
   ORDER  BY sortb;
   

   당신은 단지를 0 ~ 1 개 = Y의 테이블을 원하는 경우,이 저렴가있을 수 있습니다 :

   SELECT x, array_agg((x = y)::int) AS y_arr
   FROM   generate_series(1,10) x
        , generate_series(1,10) y
   GROUP  BY 1
   ORDER  BY 1;
   

   당신이 코멘트에서 제공 한 구축 SQL 바이올린.

   참고 sqlfiddle.com 현재 배열 값의 표시를 처치 버그가있다. 나는 그것을 해결하려면이 텍스트로 캐스팅 그래서.

from https://stackoverflow.com/questions/28337765/postgresql-9-3-dynamic-pivot-table by cc-by-sa and MIT license