[스타터스 TIL] 47일차.SQL 실전 트레이닝 (3)

웅진X유데미 STARTERS/TIL (Today I Learned)

[스타터스 TIL] 47일차.SQL 실전 트레이닝 (3) - CTE, 윈도우 함수

라밍쏭 2023. 4. 13. 18:55

728x90

1. 복잡한 데이터셋 다루기

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

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

1.2 임시 테이블

CREATE TEMPORARY TABLE 테이블 명 AS 
쿼리

💡 구매지표 분석을 할 때, 반복되는 부분을 기본 데이터베이스 테이블로 만들 때 사용할 수 있다.

1) 임시 테이블 생성

- orders 테이블 : order_id, customer_id, order_date
- order_details 테이블 :product_id, unit_price, quantity, discount
- 주문번호, 고객번호, 주문일(년,월,일), 제품번호, 제품단가, 수량, 할인율, 매출액

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
	);

2) 월별 매출액, 주문건수, 주문자수, 건당 평균 주문액, 인당 평균 주문액 추출

SELECT YEAR
	 , MONTH
	 , sum(sales) AS 매출액
	 , count(DISTINCT order_id) AS 주문건수
	 , count(DISTINCT customer_id) AS 주문자수
	 , sum(sales) / count(DISTINCT order_id) AS 건당평균주문액
	 , sum(sales) / count(DISTINCT customer_id) AS 인당평균주문액
FROM tmp_order_details
GROUP BY 1, 2;

3) 일별 매출액, 주문건수, 주문자수, 건당 평균 주문액, 인당 평균 주문액 추출

[+] YEAR, MONTH, DAY, QUARTER 별로 해당 데이터들을 추출할 수 있다. (이 때 GROUP BY에 관련 컬럼 추가)

SELECT YEAR
	 , MONTH
	 , DAY 
	 , sum(sales) AS 매출액
	 , count(DISTINCT order_id) AS 주문건수
	 , count(DISTINCT customer_id) AS 주문자수
	 , sum(sales) / count(DISTINCT order_id) AS 건당평균주문액
	 , sum(sales) / count(DISTINCT customer_id) AS 인당평균주문액
FROM tmp_order_details
GROUP BY 1, 2, 3;

2. 공통테이블 표현식 CTE (Common Table Expression)

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

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

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

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

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

- 일회용이므로 재사용하려면 코드를 저장해두어야 한다.

1) 임시 테이블 생성 + 데이터 추출

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
SELECT YEAR
	 , MONTH
	 , DAY 
	 , sum(sales) AS 매출액
	 , count(DISTINCT order_id) AS 주문건수
	 , count(DISTINCT customer_id) AS 주문자수
	 , sum(sales) / count(DISTINCT order_id) AS 건당평균주문액
	 , sum(sales) / count(DISTINCT customer_id) AS 인당평균주문액
FROM tmp_order_details
GROUP BY 1, 2, 3;

2) 임시테이블 2개 이상 만들기

[+] 임시 테이블을 1개 더 만들어서 검증을 할 수 있다.

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_order_info AS (
	SELECT YEAR
		 , MONTH
		 , DAY 
		 , sum(sales) AS 매출액
		 , count(DISTINCT order_id) AS 주문건수
		 , count(DISTINCT customer_id) AS 주문자수
		 , sum(sales) / count(DISTINCT order_id) AS 건당평균주문액
		 , sum(sales) / count(DISTINCT customer_id) AS 인당평균주문액
	FROM tmp_order_details
	GROUP BY 1, 2, 3
	)
SELECT sum(매출액)
	 , sum(주문건수)
FROM cte_order_info;

3. 윈도우 함수

윈도우 프레임을 지정하여 함수를 적용한다.

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

3.1 윈도우 함수 분류

