[STARTERS 4기 TIL] SQL 심화 #3 - WINDOW 함수, employees 테이블 분석 (230412)

 

📖 오늘 내가 배운 것

 

1. 임시테이블

2. 윈도우 함수

3. 시계열 분석

4. 윈도우 함수 - 프레임 지정

5. 과제

 

---

1. 임시테이블

복잡한 데이터셋 다루는 다양한 방법

	개념	장점	단점
ETL 스냅샷 테이블	쿼리 결과를 테이블에 저장	복잡한 로직 없이 테이블에서 원하는 쿼리 실행	실시간 데이터 보기 힘듬 저장 후 데이터 변경 어려움
VIEW	쿼리 결과를 가상 테이블로 생성	- 실시간 성 보장	테이터베이스 쓰기 권한 필요 유지보수 필요
임시테이블 ()	- 세션동안 유지되는 테이블 - CREATE INSERT로 생성	- 복잡한 로직 없이 테이블에 원하는 쿼리 실행 - 성능	데이터베이스 쓰기 권한
공통테이블 표현식 CTE	쿼리 결과에 이름을 붙여 테이블 처럼	쿼리를 간결하게 작성 가능 (가독성)	여러 CTE 구문 사용 시 중간 결과 확인이 어려움 적절한 주석 사용

가독성, 재사용성, 유지보수성, 실수 챙길 수 있다!

 

1.1 임시 테이블

임시 테이블 생성

create temporary table 테이블명 as 쿼리

 

임시 테이블 생성 연습

-- 임시 테이블 생성 연습!

-- 1. 반복 되는 컬럼 선별
-- 필요한각 테이블의 컬럼을 살펴보며 파악!
-- 주문번호, 고객번호, 주문일, 연,월,일, 제품번호, 제품단가, 수량, 할인율, 매출액

/**********************
임시 테이블 tmp_order_details 생성!
***********************/

create temporary table tmp_order_details as
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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, day
	 , sum(amount) as 매출액
	 , count(order_id) as 주문건수
	 , count(distinct customer_id) as 고객수
	 , count(distinct order_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.2 공통 테이블 표현식 CTE

사용법

WITH
테이블이름 AS(

      쿼리문

)

SELECT 구문

주의할 점은? : 쿼리와 붙여서 사용해야 한다! 한 몸으로 사용

 

CTE 생성 및 사용

/**********************
CTE 생성! cte_order_details
***********************/

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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, day
	 , sum(amount) as 매출액
	 , count(order_id) as 주문건수
	 , count(distinct customer_id) as 고객수
	 , count(distinct order_id) as 주문자수
	 , sum(amount) / count(distinct order_id) as 건당평균주문액
	 , sum(amount) / count(distinct customer_id) as 고객당평균주문액
from cte_order_details
group by 1,2,3;

 

검증하려면?

만든 주요 지표 쿼리를 다시 CTE로 만든 후 SUM!

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as amount
from orders o, order_details od
where o.order_id = od.order_id
)
, cte_order_info as (
select year, month, day
	 , sum(amount) as 매출액
	 , count(order_id) as 주문건수
	 , count(distinct customer_id) as 고객수
	 , count(distinct order_id) as 주문자수
	 , sum(amount) / count(distinct order_id) as 건당평균주문액
	 , sum(amount) / count(distinct customer_id) as 고객당평균주문액
from cte_order_details
group by 1,2,3
)
select sum(매출액), sum(주문건수) 
from cte_order_info;

 

2. 윈도우 함수

2.1 윈도우 함수란?

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

 

윈도우 프레임을 지정해서 그 안에서 함수가 실행

 

📌 윈도우 프레임 지정 방법?

1. 파티션 지정

partition by [컬럼이름]

 

2. 직접 행 지정

rows between <start> and <end>

 

* <start>, <end> 인자

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

 

📌 정렬은

order by [컬럼이름] 

 

2.2 윈도우 함수 종류

 

