[4] SQL로 분석하고 Tableau로 시각화하자 - 그룹함수

 그룹 함수 (WITH ROLLUP, WITH CUBE, GROUPING SETS, GROUPING)

 

WITH ROLLUP : GROUP BY 항목들을 오른쪽에서 왼쪽 순서대로 그룹을 묶는 역할

 

WITH ROLLUP 사용 안할경우 

SELECT YEAR(order_date) AS 연도
		,channel_code AS 채널코드
		,SUM(sales_amt) AS 주문금액
	FROM [Order]
	GROUP
	BY	YEAR(order_date)
	,channel_code

ROLL_UP 사용하지 않은 경우

마지막 ORDER BY 직전에 WITH ROLL UP추가

SELECT YEAR(order_date) AS 연도
		,channel_code AS 채널코드
		,SUM(sales_amt) AS 주문금액
	FROM [Order]
	GROUP
	BY YEAR(order_date)
		,channel_code
WITH ROLLUP
	ORDER
		BY 1 DESC, 2 ASC

ROLL_UP 사용한 경우

1. 결과를 비교해보면 ROLL_UP을 사용하지 않았을 경우, YEAR 순서대로, 그다음  channel_code 열별로 Sales_amt 합계가 집계되는 반면에, 사용할시 group by 항목들의 오른쪽에서 왼쪽 순으로 그룹으로 묶이기 때문에 (즉, channel code 순서대로, 그 다음 YEAR열별로 )  결과가 다르다.
2. WITH ROLLUP 그룹함수는 GROUP BY 다음에 작성하여야한다.
3. ORDER BY는 조회한 열의 순서를 통해, 행이 정렬된다. 따라서 ORDER BY 1 DESC, 2 ASC라고 입력했기 때문에, 열1인 연도는 내림차순, 열2인 채널코드는 오름차순으로 정렬되었다.
4. WITH ROLLUP은 총계 및 소계를 구할 때 사용된다.

 

WITH CUBE : GROUP BY 항목들의 모든 경우의 수를 그룹으로 묶는 역할 ( 더 상세한 결과 출력 )

 

SELECT YEAR(order_date) AS 연도
		,channel_code AS 채널코드
		,SUM(sales_amt) AS 주문금액
	FROM [Order]
	GROUP
	BY	YEAR(order_date)
		,channel_code
WITH CUBE
	ORDER
		BY 1 DESC, 2 ASC

 

GROUPING SETS : GROUP BY 항목들을 개별 그룹으로 묶는 역할

 

SELECT YEAR(order_date) AS 연도
		,channel_code AS 채널코드
		,SUM(sales_amt) AS 주문금액
	FROM [Order]
	GROUP
	BY GROUPING SETS( YEAR(order_date),channel_code)

 

GROUPING SETS - 연도와 채널코드를 개별 그룹으로 나누어 결과가 출력됨

 

GROUPING : WITH ROLLUP 및 CUBE에 의해 그룹화되었다면 0, 그렇지 않으면 1을 반환하는 함수

 

SELECT YEAR(order_date) AS 연도
		,GROUPING(YEAR(order_date)) AS 연도_GROUPING
		,channel_code AS 채널코드
		,GROUPING(channel_code) AS 채널코드_GROUPING
		
		,SUM(sales_amt) AS 주문금액
	FROM [Order]
	GROUP 
	BY YEAR(order_date)
	,channel_code
WITH ROLLUP
	ORDER 
	BY 1 DESC, 2 ASC

GROUPING

1. GROUPING은 WITH ROLLUP과 주로 함께 쓰인다.
2. WITH ROLLUP에 의해 그룹화된 값(NULL)이 GROUPING을 사용하면 1로 반환되어 조회된다.
3. GROUPING할 때 GROUP BY 항목들을 소괄호로 묶어야함

 

1. GROUPING 및 CASE WHEN을 활용하면, NULL이 아닌 '총계 및 소계' 로 표기할 수 있다. 다만, 여기서 GROUP BY 항목인 연도 및 채널코드 열의 형식이 숫자이므로 '총계 및 소계'와 같은 문자형으로 표기할 수 없음
2. YEAR(order_date) 및 [channel_code]를 숫자->문자로 형 변환을 해야한다.

 

GROUPING & CASE WHEN 예제**

 

SELECT CASE WHEN GROUPING(연도)=1 THEN '총계'
				ELSE 연도 END AS 연도_총계

				,CASE WHEN GROUPING(연도)=1 THEN '총계'
						WHEN GROUPING(채널코드)=1 THEN '소계'
						ELSE 채널코드 END AS 채널코드_총소계
						,SUM(sales_amt) AS 주문금액
					FROM (
						SELECT CAST(YEAR(order_date) AS VARCHAR) AS 연도
						,CAST(channel_code AS VARCHAR) AS 채널코드
						,sales_amt
						FROM [Order]
						)A
					GROUP
						BY 연도, 채널코드
					WITH ROLLUP
					ORDER
						BY 1 DESC, 2 ASC

GROUPING & CASE WHEN

 

1. CAST는 데이터 형식을 바꾸는 역할을 한다. CAST('[변환하고자 하는 데이터]' AS [데이터형식])
2. WITH ROLLUP 에 의해 그룹화된 값을 GROUPING 및 CASE WHEN을 활용하여 '총계 및 소계'가 표기됨