[230127] 멋쟁이사자처럼 AI SCHOOL 8기 [특강] SQL_이범재강사님' 복습

조세연·2023년 1월 27일

CTE Common Table Expressions INTERSECT Sub Query UNION ALL UNION DISTINCT except union with 서브쿼리

멋사 AI SCHOOL 8기

목록 보기

24/35

📝Today I learned

🚀 TIL 목차 🚀

집합
- UNION ALL
- UNION DISTINCT
- INTERSECT DISTINCT
- EXCEPT DISTINCT

서브쿼리/WITH
- 서브쿼리
- WITH

예제 오답노트

[특강] SQL

1) 집합

: 둘 이상의 쿼리 결과를 단일 결과로 결합하는 데 사용

🤚 잠깐! JOIN과 UNION의 차이

JOIN : 공통 key를 중심으로 필드를 확장하는 것 (좌우)
UNION : 2개 이상의 조회 결과를 세로로 합치는 것 (위아래)

🔹 UNION ALL

: 두 결과 집합의 결과를 결합 (중복 제거 X)

🔹 UNION DISTINCT

: 두 결과 집합의 결과를 결합 (중복 제거 O)

※ Big Query에선 UNION DISTINCT, MySQL에선 UNION으로 사용

🔹 INTERSECT DISTINCT(교집합)

: 두 결과 집합 모두에 나타나는 행만 반환 (중복 제거 O)

※ Big Query에선 INTERSECT DISTINCT만 가능, MySQL에선 INTERSECT / INTERSECT DISTINCT 모두 가능

🔹 EXCEPT DISTINCT(차집합, A-B)

: 첫 번째 결과 집합(A)에는 나타나지만 두 번째 결과 집합(B)에는 나타나지 않는 행만 반환

※ Big Query에선 EXCEPT DISTINCT만 가능, MySQL에선 EXCEPT / EXCEPT ALL / EXCEPT DISTINCT 모두 가능
.
.

2) 서브쿼리/WITH

🔹 서브쿼리

: 다른 SQL문 안에 중첩된 SELECT 문

🤚 잠깐! 서브쿼리 왜 쓰나요?

상관 서브쿼리로 사용되는 단 한 가지 경우를 제외하고는 메인 쿼리(main query)보다 더 먼저 실행
여러 번의 SELECT문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 SELECT 문으로 쉽게 얻을 수 있음
내부적으로 복잡한 연산을 수행하거나 결과 크기가 큰 서브 쿼리를 사용할 때는 성능 리스크를 고려해야 함
조인과 관련된 쿼리에서 서브 쿼리가 더 나은 경우가 있음. 테이블을 조인할 때는 조인 대상 레코드 수를 최대한 줄이는 것이 중요한데, 가끔 옵티마이저가 제대로 판단하지 못할 때는 사용자가 직접 연산 순서를 명시해줌으로써 성능적으로 좋은 결과를 얻을 수 있음.

([출처] 서브 쿼리와 성능|작성자 Joel
https://blog.naver.com/jwyoon25/221289425535)

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

select id,
  a.first_name,
  a.last_name,
  b.order_count as 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
order by a.id
limit 10;

👉 목표 : '유저 이름'과 '유저 이름별 주문 카운트 수' 출력

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

select id,
  first_name,
  last_name
from `thelook_ecommerce.users`
where id in (
  select user_id 
  from `thelook_ecommerce.orders` 
  group by user_id 
  having count(order_id) >= 3
)

👉 목표 : 주문 수가 3건 이상인 유저의 'id'와 '이름'을 조회

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 정보 중 'id', '이름', '주문 수'를 조회
.

🔹 WITH(Common Table Expressions)

: 쿼리 내에서 임시 결과를 정의하고 사용

: 마치 ‘변수’처럼 사용하는 것

: 주요 사용 목적은 복잡한 추출 과정을 분할하여 단계적으로 처리하면서 전체 데이터 추출과정을 단순화시키는 것

사용법

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

예시

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

👉 브라질과 일본의 유저 아이디 목록 조회
👉 UNION ALL 위아래 코드가 길면 복잡하니까 WITH로 변수처럼 선언하고 나중에 단순한 코드로 UNION을 건다.
.
.

3) 예제 오답 노트

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

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

