Cross tab
-
cross tab은 rows를 columns로 바꾸는 query (PIVOT)
-
cross tab은 aggregate가 이미 되어 있는 테이블에서만 pivot이 가능함
pivot과 동시에 aggregate해주는 기능이 없으므로 먼저 table을 aggregate한 후 crosstab에 적용해야 한다. -
from cross tab의 base가 되는 table을 생성하는 첫번째 쿼리는 from에 단순 테이블만 들어가야 작동
첫번째 쿼리 from 뒤에 subquery문이 들어가면 오류가 나며 왜 그런지는 아직 모르지만 첫번째 쿼리에 들어갈 테이블을 create as select을 이용하여 복사해 사용하면 정상적으로 작동 한다.
모듈 설치
- cross tab을 사용하기 위해서 tablefunc 모듈을 설치해야 함
CREATE EXTENSION IF NOT EXISTS tablefunc;test input
CREATE TABLE tbl (
section text
, status text
, ct integer -- "count" is a reserved word in standard SQL
);
INSERT INTO tbl VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7); -- ('C', 'Active') is missing단순한 형식 <누락된 속성에 적합하지 않음>
- crosstab(text) / 1개의 입력 매개변수 사용
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- needs to be "ORDER BY 1,2" here
) AS ct ("Section" text, "Active" int, "Inactive" int);
# output 형식
Section | Active | Inactive
---------+--------+----------
A | 1 | 2
B | 4 | 5
C | 7 | -- !!-
캐스팅하고 이름을 바꿀 필요가 없음
-
C에 대한 잘못된 결과에 유의 (첫 번째 열에 7이 채워짐)
때로는 이 동작이 바람직하지만 이 사용 사례에는 적합하지 않다. -
단순 형식은 제공된 입력 쿼리에서 세 개의 열(row_name , category , value)로 제한
-
아래의 2개 매개변수 대안과 같이 추가 열 을 위한 공간이 없습니다 .
안전한 형태
- crosstab(text, text) / 2개의 입력 매개변수 사용
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- could also just be "ORDER BY 1" here
, $$VALUES ('Active'::text), ('Inactive')$$
) AS ct ("Section" text, "Active" int, "Inactive" int);
#
Section | Active | Inactive
---------+--------+----------
A | 1 | 2
B | 4 | 5
C | | 7 -- !!-
C에 대한 올바른 결과
-
두 번째 매개변수는 끝에 있는 컬럼 정의 순서와 일치하는 속성당 하나의 행 을 반환하는 쿼리
-
종종 다음과 같이 기본 테이블에서 고유한 속성을 두번째 쿼리로 사용
'SELECT DISTINCT attribute FROM tbl ORDER BY 1'- 컬럼 정의 목록의 모든 컬럼을 입력해야 하므로(미리 정의된 변형 제외) 다음과 같이 적은 컬럼에 사용하는 것이더 효율적임
$$VALUES ('Active'::text), ('Inactive')$$)
or
$$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists- 인용을 쉽게 하기 위해 $ 인용사용
Reference
graph data scientist