[스타터스 TIL] 49일차.SQL 실전 트레이닝 (5) - Z차트, 그룹함수 (GROUPING SETS, ROLLUP, CUBE)

---

1. Z차트 

- 시간의 흐름에 따라 매출이 어떻게 변하는지 확인하기 위해 사용

 

 

 

 

 

 

 

- 월별 매출

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

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

   → 그래프에 표시되지 않은 과거 1년동안의 매출 추이도 읽을 수 있음

 

 

1.1 [전체 매출] 11개월치 이동연계 구하기

- 기간 : 1997-06 ~ 1998-04

- year, month, 월별매출, 매출누계, 이동연계(10개월전 ~ 현재월 매출 : 11개월치)

 

1) 쿼리 작성

✅ 내가 작성한 쿼리

① 총 매출액 구하기

     → 이동연계를 구하기 위해 전체 기간의 총 매출액을 구해야 함

② 기간 설정한 총 매출액 구하기

     → CASE WHEN을 활용하여 설정된 기간에 맞는 총 매출액 구하기

③ 매출누계, 이동연계 구하기

     → 기간 설정한 총 매출액으로 매출누계 구하기

     → 전체 기간의 총 매출액으로 이동연계 구하기

④ 기간에 맞춰 데이터 출력하기

WITH cte_products_sales AS (
	SELECT to_char(o.order_date, 'YYYY') AS YEAR
		 , to_char(o.order_date, 'mm') AS MONTH
		 , to_char(o.order_date, 'YYYY') || '-' || to_char(o.order_date, 'mm') AS year_month
		 , (od.unit_price * od.quantity * (1-od.discount)) AS sales
	FROM orders o, order_details od
	WHERE o.order_id = od.order_id)
-- 1) 총매출액 구하기 (→ 이동연계를 구하기 위함)
, cte_sales AS (
	SELECT YEAR
		 , MONTH
		 , year_month
		 , sum(sales) AS 월별매출_pre
	FROM cte_products_sales
	GROUP BY 1, 2, 3)
-- 2) 기간 설정한 총매출액 구하기
, cte_sales2 AS (
	SELECT YEAR 
		 , MONTH
		 , year_month
		 , 월별매출_pre
		 , CASE WHEN year_month BETWEEN '1997-06' AND '1998-04' THEN 월별매출_pre ELSE 0 END AS 월별매출
	FROM cte_sales
	ORDER BY 1, 2)
-- 3) 매출누계, 이동연계 구하기
, cte_sales3 AS (
	SELECT *
 		 , CASE WHEN year_month BETWEEN '1997-06' AND '1998-04' 
	 			THEN sum(월별매출) over(ORDER BY YEAR, MONTH)
	 		ELSE 0
	 		END AS 매출누계
	 	 , sum(월별매출_pre) over(ORDER BY YEAR, MONTH ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS 이동연계
	 FROM cte_sales2)
-- 4) 기간 설정하여 최종 데이터 출력
SELECT YEAR
	 , MONTH
	 , 월별매출
	 , 매출누계
	 , 이동연계
FROM cte_sales3
WHERE year_month BETWEEN '1997-06' AND '1998-04';

 

✅ 팀원이 작성한 쿼리

① 필요한 컬럼 + 총매출액 계산

② 이동연계 계산

     → 총매출액에서 바로 이동연계 계산함

     → 💡 이동연계를 따로 테이블을 만들어 계산한 이유

                이동연계의 출력 데이터가 최종 데이터에서 적용할 기간에 영향을 받지 않도록 하기 위함

③ 매출누계 게산 후에 데이터 출력

-- 1) 필요한 컬럼 + 총매출액 계산
WITH cte_products_sales AS (
	SELECT to_char(o.order_date, 'YYYY') AS YEAR
		 , to_char(o.order_date, 'mm') AS MONTH
		 , to_char(o.order_date, 'YYYY') || '-' || to_char(o.order_date, 'mm') AS year_month
		 , sum(od.unit_price * od.quantity * (1-od.discount)) AS sales
	FROM orders o, order_details od
	WHERE o.order_id = od.order_id
	GROUP BY 1, 2, 3)
