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 사용
'SQL Review' 카테고리의 다른 글
| [PostgreSQL] 다면적인 축을 사용해 데이터 집약하기 (0) | 2023.12.05 |
|---|---|
| [PostgreSQL] 시계열 기반으로 데이터 집계하기 (0) | 2023.12.02 |
| [PostgreSQL] 하나의 테이블에 대한 조작 (0) | 2023.11.22 |
| [PostgreSQL] 여러 개의 값에 대한 조작 - 2 (0) | 2023.11.20 |
| [PostgreSQL] 여러 개의 값에 대한 조작 - 1 (0) | 2023.11.17 |