[postgreSQL] 테이블을 pivot해 보자! (crosstab 사용하기)

"CROSSTAB" 이란??

- 데이터베이스(postgreSQL)에서 피벗 테이블을 생성하는데 사용되는 함수입니다. 이는 데이터를 요약하고 행과 열을 재구성하여 특정 값에 따라 데이터를 집계할수 있는 강력한 도구입니다.

[ crosstab 쿼리의 구성요소 ]

SELECT *
FROM crosstab(
    $$<source_sql_query>$$,
    $$<category_sql_query>$$
) AS ct(<output_column_definitions>);

crosstab 함수:
- 'source_sql_query' : 피벗 테이블의 행과 열을 구성할 데이터 쿼리.
- 'category_sql_query' : 각 행에 대해 생성할 열의 이름을 결정하는 쿼리 (선택사항).
AS ct :
- 'AS' 키워드는 결과 테이블에 별칭을 부여하는 데 사용됨.
- 'ct'는 별칭으로, 이는 테이블 별칭의 이름을 지정하는 것.
output_column_definitions:
- 결과 테이블의 열(컬럼) 이름과 데이터 타입을 정의

[ 기본 'crosstab' 사용법 ]

- 먼저, 예제로 사용할 데이터를 준비해보자

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product TEXT,
    sales_month TEXT,
    amount INT
);

INSERT INTO sales (product, sales_month, amount) VALUES
('Product A', 'January', 100),
('Product A', 'February', 150),
('Product B', 'January', 200),
('Product B', 'February', 250);

생성한 테이블을 출력하면 아래와 같은 형태를 띈다.

id	product	sales_month	amount
1	Product A	January	100
2	Product A	February	150
3	Product B	January	200
4	Product B	February	250

이 테이블의 값은 아래와 같은 데이터를 나타낸다

id : 각 레코드의 고유 식별자.
product : 판매된 제품의 이름.
sales_month : 판매가 이루어진 월.
amount : 해당 월에 판매된 금액.

이 테이블을 피벗해보자!

SELECT *
FROM crosstab(
    $$SELECT product, sales_month, amount FROM sales ORDER BY 1,2$$
) AS ct(product TEXT, january INT, february INT);

위에 작성된 피봇 쿼리는 'sales' 테이블에서 제품 별로 월별 판매량을 요약한 결과를 생성한다.

'crosstab' 함수의 매개변수로는 데이터를 선택하는 sql 쿼리를 제공한다. 결과는 각 제품에 대한 월별 판매량을 보여주는 피법 테이블이 된다.

product	january	february
Product A	100	150
Product B	200	250

Q1. 컬럼 A, B, C, D, E 중 A와 B는 기존 테이블과 같이 출력 나머지 C,D,E 컬럼만 피벗하려면 어떻게 작성해야 하나요?

A.

[일부 컬럼만 피벗하기 예제]

1) 예제 테이블 생성하기

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    region TEXT,
    product TEXT,
    sales_month TEXT,
    amount INT,
    discount INT
);

INSERT INTO sales (region, product, sales_month, amount, discount) VALUES
('North', 'Product A', 'January', 100, 5),
('North', 'Product A', 'February', 150, 10),
('North', 'Product B', 'January', 200, 15),
('North', 'Product B', 'February', 250, 20),
('South', 'Product A', 'January', 110, 7),
('South', 'Product A', 'February', 160, 12),
('South', 'Product B', 'January', 210, 17),
('South', 'Product B', 'February', 260, 22);

1-1) 피벗 전 테이블

id	region	product	sales_month	amount	discount
1	North	Product A	January	100	5
2	North	Product A	February	150	10
3	North	Product B	January	200	15
4	North	Product B	February	250	20
5	South	Product A	January	110	7
6	South	Product A	February	160	12
7	South	Product B	January	210	17
8	South	Product B	February	260	22

2) 피벗하기!

SELECT *
FROM crosstab(
    $$SELECT region, product, sales_month, amount, discount FROM sales ORDER BY 1, 2, 3$$,
    $$VALUES ('January'::text), ('February'::text)$$
) AS ct(
    region TEXT,
    product TEXT,
    january_amount INT,
    january_discount INT,
    february_amount INT,
    february_discount INT
);

