세로 기반 데이터를 가로 기반으로 변환하기
SQL은 행 기반으로 처리하는 것이 기본이다. 행 단위로 저장된 '세로기반'을 열 또는 쉼표로 구분된 문자열 등의 '가로 기반'으로 변환해보자.
다음과 같은 테이블이 있다.
SELECT dt
, MAX(CASE WHEN indicator = 'impressions' THEN val END) AS impressions
, MAX(CASE WHEN indicator = 'sessions' THEN val END) AS sessions
, MAX(CASE WHEN indicator = 'users' THEN val END) AS users
FROM daily_kpi
GROUP BY dt
ORDER BY dt
다음과 같은 테이블에서는 위의 방법이 유효하지 않다. 열의 수를 미리 정할 수 없기 때문이다. 따라서 이 경우에는 데이터를 쉼표 등으로 구분한 문자열로 변환하는 방법을 생각해 볼 수 있다.
SELECT purchase_id
, listagg(product_id, ', ') AS product_ids
, SUM(price) AS amount
FROM purchase_detail_log
GROUP BY purchase_id
ORDER BY purchase_id
LISTAGG : 행을 문자열로 집약하는 함수. REDSHIFT에서 사용된다. PostgreSQL 에서는 STRING_AGG 함수를 사용한다.
가로 기반 데이터를 세로 기반으로 변환하기
가로 기반 데이터를 세로 기반으로 변환하는 것은 간단한 일이 아니다. 세로 기반 데이터를 가로 기반으로 변환해서 가공이 쉬운 데이터 형식으로 만드는 방법을 알아보자.
위 테이블은 q1부터 q4까지 행으로 전개할 데이터 수가 고정되었다. 그러한 데이터 수와 같은 수의 일련 번호를 가진 피벗 테이블을 만들고 CROSS JOIN하면 된다.
SELECT q.year
, CASE WHEN p.idx = 1 THEN 'q1'
WHEN p.idx = 2 THEN 'q2'
WHEN p.idx = 3 THEN 'q3'
WHEN p.idx = 4 THEN 'q4'
END AS quarter
, CASE WHEN p.idx = 1 THEN q.q1
WHEN p.idx = 2 THEN q.q2
WHEN p.idx = 3 THEN q.q3
WHEN p.idx = 4 THEN q.q4
END AS sales
FROM quarterly_sales AS q
CROSS JOIN ( SELECT 1 AS idx
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx
UNION ALL SELECT 4 AS idx) AS p
ORDER BY year, quarter
자세한 설명도 없이 CROSS JOIN 과 UNION 구문이 나와서 이해가 안 된 부분이다. 뒤에 챕터에서 자세히 설명이 나온다고 하니... 일단 그런가보다 하고 패스.
여러 개의 테이블 조작하기
여러 개의 테이블을 세로로 결합하기
위와 같은 2개의 테이블이 있다.
SELECT 'app1' AS app_name
, user_id
, name
, email
FROM app1_mst_users
UNION ALL
SELECT 'app2' AS app_name
, user_id
, name
, NULL AS email
FROM app2_mst_users
이처럼 비슷한 구조를 가지는 테이블의 데이터를 일괄 처리하고 싶은 경우, UNION ALL 구문을 사용해 여러 개의 테이블을 세로로 결합하면 된다. 결합할 때는 테이블의 컬럼이 완전히 일치해야 하므로, 한쪽 테이블에만 존재하는 컬럼은 SELECT구문에서 포함시키지 않거나, email 컬럼처럼 디폴트 값을 주면 된다.
UNION ALL 구문 대신 UNION 또는 UNION DISTINCT 구문을 사용하면 데이터의 중복을 제외한 결과를 얻을 수 있다.
여러 개의 테이블을 가로로 정렬하기
다음과 같은 3개의 테이블이 있다.
맨 위의 테이블이 마스터 테이블이다. 다음 코드 예는 마스터 테이블의 행 수를 유지한 상태로 여러 개의 테이블을 가로롤 정렬하는 쿼리이다.
SELECT m.category_id
, m.name
, s.sales
, r.product_id AS top_sales_product
FROM mst_categories AS m
LEFT JOIN category_sales AS s
ON m.category_id = s.category_id
LEFT JOIN product_sale_ranking AS r
ON m.category_id = r.category_id
AND r.rank = 1
JOIN : 서로 다른 테이블을 결합해준다. ON을 이용해 JOIN 구문의 조건을 줄 수 있다.
SELECT m.category_id
, m.name
, (SELECT s.sales
FROM category_sales AS s
WHERE m.category_id = s.category_id) AS sales
, (SELECT r.product_id
FROM product_sale_ranking AS r
WHERE m.category_id = r.category_id
ORDER BY sales DESC
LIMIT 1) AS top_sales_product
FROM mst_categories AS m
ORDER BY 1
위 예시는 서브 쿼리를 사용해서 이전의 코드와 같은 결과를 내는 코드를 만든 것이다. JOIN을 사용하지 않아 원래 마스터 테이블의 행 수가 변할 걱정 자체가 없으므로, 테이블의 누락과 중복을 회피할 수 있다.
