[PostgreSQL] 여러 개의 테이블 조작하기

1. union all을 사용해 테이블을 세로로 결합

 

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 : 유사한 구조의 테이블 데이터를 일괄 처리하고 싶을 때, 세로로 결합하기 위해 사용. 단, 데이터가 많아지면 계산량이 늘어나므로 주의
• 한쪽 테이블에만 존재하는 column은 select에서 제외하거나, default value를 지정해야 함.

 

---

2.  여러 개의 테이블을 결합해서 가로로 정렬

 

select m.category_id, m.name, c.sales, p.product_id as sale_product
from mst_categories as m
join
category_sales as c
on m.category_id = c.category_id
join
product_sale_ranking as p
on m.category_id = p.category_id;

 

• 테이블의 행 수를 변경하지 않고 정렬하려면, left join을 사용해야 함
• on에서 2개의 테이블 간 연결 조건을 명시

 

---

3.  left join 활용해서 카테고리 별로 최고 매출 상품 추출

 

select m.category_id, m.name, c.sales, p.product_id as top_sale_product
from mst_categories as m
left join
category_sales as c
on m.category_id = c.category_id
left join
product_sale_ranking as p
on m.category_id = p.category_id and p.rank = 1;

-- 서브쿼리 활용

select m.category_id, m.name,
	(select c.sales from category_sales as c where m.category_id = c.category_id),
	(select p.product_id from product_sale_ranking as p where m.category_id = p.category_id 
    order by rank limit 1)
from mst_categories as m;

 

• left join을 활용하면 왼쪽 테이블을 기준으로 맞춰서 정렬 가능
• 서브쿼리도 활용할 수 있음
• 단, join 조건 및 서브쿼리 where절 아래의 조건식에 유의해야 함

 

---

 

4. 신용 카드 등록과 구매 이력 유무

 

select m.user_id, m.card_number, count(p.user_id) as purchase_count,case
when card_number notnull 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
order by user_id;

 

• notnull : null이 아닌 경우
• 카드 번호가 null이면 0, 그렇지 않으면 1인 case when문을 활용 / ex) case when 조건 then val else val 2 end
• sign 함수 : 값이 양수면 1, 0이면 0, 음수면 -1을 return
• user_id와 card_number는 사용자 db에서 PK이므로, group by에 함께 사용해야함

 

---

5. 카테고리별 순위를 추가한 테이블에 이름 붙이기

 

with product_with_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_with_ranking;

 

• CTE(공통 테이블식) : with name as () 형식으로, 일시적으로 테이블을 만들어서 메인 쿼리에서 재사용 가능
• 테이블 생성 권한이 없는 경우 편리하게 사용 가능
• 단, 자주 사용하는 유사 테이블은 테이블로 등록하는 것이 효율적임

 

---

6. 카테고리들의 순위에서 유니크한 순위 목록 계산

 

with product_with_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_with_ranking order by rank)

select * from mst_rank;

 

---

7. 카테고리 순위를 횡단으로 출력

 

with product_with_ranking as(
	select category_name, product_id, sales,
	row_number() over (partition by category_name order by sales desc) as ranking
	from product_sales),
	mst_rank as(
	select distinct ranking as ranking
	from product_with_ranking order by ranking)

select m.ranking, 
	r1.product_id as book, r1.sales as book_sales,
	r2.product_id as cd, r2.sales as cd_sales,
	r3.product_id as dvd, r3.sales as dvd_sales
from mst_rank as m
left join
	product_with_ranking as r1
	on m.ranking = r1.ranking and r1.category_name = 'book'
left join
	product_with_ranking as r2
	on m.ranking = r2.ranking and r2.category_name = 'cd'
left join
	product_with_ranking as r3
	on m.ranking = r3.ranking and r3.category_name = 'dvd'
order by m.ranking;

 

• CTE를 사용하지 않으면, row_number를 여러번 사용해야하므로 가독성이 떨어짐
• alias에 주의할 것. rank는 내장 함수로, alias로 rank를 사용하면 DBMS가 rank 함수로 요인할 수 있음

 

---

8. 디바이스 ID와 이름의 유사 테이블을 만들고, 코드를 레이블로 변환

 

with mst_devices as(
	select 1 as device_id, 'PC' as device_name
	union select 2 as device_id, 'SP' as device_name
	union select 3 as device_id, '애플리케이션' as device_name
	order by device_id
)

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;

 

---

9. values 구문을 사용해 동적 테이블 만들기

 

with mst_devices(device_id, device_name) as (
	values
	(1,'PC'),
	(2,'SP'),
	(3,'애플리케이션')
)

select * from mst_devices;

 

• postgresql은 CTE에 values 구문을 사용해 레코드를 만들수 있음

 

---

10. 순번을 가진 유사 테이블 작성

 

with series as(
	select generate_series(1,5) as idx
)

select * from series;

 

• generate_series(start, end) : start부터 end까지 순번을 만들고 싶을 때 사용
• bigquery에서는 generate_array 사용