[DAY 23] SQL 집합연산, 서브쿼리, WITH, CTE

새로운 내용에 앞서 지난 시간에 공부한 JOIN에 대한 연습문제와 프로그래머스 문제를 풀었다.

이후 집합연산, 서브쿼리, WITH과 CTE에 대해 공부했다.

JOIN 연습문제와 프로그래머스 문제

JOIN 연습문제

### SQL 연습문제 8-6
주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문한 상품의 브랜드 별 평균판매가격(brand_avg_sale_price)을 조회하시오. 
- 가격은 소수점 2자리까지 표시, 반올림 처리
- 정렬 : 브랜드명 오름차순

select t2.brand, round(avg(t1.sale_price), 2)
from `thelook_ecommerce.order_items` t1
left join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
group by brand
order by brand

 

### SQL 연습문제 8-7
주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여
여성파트 주문완료상품의 날짜별 상품별 매출합계를 구하세요.
- 주문일 표시 형식 : 2022-01-01
- 매출합계 반올림하여 소수점 2자리까지 표시

select
  DATE(t1.created_at) order_date,
  t2.name product_name,
  count(order_id) order_count,
  round(sum(t1.sale_price), 2) sum_sale_price
from `thelook_ecommerce.order_items` t1
left join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where t1.status = 'Complete' and t2.department='Women'
group by order_date, product_name

 

### SQL 연습문제 8-8
주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문id 당 정보를 구하세요.
    - 주문 id(order_id)
    - 비용 합계(sum_cost)
    - 판매가격 합계(sum_retail_price)
    - 총 이익(sum_profit) : 이익 = 판매가격 - 비용
    
select
  t1.order_id order_id,
  sum(t2.cost) sum_cost,
  sum(t2.retail_price) sum_retail_price,
  sum(t2.cost - t2.retail_price) sum_profit
from `thelook_ecommerce.order_items` t1
left join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
group by order_id

프로그래머스 문제
Level 1
과일로 만든 아이스크림 고르기

Level 2
상품 별 오프라인 매출 구하기
성분으로 구분한 아이스크림 총 주문량
조건에 맞는 도서와 저자 리스트 출력하기

Level 3
카테고리 별 도서 판매량 집계하기
오랜 기간 보호한 동물(1)
오랜 기간 보호한 동물(2)
있었는데요 없었습니다
없어진 기록 찾기

집합 연산

둘 이상의 쿼리 결과를 연산하여 단일 결과를 반환

BigQuery 집합 연산자 종류
1. UNION ALL
2. UNION DISTINCT
3. INTERSECT DISTINCT
4. EXCEPT DISTINCT

그림으로 나타내면 아래와 같다

유저 정보가 각각 8명씩 저장된 테이블 USER1과 USER2를 통해 이해해보자.

select * from user1 order by id

USER1 테이블 정보

select * from user2 order by id

USER2 테이블 정보

위의 두 테이블을 이용하여 집합 연산을 하고 결과를 이해해보자.
USER1 테이블은 101~108번 유저, USER2 테이블은 106~113번 유저가 있음을 기억해두자.

UNION ALL

select * from user1
union all
select * from user2
order by id

UNION ALL 결과

UNION ALL은 쿼리의 결과 집합들을 결합하고 중복을 제거하지 않는다.
각각 8행이었던 두 테이블을 UNION ALL 한 결과 16행임을 확인할 수 있다.
id가 106~108인 유저 정보가 두 번씩 출력되었다.


UNION DISTINCT

select * from user1
union distinct
select * from user2
order by id

UNION DISTINCT 결과

UNION DISTINCT는 쿼리의 결과 집합들을 결합하고 중복을 제거한다.
두 테이블에 공통적인 데이터들이 중복해서 출력되지 않는 것을 확인할 수 있다.


INTERSECT DISTINCT

select * from user1
intersect distinct
select * from user2
order by id

INTERSECT DISTINCT 결과

INTERSECT DISTINCT는 두 테이블에서 중복으로 나타나는 레코드만 반환한다.
USER1 테이블과 USER2 테이블에서 겹치는 데이터 3행만 출력된 것을 확인할 수 있다.


EXCEPT DISTINCT

select * from user1
except distinct
select * from user2
order by id

EXCEPT DISTINCT 결과

A EXCEPT DISTINCT B는 A 결과 집합 레코드 중 B 결과 집합에 속하지 않는 레코드를 제거한다.
USER1 테이블의 8행 중 USER2 테이블과 겹치는 3행이 제거된 것을 확인할 수 있다.


집합 연산 활용

# 연도별 회원가입자수와 전체 회원가입자수 합계를 함께 출력

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

연도별 가입자 수를 구하고, 전체 가입자 수를 구한 후 UNION ALL 하였다.

집합 연산 연습문제

### SQL 연습문제 9-4
상품정보(products) 테이블에서 카테고리가 ‘Clothing Sets’인 정보와
'Jumpsuits & Rompers' 인 정보를 각각 조회후 합쳐서 조회하세요.

select * from `thelook_ecommerce.products` 
where category = 'Clothing Sets'
union all
select * from `thelook_ecommerce.products` 
where category = 'Jumpsuits & Rompers'

 

### SQL 연습문제 9-5
회원(users) 테이블에서 국가가 'South Korea' 인 회원정보에서 
나이가 20세~40세인 회원을 뺀 나머지 회원의 정보를 조회하시오.

select * from `thelook_ecommerce.users`
where country = 'South Korea'
except distinct
select * from `thelook_ecommerce.users`
where age between 20 and 40

 

