[TIL] 47일차 TIL(20230412) - SQL CTE, WINDOW 함수

 

 

[TIL] 47일차 TIL(20230412) - SQL CTE, WINDOW 함수

 

 

 

📗 임시테이블, CTE

 

✅ 임시테이블 생성

임시 테이블은 현재 세션이 유지되는 동안만 존재하는 테이블로, 반복적으로 사용해야하는 쿼리가 있다면 해당 쿼리를 사용하여 테이블을 임시로 생성해둔 뒤 지속적으로 활용할 수 있다. 임시 테이블은 실제 테이블과 동일한 방식으로 사용 가능하다. (SELECT ~ FROM)

CREATE TEMPORARY TABLE 테이블명 AS
(QUERY ~)

 

 

✅ 공통테이블 표현식 CTE

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

- 복잡한 쿼리문이 반복해서 사용될 때 유용하다.

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

- 일회용이므로 재사용하려면 코드를 저장해두어야 하며, WITH구문과 SELECT 구문은 연결되어서 사용되어야 한다.

WITH cte_order_details AS (
QUERY ~ 
)
SELECT * FROM cte_order_details;

 

📗 WINDOW 함수

 

✅ 집계

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

- 그룹 집계: 집계함수 OVER(PARTITION BY <컬럼이름>)

- 윈도우프레임 집계: 집계함수 OVER(ROWS BETWEEN start AND end)

 

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY,
sum(SALES) over() --전체의 합을 모든 row에 동일하게 보여줌, 비율을 구할 때 유용
FROM CTE_ORDER_DETAILS;

 

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY, 
	sum(SALES) over(ORDER BY order_year)  --년도별 합을 구하고, 누적
FROM CTE_ORDER_DETAILS;

집계함수에 over(ORDER BY order_year)를 지정한 결과 year단위로 윈도우를 지정하여 year순서로 누적 합계를 반환한다.

 

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY, 
	sum(SALES) over(PARTITION BY order_year ORDER BY order_year, order_month)  -- 동일한 partition 안에서 윈도우 함수를 적용
FROM CTE_ORDER_DETAILS;

집계함수에 PARTITION BY order_year를 사용한 결과, 다음 년도가 되면 누적 값을 초기화하여 집계하게 된다.

 

SELECT *, lag(amount, 3) over(ORDER BY order_year, order_month, order_day) 3전일매출
FROM cte_pre_amount

 

• 집계함수 over(PARTITION BY ~ ORDER BY ~ )
• PARTITION BY는 집계함수를 적용할 그룹느낌으로, 해당 그룹을 벗어나면 결과를 초기화한 뒤 다시 집계
• ORDER BY는 집계함수를 적용할 기준 느낌으로, 해당 컬럼별 차원을 기준으로 집계를 적용
  • ex) sum over(order by year, month): 월별 합계를 보여주고 누적, 일자별 합계를 알 수 없음

 

 

✅ 월별 매출 변동 집계

- lag함수를 사용하여 전월 매출을 가져오고, 현재 월과 전월을 비교하여 전월대비 증감액, 전월대비 매출 비율 등을 구하는 쿼리를 작성했다.

 

WITH cte_order_details AS (
SELECT O.ORDER_ID, O.customer_id, O.order_date, 
	DATE_PART('YEAR', O.order_date) order_year, 
	DATE_PART('month', O.order_date) order_month, 
	DATE_PART('day', O.order_date) order_day,
	date_part('quarter', o.order_date) order_quarter, 
	od.product_id, od.unit_price, od.quantity, od.discount,
	od.quantity * od.unit_price * (1-od.discount) sales
FROM ORDERS O, ORDER_DETAILS OD
WHERE O.ORDER_ID = OD.ORDER_ID
)
, cte_pre_amount as(
SELECT order_year, order_month, sum(sales) amount
FROM cte_order_details
GROUP BY 1,2
ORDER BY 1,2
)
, cte_pre_amount2 as(
SELECT *, lag(amount, 1) over(ORDER BY order_year, order_month) 전월매출액
FROM cte_pre_amount
)
SELECT *, amount - 전월매출액 전월대비증감액,
	round((amount / 전월매출액 * 100)) || '%' 전월대비매출비율,
	CASE 
	WHEN amount - 전월매출액 >= 0 THEN '+'
	WHEN amount - 전월매출액 < 0 THEN '-'
	WHEN 전월매출액 IS NULL THEN '해당없음'
	WHEN amount - 전월매출액 = 0 THEN '동일'
	END AS 증감여부
FROM cte_pre_amount2

 

 

 

✅ 윈도우 프레임 지정

- ROWS BETWEEN start AND end

- start와 end에 올 수 있는 요소들은 다음과 같다.

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

 

- SUM(cnt) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN CURRENT row AND 4 FOLLOWING)를 이용해 현재 행에서 이후 4개의 행을 포함한 총5개의 행의 합계를 구하는 window_sum을 구현할 수 있다.

- 이동평균을 구하고싶다면, sum을 avg로만 변경해주면 된다.