-- 2) 이동연계 계산
, cte_moving_sum AS (
	SELECT *
		 , sum(sales) over(ORDER BY YEAR, MONTH ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS 이동연계
	FROM cte_products_sales)
-- 3) 매출누계 계산 후 데이터 출력
SELECT YEAR
	 , MONTH
	 , sales AS 월별매출
	 , sum(sales) over(ORDER BY YEAR, MONTH) AS 매출누계
	 , 이동연계
FROM cte_moving_sum
WHERE year_month BETWEEN '1997-06' AND '1998-04';

 

2) 시각화

 

1.2 [카테고리 별 전체 매출] 11개월치 이동연계 구하기

1) 쿼리 작성

-- 1) 총 매출액 구하기
WITH cte_products_sales AS (
	SELECT c.category_id 
		 , c.category_name
		 , to_char(o.order_date, 'YYYY') || '-' || to_char(o.order_date, 'mm') AS year_month
		 , sum(od.unit_price * od.quantity * (1-od.discount)) AS sales
	FROM orders o, order_details od, products p, categories c
	WHERE o.order_id = od.order_id
	  AND od.product_id = p.product_id
	  AND p.category_id = c.category_id
	GROUP BY 1, 3
	ORDER BY 1, 3)
-- 2) 이동연계 구하기
, cte_moving AS (
	SELECT *
		 , sum(sales) over(PARTITION BY category_id ORDER BY year_month ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS 이동연계
	FROM cte_products_sales)
-- 3) 매출누계 계산 후 데이터 출력
SELECT category_id
	 , category_name
	 , year_month
	 , sales AS 월별매출
	 , sum(sales) over(partition BY category_id ORDER BY year_month) AS 매출누계
	 , 이동연계
FROM cte_moving
WHERE year_month BETWEEN '1997-06' AND '1998-04';

 

2) 시각화

아래의 그래프는 카테고리 중 'Beverages'로만 시각화를 나타낸 것이다.

---

2. 그룹함수 - GROUPING SETS

📌 그룹함수의 종류

- GROUPING SETS

- CUBE

- ROLLUP

 

2.1 카테고리, 제품 등 여러 지표를 한 번에 추출하기

👇 아래 지표들을 한 번에 추출하기

- 카테고리별 매출액

- 제품 별 매출액

- 공급업체 별 매출액

- 국가 별 매출액

- (국가, 도시) 별 매출액

- 전체 매출액

 

1) 기본 WITH절

WITH cte_products_sales AS (
	SELECT o.order_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.unit_price AS 판매단가
		 , od.quantity 
		 , od.discount 
		 , (od.unit_price * od.quantity * (1-od.discount)) AS sales
		 , c.category_id
		 , c.category_name
		 , p.product_id 
		 , p.product_name 
		 , p.unit_price AS 마스터단가
		 , p.discontinued
		 , s.supplier_id 
		 , s.company_name
		 , s.country 
		 , s.city 
	FROM orders o, order_details od, products p, categories c, suppliers s
	WHERE o.order_id = od.order_id
	  AND od.product_id = p.product_id
	  AND p.category_id = c.category_id
	  AND p.supplier_id = s.supplier_id)

 

2) 제품 속성 별로 매출액 grouping sets

→ group by 시 GROUPING SETS를 추가하여 각 그룹 별로 집계를 한 값들이 나온다.

→ 각 컬럼 별로 해당되지 않은 데이터에는 NULL값으로 출력이 된다.

-- 1) 제품 속성 별 매출액 grouping sets
, cte_grouping_sets AS (
	SELECT category_name
		 , product_name
		 , company_name
		 , country
		 , city
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	--GROUP BY GROUPING SETS (category_name, product_name, company_name, country, city);
	--GROUP BY GROUPING SETS (category_name, product_name, company_name, country, (country, city)); -- 2개의 컬럼을 한번에 그룹핑한 값
	GROUP BY GROUPING SETS (category_name, product_name, company_name, country, (country, city), ())) -- 전체를 합한 매출액이 출력됨

 

3) 각 제품 속성 별 CTE 테이블

다음과 같이 각 CTE 테이블을 만들고 원하는 데이터를 출력할 때, 각 데이터를 select하면 된다.

 

- 카테고리 별 매출액 : cte_category_sales
- 제품별 매출액 : cte_product_sales
- 공급사 별 매출액 : cte_supplies_sales
- 공급 국가 별 매출액 : cte_country_sales
- 공급 국가, 도시 별 매출액 : cte_country_city_sales
- 전체 매출액 : cte_total_sales