조회 항목

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

정렬조건

연도(year)

#최종 답
(SELECT
  2019 AS year,
  prd.name as product_name,
  ROUND(SUM(prd.retail_price), 2) as sum_sale_price
FROM `thelook_ecommerce.order_items` ori
JOIN `thelook_ecommerce.products` prd
  ON ori.product_id = prd.id
WHERE EXTRACT(YEAR FROM ori.created_at) = 2019
GROUP BY product_name
ORDER BY sum_sale_price DESC
LIMIT 1)
UNION ALL
(SELECT
  2020 AS year,
  prd.name as product_name,
  ROUND(SUM(prd.retail_price), 2) as sum_sale_price
FROM `thelook_ecommerce.order_items` ori
JOIN `thelook_ecommerce.products` prd
  ON ori.product_id = prd.id
WHERE EXTRACT(YEAR FROM ori.created_at) = 2020
GROUP BY product_name
ORDER BY sum_sale_price DESC
LIMIT 1)
UNION ALL
(SELECT
  2021 AS year,
  prd.name as product_name,
  ROUND(SUM(prd.retail_price), 2) as sum_sale_price
FROM `thelook_ecommerce.order_items` ori
JOIN `thelook_ecommerce.products` prd
  ON ori.product_id = prd.id
WHERE EXTRACT(YEAR FROM ori.created_at) = 2021
GROUP BY product_name
ORDER BY sum_sale_price DESC
LIMIT 1)
UNION ALL
(SELECT
  2022 AS year,
  prd.name as product_name,
  ROUND(SUM(prd.retail_price), 2) as sum_sale_price
FROM `thelook_ecommerce.order_items` ori
JOIN `thelook_ecommerce.products` prd
  ON ori.product_id = prd.id
WHERE EXTRACT(YEAR FROM ori.created_at) = 2022
GROUP BY product_name
ORDER BY sum_sale_price DESC
LIMIT 1)
ORDER BY year

👉 연도 필터를 걸 땐 SELECT문에서 말고 WHERE문에서 조건 걸기
👉 필드 year에 연도를 표시하고 싶을 땐 SELECT문에 2023 AS year라고 그냥 적어주면 됨
👉 상위 1위를 구할 땐 ORDER BY에 DESC 까먹지 말기
.
.
2. SQL 연습문제 10-2
<문제> thelook_ecommerce 데이터 세트 - 회원(users) 테이블과 주문(orders) 테이블에서
연령대별(user_count) 회원수와 주문횟수합계(order_count)를 조회하세요.

조회 항목

연령대 (age_group)
- 예 : 10대, 20대, 30대, 40대 ~~ 70대
- 참고 함수 trunc : 소수점 버리기
  - select trunc(78, -1) || '대' --> 70대
  - select trunc(32, -1) || '대' --> 30대
회원수(user_count)
주문횟수합계 (order_count)

정렬순서

연령대 오름차순

# 틀린 나의 답 (user_id를 key 값으로 사용)
SELECT
  TRUNC(age, -1) || '대' AS age_group,
  COUNT(id) AS user_count,
  COUNT(order_id) AS order_count
FROM `thelook_ecommerce.users` usr
LEFT JOIN `thelook_ecommerce.orders` ord
  ON usr.id = ord.user_id
GROUP BY age_group
ORDER BY age_group;

# 강사님 답 (age_group을 key 값으로 사용)
select 
  g1.age_group,
  g1.user_count,
  g2.order_count
