[ 유데미 스타터스 ] 47일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - CTE테이블, 윈도우 함수

1. SQL 실습

1.1 복잡한 데이터셋을 다루기 위한 방법

- 복잡한 데이터셋은 가독성 ↓, 재사용성 ↓, 유지보수성 ↓, 실수 ↑

	개념	장점	단점
ETL 스냅샷테이블	- 쿼리 결과를 테이블에 저장 - 예약된 시간에 백그라운드에서 실행	- 복잡한 로직 없이 테이블에서 원하는 쿼리 바로 실행	- 실시간 데이터 보기 어려움 - 저장 후 데이터 변경 어려움 - 엔지니어의 도움 필요
VIEW	- 쿼리 결과를 가상테이블로 생성하여 사용	- 실시간성 보장	- 데이터베이스 쓰기 권한 필요 - 유지보수 필요
임시테이블 Temporary table	- 현재 세션동안 유지되는 테이블 - create, insert 구문을 통해 생성	- 복잡한 로직 없이 테이블에서 원하는 쿼리 바로 실행 - 성능	- 데이터베이스 쓰기권한 필요
공통테이블 표현식 CTE	- 쿼리 결과에 이름을 붙여 테이블처럼 사용	- 쿼리를 간결하게 작성할 수 있음 (가독성)	- 여러 CTE구문 사용 시 중간 결과 확인이 어려움 (적절한 주석 사용)

 

1.2 임시테이블 생성

📌 임시테이블 생성

- orders : 주문번호, 고객번호, 주문일, 연, 월, 일

- order_details : 제품번호, 제품단가, 수량, 할인율, 매출액

- 세션이 유지되는 동안 일반 테이블처럼 사용(alter, drop 가능)

create temporary table tmp_order_details as
select o.order_id, o.customer_id, o.order_date
		, to_char(o.order_date, 'YYYY') as year
		, to_char(o.order_date, 'mm') as month
		, to_char(o.order_date, 'dd') as day
		, to_char(o.order_date, 'q') as quarter
		, od.product_id, od.unit_price, od.quantity, od.discount
		, od.unit_price * od.quantity * (1-od.discount) as amount
from orders o, order_details od
where o.order_id = od.order_id;

 

❓ 월별 매출액, 주문건수, 주문자수, 건당평균주문액, 고객당평균주문액

select year, month
	 , sum(amount) as 매출액
	 , count(distinct order_id) as 주문건수
	 , count(distinct customer_id) as 주문자수
	 , sum(amount)/count(distinct order_id) as 건당평균주문액
	 , sum(amount)/count(distinct customer_id) as 고객당평균주문액 
from tmp_order_details
group by 1,2;

 

❓ 일별 매출액, 주문건수, 주문자수, 건당평균주문액, 고객당평균주문액

select year, month, day
	 , sum(amount) as 매출액
	 , count(distinct order_id) as 주문건수
	 , count(distinct customer_id) as 주문자수
	 , sum(amount)/count(distinct order_id) as 건당평균주문액
	 , sum(amount)/count(distinct customer_id) as 고객당평균주문액 
from tmp_order_details
group by 1,2,3;

 

1.3 공통 테이블 표현식

✅ CTE ( Common Table Expression)

- 데이터셋을 생성하기 위해 복잡한 코드와 로직이 필요한 경우

- 동일한 로직이 반복되는 경우

- 복잡한 쿼리문의 결과에 이름을 붙여 임시 테이블로 사용

- 복잡한 쿼리문이 반복될 때 유용

- 코드의 가독성, 재사용성을 높일 수 있음

- 일회용이므로 재사용하려면 콛를 저장해두어야 함

 

📌 CTE테이블 생성

with 
cte_order_details as (
select o.order_id, o.customer_id, o.order_date
		, to_char(o.order_date, 'YYYY') as year
		, to_char(o.order_date, 'mm') as month
		, to_char(o.order_date, 'dd') as day
		, to_char(o.order_date, 'q') as quarter
		, od.product_id, od.unit_price, od.quantity, od.discount
		, od.unit_price * od.quantity * (1-od.discount) as amount
from orders o, order_details od
where o.order_id = od.order_id
)

 

❓ CTE테이블 생성, 데이터 추출, 검증

, cte_order_info as(
select year, month
	 , sum(amount) as 매출액
	 , count(distinct order_id) as 주문건수
	 , count(distinct customer_id) as 주문자수
	 , sum(amount)/count(distinct order_id) as 건당평균주문액
	 , sum(amount)/count(distinct customer_id) as 고객당평균주문액 
from cte_order_details
group by 1,2
)
select sum(매출액), sum(주문건수) from cte_order_info; -- 검증하기

 

1.4 윈도우 함수

✅ 윈도우 함수 구문

- 함수() over(윈도우 프레임 지정)

- 윈도우 프레임 지정

  → [partition by <컬럼이름> ] order by <컬럼이름> 정렬방법

  → rows between <start> and <end>

 

