[TIL] 49일차 TIL(20230414) - SQL Z차트, 그룹함수(grouping sets, roll up)

STARTERS/TIL

[TIL] 49일차 TIL(20230414) - SQL Z차트, 그룹함수(grouping sets, roll up)

라딘 2023. 4. 14. 15:49

[TIL] 49일차 TIL(20230414) - SQL Z차트, 그룹함수(grouping sets, roll up)

📗 Z차트

✅ Z차트 구성요소

- 월별매출, 매출누계, 이동년계를 이용해 Z형태의 차트를 그린다.

- 매출누계: 해당 월 매출에 이전 월까지 매출 누계

- 이동년계: 해당 월의 매출에 과거 11개월의 매출을 합한 값(최근 1년차 누적 합계)

- 월별매출과 매출누계를 통해 단기적 추이를 확인하고, 이동년계를 이용해 장기적 추이를 확인할 수 있다.

-- 월별매출, 매출누계, 이동연계
WITH ym_sales AS (
SELECT to_char(o.order_date, 'yyyy-mm') order_ym, 
	sum(od.quantity * od.unit_price * (1-od.discount)) sales
FROM orders o, order_details od
WHERE o.order_id = od.order_id
GROUP BY order_ym
ORDER BY 1
), ym_sales_moving_sum AS (
SELECT *,
	round(sum(sales) over(ORDER BY order_ym ROWS BETWEEN 10 PRECEDING AND CURRENT row)::NUMERIC, 2) 이동연계
FROM ym_sales
)
SELECT order_ym,
	sales 월별매출, sum(sales) over(ORDER BY order_ym) 매출누계, 이동연계
FROM ym_sales_moving_sum
WHERE order_ym >= '1997-06'

📗 그룹 함수

✅ grouping sets

- group by절에 grouping sets를 지정하여 여러 컬럼을 기준으로 그룹화한 집계값을 확인할 수 있다.

- 해당 컬럼을 제외한 나머지 컬럼은 결측치로 표시된다. 따라서 큰 테이블을 만들어두고, is not null구문을 사용하여 목적에 따라 필요로 하는 집계값을 찾아서 볼 수 있다.

- gruping sets 내부에 (col1, col2)를 사용하면, 두 컬럼을 동시에 기준으로 하여 집계하고, ()를 넣으면 전체를 기준으로 집계한다.

-- 연도, 분기, 월, 일별 매출액, 주문건수 집계
WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') || '년' order_year,
	to_char(order_date, 'YYYY-MM') || '월' order_MONTH,
	TO_CHAR(ORDER_DATE, 'YYYY-q') || '분기' order_QUARTER,
	od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	p.discontinued, s.supplier_id, s.company_name, s.country, s.city
FROM orders o, order_details od, categories c, products p, suppliers s
WHERE o.order_id = od.order_id AND c.category_id = p.category_id AND
	od.product_id = p.product_id AND p.supplier_id = s.supplier_id
),
-- grouping set 구문
cte_grouping_sets AS (
SELECT order_year, order_quarter, order_month, order_date, sum(sales) 매출액, count(DISTINCT order_id) 주문건수
FROM cte_product_sale
GROUP BY GROUPING SETS (order_year, order_quarter, order_month, order_date, ())
),
-- year별 매출액, 주문건수
cte_year_amount AS (
SELECT order_year, 매출액, 주문건수
FROM cte_grouping_sets
WHERE order_year IS NOT NULL
),
-- month별 매출액, 주문건수
cte_month_amount AS (
SELECT order_month, 매출액, 주문건수
FROM cte_grouping_sets
WHERE order_month IS NOT NULL
),
-- quarter별 매출액, 주문건수
cte_quarter_amount AS (
SELECT order_quarter, 매출액, 주문건수
FROM cte_grouping_sets
WHERE order_quarter IS NOT NULL
),
-- day별 매출액, 주문건수
cte_day_amount AS (
SELECT order_date, 매출액, 주문건수
FROM cte_grouping_sets
WHERE order_date IS NOT NULL
),
-- 전체 매출액, 주문건수
cte_all_amount AS (
SELECT 'ALL' 전체, max(매출액), max(주문건수)
FROM cte_grouping_sets
)
-- year별 매출액, 주문건수: cte_year_amount
-- month별 매출액, 주문건수: cte_month_amount
-- quarter별 매출액, 주문건수: cte_quarter_amount
-- day별 매출액, 주문건수: cte_day_amount
-- 전체 매출액, 주문건수: cte_all_amount
SELECT * FROM cte_all_amount;