from (
  select 
    trunc(age, -1) || '대' as age_group,
    count(id) as user_count
  from `thelook_ecommerce.users`
  group by age_group
) g1
join (
  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
) g2 on g1.age_group = g2.age_group
order by age_group;

# WITH를 사용한 강사님 답
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

🧐 왜 내 답이 틀렸는지 찾는 여정 <왜 LEFT JOIN을 하면 안되는가?>

1) 내 답과 강사님의 답 비교

👆 order_count는 동일한데 비해 user_count를 보면 내 쿼리의 결과가 강사님 쿼리 결과보다 더 큰 수가 나옴. 왜????
.
2) users 테이블에서 id와 orders 테이블에서 user_id의 개수 비교

👆 users 테이블에서 unique한 id는 100,000개

👆 orders 테이블에서 unique한 user_id는 79,893개

👆 orders 테이블에서 모든 행의 개수는 124,864개
즉, users 테이블에 있는 유저 중 주문을 하지 않은 유저도 있음!
또 한 명의 유저 당 여러 개의 주문을 할 수도 있음.🌟🌟🌟🌟🌟
.
3) 드디어 문제 해결!!
👉 따라서 users 테이블에 orders 테이블을 LEFT JOIN하면
COUNT(id) AS user_count를 실행했을 때 한 명의 유저 당 여러 개의 주문이 있어 한 명의 유저 id가 그 유저의 주문 개수만큼 늘어나므로 강사님의 답보다 내 답의 user_count에서 더 큰 수가 나온 것!!
👉 COUNT(order_id) AS order_count에서 order_id 필드는 원래 개수는 그대로 존재하고 주문하지 않은 유저에는 null값으로 채워지며 어차피 COUNT()로 감싸는 과정에서 null값은 계산되지 않으므로 옳은 계산이 나옴. 따라서 내 답과 강사님의 답에서 order_count는 동일하게 나온 것!!
끄아아아아아아아라낭란아라아라ㅏ다ㅏ가ㅏ다 드디어 이해했따!!!!!

👆이해를 돕기 위해 엑셀로 만든 표. 👼엔젤 이범재 강사님👼 감사합니다.

❗이것만은 외우고 자자 Top 3

📌 DATEDIFF를 사용할 땐 시간까지 고려하기

📌 상위 1위를 구할 땐 ORDER BY에 DESC 까먹지 말기

📌 JOIN을 할 땐 JOIN 후 어떤 변화가 일어나는지 생각하고 쿼리짜기

🌟데일리 피드백

1. 오늘의 칭찬&반성

그야말로 어마어마한 날이었다. JOIN부터 시작해 UNION, WITH, 서브쿼리까지 쿼리짜다가 울뻔했다. 내가 테이블을 조인하는 건지, 테이블이 나를 조인하는 건지.. 너무 어려운 내용이었지만 오늘 내로 확실히 이해하고 싶어서 늦은 밤(현재시각 12:50)까지 TIL을 적었다. 오늘 SQL 수업 통틀어서 질문도 엄청 많이 했다. 포기하지 않고 내가 할 수 있는 만큼 노력한 내 자신이 대견하다! 대신 쿼리 짤 땐 좀 늦더라도 하나하나 확인하자~ 아무 생각 없이 JOIN 걸지 말자 ㅠㅠ

2. 내가 부족한 부분

1) JOIN, UNION, WITH, 서브쿼리를 활용하는 법을 많이 연습해야겠다. 익숙하지 않아서 두려운 법! 익숙해질 때까지 연습하자.
2) Big Query, MySQL, Oracle 등등 문법이 조금씩 달라서 너무 헷갈린다. 이 부분도 나중에 한방에 정리해야겠다.

3. 내일의 목표

복습하느라 약간 미뤄둔 미니플젝 끝내기^^

조세연

HR Analyst가 되고 싶은

이전 포스트

[230126] 멋쟁이사자처럼 AI SCHOOL 8기 [특강] SQL_이범재강사님' 복습

다음 포스트