SQL Review (5th UNION)

1. 집합

집합 작업은 둘 이상의 쿼리 결과를 단일 결과로 결합하는 데 사용됩니다. 결합된 쿼리는 동일한 수의 열을 반환해야 합니다. 호환 가능한 데이터 유형. 해당 열의 이름은 다를 수 있습니다.

출처 : 위니브

출처 : 위니브

 

 

2. UNION(합집합)

UNION은 두 결과 집합의 결과를 결합하고 중복을 제거

UNION ALL은 중복 행 제거 x

 

• all : 모든 결과 확인

select * from `weniv.weniv_user` as user1
UNION ALL
select * from `weniv.weniv_user3` as user3

컬럼의 수가 맞아야 결합 가능

 

 

• distinct : 중복 제거

select * from `weniv.weniv_user` as user1
UNION DISTINCT = UNION
select * from `weniv.weniv_user3` as user3

 

3. INTERSECT(교집합)

• INTERSECT는 두 결과 집합 모두에 나타나는 행만 반환

select * from `weniv.weniv_user` as user1
INTERSECT DISTINCT
select * from `weniv.weniv_user3` as user3

 

 

4. EXCEPT(차집합, A-B)

• EXCEPT 첫 번째 결과 집합에는 나타나지만 두 번째 결과 집합에는 나타나지 않는 행만 반환

select * from `weniv.weniv_user` as user1
except DISTINCT
select * from `weniv.weniv_user3` as user3

 

연도별 회원가입자수와 전체 회원가입자수 합계를 함께 보여주기

select 
extract(year from date(created_at)) as year,
count(id) as user_count
FROM `thelook_ecommerce.users`
group by year   # + 이렇게 나온 쿼리 결과에 젤 밑줄에 TOTAL : sum(user_count) 넣고 싶음
union all
select 
'TOTAL' as year,
count(id) as user_count
from `thelook_ecommerce.users`
order by year

이렇게 하면

에러 발생.. 강사님이 수업중 이래서 year 부분을 cast 해서 string 형식으로 바꾸어 주어야 한다고 하신 것이 기억난다.

대충 해석해보면 첫번째 열 ( year )에서 숫자데이터( 2019, 2020..) 이랑 TOTAL 이 같이 있을 수 없다고 하는 것 같다

 

 

select 
  CAST(extract(year from created_at) AS STRING) as year,
  count(id) as user_count
from `thelook_ecommerce.users`
group by year
UNION ALL
select 
  'TOTAL' as year,
  count(id) as user_count
from `thelook_ecommerce.users`
order by year

문자열을 숫자로 바꾸어 줄 순 없으니 숫자들을 string 형식으로 변경

 

 

---

+ 자체 연습문제

 

SQL 연습문제 9-2

# 위니브 회원(weniv_user) 테이블에서 주문 이력(weniv_order)이 있는 사람의 id을 조회하세요.

from `weniv.weniv_user`

from `weniv.weniv_order`

나 이런 게 어려워하네... 사실 문제가 잘 이해가 안된다

이게 여기 있으니까 Union을 쓰겠지 하는거지 

그냥 문제만 보면 Union이 바로 생각이 날까 싶다

자주 보는 수 밖에 없는 것 같다

 

select id from `weniv.weniv_user` # as user1
intersect DISTINCT
select user_id from `weniv.weniv_order` # as orders

아무튼 해답과 상기에 있던 그림을 좀 참고해보면

일단 id를 조회하라고 했으니

왼쪽  'weniv_user' 테이블(table A) 에서 id를 불러오고,

오른쪽 'weniv_order 테이블(table B)에서 user_id를 불러온다(order 테이블에 user_id 있으면 주문 내역이 있다는 것)

 

그 다음,

주문 내역이 있는(order 테이블에서 user_id가 있는) 회원의 ID는

양쪽 테이블(table A, table B)의 교집합이 되기 때문에 

교집합 함수를 쓰는 것 같다.

 

 

 

 

SQL 연습문제 9-6

주문아이템(order_items) 테이블과 상품(products)테이블을 이용하여 
2019 ~ 2022연도의 연도별로 매출총액이 1위인 상품의 상품명과 매출합계를 표시하세요.

각 연도의 매출총액 1위 상품의 상품명과 매출합계를 구한뒤 
합쳐서 조회하세요.

조회 항목

- 연도(year)
- 상품명(product_name)
- 매출합계금액(sum_sale_price)

정렬조건

- 연도(year)

select
extract(year from date(o.created_at)) as year,
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by year, product_name
order by year, sum_sale_price desc
limit 1

문제가 어려운 것 같아서 우선 2019년 한 해만의 매출총액 1위 상품의 상품명과 매출합계 조회

 

 

 

# 여러개의 조회값 합쳐야 하니까 () 감싸주기 . 사실 언제 감싸고 안 감싸는지 헷갈린다. 우선 항목 많아지면 감싸자
(select
2019 as year, #어차피 각 연도별 항목 1개만 조회하기 때문에 year 대신 2019 숫자로 가능 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by product_name #2019년 한 해이기 때문에 그룹화 필요 X
order by sum_sale_price desc # 정렬도 마찬가지
limit 1)
UNION ALL
(select
2020 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by product_name 
order by sum_sale_price desc 
limit 1)
UNION ALL
(select
2021 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by product_name 
order by sum_sale_price desc 
limit 1)
UNION ALL
(select
2022 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by product_name 
order by sum_sale_price desc 
limit 1)

근데 이렇게 연도만 바뀌고 2019년의 항목과 매출총액이 그대로 나옴....

생각해보니 SELECT에서 숫자만 YEAR로 바꿔주고(표출되는 값만. 내부에서 필터를 걸어줘야 함)

나머지는 그대로 가져와서 그런 것 같다.

어떤 항목을 가져왔나 보니까 연도 상관없이 SUM_SALE_PRICE 가 제일 높은(내림차순) 항목 1개를 (LIMIT 1) 

가져온 것 같다.

 

(select
2019 as year,  
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where extract(year from o.created_at) = 2019
group by product_name 
order by sum_sale_price desc 
limit 1)
UNION ALL
(select
2020 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where extract(year from o.created_at) = 2020
group by product_name 
order by sum_sale_price desc 
limit 1)
UNION ALL
(select
2021 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where extract(year from o.created_at) = 2021
group by product_name 
order by sum_sale_price desc 
limit 1)
UNION ALL
(select
2022 as year, 
p.name as product_name,
sum(o.sale_price) as sum_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where extract(year from o.created_at) = 2022
group by product_name 
order by sum_sale_price desc 
limit 1)
order by year

 

이 문제만 제대로 알고 넘어가도 Union 이해하는데 도움이 될 것 같다

 

+ 서로 겹치지 않는 필드(유니크한 필드)는 앞에 어느 테이블에서 가져오는지 언급하지 않아도 괜찮음

 

 

★

Join은 옆으로 Union은 위아래로 합침