-- 2) 카테고리 별 매출액
, cte_category_sales AS (
	SELECT category_name
		 , 매출액
	FROM cte_grouping_sets
	WHERE category_name IS NOT NULL)
SELECT *
FROM cte_category_sales;

-- 3) 제품별 매출액
, cte_product_sales AS (
	SELECT product_name
		 , 매출액
	FROM cte_grouping_sets
	WHERE product_name IS NOT NULL)
SELECT *
FROM cte_product_sales;

-- 4) 공급사 별 매출액
, cte_supplies_sales AS (
	SELECT company_name
		 , 매출액
	FROM cte_grouping_sets
	WHERE company_name IS NOT NULL)
SELECT *
FROM cte_supplies_sales;

-- 5) 공급 국가 별 매출액
, cte_country_sales AS (
	SELECT country
		 , 매출액
	FROM cte_grouping_sets
	WHERE country IS NOT NULL AND city IS null) -- country와 city가 묶여있기 때문에 city가 null인 값을 가지고 와야 함
SELECT *
FROM cte_country_sales;

-- 6) 공급 국가, 도시 별 매출액
, cte_country_city_sales AS (
	SELECT country
		 , city
		 , 매출액
	FROM cte_grouping_sets
	WHERE country IS NOT NULL and city IS NOT null)
SELECT *
FROM cte_country_city_sales;

-- 7) 전체 매출액
, cte_total_sales AS (
	SELECT 'all' AS 전체매출액
		 , 매출액
	FROM cte_grouping_sets
	ORDER BY 매출액 DESC
	LIMIT 1)
SELECT *
FROM cte_total_sales;

 

2.2 연, 분기, 월, 일 별 매출액, 주문수량 한 번에 추출하기

1) 기본 CTE 테이블

기본 테이블에서 연, 분기, 월, 일에 대한 컬럼을 미리 설정해야 함

WITH cte_sales_cnt AS (
	SELECT to_char(o.order_date, 'YYYY')||'년' AS YEAR
		 , to_char(o.order_date, 'YYYY')||'-'||to_char(o.order_date, 'q')||'분기' AS quarter
		 , to_char(o.order_date, 'YYYY')||'-'||to_char(o.order_date, 'mm')||'월' AS MONTH
		 , to_char(o.order_date, 'YYYY')||'-'||to_char(o.order_date, 'mm')||'-'||to_char(o.order_date, 'dd') AS day
		 , sum(od.unit_price * od.quantity * (1-od.discount)) AS 매출액
		 , count(DISTINCT o.order_id) AS 주문건수
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
	GROUP BY GROUPING SETS (YEAR, quarter, MONTH, DAY, ()))

 

2) 각 기간 별 CTE 테이블

- 연도 : cte_year
- 분기 : cte_quarter
- 월 : cte_month
- 일 : cte_day
- 전체 : cte_all

-- 1) 연도 별 매출액, 주문건수
, cte_year AS (
	SELECT YEAR
		 , 매출액
		 , 주문건수
	FROM cte_sales_cnt
	WHERE YEAR IS NOT null)
-- 2) 분기 별 매출액, 주문건수
, cte_quarter AS (
	SELECT quarter 
		 , 매출액
		 , 주문건수
	FROM cte_sales_cnt
	WHERE quarter IS NOT null)
-- 3) 월별
, cte_month AS (
	SELECT month 
		 , 매출액
		 , 주문건수
	FROM cte_sales_cnt
	WHERE month IS NOT null)
-- 4) 일별
, cte_day AS (
	SELECT day 
		 , 매출액
		 , 주문건수
	FROM cte_sales_cnt
	WHERE day IS NOT null)
-- 5) 전체
, cte_all AS (
	SELECT 'all' AS 전체
		 , 매출액
		 , 주문건수
	FROM cte_sales_cnt
	ORDER BY 매출액 DESC, 주문건수 DESC 
	LIMIT 1)
-- 연도 : cte_year
-- 분기 : cte_quarter
-- 월 : cte_month
-- 일 : cte_day
-- 전체 : cte_all
SELECT *
FROM cte_all;

---

3. 그룹함수 - ROLLUP

1) 카테고리, 제품 별 소계

-- 1) 카테고리, 제품 별 소계
, cte_category_product_rollup AS (
	SELECT category_name
		 , product_name
		 , sum(sales)
	FROM cte_products_sales
	GROUP BY ROLLUP (category_name, product_name)
	ORDER BY 1, 2)