집계 함수	집계함수 (count, sum 등)
순위함수	RANK	중복 순위 존재 시 건너띔 (1, 1, 3, 4, 5, 5, 7...)
	DENSE_RANK	중복 순위 존재 시 건너 뛰지 않음 (1, 1, 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	구간 나누기

3.2 집계

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

✅ 전체 집계

→ 레코드 전체가 하나의 집계

→ 비율을 계산할 때 사용할 수 있음 (전체 대비 %)

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
SELECT YEAR, MONTH, DAY
	 , sum(sales) over() -- 전체에 대한 SUM하기
FROM cte_order_details;

✅ 그룹 집계 (order by)

→ year 단위로 윈도우 지정하여 year 순서로 누적 합계

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
SELECT YEAR, MONTH, DAY
	 , sum(sales) over(ORDER BY YEAR) -- YEAR 단위로 SUM을 함
FROM cte_order_details;

✅ 그룹 집계 (partition by + order by)

→ partition by로 설정한 단위 (year)로 누적 합계가 진행됨

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
SELECT YEAR, MONTH, DAY
	 , sum(sales) over(PARTITION BY YEAR ORDER BY YEAR, MONTH) -- YEAR 단위로 누적합계 진행
FROM cte_order_details;

[+] 주문건수 누적 카운트 (연도-월 순서로)

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
SELECT YEAR, MONTH, DAY
	 , count(order_id) over(PARTITION BY YEAR, MONTH ORDER BY YEAR, MONTH, DAY)
FROM cte_order_details;

3.3 이전 행 추출 (lag)

💡 전 일 매출액을 함께 확인할 때 사용함

lag(컬럼명, n) over()

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_pre_sales AS (
	SELECT YEAR
		 , MONTH
		 , DAY
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 3
	)
SELECT *
	 , lag(총매출액) over(ORDER BY YEAR, MONTH, DAY) AS 전일매출
FROM cte_pre_sales;

3.4 다음 행 추출 (lead)

lead(컬럼명, n) over()

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_pre_sales AS (
	SELECT YEAR
		 , MONTH
		 , DAY
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 3
	)
SELECT *
	 , lead(총매출액) over(ORDER BY YEAR, MONTH, DAY) AS 익일매출
FROM cte_pre_sales;

3.5 윈도우 프레임 지정

ROWS BETWEEN start AND end

👇 start와 end에 들어갈 프레임

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

1) 예시

sum(order_cnt) over(ORDER BY YEAR, MONTH, DAY ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)

→ 현재 행부터 이후 4행까지의 합산

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_sales AS (
	SELECT YEAR, MONTH, DAY
		 , count(order_id) AS order_cnt
	FROM cte_order_details
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 3)  -- 일별 주문건수
SELECT *
	 , sum(order_cnt) over(ORDER BY YEAR, MONTH, DAY ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)
FROM cte_sales;

2) 5일 이동평균

avg(total_sales) over(ORDER BY YEAR, MONTH, DAY ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

→ 현재 행 기준 이전 4개의 행부터 현재행까지의 평균 값

[+] 평균을 내는 행의 갯수가 5개 미만이면 NULL값 출력하기 (→ CASE WHEN 활용)

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_sales AS (
	SELECT YEAR, MONTH, DAY
		 , sum(sales) AS total_sales
	FROM cte_order_details
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 3)
SELECT *
	 , CASE WHEN row_number() over(ORDER BY YEAR, MONTH, DAY) >= 5
	 			 THEN avg(total_sales) over(ORDER BY YEAR, MONTH, DAY ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
	 		ELSE NULL
	 		END AS 이동평균
FROM cte_sales;

4. 매출 증감 분석

4.1 월별 매출 증감

- YEAR, MONTH를 기준으로 총 매출액을 출력

- LAG() 함수를 활용하여 전월매출액을 계산

- 총 매출액과 전월 매출액의 차이를 구하여 비율까지 구하기

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_sales AS (
	SELECT YEAR
		 , MONTH
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2
	ORDER BY 1, 2
	), cte_pre_sales AS (
		SELECT *
			 , lag(총매출액) over(ORDER BY YEAR, MONTH) AS 전월매출액
		FROM cte_sales
		)
SELECT *
	 , 총매출액 - 전월매출액 AS 전월대비증감액
	 , (round((총매출액 / 전월매출액)*100)::varchar(10)||'%') AS 전월대비매출액비율
	 , (CASE WHEN (총매출액 - 전월매출액) < 0 THEN '-' 
	 		 WHEN (총매출액 - 전월매출액) > 0 THEN '+'
	 		 WHEN (총매출액 - 전월매출액) = 0 THEN '동일'
	 		 ELSE '해당없음'
	 		 END) AS 증감여부
FROM cte_pre_sales;

4.2 일별 매출 누계 (전체 누계)

sum(총매출액) OVER(ORDER BY YEAR, MONTH, DAY)를 통해 일 별 매출 누계(+전체 누계) 구하기

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
)
-- 1) 일(DAY) 기준으로 총 매출액 계산
, cte_sales AS (
	SELECT YEAR, MONTH, DAY
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 3)
-- 2) 일 별로 총매출액 계산
SELECT YEAR, MONTH, DAY 
	 , sum(총매출액) OVER(ORDER BY YEAR, MONTH, DAY) AS 총매출액