WITH cte_order_details AS (
SELECT O.ORDER_ID, O.customer_id, O.order_date, 
	date_part('YEAR', O.order_date) order_year,
	DATE_PART('month', O.order_date) order_month, 
	DATE_PART('day', O.order_date) order_day,
	date_part('quarter', o.order_date) quarter, 
	od.product_id, od.unit_price, od.quantity, od.discount,
	od.quantity * od.unit_price * (1-od.discount) amount
FROM ORDERS O, ORDER_DETAILS OD
WHERE O.ORDER_ID = OD.ORDER_ID
), cte_ordercnt AS (
-- 일별 주문 건수
SELECT order_year, order_month, order_day, count(order_id) cnt
FROM cte_order_details
GROUP BY 1,2,3
ORDER BY 1,2,3
)
SELECT *,
	SUM(cnt) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN CURRENT row AND 4 FOLLOWING)
FROM cte_ordercnt;

 

✅ 5일 이동평균

SELECT *,
	CASE WHEN count(amount) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN 4 PRECEDING AND CURRENT row) = 5
	THEN avg(amount) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN 4 PRECEDING AND CURRENT row)
	END AS moving_avg
FROM cte_amount;

 

 

 

📗 직원의 매출 분석

 

✅ 직원별 매출 & 판매량

먼저 전반적인 현황을 볼 수 있는 직원별 총 매출과 판매량을 구했다.

전체 매출액을 기준으로 가장 많은 성과를 낸 직원은 Margaret Peacock이었고, 가장 낮은 성과를 낸 직원은 Steven Buchnan이었다.

 

 

-- 직원별 매출, 판매량 구하기
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;

 

 

✅ 직원별 매출 Top 상품

직원들이 어떤 상품을 많이 판매하고 있는지, 직원별로 주력 판매 상품에 차이가 있는지 비교하였다.

결과적으로 대부분의 직원이 Cote de Blaye라는 상품에서 가장 많은 매출을 달성하고 있었다.

 

 

-- 직원별 가장 큰 매출을 올린 상품 구하기
WITH employee_table as
(SELECT o.order_id, order_date, o.employee_id, e.last_name || ' ' || e.first_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_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
),
-- 직원별, 상품별 top 매출
employee_top_sales AS
(SELECT employee_id, employee_name, max(sales) max_sales
FROM employee_product_sales
GROUP BY 1, 2)
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
;

 

 

✅ 직원의 월별 매출 추이

직원들의 월별 매출액 변동과 전반적인 성과에 대해 알아보고자 월별 매출 추이를 구했다.

그래프를 수록하지는 않았지만 전월대비 증감액과 비율을 구하기 위해 lag 함수를 사용했다.

lag(sales, 1) over(partition by employee_name over employee_name, order_date)

order by와 partition을 제대로 지정해주지 않으면 동일한 직원의 전달 성과가 아닌, 다른 직원의 성과와 비교하게 될 수도 있어 집계함수 내에 올바른 옵션들을 지정해주어야 한다.

 

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

 

-- 직원의 월별 매출 추이
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;

 

 

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

한 국가 안에 상품을 판매하는 여러 직원이 있는데, 그 중에서 가장 매출을 잘 내고있는 직원의 이름과 매출 성과를 구했다.

동일한 지역 내에서의 각 직원들의 성과를 비교했을 때, 역시 Margaret Peacock이 가장 좋은 성과를 내고 있는 지역들이 꽤 많았다.

 

 

-- 도시별 직원의 매출 구하기
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 ;

 

 

✅ 출생 연도별 직원의 성과

추가적으로 어떤 연령대의 직원이 가장 좋은 성과를 내고있는지 확인했다.

결과적으로 연령대가 높을수록 매출이 높은 추세를 확인할 수 있었다.

 

 

-- 연령대별 직원들의 평균 매출
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;

 

✅ 결론

- 전 부문에서 가장 우수한 성과를 거둔 Margaret Peacock은 나이가 가장 많은 직원으로, 이를 통해 좋은 성과를 내기 위한 하나의 요소가 '경험'일 수 있다는 결론을 내렸다.

 

 

 

---

 

소감 및 정리

집계함수에 대해 배웠는데 이해된 듯 하면서 약간 헷갈리는 부분들이 있어 추가적인 정리가 필요할 것 같다. 간단한 분석 과제를 진행했는데, 배운게 많아지면서 sql > 태블로 > ppt까지 모든 작업을 진행하다보니 정규 시간 내에 끝내기 어려웠다. 단기간에 결론까지 도출하려다보니 완벽하지 못하고, 논리적으로 오류가 있는 부분도 존재하겠지만 어쨋든 나름대로 생각해볼만한 결론을 얻어낸 점이 의미있었다고 생각한다. SQL을 배우기 시작하면서 태블로를 할 때보다 하루에 다루는 양이 더 많아진 것 같다. 힘들기도 하지만 배운 내용을 그 날 안에 어느정도 소화하고 적용까지 해본다는 점에서 실력은 많이 늘어가고 있는 것 같다 😊