[DAY 33] SQL 마지막 날 - 추천도서, 내용 정리, 연습문제

Special Lecture인 SQL이 벌써 7회차로 마지막이 되었다.

전체 내용을 한 번 쭉 복습하고 연습문제를 풀며 마무리했다.

추천도서

SQL 도서를 추천해 달라는 요청을 받으신 강사님의 3가지 추천도서

1. 모두의 SQL

2. SQL로 맛보는 데이터 전처리 분석

3. 데이터 분석을 위한 SQL 레시피

1번은 e-book으로도 볼 수 있다는 장점이 있다.
개발자, DB관리자, 데이터분석가 등 본인의 직무에 따라 사용하게 되는 SQL이 차이가 있을 것이라는 말씀도 덧붙여 주셨다.

전체 내용 및 연습문제

select~from의 기본적 형태부터 where절, group by, having, order by, 함수, join, 조건분기, with, 서브쿼리, 데이터 집계, 윈도우 함수 등 전체 내용을 쭉 상키시킨 후 연습문제를 풀었다.

### 2.1 2022년 전체 주문건, 총 매출, 평균 매출
order_items 테이블에서 2022년도의 전체 주문건수, 총 매출, 평균 매출을 조회

select 
  count(id) as total_order_count,
  sum(sale_price) as total_sale_price,
  avg(sale_price) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) = 2022

- 연도추출
- 집계함수를 사용해서 건수, 매출합계, 평균매출 조회

### 2.2.1 2022년 월별 주문건, 총 매출, 평균 매출
order_items 테이블에서 2022년도 월별 주문건수, 총 판매금액, 평균 판매금액을 조회
- 정렬 : 월(month) 오름차순

select 
  extract(month from created_at) month,
  count(id) total_order_count,
  round(sum(sale_price), 2) total_sale_price,
  round(avg(sale_price), 2) avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) = 2022
group by month
order by month

- 연도, 월 추출
- 집계함수 사용
- round를 통한 반올림하여 소수점 둘째자리까지 표현
- group by와 order by

### 2.3.2 2022년도 이하 모든 연도의 분기별 주문건, 총 매출, 평균 매출
order_items 테이블에서 2022년도 이하 모든 연도의 분기별 주문건수, 총 판매금액, 평균 판매금액을 조회
정렬 : 분기(quarter) 오름차순