FROM cte_sales;

4.3 일별 매출 누계 (월 별 누계)

sum(총매출액) OVER(PARTITION BY YEAR, MONTH ORDER BY YEAR, MONTH, DAY)

→ Year, Month로 Partition By를 하여 월 별로 누계가 계산이 되며 다음 월부터는 새롭게 누계가 진행되는 방식

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), 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 YEAR, MONTH, DAY 
	 , sum(총매출액) OVER(PARTITION BY YEAR, MONTH ORDER BY YEAR, MONTH, DAY) AS 총매출액
FROM cte_sales;

4.4 월별 매출 누계 (전체 누계)

sum(총매출액) OVER(ORDER BY YEAR, MONTH)를 통해 월 별 매출 누계(+전체 누계) 구하기

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_sales AS (
	SELECT YEAR, MONTH
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2
	ORDER BY 1, 2)
SELECT YEAR, MONTH
	 , sum(총매출액) OVER(ORDER BY YEAR, MONTH) AS 총매출액
FROM cte_sales;

4.5 월별 매출 누계 (연도별 누계)

sum(총매출액) OVER(PARTITION BY YEAR ORDER BY YEAR, MONTH)

→ Year로 Partition By를 하여 연도 별로 누계가 계산이 되며 다음 연도부터는 새롭게 누계가 진행되는 방식

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 sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
), cte_sales AS (
	SELECT YEAR, MONTH
		 , sum(sales) AS 총매출액
	FROM cte_order_details
	GROUP BY 1, 2
	ORDER BY 1, 2)
SELECT YEAR, MONTH
	 , sum(총매출액) OVER(PARTITION BY YEAR ORDER BY YEAR, MONTH) AS 총매출액
FROM cte_sales;

5. 팀 프로젝트

northwind 데이터 셋을 활용하여 직원 별 매출을 분석하는 주제로 팀 프로젝트를 진행하였다.

5.1 ERD로 테이블 선정 및 분석 주제 선정

ERD를 보며 어떤 테이블과 데이터를 선정하고 이를 바탕으로 분석을 할지에 대해 확인해보았다.

✔ 활용할 테이블 : employees, orders, order_details, customers

✔ 분석 주제

- 국가 별 높은 매출을 기록한 직원

- 연령별 직원의 성과

- 직원별 가장 큰 매출을 올린 상품

- 직원별 매출 판매량 best, worst

- 직원별 매출 추이

5.2 데이터 분석 진행

1) 직원 별 매출, 판매량

orders, employees, order_details 테이블을 조인하여 각 직원 별 매출, 판매량 구하기

✔ 쿼리 작성

SELECT e.employee_id
	 , e.last_name
	 , e.first_name
	 , sum(unit_price * quantity * (1-discount)) AS total_sales
	 , sum(od.quantity) AS total_quantity
