[5] SQL로 분석하고 Tableau로 시각화하자 - 윈도우 함수,집합 연산자

윈도우 함수

 

윈도우 함수는 행과 행간의 관계를 정의하며, 순위 및 집계함수(누적) 2가지 종류가 있다.

 

1. 윈도우 순위 함수 : 행과 행간의 순위 반환

2. 윈도우 집계 함수  : 행과 행간의 누적 집계 반환

 

순위함수 : ROW_NUMBER() 동일한 값의 고유한 순위  /  RANK() 동일한 값의 고유한 순위(1위,2위..)  /  DENSE_RANK()  앞의 함수와 흡사하나, 동일한 순위를 하나의 등수로 간주함.

 

집계함수(누적) : COUNT()  /  SUM()  /  AVG()  /  MAX()  /  MIN()  

 

 

윈도우 함수 기본 문법**

 

윈도우 함수 + OVER + (ORDER BY 열 ASC OR DECS) 

 

윈도우 함수 + OVER + (PARTITION BY 열 ASC or DECS)  *옵션으로 partition by 가 추가된 경우

 

1-1. 순위함수 예제

 

SELECT order_date
		,ROW_NUMBER() OVER (ORDER BY order_date ASC) AS ROW_NUMBER
		,RANK()		OVER (ORDER BY order_date ASC) AS RANK
		,DENSE_RANK()	OVER (ORDER BY order_date ASC) AS DENSE_RANK
	FROM [Order]

 

순위 함수 결과 예시

결과를 보면 윈도우 함수 종류에 따라 다르게 순위를 반환해준다. 

1. ROW_NUMBER은 고유한 순위 반환 (1,2,3,4..)
2. RANK는 동일한 순위 반환 (1,1,1,4,4,4,7..)
3. DENSE_RANK는 동일한 순위 반환, 대신 하나의 등수로 간주 (1,1,1,2,2,2..)

 

1-2. ORDER BY + PARTITION BY 순위함수 예제

 

SELECT mem_no
		,order_date
		,ROW_NUMBER() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS ROW_NUMBER
		,RANK() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS RANK
		,DENSE_RANK() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS DENSE_RANK
	FROM [Order]

 

 

1. 위의 예제에서 PARTITION BY만 추가되었다.
2. 위의 예제와 같이 [order_date] 열 기준 오름차순으로 순위가 반환되지만, PARTITION BY로 인해 [mem_no] 열별로 구분되어 순위가 조회된다.

 

2-1. 집계함수(누적) 예제

 

SELECT order_date
		,sales_amt
		,COUNT(sales_amt) OVER (ORDER BY order_date ASC) AS 구매횟수
		,SUM(sales_amt) OVER (ORDER BY order_date ASC) AS 구매금액
		,AVG(sales_amt) OVER (ORDER BY order_date ASC) AS 평균구매금액
		,MAX(sales_amt) OVER (ORDER BY order_date ASC) AS 가장높은구매금액
		,MIN(sales_amt) OVER (ORDER BY order_date ASC) AS 가장낮은구매금액
	FROM [Order]

1. [order_date]열 기준 오름차순으로 누적 집계가 반환된다.
2. 윈도우 함수 종류에 따라 동일 날짜(order_date)에 대한 [sales_amt] 누적 집계 방식이 다르다.

 

2-2. 집계함수(누적) 예제 & PARTITION BY

 

SELECT mem_no
	,sales_amt
	,COUNT(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적_구매횟수
	,SUM(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적_구매금액
	,AVG(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적_평균구매금액
	,MAX(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적_가장높은구매금액
	,MIN(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적_가장낮은구매금액
FROM [Order]

 

집합 연산자 ( UNION, UNION ALL, INTER-SECT, EXCEPT )

집합 연산자는 두개 이상의 SELECT 절의 결과를 하나로 결합한다.

 

UNION 은 (합집합 + 중복된 행을 하나의 행으로 반환) , UNION ALL은 (합집합 + 중복된 행을 그대로 반환),

INTERSET는 교집합, EXCEPT는 차집합, 둘 다 중복된 행은 하나의 행으로 반환.

 

예제 파일

https://github.com/bjpublic/SQL-Tableau  ( ch4 - 1.자주 쓰이는 sql 문법 - 1-5.집합연산자)

파일 가져오는 법 : Object Explorer 에서 DB 오른쪽 클릭 -> Tasks -> Import flat file

 

UNION / UNION ALL 예제

 

SELECT *
	FROM [Member_1]
UNION
SELECT *
	FROM [Member_2]

UNION - 중복된 행은 하나만 반환

SELECT *
	FROM [Member_1]
UNION ALL
SELECT *
	FROM [Member_2]

UNION ALL - 중복이어도 모두 반환

 

INTERSECT 

 

SELECT *
	FROM [Member_1]
	INTERSECT
	SELECT *
	FROM [Member_2]

 

EXCEPT

 

SELECT *
	FROM [Member_1]
EXCEPT
SELECT *
	FROM [Member_2]