첫 번째 쿼리 'SELECT region, product, sales_month, amount, discount FROM sales ORDER BY 1, 2, 3'는 데이터를 선택하고 정렬
두 번째 쿼리 'VALUES ('January'::text), ('February'::text)'는 피벗할 열의 이름을 정의
'AS ct(region TEXT, product TEXT, january_amount INT, january_discount INT, february_amount INT, february_discount INT)' 는 결과 테이블의 스키마를 정의

2-1) 피벗 후 출력 테이블

region	product	january_amount	january_discount	february_amount	february_discount
North	Product A	100	5	150	10
North	Product B	200	15	250	20
South	Product A	110	7	160	12
South	Product B	210	17	260	22

이 쿼리는 region과 product 열을 그대로 두고 sales_month, amount, discount 열을 피벗하여 월별 금액과 할인을 요약한 결과를 제공한다.

Q1-1. 피벗할 컬럼은 어디서 지정하는 건가요?? Values 절에서 지정하는 건가요??

A. 피벗할 열의 값은 'crosstab' 함수의 두 번째 매개변수(피벗할 열의 값을 지정하는 쿼리)에서 지정한다. 첫번째 매개변수는 피벗할 데이터를 포함하는 쿼리이고, 두 번째 매개변수는 피벗할 열의 고유한 값을 나열하여 각 값을 열로 변환한다.

마지막으로 'AS' 절에서 결과 스키마를 정의한다.

예제를 통해 살펴보자

피벗할 쿼리 작성

SELECT region, product, to_char(sale_date, 'YYYY-MM') as formatted_date, amount, discount
FROM sales
ORDER BY region, product, formatted_date;

피벗할 열의 값을 지정 (두 번째 매개변수)

: 피벗할 열의 고유한 값(여기서는 formatted_date 값)을 나열한다. 이 예제에서는 '2024-01'과 '2024-02'를 피벗한다.

VALUES ('2024-01'), ('2024-02')

결과 스키마 정의

: 결과 스키마 정의에서 AS ct를 사용하여 결과 테이블의 구조를 명시한다. 피벗된 결과 열을 정의한다.

SELECT *
FROM crosstab(
    $$SELECT region, product, to_char(sale_date, 'YYYY-MM') as formatted_date, amount, discount FROM sales ORDER BY 1, 2, 3$$,
    $$VALUES ('2024-01'), ('2024-02')$$
) AS ct(
    region TEXT,
    product TEXT,
    "2024-01_amount" INT,
    "2024-01_discount" INT,
    "2024-02_amount" INT,
    "2024-02_discount" INT
);

최종 쿼리 및 설명

첫 번째 매개변수 : 피벗할 데이터를 포함하는 쿼리이다. 여기서는 region, product, formatted_date, amount, discount을 선택
두 번째 매개변수: 피벗할 열의 고유한 값을 나열한다. VALUES ('2024-01'), ('2024-02')는 formatted_date 열의 고유한 값을 정의
결과 스키마 정의: AS ct(region TEXT, product TEXT, "2024-01_amount" INT, "2024-01_discount" INT, "2024-02_amount" INT, "2024-02_discount" INT)는 결과 테이블의 구조를 명시

[피벗 후 결과]

region	product	2024-01_amount	2024-01_discount	2024-02_amount	2024-02_discount
North	Product A	100	5	150	10
North	Product B	200	15	250	20
South	Product A	110	7	160	12
South	Product B	210	17	260	22

이 구조에서 region과 product는 그대로 유지되고, formatted_date의 값에 따라 amount와 discount가 각각 피벗된 열로 변환된다.

Q1-2. values에서 지정산 '2024-01'이 어떻게 as이후 의 스키마 지정때 '2024-01_amount' 컬럼에 어떻게 인식이 된걸까요?

A.

SELECT *
FROM crosstab(
    $$SELECT region, product, to_char(sale_date, 'YYYY-MM') as formatted_date, amount, discount FROM sales ORDER BY 1, 2, 3$$,
    $$VALUES ('2024-01'), ('2024-02')$$
) AS ct(
    region TEXT,
    product TEXT,
    "2024-01_amount" INT,
    "2024-01_discount" INT,
    "2024-02_amount" INT,
    "2024-02_discount" INT
);