FROM orders o 
	INNER JOIN employees e
		ON o.employee_id = e.employee_id 
	INNER JOIN order_details od
		ON o.order_id = od.order_id
GROUP BY 1
ORDER BY 4 desc;

✔ 작성한 쿼리에 대한 검증 진행

SELECT sum(total_sales) from
(SELECT e.employee_id
	 , e.last_name
	 , e.first_name
	 , sum(unit_price * quantity * (1-discount)) AS total_sales
	 , sum(od.quantity) AS total_quantity
FROM orders o 
	INNER JOIN employees e
		ON o.employee_id = e.employee_id 
	INNER JOIN order_details od
		ON o.order_id = od.order_id
GROUP BY 1
ORDER BY 4 DESC) a;

2) 직원별 매출 top 상품

각 직원이 판매한 상품들 중에서 가장 큰 매출을 올린 상품을 확인해보자.

-- 직원별 가장 큰 매출을 올린 상품 구하기
WITH employee_table as
(SELECT o.order_id
      , order_date
      , o.employee_id
      , e.last_name || ' ' || e.first_name as employee_name
      , od.product_id
      , p.product_name
      , (od.unit_price * od.quantity * (1-od.discount)) sales
	  , od.unit_price, od.quantity, od.discount
FROM employees e, orders o, order_details od, products p
WHERE e.employee_id = o.employee_id AND od.order_id = o.order_id and od.product_id = p.product_id
),
-- 1) 직원의 상품별 총 매출
employee_product_sales AS
(SELECT employee_id
      , employee_name
      , product_id
      , product_name
      , sum(sales) sales
      , sum(quantity) quantity
FROM employee_table
GROUP BY employee_id, employee_name, product_id, product_name),
-- 2) 직원별, 상품별 top 매출
employee_top_sales AS
(SELECT employee_id
      , employee_name
      , max(sales) max_sales
FROM employee_product_sales
GROUP BY 1, 2)
-- 3) 
SELECT e1.employee_id
     , e1.employee_name
     , e2.product_id
     , e2.product_name
     , e1.max_sales
     , e2.quantity
FROM employee_top_sales e1, employee_product_sales e2
WHERE e1.employee_id = e2.employee_id AND e1.max_sales = e2.sales
ORDER BY max_sales desc
;

→ Côte de Blaye라는 상품이 가장 많이 판매되었다.

→ Peacock Margaret이 해당 상품에 대한 매출액이 가장 많다.

3) 직원의 월 별 매출 추이

직원 별로 전월 대비 매출 증가액과 전월 대비 매출 증가 비율을 구해보자.

✔ 쿼리 작성

-- 직원의 월별 매출 추이
WITH employee_table as
(SELECT o.order_id, order_date, o.employee_id, e.first_name || ' ' || e.last_name employee_name, 
	od.product_id, p.product_name, (od.unit_price * od.quantity * (1-od.discount)) sales,
	od.unit_price, od.quantity, od.discount
FROM employees e, orders o, order_details od, products p
WHERE e.employee_id = o.employee_id AND od.order_id = o.order_id and od.product_id = p.product_id
),
-- 년월별 직원의 매출
employee_yyyymm_sales AS (
SELECT to_char(order_date, 'YYYY-MM') order_date,
	employee_id, employee_name, sum(sales) sales
FROM employee_table
GROUP BY 1, 2, 3
ORDER BY 2,1
),
-- 전월 매출액
employee_yyyymm_sales2 AS (
SELECT *, LAG(sales, 1) over(PARTITION BY employee_id ORDER BY employee_id, order_date) 전월매출액
FROM employee_yyyymm_sales
ORDER BY 2,1
)
SELECT *, sales - 전월매출액 전월대비증감액,
	round(sales/전월매출액 * 100) 전월대비매출비율
FROM employee_yyyymm_sales2
ORDER BY 2,1;