마지막 select 구문에 목적에 부합하는 테이블을 입력하면 해당 조건에 따른 집계 결과 테이블만 확인할 수 있다.

✅ ROLL UP

- 카테고리별 단계가 있는 경우에 중간에 소계를 두어 각 단계별 합계를 중간에 확인할 수 있도록 하는 함수

SELECT category_name, product_name, sum(sales)
FROM cte_product_sale
GROUP BY rollup(category_name, product_name)
ORDER BY 1,2

category_name을 기준으로 카테고리에 해당하는 상품들의 매출액을 모두 합한 소계를 보여주고, 마지막 행에서는 category_name의 모든 매출액을 합한, 결국 전체 매출액을 보여주게 된다.

✅ CUBE

- 각 컬럼들에 대해 가능한 모든 조합으로 grouping을 실시하여 집계

SELECT order_year, order_quarter, order_month, sum(sales)
FROM cte_product_sale
GROUP BY cube(order_year, order_quarter, order_month)

cube(order_year, order_quarter, order_month)를 사용하여 다양한 조합에 대한 소계를 반환하였다. 52~56행은 년월별 매출의 합계를 반환하고, 57행은 1998년의 전체 매출을 반환한다. 또 58행은 년도에 관계없이 모든 1분기 1월의 값을 합하여 보여준다.

📗 그룹 함수를 활용한 간단한 고객 분석 쿼리

그룹함수와 집계함수를 사용해 고객의 속성이나 구매 이력을 확인할 수 있는 쿼리를 작성했다.

크게 카테고리별, 국가/도시별, 주문일자별로 고객의 구매 정보를 확인했다.