두 번째 매개변수 (VALUES ('2024-01'), ('2024-02')):
- 여기서 VALUES 절에 있는 각 값은 결과 테이블의 열 이름으로 사용된다. 즉, '2024-01'은 "2024-01_amount"와 "2024-01_discount" 열에 매핑.
결과 스키마 정의 (AS ct(...)):
- AS ct(...) 부분에서는 결과 테이블의 각 열 이름과 데이터 타입을 명시한다. 이 예제에서는 "2024-01_amount", "2024-01_discount", "2024-02_amount", "2024-02_discount" 등의 열 이름을 정의하여 crosstab 함수가 데이터를 피벗할 때 해당 값을 열로 매핑한다.

따라서 values에서 지정한 '2024-01' 값은 crosstab 함수가 피벗할 결과 테이블의 열 이름으로 사용되며, 그에 따라 데이터가 해당 열에 맞게 피벗되어 들어가게 된다.

[중요]

crosstab 함수에서 AS 절 다음에 작성되는 컬럼명들은 반드시 두 번째 매개변수인 VALUES 절에 작성된 값들과 일치하는 열 이름으로 구성되어야 한다. 이는 crosstab 함수가 결과를 피벗할 때 각 값에 해당하는 열을 생성하고, 그 값을 해당 열에 매핑하기 때문에 중요하다.

Q2. 결과 테이블의 스키마 정의 시 데이터 타입 지정을 생략해도 되나요?

A. 생략 불가능!

결과 테이블의 스키마 정의는 crosstab 함수에서 필수적이다. 이는 PostgreSQL이 반환되는 데이터를 올바르게 해석하고 구조화하는 데 필요하다. 따라서 데이터 타입을 생략할 수 없다.

Q3. 피벗 전 쿼리에 TO_CHAR(DATE, 'YYYY-MM-DD') AS DATE 와 같이 작은 따옴표가 포함되어 있으면 어떻게 작성해야 해??

A. 예시를 보자

[피벗 전 테이블]

id	region	product	sale_date	amount
1	North	Product A	2024-01-01	100
2	North	Product A	2024-02-01	150
3	North	Product B	2024-01-01	200
4	North	Product B	2024-02-01	250
5	South	Product A	2024-01-01	110
6	South	Product A	2024-02-01	160
7	South	Product B	2024-01-01	210
8	South	Product B	2024-02-01	260

[피벗쿼리]

첫 번째 쿼리에서 'to_char' 함수를 사용하여 날짜를 포맷하고, 이 결과를 'formatted_date' 로 명명
두 번째 매개변수에서 피벗할 값을 지정
결과 스키마 정의에서 'formatted_date'와 일지하는 열 이름을 사용

SELECT *
FROM crosstab(
    $$SELECT region, product, to_char(sale_date, 'YYYY-MM') as formatted_date, amount FROM sales ORDER BY 1, 2, 3$$,
    $$VALUES ('2024-01'), ('2024-02')$$
) AS ct(
    region TEXT,
    product TEXT,
    "2024-01" INT,
    "2024-02" INT
);

첫 번째 쿼리: SELECT region, product, to_char(sale_date, 'YYYY-MM') as formatted_date, amount FROM sales ORDER BY 1, 2, 3에서 to_char(sale_date, 'YYYY-MM')을 사용하여 sale_date를 포맷하고 이를 formatted_date로 명명
두 번째 매개변수: $$VALUES ('2024-01'), ('2024-02')$$에서 피벗할 값으로 '2024-01'과 '2024-02'를 지정
결과 스키마 정의: AS ct(region TEXT, product TEXT, "2024-01" INT, "2024-02" INT)에서 formatted_date 값에 해당하는 열 이름을 큰따옴표로 묶어 정확히 일치시키고 데이터 타입을 지정

[피벗 후 테이블]

region	product	2024-01	2024-02
North	Product A	100	150
North	Product B	200	250
South	Product A	110	160
South	Product B	210	260