✔ 작성한 쿼리에 대한 검증 진행

WITH employee_table as
(SELECT o.order_id, order_date, o.employee_id, e.first_name || ' ' || e.last_name employee_name, 
	od.product_id, p.product_name, (od.unit_price * od.quantity * (1-od.discount)) sales,
	od.unit_price, od.quantity, od.discount
FROM employees e, orders o, order_details od, products p
WHERE e.employee_id = o.employee_id AND od.order_id = o.order_id and od.product_id = p.product_id
),
-- 년월별 직원의 매출
employee_yyyymm_sales AS (
SELECT to_char(order_date, 'YYYY-MM') order_date,
	employee_id, employee_name, sum(sales) sales
FROM employee_table
GROUP BY 1, 2, 3
ORDER BY 2,1
),
-- 전월 매출액
employee_yyyymm_sales2 AS (
SELECT *, LAG(sales, 1) over(PARTITION BY employee_id ORDER BY employee_id, order_date) 전월매출액
FROM employee_yyyymm_sales
), test as
(SELECT *, sales - 전월매출액 전월대비증감액,
	round(sales/전월매출액 * 100) 전월대비매출비율
FROM employee_yyyymm_sales2
ORDER BY 2,1)
SELECT sum(sales) FROM test
;

4) 국가별 직원의 매출

각 국가, 도시 별로 직원의 매출을 구해보자.

직원들은 여러 국가, 도시를 관리하고 있으며, 전체적으로 잘하는 직원이 아닌 특정 국가에서 잘하는 직원을 확인하고자 한다.

✔ 쿼리 작성

select c.country 
	 , c.city 
	 , e.first_name ||' '|| e.last_name as employee_name
	 , sum(od.unit_price * od.quantity * (1-od.discount)) as city_sales
from customers c , orders o , employees e , order_details od 
where c.customer_id = o.customer_id 
  and o.order_id = od.order_id 
  and o.employee_id = e.employee_id 
group by 1,2,3
order by c.country ,c.city ;

✔ 작성한 쿼리에 대한 검증 진행

select sum(city_sales) 
from (select c.country 
			, c.city 
			, e.first_name ||' '|| e.last_name as employee_name
			, sum(od.unit_price * od.quantity * (1-od.discount)) as city_sales
		from customers c , orders o , employees e , order_details od 
		where c.customer_id = o.customer_id 
		  and o.order_id = od.order_id 
		  and o.employee_id = e.employee_id 
		group by 1,2,3
		order by c.city
		) a;

5) 연령별 직원의 성과

직원의 birth_date를 활용하여 각 태어난 해 별로 매출액이 어떠한지 확인을 해보자.

with add_year as(
select to_char(birth_date, 'YYYY')::integer AS birth_year
	 , sum(unit_price * quantity * (1-discount)) AS sales
from employees e , order_details od , orders o
where e.employee_id = o.employee_id
  and o.order_id = od.order_id
group by birth_year
order by birth_year)
SELECT CASE WHEN birth_year >= 1960 THEN '1960'
			WHEN birth_year >= 1950 THEN '1950'
			WHEN birth_year >= 1940 THEN '1940'
			WHEN birth_year >= 1930 THEN '1930'
			END AS year_2
	 , round(avg(sales))
from add_year
GROUP BY year_2
ORDER BY year_2;

→ 1930년대생의 매출이 가장 높다.

→ 전반적으로 나이가 많을수록 매출이 높은 것을 확인할 수 있다.

5.3 시각화

1) 직원 별 매출, 판매량

각 직원의 성과를 가장 간단하게 볼 수 있는 리스트이며, 판매량이 높은 직원이 매출액이 높은 경향을 띄는 것을 확인할 수 있다.

2) 직원별 매출 top 상품

직원별 매출 top 상품을 확인할 수 있는 리스트 차트이다.
주로 Côte de Blaye가 여러 직원들의 최고 매출 상품인것을 알 수 있으며, 수량에 비해서 매출액이 큰 것을 보아 해당 상품의 판매액이 높은 것을 확인할 수 있다.