집계함수	집계함수(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	파티션 별 윈도우에서 다음 행 값
그룹 내 비율 함수	PERCENT_RANK	백분위 수
	NTILE	구간 나누기

 

1. 집계 함수 - SUM, COUNT

전체 집계 : 집계함수 OVER()

그룹 집계 : 집계함수 OVER(PARTION BY [컬럼 이름])

프레임 지정 : 집계함수 OVER(윈도우 프레임 지정)

 

📌 전체 집계 - over()

- 전체에 대한 비율 구할 때 많이 사용~

-- 윈도 집계 함수!
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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, day
	 , sum(amount) over()
from cte_order_details;

 

 

📌 전체 집계 - over(order by [컬럼])

order by에 정의한 컬럼 순으로 정렬을 해서 누적합 계산!

예시는 sum인데 count 하면 누적 count~

-- 윈도 집계 함수!
-- year-month에 따른 amount 누적합
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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, day
	 , sum(amount) over(order by year, month)
from cte_order_details;

 

📌 전체 집계 - over(partition by [컬럼] order by [컬럼])

-- year-month에 따른 amount 누적합
-- year이 바뀌면 누적을 새롭게 시작!
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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, day
	 , sum(amount) over(partition by year order by year, month)
from cte_order_details;

 

📌 전체 집계 : COUNT - over(order by [컬럼])

주의할 점 : order by 서서 정렬해 주면 확실하게 갈 수 있다.

-- 주문 건수 누적 카운트(연도-월-일 순서)
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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,day, amount
	 , count(order_id) over(order by year, month, day)
	 --distinct가 안 된다! 주문 건수로 하려면 가공 필요
from cte_order_details
order by 1,2,3;

 

📌 전체 집계 : COUNT - over(partition by [컬럼] order by [컬럼])

-- 주문 건수 누적 카운트(연도-월 순서)
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(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,day, amount
	 , count(order_id) over(partition by year, month order by year, month, day)
	 --distinct가 안 된다! 주문 건수로 하려면 가공 필요
from cte_order_details
order by 1,2,3;

 

2. LAG, LEAD

📌 lag = 이전 행 값 가져오기

옵션으로 숫자 주면 그만큼의 이전 행 값을 가져온다! lag([컬럼], 숫자)

-- 전월, 전일 대비 매출액 차이를 구하고 싶다!?
--LAG/LEAD 사용!
-- 일별 매출액 먼저 가져오기

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as amount
from orders o, order_details od
where o.order_id = od.order_id
), 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
)
-- 이전 행 = lag
select *
	 , lag(amount) over(order by year, month, day)
from cte_pre_amount;

 

📌 lead = 이후 행 값 가져오기

-- LEAD 사용!
-- 일별 매출액 먼저 가져오기

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as amount
from orders o, order_details od
where o.order_id = od.order_id
), 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
)
-- 이전 행 = lead (옵션으로 숫자 주면 그 만큼의 이전 행 값을 가져온다.)
select *
	 , lead(amount, 3) over(order by year, month, day)
from cte_pre_amount;

 

3. 시계열 분석

3.1 월별 매출 증감

--월별 매출 증감

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as amount
from orders o, order_details od
where o.order_id = od.order_id
), cte_pre_amount as (
select year, month
	 , sum(amount) as amount
from cte_order_details
group by 1,2
order by 1,2
)
-- 이전 행 = lead (옵션으로 숫자 주면 그 만큼의 이전 행 값을 가져온다.)
select year as 연
	 , month as 월
	 , amount as 매출액
	 , lag(amount) over(order by year, month) as 전월매출액
	 , amount - lag(amount) over(order by year, month) as 전월대비증감액
	 , (round(amount / abs(lag(amount) over(order by year, month)) * 100)::text || '%'::text)as 전원대비매출액비율
	 , case 
	 	when amount - lag(amount) over(order by year, month) < 0 then '-'
	 	when amount - lag(amount) over(order by year, month) >= 0 then '+'
	   end as 증감여부
from cte_pre_amount;

 

강사님 쿼리

-- 강사님 쿼리
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
), cte_pre_amount as (
select *
	 , lag(매출액) over(order by year, month) as 전월매출
from cte_order_details
)
select *
	 , 매출액-전월매출 as 매출증감액
	 , round(매출액/전월매출 * 100)::varchar(10) || '%' as 전월비 
	 , case 
	 	when 매출액-전월매출 < 0 then '-'
	 	when 매출액-전월매출 >= 0 then '+'
	   end as 증감여부
from cte_pre_amount;

 

3.2 작대비

lag에 옵션으로 12 = 12개월 전

-- 작대비 매출액
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
), cte_pre_amount as (
select *
	 , lag(매출액, 12) over(order by year, month) as 작년매출
from cte_order_details
)
select year, month, day, 매출액
	 , 매출액-작년매출 as 매출증감액
	 , round(매출액/작년매출 * 100)::varchar(10) || '%' as 전년비
	 , case 
	 	when 매출액-작년매출 < 0 then '-'
	 	when 매출액-작년매출 >= 0 then '+'
	   end as 증감여부
from cte_pre_amount;

 

3.3 매출 누계

📌 일별 매출 누계