이 예제에서는 to_char 함수로 포맷된 날짜(formatted_date)가 피벗 테이블의 열 이름으로 사용되었고, 결과 스키마 정의에서 해당 열 이름을 정확히 명시했다. 중요한 점은, 열 이름이 숫자나 특수 문자를 포함할 때 큰따옴표를 사용하여 정확히 일치시켜야 한다는 것이다.

Q3-1. SELECT 앞에 $$는 어떤 의미로 붙는거야?

A.

PostgreSQL에서 crosstab 함수를 사용할 때, 일반적으로 첫 번째와 두 번째 매개변수에 사용되는 SQL 쿼리를 문자열로 전달해야 한다. 이때, 문자열을 안전하게 전달하기 위해 '$$' 기호를 사용하여 복잡한 문자열이나 내부에 작은 따옴표가 포함된 문자열을 처리할 수 있다.
'$$'는 ProgreSQL의 Dollar-Quated String 기능이다. 이는 문자열 리터럴을 작은따옴표 없이 묶을 수 있는 방법으로, 문자열 내에 작은따옴표를 포함할 때 유용하다.
- 문자열 내 작은따옴표 사용 : 쿼리 문자열 내에 작은따옴표를 포함할 수 있다.
- 가독성 : 긴 쿼리 문자열을 작은따옴표 없이 더 읽기 쉽게 작성할 수 있다.
- 안전성 : 작은따옴표로 인한 혼동을 피할 수 있다.

저작자표시

'Database' 카테고리의 다른 글

[DBeaver] CVS파일로 가져오기 할 때 한글 깨짐 (0)	2022.12.08
[SQL] SQL 기초(2) (0)	2022.09.02
[SQL] SQL의 기초(1) (0)	2022.08.23
[SQL] Data base 개요 (0)	2022.08.23

바닥부터 올라가는 개발의 산

[postgreSQL] 테이블을 pivot해 보자! (crosstab 사용하기)

"CROSSTAB" 이란??

[ crosstab 쿼리의 구성요소 ]

[ 기본 'crosstab' 사용법 ]

Q1. 컬럼 A, B, C, D, E 중 A와 B는 기존 테이블과 같이 출력 나머지 C,D,E 컬럼만 피벗하려면 어떻게 작성해야 하나요?

A.

Q1-2. values에서 지정산 '2024-01'이 어떻게 as이후 의 스키마 지정때 '2024-01_amount' 컬럼에 어떻게 인식이 된걸까요?

A.

Q2. 결과 테이블의 스키마 정의 시 데이터 타입 지정을 생략해도 되나요?

A. 생략 불가능!

Q3. 피벗 전 쿼리에 TO_CHAR(DATE, 'YYYY-MM-DD') AS DATE 와 같이 작은 따옴표가 포함되어 있으면 어떻게 작성해야 해??

A. 예시를 보자

Q3-1. SELECT 앞에 $$는 어떤 의미로 붙는거야?

A.

'Database' 카테고리의 다른 글

티스토리툴바

[postgreSQL] 테이블을 pivot해 보자! (crosstab 사용하기)

"CROSSTAB" 이란??

[ crosstab 쿼리의 구성요소 ]

[ 기본 'crosstab' 사용법 ]

Q1. 컬럼 A, B, C, D, E 중 A와 B는 기존 테이블과 같이 출력 나머지 C,D,E 컬럼만 피벗하려면 어떻게 작성해야 하나요?

A.

Q1-2. values에서 지정산 '2024-01'이 어떻게 as이후 의 스키마 지정때 '2024-01_amount' 컬럼에 어떻게 인식이 된걸까요?

A.

Q2. 결과 테이블의 스키마 정의 시 데이터 타입 지정을 생략해도 되나요?

A. 생략 불가능!

Q3. 피벗 전 쿼리에 TO_CHAR(DATE, 'YYYY-MM-DD') AS DATE 와 같이 작은 따옴표가 포함되어 있으면 어떻게 작성해야 해??

A. 예시를 보자

Q3-1. SELECT 앞에 $$는 어떤 의미로 붙는거야?

A.

'Database' 카테고리의 다른 글

관련글

티스토리툴바