여러 개의 테이블에 대한 조작하기

목차

1. 마스터 테이블의 행 수를 변경하지 않고 여러 개의 테이블을 가로로 정렬하는 쿼리

 

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

 

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

 

4. 순번을 사용해 테이블 작성하기

 

 

실습에 필요한 테이블

mst_categories 테이블

product_sale_ranking 테이블

 

category_sales 테이블

mst_users_with_card_number 테이블

purchase_log 테이블

 

마스터 테이블의 행 수를 변경하지 않고 여러 개의 테이블을 가로로 정렬하는 쿼리

• 일반적으로 테이블을 가로로 정렬 할 시에 JOIN을 사용하게 되는데 이로 인해 중복데이터, 결합하지 못한 데이터가 발생 됨
• 따라서 LEFT JOIN을 사용하여 마스터 테이블은 그대로 놔두고 합치려는 테이블 데이터만 변경
• 이로 인해 테이블 누락, 중복을 회피

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;

 

 

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

• 예시로) 신용카드 번호를 포함한 마스터 테이블에 구매 로그 테이블을 결합해서 사용자들의 신용카드 번호 등록 여부, 구매 이력 여부 확인하는 경우
• 조건 플래그로 변환하는 방법 에는 CASE 식을 이용하는 경우, SIGN 함수를 이용하는 경우가 있음
• 해당 쿼리는 신용 카드 등록과 구매 이력 유무를 0과 1이라는 플래그로 나타내는 쿼리

SELECT 
	m.user_id
	, m.card_number
	, COUNT(p.user_id) AS purchase_count
 	-- 신용 카드 번호 등록한 경우 1, 아닌 경우 0
	, CASE WHEN m.card_number IS NOT NULL THEN 1 ELSE 0 END AS has_card
	 -- 구매 이력이 있는 경우 1, 아닌 경우 0
	, 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

 

 

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

• WITH 문을 이용해 product_sale_ranking이라는 블록을 반복적으로 사용하기 위해 정의(객체지향에서 함수의 재사용성)
• ROW_NUMBER를 통해 순위를 겹치지않게 부여
• CTE(임시 테이블)를 사용하면 일시적인 테이블에 이름을 붙여 재사용
• ROW_NUMBER로 카테고리별 순위 부여 -> CTE 구문 -> WITH 구문 사용

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
)
SELECT *
FROM 
	product_sale_ranking;

 

 

순번을 사용해 테이블 작성하기

• PostgreSQL에서만 generate_series라는 함수를 지원

WITH
series AS (
-- 1부터 5까지의 순번 생성하기
	SELECT generate_series(1,5) AS idx
)
SELECT *
FROM series;