3) 직원의 월 별 매출 추이

Margaret Peacock는 1998-01 이전까지 평균보다 높거나 평균에 근접한 매출을 내고 있으나 1998-02부터는 평균보다 낮은 매출을 달성하고 있다.
Steven Buchanan은 매출이 전월에 비해 급격하게 상승한 몇몇 달을 제외하면 대부분 평균보다 낮은 수익을 내고 있다.

4) 국가별 직원의 매출

한 국가 안에 상품을 판매하는 여러 직원이 있는데,
그 중에서 가장 매출을 잘 내고있는 직원의 이름과 매출성과를 지도에서 볼 수 있다. Margaret Peacock이 여러 나라에서 좋은 성과를 보여주는것을 알 수 있다.

5) 연령별 직원의 성과

어느 연령대가 평균적으로 가장 수익을 잘 내는지 확인 결과, 연령대가 높을수록 매출이 높은 추세를 보인다.

5.4 발표자료

💡 회고

오늘 주어진 팀 프로젝트를 4시간동안 끝내려니 매우매우 힘들었다.

주제 정하고, SQL로 데이터 추출하고 분석하고, 태블로로 시각화하고 마지막으로 발표자료까지 만드는데 주어진 시간은 단 4시간이라니... 정말로 빡셌다.

대충할 수 없는 우리이기에 더욱더 열심히하고 그만큼 시간도 많이 썼다.

팀 프로젝트를 하면서 각자 특정 주제를 정해서 쿼리를 짰다.

그리고 다같이 시각화하고 발표자료 만들고 마무리를 하고 있었는데, 뭔가 데이터가 이상해서 혹시나 하는 마음에 팀원들에게 내 쿼리를 확인해달라고 했다.

혹시나가 역시나였다.ㅠㅠ😭😭

쿼리가 잘못 되었던 것이었고 그로 인해서 결과물도 잘못 나온 것이었다....... 이것때문에 6시에 겨우 갈 수 있었는데 7시에 가게 되었다..흑흑...

나혼자 늦게 가는 것은 상관없지만 팀원들이 나 때문에 늦게 가는게 너무너무너무나도 미안했다.

주말동안 SQL 쿼리 연습 더 해서 다음주, 또 해커톤 때 다른 팀원들에게 피해가 가지 않도록 진짜 열심히 해야겠다...🥴💪

728x90

'웅진X유데미 STARTERS > TIL (Today I Learned)' 카테고리의 다른 글

[스타터스 TIL] 49일차.SQL 실전 트레이닝 (5) - Z차트, 그룹함수 (GROUPING SETS, ROLLUP, CUBE) (0)	2023.04.14
[스타터스 TIL] 48일차.SQL 실전 트레이닝 (4) - 제품/카테고리 매출 지표 분석, ABC 분석 (0)	2023.04.13
[스타터스 TIL] 46일차.SQL 실전 트레이닝 (2) - 컬럼 연산자, 날짜/시간형 데이터, 다중 행 함수 (0)	2023.04.11
[스타터스 TIL] 45일차.SQL 실전 트레이닝 (1) - 데이터 분석, 테이블파악 (0)	2023.04.10
[스타터스 TIL] 44일차.태블로 실전 트레이닝 (20) - 태블로 자격증 공부 (Test 1, Test 2) (1)	2023.04.08

현재글[스타터스 TIL] 47일차.SQL 실전 트레이닝 (3) - CTE, 윈도우 함수

ARAMDAUN

부트캠프후기, 태블로, 파이썬 코딩테스트, 유데미부트캠프, 데이터분석, Cos Pro 2급, SQL, 데이터시각화, 취업부트캠프, 유데미큐레이션, COS PRO 파이썬, 유데미코리아, 스타터스부트캠프, 코딩테스트, 백준, 데이터드리븐, 파이썬, 유데미, Python, MySQL,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31