3장 데이터 가공을 위한 SQL

8강 여러 개의 테이블 조작하기

2. 여러 개의 테이블을 가로로 정렬하기

• 마스터 테이블(mst_categories) 행 수를 유지한 상태로 LEFT JOIN 사용

코드 8-3

SELECT m.category_id, m.name, s.sales, r.product_id AS top_sale_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

• SELECT 구문 내부에서 서브쿼리 사용. 결과는 같다.

코드 8-4

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_sale_product
FROM mst_categories AS m

category_id	name	sales	top_sale_product
1	dvd	850000	D001
2	cd	500000	C001
3	book

3. 조건 플래그를 0과 1로 표현하기

코드 8-5

SELECT m.user_id, m.card_number
	 , count(p.user_id) AS purchase_count
	 , CASE WHEN m.card_number IS NOT NULL THEN 1 ELSE 0 END AS has_card
	 , sign(count(p.user_id)) AS has_purchased
FROM mst_users_with_card_number AS m
	LEFT JOIN purchase_log AS p ON m.user_id = p.user_id
GROUP BY m.user_id, m.card_number

SIGN( ) 함수는 0 이상 숫자는 0, 1 이상의 숫자는 1로 변환한다.

4. 계산한 테이블에 이름 붙여 재사용하기

코드 8-8

WITH product_sale_ranking AS (
	SELECT category_name, product_id, sales
		 , ROW_NUMBER() OVER(PARTITION BY category_name ORDER BY sales DESC) AS rank
    FROM product_sales
)
, mst_rank AS (
	SELECT DISTINCT rank
	FROM product_sale_ranking
)
SELECT m.rank
     , r1.product_id AS dvd
     , r1.sales AS dvd_sales
     , r2.product_id AS cd
     , r2.sales AS cd_sales
     , r3.product_id AS book
     , r3.sales AS book_sales
FROM mst_rank AS m
	LEFT JOIN product_sale_ranking AS r1 
		ON m.rank = r1.rank AND r1.category_name = 'dvd'
	LEFT JOIN product_sale_ranking AS r2 
		ON m.rank = r2.rank AND r2.category_name = 'cd'
	LEFT JOIN product_sale_ranking AS r3 
		ON m.rank = r3.rank AND r3.category_name = 'book'
ORDER BY m.rank

rank	dvd	dvd_sales	cd	cd_sales	book	book_sales
1	D001	50000	C001	30000	B001	20000
2	D002	20000	C002	20000	B002	15000
3	D003	10000	C003	10000	B003	10000
4					B004	5000

5. 유사 테이블 만들기

임의의 레코드를 가진 유사 테이블 만들기

• SELECT 구문으로 유사 테이블(mst_devices) 만들어 사용

코드 8-10

WITH mst_devices AS (
	SELECT 1 AS device_id, 'PC' AS device_name
	UNION ALL SELECT 2 AS device_id, 'SP' AS device_name
	UNION ALL SELECT 3 AS device_id, '애플리케이션' AS device_name
)
SELECT u.user_id
	 , d.device_name
FROM mst_users AS u
	LEFT JOIN mst_devices AS d ON u.register_device = d.device_id