✅ 윈도우 함수 분류

집계함수	집계함수 (count, sum 등)
순위함수	RANK	중복 순위 존재 시 건너뜀 (1,1,3,4,5,5,7,...)
	DENSE_RANK	중복 순위 존재 시 건너뛰지 않음 (1,1,2,3,4,4,5,...)
	ROW_NUMBER	동일 순위 존재하여도 연속번호 부여 (1,2,3,4,5,6,7,...)
행 순서 함수	FIRST_VALUE	파티션 별 윈도우에서 가장 먼저 나오는 값 (min)
	LAST_VALUE	파티션 별 윈도우에서 가장 늦게 나오는 값 (max)
	LAG	파티션 별 윈도우에서 이전 행 값
	LEAD	파티션 별 윈도우에서 다음 행 값
그룹 내 비율 함수	RATIO_TO_REPORT
	PERCENT_RANK	백분위 수
	CUME_DIST
	NTILE	구간 나누기

 

✅ 집계

전체 집계	집계함수 OVER()
그룹 집계	집계함수 OVER(PARTITION BY <컬럼이름>)
프레임지정	집계함수 OVER(윈도우프레임지정)

 

❓ 누적합계

with
cte_order_details as (
-- 생략
)
select year, month, day
    -- 레코드 전체 합계. 비율을 계산할 때 사용 (전체 대비 몇 %)
	, sum(sales) over()
    -- 년도 단위로 누적 합계
	, sum(sales) over(order by year)
    -- 년도, 월, 일 단위로 누적 합계
	, sum(sales) over(order by year, month, day)
    -- 년도가 바뀌면 새로 누적
    , sum(sales) over(partition by year order by year, month)
from cte_order_details;

 

❓ 이전 행, 다음 행 추출

with
cte_order_details as (
-- 생략
)
, cte_pre_amount as(
select year, month, day, sum(amount) as amount
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select * 
	, lag(amount) over(order by year, month, day) as 전일매출
	, lag(amount, 3) over(order by year, month, day) as 삼일전매출
	, lead(amount) over(order by year, month, day) as 익일매출
from cte_pre_amount;

 

1.5 매출 증감 및 누계

❓ 매출 증감 분석 (전월 대비 매출 증감액, 전월 대비 매출액 비율, 증감여부)

with
cte_order_details as (
-- 생략
)
-- 1) 월별 매출액
, cte_amount as(
select year, month, sum(amount) as 매출액
from cte_order_details
group by 1,2
)
-- 2) 전월 매출액
, cte_pre_amount as(
select *
	, lag(매출액) over(order by year, month) as 전월매출
from cte_amount
)
-- 3) 매출증감액, 전월비, 증감여부
select *
	, 매출액-전월매출 as 매출증감액
	, round(매출액/전월매출*100)::varchar(10)||'%' as 전월비
	, case 
		when 매출액-전월매출>0 then '+'
		when 매출액-전월매출<0 then '-'
		when 매출액-전월매출 is null then '해당없음'
		when 매출액-전월매출=0 then '동일'
	end as 증감여부
from cte_pre_amount;

 

❓ 매출 누계 (월별 매출 누계(전체 누계), 월별 매출 누계(연도별 파티션))

with
cte_order_details as (
-- 생략
)
, cte_month_sales as(
select year, month, sum(sales) as sales
from cte_order_details
group by 1,2
)
select *
	, sum(sales) over(order by year, month) as 월별매출누계_전체
	, sum(sales) over(partition by year order by year, month) as 월별매출누계_연도별파티션
from cte_month_sales;

 

1.6 이동평균

✅ 윈도우 프레임 지정

ROWS BETWEEN START AND END

CURRENT ROW	현재 행
n PRECEDING	n행 앞
n FOLLOWING	n행 뒤
UNBOUNDED PRECEDING	이전 행 전부
UNBOUNDED FOLLOWING	이후 행 전부

 

❓ 현재 행 ~ n행 뒤의 행까지 더하기