--------------------
-- 매출 누계!!!
-- 일별 매출 누계 (1전체 누계, 2월별 파티션)
--1 전체 누계
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
), cte_pre_amount as (
select *
	 , sum(매출액) over(order by year, month, day) as 일별매출누계
from cte_order_details
)
select year, month, day, 매출액, 일별매출누계
from cte_pre_amount

 

📌 월별 매출 누계

-- 월별 매출 누계 (1전체 누계, 연도별 파티션)
with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
), cte_pre_amount as (
select *
	 , sum(매출액) over(order by year, month) as 월별매출누계
from cte_order_details
)
select year, month, day, 매출액, 월별매출누계
from cte_pre_amount

 

4. 윈도우 프레임 지정 (rows)

4.1 사용법

[ROWS BETWEEN start AND end]

 

CURRENT ROW : 현재 행

UNBOUNDED PRECEDING : 이전 행 전부

UNBOUNDED FOLLOWING : 이후 행 전부

 

N PRECEDING : N 번째 이전 행까지

N FOLLOWING : N 번째 이후 행까지

 

이동 평균 구할 때 주로 사용!

 

📌 1번 예제

rows between current row and n following

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
)
, cte_ordercnt as( --group by 썼기 때문에 CTE 만들어주고
--일별 주문 건수 (연습이니까 order_details 한 행이 한 건인 거로 치기)
select year, month, day, count(order_id) cnt
from CTE_ORDER_DETAILS
group by 1,2,3
order by 1,2,3
)
select *
	 --5행의 합을 보고 싶다면?
	 , sum(cnt) over(order by year, month, day rows between current row and 4 following)
from cte_ordercnt;

현재 행 ~ 이후 4개 행 누적합

 

📌 2번 예제

rows between n preceding and current row

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
)
, cte_ordercnt as( --group by 썼기 때문에 CTE 만들어주고
--일별 주문 건수 (연습이니까 order_details 한 행이 한 건인 거로 치기)
select year, month, day, count(order_id) cnt
from CTE_ORDER_DETAILS
group by 1,2,3
order by 1,2,3
)
select *
	 --5행의 합을 보고 싶다면?
	 , sum(cnt) over(order by year, month, day rows between 2 preceding and current row)
from cte_ordercnt;

이전 2개 행 ~ 현재 행 누적합

 

📌 5일 이동 평균 구해보기

with cte_order_details as (
select o.order_id, o.customer_id, o.order_date
	 --연월일 파생 컬럼을 만들기
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id, od.unit_price, od.quantity, od.discount
	 , round(od.unit_price * od.quantity * (1-od.discount)) as 매출액
from orders o, order_details od
where o.order_id = od.order_id
)
, cte_amount as( --group by 썼기 때문에 CTE 만들어주고
--일별 주문 건수 (연습이니까 order_details 한 행이 한 건인 거로 치기)
select year, month, day, round(sum(매출액)) 매출액
from CTE_ORDER_DETAILS
group by 1,2,3
order by 1,2,3
)
select year, month, day, 매출액
	 --5행의 합을 보고 싶다면?
	 , 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 매출액5일이동평균
from cte_amount;

 

5. 과제

직원들, 별 매출 (현황) 분석하기!

- 지표는 상의해서 설정

 

쿼리는

- CTE 사용, 윈도우 함수 사용

-- 직원별 절대 매출 및 매출 비율 (기여도)
with 
employees_sales as (
	select e.employee_id
	, (e.first_name||' '||e.last_name) as e_name
	, od.unit_price , od.quantity , od.discount
	, round( od.unit_price * od.quantity * (1-od.discount) ) as sales
	from orders o 
	left join order_details od ON od.order_id = o.order_id
	left join employees e ON o.employee_id = e.employee_id), 
e_total_sales as (
	select employee_id, e_name, sum(sales) as 총매출
	from employees_sales
	group by 1,2
	order by 1,2
)
select employee_id as 직원아이디, e_name as 직원이름, 총매출, round(총매출 / sum(총매출) over () * 100)::varchar(10) || '%' as 매출비율
from e_total_sales;


-- 직원별 - 카테고리별 매출 및 비율 
with 
e_c_sales as (
	 select e.employee_id, (e.first_name||' '||e.last_name) as e_name
	 , c.category_name, p.product_name
 	 , od.unit_price , od.quantity, od.discount,
 	 od.unit_price * od.quantity * (1-od.discount) as sales
	 from orders o
	 left join order_details od ON od.order_id = o.order_id
	 left join employees e ON o.employee_id = e.employee_id
	 left join products p on p.product_id = od.product_id
	 left join categories c on p.category_id = c.category_id), 