SUBQUERY(서브쿼리)

SQL문 안에 포함된 또 다른 SQL문(SELECT문)
대표적으로 SELECT절, FROM절, WHERE절에 사용된다.

SELECT절에서 사용되는 서브쿼리

select id,
  first_name,
  last_name,
  (select count(order_id) from `thelook_ecommerce.orders` where user_id = a.id) as order_count
from `thelook_ecommerce.users` a
order by a.id
limit 10;

user 정보를 조회할 때 해당 유저의 주문수(order_count)를 조회하기 위해 select 절에서 서브쿼리를 사용하였다.


FROM절에서 사용되는 서브쿼리

select id,
  a.first_name,
  a.last_name,
  b.order_count
from `thelook_ecommerce.users` a
left join (
    select user_id, count(order_id) as order_count 
    from `thelook_ecommerce.orders`
    group by user_id
  ) b on a.id = b.user_id

유저의 id와 이름 그리고 주문수를 조회하였다.
left join을 이용하여 users테이블의 정보와 user별 주문수를 조회하는 서브쿼리를 연결해서 유저의 주문수를 조회하였다.


WHERE 절에서 사용되는 서브쿼리

select * 
from `thelook_ecommerce.orders`
where user_id in (
  select id 
  from `thelook_ecommerce.users` 
  where country = 'Brasil'
)

국가가  ‘Brasil’인 유저의 주문정보(orders)를 조회 하는 쿼리이다.
orders를 조회하는 select문의 where절 안에서 user의 국가가 ‘Brasil’인 id를 조회하는 쿼리를 하위로 넣어서 실행하였다.


서브쿼리 연습문제

### SQL 연습문제 10-3
회원(users) 테이블에서 
가장 많은(높은) 회원 나이를 구하고 그 나이 회원들의 이름을 조회하세요.

select
  age,
  first_name, 
  last_name,
from `thelook_ecommerce.users`
where age = (select max(age) from `thelook_ecommerce.users`)

 

### SQL 연습문제 10-4
모니터를 구입한 회원(user)의 이름(name)과 우편번호(postal_code)를 서브쿼리만 이용하여 조회하세요.
(join을 사용하지 않습니다.)

select name, postal_code from `weniv.weniv_user`
where id in (select user_id from `weniv.weniv_order`
              where product_id in (select id from `weniv.weniv_product`
                                  where name = 'monitor'))

의도적으로 join을 사용하지 않고 서브쿼리를 이용해서 조회하는 예제다.

CTE를 정의하는 WITH절

WITH절은 쿼리 내에서 임시 결과를 정의한다.
주로 사용 목적은 복잡한 추출 과정을 분할하여 단계적으로 처리하면서 전체 데이터 추출 과정을 단순화시키는 것이다.

CTE(Common Table Expressions)는 단순 SELECT문에서 생성된 임시 결과 집합이다.
CTE는 가상 테이블 개념으로 저장되어 이후 기본 쿼리에서 참조될 수 있다.

사용법

WITH `CTE명` AS ( `쿼리 표현식` )

예시 1 - 회원 id 조회

WITH user_data AS (select id from `thelook_ecommerce.users`)
select * from user_data

user_data CTE를 정의한다. CTE의 내용은 users 테이블의 id 값을 조회하는 것이다.
select문을 통해 정의한 user_data로부터 데이터를 조회한다.

예시 2 - 회원 수가 1000명 이상인 국가명과 국가의 회원수

WITH user_counts AS (
  select 
    country, 
    count(id) as user_count
  from `thelook_ecommerce.users`
  group by country
  having count(id) >= 1000
)
select * from user_counts

국가별 유저 수를 조회하는 user_counts CTE를 정의하고, select문을 통해 user_counts 데이터 출력

예시 3 - 브라질과 일본의 유저 아이디 목록

WITH user_id_brasil AS (
  select id, country 
  from `thelook_ecommerce.users` 
  where country = 'Brasil' limit 10
),
user_id_japan AS (
  select id, country 
  from `thelook_ecommerce.users` 
  where country = 'Japan' limit 10
)
select id, country from user_id_brasil
UNION ALL
select id,country from user_id_japan

국가가 Brasil인 유저의 id와 country를 조회하는 user_id_brasil CTE를 정의하고
국가가 Japan인 유저의 id와 country를 조회하는 user_id_japan CTE를 정의한 후
UNION ALL을 통해 user_id_brasil과 user_id_japan의 데이터를 합쳐서 조회한다.

WITH 연습문제

### SQL 연습문제 10-2
회원(users) 테이블과 주문(orders) 테이블에서 
연령대별(user_count) 회원수와 주문횟수합계(order_count)를 조회하세요.
정렬 : 연령대 오름차순

WITH user_counts AS (
  select 
    trunc(age, -1) || '대' as age_group,
    count(id) as user_count
  from `thelook_ecommerce.users`
  group by age_group
),
order_counts AS (
  select 
    trunc(t2.age, -1) || '대' as age_group,
    count(t1.order_id) as order_count
  from `thelook_ecommerce.orders` t1
  left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
  group by age_group
) 
select
  t1.age_group,
  t1.user_count,
  t2.order_count
from user_counts t1 
join order_counts t2 on t1.age_group = t2.age_group
order by t1.age_group

실행 결과

서브쿼리가 활용도가 매우 높은 것을 알고 있으나 어려워서.. 하나하나 꼼꼼히 이해하려고 노력해야겠다.