SELECT *
FROM cte_category_product_rollup;

→ 노란색 박스 : 카테고리 별 소계

→ 빨간색 박스 : 전체 카테고리(제품) 총계

 

2) 연도, 분기, 월별 소계

-- 2) 연도, 분기, 월별 소계
, cte_year_quarter_month_rollup AS (
	SELECT YEAR
		 , quarter
		 , MONTH
		 , sum(sales)
	FROM cte_products_sales
	GROUP BY ROLLUP (YEAR, quarter, MONTH)
	ORDER BY 1, 2, 3)
SELECT *
FROM cte_year_quarter_month_rollup;

→ 노란색 박스 : 분기 별 소계

→ 연두색 박스 : 연도별 소계

→ 빨간색 박스 : 전체 시기 총계

---

4. 그룹함수 - CUBE

CUBE 함수는 컬럼 조합의 모든 경우의 수에 해당하는 소계를 출력한다.

 

1) 카테고리, 제품 별 소계

-- 1) 카테고리, 제품 별 소계
, cte_category_product_cube AS (
	SELECT category_name
		 , product_name
		 , sum(sales)
	FROM cte_products_sales
	GROUP BY CUBE (category_name, product_name))
SELECT *
FROM cte_category_product_cube;

→ 빨간색 박스 : 총계

→ 노란색 박스 : 제품명 별 소계

→ 연두색 박스 : 카테고리 별 소계

→ 하늘색 박스 : 제품명 별 소계

 

2) 연도, 분기, 월별 소계

 

- 2) 연도, 분기, 월별 소계
, cte_year_quarter_month_cube AS (
	SELECT YEAR
		 , quarter
		 , MONTH
		 , sum(sales)
	FROM cte_products_sales
	GROUP BY CUBE (YEAR, quarter, MONTH))
SELECT *
FROM cte_year_quarter_month_cube;

→ 빨간색 박스 : 총계

→ 그 외 다양한 조합 별 소계 출력

---

5. 과제

📢 고객 속성, 구매 이력에 대한 집계 (그룹함수 이용)

-  customers, orders, order_details 테이블 사용

- 고객 회사 별, 고객 국가 별, 고객 도시 별, 고객 회사 직원의 직급 별 총 매출액, 주문건수

5.1 GROUPING SETS

1) 쿼리 작성

-- 1) grouping sets
WITH cte_customer_sales_cnt AS (
	SELECT c.company_name
		 , c.contact_title
		 , c.country
		 , c.city
		 , sum(od.unit_price * od.quantity * (1 - od.discount)) AS sales
		 , count(DISTINCT o.order_id) AS order_cnt
	FROM orders o, order_details od, customers c	
	WHERE o.order_id = od.order_id
	  AND o.customer_id = c.customer_id
	GROUP BY GROUPING SETS (company_name, contact_title, country, (country, city), ())
-- 2) 고객 회사 별 총매출액 & 주문건수
, cte_company_sales_cnt AS (
	SELECT company_name
		 , sales
		 , order_cnt
	FROM cte_customer_sales_cnt
	WHERE company_name IS NOT NULL
	ORDER BY sales DESC)
-- 3) 고객 회사의 직원 직급 별 총매출액 & 주문건수
, cte_title_sales_cnt AS (
	SELECT contact_title
		 , sales
		 , order_cnt
	FROM cte_customer_sales_cnt
	WHERE contact_title IS NOT NULL
	ORDER BY sales DESC)
-- 4) 고객 국가 별 총매출액 & 주문건수
, cte_country_sales_cnt AS (
	SELECT country
		 , sales
		 , order_cnt
	FROM cte_customer_sales_cnt
	WHERE country IS NOT NULL
	ORDER BY sales DESC)
-- 5) 고객 도시 별 총매출액 & 주문건수
, cte_city_sales_cnt AS (
	SELECT country 
		 , city
		 , sales
		 , order_cnt
	FROM cte_customer_sales_cnt
	WHERE city IS NOT NULL
	ORDER BY sales DESC)
-- 6) 전체 총매출액 & 주문건수
, cte_all_sales_cnt AS (
	SELECT 'all' AS ALL
		 , sales
		 , order_cnt
	FROM cte_customer_sales_cnt
	ORDER BY sales DESC
	LIMIT 1)