WITH CUSTOMERS_ORDERS_INFO AS
(SELECT o.order_id, c.category_name, p.product_name, cu.customer_id, cu.company_name, 
	o.order_date, o.ship_via, cu.city, cu.country,
	od.unit_price, od.quantity, od.discount, round((od.unit_price * od.quantity * (1-od.discount))::NUMERIC, 2) sales
FROM categories c, products p, order_details od, orders o, customers cu
WHERE c.category_id = p.category_id AND p.product_id = od.product_id AND
	o.order_id = od.order_id AND cu.customer_id = o.customer_id
),
-- 1. 고객별 총 주문액, 주문량, 주문횟수
customer_sales AS (
SELECT company_name, sum(sales), sum(quantity), count(DISTINCT order_id)
FROM CUSTOMERS_ORDERS_INFO
GROUP BY company_name
ORDER BY 2 DESC, 3 desc
),
-- 2. 고객의 카테고리별 총 주문액, 주문량, 주문횟수(ROLL UP)
customer_category_sales AS (
SELECT company_name, category_name, sum(sales), sum(quantity), count(DISTINCT order_id)
FROM CUSTOMERS_ORDERS_INFO
GROUP BY ROLLUP(company_name, category_name)
ORDER BY 1,2
),
-- 3. 고객별 최다 주문액 카테고리
customer_category_rnk AS (
SELECT company_name, category_name, sum(sales) 주문액, rank() over(PARTITION BY company_name ORDER BY sum(sales) desc) rnk
FROM CUSTOMERS_ORDERS_INFO
GROUP BY 1, 2
),
customer_top_category AS (
SELECT company_name, category_name, 주문액
FROM customer_category_rnk
WHERE rnk = 1
),
--4. 고객 국가/도시별 총 주문액, 주문량, 주문횟수(ROLL UP)
customer_country_sales AS (
SELECT country, city, company_name,
	sum(sales) sales, sum(quantity) quantity, count(DISTINCT order_id) order_cnt
FROM CUSTOMERS_ORDERS_INFO
GROUP BY ROLLUP(country, city, company_name)
ORDER BY 1, 2, 3
),
-- 5. 고객의 년도/분기/월별 주문액, 주문량, 주문 횟수 기본 테이블
customer_date_sales AS (
SELECT company_name, to_char(order_date, 'yyyy') || '년' order_y, 
	to_char(order_date, 'yyyy-mm') || '월' order_ym,
	to_char(order_date, 'yyyy-q') || '분기' order_yq, 
	sum(sales) sales, sum(quantity) quantity, count(DISTINCT order_id) order_cnt
FROM CUSTOMERS_ORDERS_INFO
GROUP BY GROUPING SETS ((1, 2), (1,3), (1,4))
),
-- 5-1. 고객의 년도별 주문액, 주문량, 주문 횟수
customer_year_sales AS (
SELECT company_name, order_y, sales, quantity, order_cnt
FROM customer_date_sales
WHERE order_y IS NOT null
),
-- 5-2. 고객의 분기별 주문액, 주문량, 주문 횟수
customer_quarter_sales AS (
SELECT company_name, order_yq, sales, quantity, order_cnt
FROM customer_date_sales
WHERE order_yq IS NOT null
),
-- 5-3. 고객의 월별 주문액, 주문량, 주문 횟수
customer_month_sales AS (
SELECT company_name, order_ym, sales, quantity, order_cnt
FROM customer_date_sales
WHERE order_ym IS NOT null
),
-- 6. 고객별 운송사 이용 횟수
customer_ship_info AS (
SELECT cu.company_name, o.ship_via, count(o.order_id) ship_cnt 
FROM orders o, customers cu
WHERE cu.customer_id = o.customer_id
GROUP BY company_name, ship_via
ORDER BY 1
)
-- 1. 고객별 총 주문액, 주문량, 주문횟수: customer_sales
-- 2. 고객의 카테고리별 총 주문액, 주문량, 주문횟수 (ROLL UP): customer_category_sales
-- 3. 고객별 최다 주문액 카테고리: customer_category_rnk
-- 4. 고객 국가/도시별 총 주문액, 주문량, 주문횟수 (ROLL UP): customer_country_sales
-- 5-1. 고객의 년도별 주문액, 주문량, 주문 횟수 (grouping sets): customer_year_sales
-- 5-2. 고객의 분기별 주문액, 주문량, 주문 횟수 (grouping sets): customer_quarter_sales
-- 5-3. 고객의 월별 주문액, 주문량, 주문 횟수 (grouping sets): customer_month_sales
-- 6. 고객별 운송사 이용 횟수: customer_ship_info
SELECT * FROM customer_sales

소감 및 정리

grouping sets를 이용해 여러 조건에 대한 집계를 한번에 수행하고, 원하는 테이블을 그때그때 꺼내볼 수 있도록 쿼리를 작성하는 방식이 흥미로웠다. 다른 언어들에서 함수를 사용하는 것처럼 활용할 수 있을 것 같아 꽤 유용했다! 또 z차트의 쿼리를 작성하고 직접 시각화를 해보면서 상품의 판매 추이를 다양한 관점에서 한번에 살펴볼 수 있는 점이 흥미로웠고, sql쿼리 작성 시간이 점점 줄어드는 것 같아 뿌듯했다. 고객 분석 쿼리를 작성하는 개인과제를 통해 헷갈렸던 누계함수에서의 partition by와 order by를 활용해보며 다시 개념을 잡을 수 있었다. 한 주가 굉장히 빠르게 흘러갔지만 배운 내용은 엄청 많았던 것 같다. 데이터 분석가에게 데이터 추출 능력은 매우매우 중요한 만큼 앞으로도 복습을 꾸준히 해야겠다😃