with
cte_order_details as (
-- 생략
)
-- 일별 주문건수
, cte_ordercnt as (
select year, month, day, count(order_id) as cnt
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select *
	, sum(cnt) over(order by year, month, day rows between current row and 4 following)
from cte_ordercnt;

 

❓ 5일 이동평균

with
cte_order_details as (
-- 생략
)
-- 일별 매출액
, cte_sales as (
select year, month, day, sum(sales) as 매출액
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select *
	, avg(매출액) over(order by year, month, day rows between 4 preceding and current row) as "5일이동평균1" -- 앞 4개의 행은 이동평균이 아님
	, count(매출액) over(order by year, month, day rows between 4 preceding and current row) as cnt
	, case 
		when count(매출액) over(order by year, month, day rows between 4 preceding and current row) = 5 then
		avg(매출액) over(order by year, month, day rows between 4 preceding and current row)
	end as "5일이동평균2"
from cte_sales;

 

---

2. 팀 과제

📌 Northwind 데이터셋으로 직원 별 매출 분석하기

 

0. 직원 테이블 파악

- employees 테이블의 reports_to 컬럼을 통해 직원들의 보고체계를 유추할 수 있음

- UK에 거주하는 직원들은 sales manager에게, sales manager와 USA에 거주하는 직원들은 vice president에게 보고하고 있어서 USA를 본사, UK를 지사로 가정하고 국가별로 분석을 진행하였음

 

1. 주제 및 목표

- 회사가 가장 큰 성장을 이룬 시점에 매출에 많이 기여한 직원과 그 직원이 판매한 상품 중 매출액 비율이 가장 높은 TOP 5 제품을 찾는다.

1) 국가 별로 매출 추이를 살펴보고, 전분기 대비 매출이 가장 크게 상승한 연도의 분기에 집중한다.

2) 직원 별로 해당 분기의 매출에 얼마나 기여했는지 살펴본다.

3) 이때, 실적이 우수한 직원과 부진한 직원이 판매한 TOP 5 상품과 실적에 대한 비중을 살펴본다.

 

2. 테이블 관계도 파악

 

3. CTE 테이블 작성

with cte_employees_detail as (
select to_char( o.order_date, 'yyyy') as year
      ,to_char( o.order_date, 'q') as quarter
      ,to_char( o.order_date, 'mm') as month
      ,to_char( o.order_date, 'dd') as day
      ,o.order_id , o.customer_id, od.product_id, p.product_name
      ,e.employee_id, e.country, e.reports_to 
      ,od.unit_price * od.quantity * (1 - od.discount) as sales
from northwind.employees e, northwind.order_details od, northwind.orders o, northwind.products p
where e.employee_id = o.employee_id 
	and od.order_id = o.order_id 
    and p.product_id = od.product_id
)

 

4. 데이터 분석

❓ 국가별 매출 추이 - USA

- 1998년 1분기 매출 증감액이 가장 큼

with cte_employees_detail as (
–- 생략
)
select 
    year,
    quarter, 
    round(sum(sales)::numeric,2) 매출, 
    round((sum(sales) - lag(sum(sales)) over (order by year,quarter))::numeric,2) 전분기대비매출증감액
from cte_employees_detail
where country='USA' 
group by 1,2;

❓ 국가별 매출 추이 - UK

- 1998년 1분기 매출 증감액이 가장 큼

with cte_employees_detail as (
–- 생략
)
select 
    year,
    quarter, 
    round(sum(sales)::numeric,2) 매출, 
    round((sum(sales) - lag(sum(sales)) over (order by year,quarter))::numeric,2) 전분기대비매출증감액
from cte_employees_detail
where country='UK' 
group by 1,2;

❓ 직원별 매출 기여도 - USA

- 3번 직원의 기여도가 가장 높고, 8번 직원의 기여도가 가장 낮음

with cte_employees_detail as (
-- 생략
), cte_981q_sales as (
select 
    year, 
    quarter, 
    employee_id, 
    round(sum(sales)::numeric, 2) as 매출액
from cte_employees_detail
where year='1998' and quarter='1' and country='USA'
group by 1,2,3
order by 1,2,3)
select *
, round(매출액/sum(매출액)over()*100)::varchar(10)||'%' as 기여도
from cte_981q_sales
order by 5 desc;

 

❓ 직원별 매출 기여도 - UK

- 9번 직원의 기여도가 가장 높고, 6번 직원의 기여도가 가장 낮음

with cte_employees_detail as (
-- 생략
), cte_981q_sales as (
select 
    year, 
    quarter, 
    employee_id, 
    round(sum(sales)::numeric, 2) as 매출액
from cte_employees_detail
where year='1998' and quarter='1' and country='UK'
group by 1,2,3
order by 1,2,3)
select *
, round(매출액/sum(매출액)over()*100)::varchar(10)||'%' as 기여도
from cte_981q_sales
order by 5 desc;

 

❓ 상품별 매출 기여도 - USA, UK

with cte_employees_detail as (
-- 생략
), a as (
select 
    employee_id,
    year,
    quarter,
    product_id,
    product_name,
    round(sum(sales)::numeric, 2) 상품별매출액
from cte_employees_detail
where country = 'USA' and year = '1998' and quarter = '1' and employee_id = '3'
group by 1,2,3,4,5
order by 6 desc
)
select
    product_name,
    상품별매출액,
    sum(상품별매출액) over () 전체매출액,
    round((상품별매출액 / sum(상품별매출액) over () * 100)::numeric, 0)||'%'  상품별기여도
from a
order by 6 desc

▲ USA 우수직원(3)의 상위 매출 상품 TOP 5 / USA 부진직원(8)의 상위 매출 상품 TOP 5

▲ UK 우수직원(9)의 상위 매출 상품 TOP 5 / UK 부진직원(6)의 상위 매출 상품 TOP 5