-- 고객 회사 별 : cte_company_sales_cnt
-- 고객 국가 별 : cte_country_sales_cnt
-- 고객 도시 별 : cte_city_sales_cnt
-- 고객 회사의 직원 직급 별 : cte_title_sales_cnt
SELECT *
FROM cte_all_sales_cnt;

 

2) 각 CTE 별 테이블 출력

 

5.2 ROLLUP

1) 쿼리 작성

WITH cte_customer_sales_cnt AS (
	SELECT 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
		 , c.company_name
		 , c.contact_title
		 , c.country
		 , c.city
		 , od.unit_price * od.quantity * (1 - od.discount) AS sales
		 , o.order_id
	FROM orders o, order_details od, customers c	
	WHERE o.order_id = od.order_id
	  AND o.customer_id = c.customer_id)
-- 2) 고객 회사 별 총매출액 & 주문건수
, cte_customers_sales_cnt_rollup AS (
	SELECT company_name
		 , contact_title
		 , country
		 , city
		 , sum(sales) AS total_sales
		 , count(DISTINCT order_id) AS order_cnt
	FROM cte_customer_sales_cnt
	GROUP BY ROLLUP (company_name, contact_title, country, city)
	ORDER BY 1, 2, 3, 4)
-- 3) 시기 별 총매출액 & 주문건수
, cte_days_sales_cnt_rollup AS (
	SELECT YEAR
		 , quarter
		 , MONTH
		 , DAY
		 , sum(sales) AS total_sale
		 , count(DISTINCT order_id) AS order_cnt
	FROM cte_customer_sales_cnt
	GROUP BY ROLLUP (YEAR, quarter, MONTH, DAY)
	ORDER BY 1, 2, 3, 4)
-- 고객 회사 별 : cte_customers_sales_cnt_rollup
-- 시기 별 : cte_days_sales_cnt_rollup
SELECT *
FROM cte_customers_sales_cnt_rollup;

 

2) 각 CTE 별 테이블 출력

 

5.3 CUBE

1) 쿼리 작성

WITH cte_customer_sales_cnt AS (
	SELECT 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
		 , c.company_name
		 , c.contact_title
		 , c.country
		 , c.city
		 , od.unit_price * od.quantity * (1 - od.discount) AS sales
		 , o.order_id
	FROM orders o, order_details od, customers c	
	WHERE o.order_id = od.order_id
	  AND o.customer_id = c.customer_id)
-- 2) 고객 회사 별 총매출액 & 주문건수
, cte_customers_sales_cnt_cube AS (
	SELECT company_name
		 , contact_title
		 , country
		 , sum(sales) AS total_sales
		 , count(DISTINCT order_id) AS order_cnt
	FROM cte_customer_sales_cnt
	GROUP BY CUBE (company_name, contact_title, country))
-- 3) 시기 별 총매출액 & 주문건수
, cte_days_sales_cnt_cube AS (
	SELECT YEAR
		 , quarter
		 , MONTH
		 , sum(sales) AS total_sale
		 , count(DISTINCT order_id) AS order_cnt
	FROM cte_customer_sales_cnt
	GROUP BY CUBE (YEAR, quarter, MONTH))
-- 고객 회사 별 : cte_customers_sales_cnt_cube
-- 시기 별 : cte_days_sales_cnt_cube
SELECT *
FROM cte_days_sales_cnt_cube;

 

2) 각 CTE 별 테이블 출력

---

💡 회고

벌써 SQL 오프라인 강의 5일차가 지났다.

오늘은 Z함수와 그룹함수에 대해 배웠다. 처음 배우는 내용들이 대부분이라 수업을 따라가기 조금은 어렵긴 했지만, 강사님께서 시간을 넉넉하게 주셔서 다행히 수업 시간에 잘 따라갈 수 있었다.

특히 GROUPING SETS의 경우, 처음에는 잘 쓰이지 않지 않을까라는 생각을 했는데 이를 원하는 컬럼으로만 CTE를 만들어서 데이터를 추출하는 것에 있어서 생각보다 유용할 수 있겠다는 생각이 들었다.

 

오늘 배운 것들을 잘 활용한다면 데이터를 추출하는 데에 큰 도움이 될 듯하다.데이터를 보고 추출할 때, 이런 함수들이 잘 떠오를 수 있도록 반복적으로 이것들을 쓰려고 해봐야겠다 :)