e_c_totals as (
	 select employee_id, e_name , category_name , sum(sales) as 총매출
	 from e_c_sales
	 group by 1, 2, 3
	 order by 1,2,3
	 )
select *, (총매출 / sum(총매출) over (partition by employee_id) * 100)::varchar(10)  || '%' as 비율
from e_c_totals;


-- 직원별 재주문율 
-- 먼저 직원과 고객 정보를 불러온다
with cte_employee_sales as (
select e.employee_id
	 , e.first_name||' '||e.last_name as employee_fullname
	 , o.customer_id
	 , o.order_id
from employees e 
	 left join orders o on e.employee_id = o.employee_id)
, cte_employee_reorder as (
-- 직원 별 고객 별 고객별 주문 횟수를 불러온다
select employee_id
	 , employee_fullname
	 , customer_id
	 , count(distinct order_id) as 고객별주문횟수
from cte_employee_sales
group by 1,2,3
),cte_employee_customer_orders as (
-- 여기서 직원 별 담당 고객과 주문 횟수 그리고 고객 별 주문 횟수가 2번 이상인 경우 1을 아닌 경우 0을 준다
-- 2번 이상 구매한 고객=재주문한 고객 1, 아닌 고객은 0으로 표현
select *
	 , case when 고객별주문횟수 >= 2 then 1 else 0 end as 재주문고객여부
from cte_employee_reorder
)
-- 최종적으로 직원 별로 재주문한 고객 수를 더하고 전체 고객 수로 나눠준다
select employee_id
	 , employee_fullname
	 , sum(재주문고객여부) as 재주문고객
	 , count(distinct customer_id) as 전체고객수
	 , sum(재주문고객여부) * 100 / count(customer_id)||'%' as 재주문율
from cte_employee_customer_orders
group by 1,2
order by 5 DESC


-- 직원별 고객사별 매출, 주문건수, 건당주문금액 
with
cte_employee_sales as
	(select e.employee_id, e.first_name||' '||e.last_name as employee_fullname,
	o.customer_id, o.order_id, od.unit_price, od.quantity, od.quantity, c.company_name, c.country, c.city, c.contact_name,
	round(od.unit_price * od.quantity * (1-od.discount)) as amount
	from employees e
	left join orders o on e.employee_id = o.employee_id
	left join order_details od on od.order_id = o.order_id
	left join customers c on c.customer_id = o.customer_id)
select employee_id, employee_fullname, customer_id, 
       company_name, country, city, contact_name,
       count(distinct order_id) as order_count, sum(amount) as amount_sum,
       sum(amount)/count(distinct order_id) as 건당주문금액,
       rank() over(partition by employee_id order by sum(amount) desc)
from cte_employee_sales
group by 1,2,3,4,5,6,7
order by 1,2,9 desc;


-- 직원별 근속기간과 매출의 상관분석 
with 
cte_org as ( 
	select concat(e.first_name,' ',e.last_name) as emp_name,
		   round((max(o.shipped_date) over () - e.hire_date) / 30) as hire_mon, 
	       round(od.unit_price * od.quantity * (1 - od.discount)) as sales 
	from northwind.orders o, northwind.employees e, northwind.shippers s, northwind.order_details od
	where e.employee_id = o.employee_id 
	and s.shipper_id = o.ship_via and
	o.order_id = od.order_id
), 
cte_agg as (
	select emp_name,
	avg(hire_mon) as hire_mon, 
	sum(sales) as sales 
	from cte_org
	group by emp_name 
	order by emp_name
	)
select corr(hire_mon, sales) from cte_agg;

 

---

💪🏻 좋았던 점, 앞으로 개선해야 할 점 (추가로 배워야 할 점)

 

📌 다수의 테이블-복잡한 데이터 다루는 방법을 학습할 수 있어서 좋았고, 분석 시 윈도우 함수 활용 방법을 학습했다!

윈도우 함수는 더 능숙하게 사용할 수 있도록 추가적인 학습이 필요하다.

📌 과제 : 근속 기간-매출 간 상관관계 분석을 수행했는데, 너무 당연한 결과였기 때문에

기간, 소속 국가 등 속성을 추가해서 진행했어야 했다는 아쉬움이 있다! 추후에 진행하게 된다면 이 부분 고려하도록 하자

 

#유데미, #유데미코리아, #유데미부트캠프, #취업부트캠프, #부트캠프후기, #스타터스부트캠프, #데이터시각화 #데이터분석 #태블로