select 
  extract(quarter from created_at) as quarter,
  round(count(id), 2) as total_order_count,
  round(sum(sale_price),2) as total_sale_price,
  round(avg(sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) <= 2022
group by quarter
order by quarter

- quarter(분기) 추출
- 집계함수 사용
- round를 통한 반올림하여 소수점 둘째자리까지 표현
- group by와 order by

### 2.8.1 브랜드별 전체 주문건, 2022년 주문건 및 주문건 순위
order_items 테이블과 products 테이블에서 2022년도와 2022년도 이하 모든 연도의 브랜드별 주문건수와 주문건수 순위 조회
정렬 : 2022년도 기준 주문건수 순위 (rank_2022) 오름차순

select
  t2.brand,
  count(case when extract(year from t1.created_at) <= 2022 then t1.id end) order_count_total,
  count(case when extract(year from t1.created_at) = 2022 then t1.id end) order_count_2022,
  RANK() OVER (ORDER BY count(case when extract(year from t1.created_at)=2022 then t1.id end) desc )
        AS rank_2022,
from `thelook_ecommerce.order_items` t1
left join `thelook_ecommerce.products` t2
on t1.product_id = t2.id
group by t2.brand
order by rank_2022

- case 조건분기
- rank 함수로 순위 조회
- 두 테이블을 left join

### 2.8.2 브랜드별 전체 총 매출, 2022년 총 매출 및 매출 순위
order_items 테이블과 products 테이블에서 2022년도와 2022년도 이하 모든 연도의 브랜드별 판매금액 합계, 평균 판매금액을 조회
정렬 : 2022년도 기준 판매금액 합계 순위 (rank_2022) 오름차순

with brand_orders as (
  select 
    t2.brand,
    round(sum(t1.sale_price), 2) as sum_sale_price_total,
    round(sum(case when extract(year from created_at) = 2022 then t1.sale_price end), 2) as sum_sale_price_2022
  from `thelook_ecommerce.order_items` t1
  join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
  where extract(year from created_at) <= 2022
  group by brand
)
select 
  brand,
  sum_sale_price_total,
  sum_sale_price_2022,
  rank() over(order by sum_sale_price_2022 desc) as rank_2022
from brand_orders
order by rank_2022

- with절 사용, CTE 정의
- inner join

### 2.9.2 카테고리별 전체 총 매출, 2022년 총 매출 및 매출 순위
order_items 테이블과 products 테이블에서 2022년도와 2022년도 이하 모든 연도의 카테고리별 판매금액 합계, 평균 판매금액을 조회
정렬 : 2022년도 기준 판매금액 합계 순위 (rank_2022) 오름차순

with category_orders as (
  select 
    t2.category,
    round(sum(t1.sale_price), 2) as sum_sale_price_total,
    round(sum(case when extract(year from created_at) = 2022 then t1.sale_price end), 2) as sum_sale_price_2022
  from `thelook_ecommerce.order_items` t1
  join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
  where extract(year from created_at) <= 2022
  group by category
)
select 
  category,
  sum_sale_price_total,
  sum_sale_price_2022,
  rank() over(order by sum_sale_price_2022 desc) as rank_2022
from category_orders
order by rank_2022

- with절 사용, CTE 정의
- inner join

### 3.1.1 VVIP
VVIP 유저(구매 금액 합계 상위 0.1% 유저) 목록을 구하기 위해 유저아이디별 주문수, 판매금액합계, 판매금액 합계 순위를 조회
- 판매금액합계 퍼센트 랭크 (percent_rank_sale_price) : 소수점 4자리까지 표시 + ‘%’
정렬 : 판매금액합계 순위 (rank_sale_price) 오름차순

with sale_list as 
(
  select 
    user_id,
    count(distinct order_id) as order_count,
    count(id) as order_item_count,
    sum(sale_price) as sum_sale_price
  from `thelook_ecommerce.order_items`
  group by user_id
)
select 
  user_id,
  order_count,
  order_item_count,
  sum_sale_price,
  rank() over( order by sum_sale_price desc ) as rank_sale_price,
  trunc(percent_rank() over( order by sum_sale_price desc ) * 100, 4) || '%' as percent_rank_sale_price,
from sale_list
order by rank_sale_price

- with절 사용, CTE 정의
- trunc를 사용한 소수점 버림
- ||를 사용한 문자열 연결
- percent_rank() 사용

### 3.1.2 VVIP 고객의 주문, 구매 분석
1. VVIP의 주문 횟수는 몇회 일까?
2. VVIP의 주문 상품 개수는 몇개 일까?
3. VVIP의 구매 금액 합계는 얼마 이상 일까?
VVIP 유저의 평균주문건수, 평균 주문 아이템 건수, 평균 판매가격, 최소구매횟수, 최소 구매금액을 조회

with sale_list as 
(
  select 
    user_id,
    count(distinct order_id) as order_count,
    count(id) as order_item_count,
    sum(sale_price) as sum_sale_price
  from `thelook_ecommerce.order_items`
  group by user_id
),
user_order_list as 
(
  select 
    user_id,
    order_count,
    order_item_count,
    sum_sale_price,
    rank() over( order by sum_sale_price desc ) as rank_sale_price,
    trunc(percent_rank() over( order by sum_sale_price desc ) * 100, 4) as percent_rank_sale_price,
  from sale_list
  order by rank_sale_price
)
select 
  avg(order_count) as avg_order_count,
  avg(order_item_count) as avg_order_item_count,
  avg(sum_sale_price) as avg_sum_sale_price,
  min(order_item_count) as min_order_item_count,
  min(sum_sale_price) as min_sum_sale_price
from user_order_list
where percent_rank_sale_price <= 0.1

- with절 사용, CTE 2개 정의
- trunc를 사용한 소수점 버림
- ||를 사용한 문자열 연결
- percent_rank() 사용


AI SCHOOL에서의 SQL 강의는 여기까지지만 앞으로 더 공부하며 나아가야겠다.