[SQL Query] Window Function (원도우 함수)

■ 핵심 정리
 - 윈도우 함수의 특징을 한마디로 정리하면, "집약 기능이 없는 GROUP BY 구" 입니다.
 - 원도우 함수는 주변을 둘러싼 로우를 인지하므로, 전통적인 집계함수와 문자 수준의 그루핑보다는
   쉽게 이동 집계 연산을 할 수 있다.
 - 커서사용으로 인한 리소스 낭비의 대안이 될 수 있다.
 - 윈도우 함수는  SUM(), COUNT(), AVG() 같은 기존 집계 함수와 함께 사용할 수 있으며, OVER절과도 함께 사용한다.
 - PARTITION BY 조건은 집계 표현식을 적용해야 하는 그룹을 명시하는 데 사용한다.
 - ORDER BY 조건은 뒤에 이러서 나오는 로우들의 집계 표현식 계산을 수행하는 방법에 영향을 주므로 중요하다.


■ 예제 쿼리

쿼리 조건
  - addres(주서) 별 합계를 구하시오


윈도우 함수를 이용한 쿼리

SELECT address, 
	COUNT(*) OVER(PARTITION BY address)
FROM Address

윈도우 함수의 기본적인 구문은
집약 함수 뒤에 OVER 구를 작성하고,
내부에 자를 키를 지정하는 PARTITION BY 또는 ORDER BY 를 입력하는 것입니다.
작성하는 장소는 SELECT 구라고만 생각해도 문제없습니다.

. 결과 값

adress	count
속초시	1
인천시	2
인천시	2
서울시	3
서울시	3
서울시	3
부산시	2
부산시	2
서귀포시	1

GROUP BY 함수를 이용한 쿼리

SELECT address, COUNT(*)
FROM Address
GROUP BY address

. 결과 값

adress	count
서울시	3
인천시	2
부산시	2
속초시	1
서귀포시	1

위의 2개의 처리 시의 차이점은
 - GROUP BY 사용 시
   일단 address 필드로 테이블을 자르고, 이어서 잘라진 조각 개수만큼의 레코드 수를 더해 결과를 출력합니다.
   이때 출  력 결과의 레코드수는 Address 테이블에 포함되어 있는 지역 수인 5개가 됩니다.
 - PARTITION BY 사용 시
   테이블을 자르는 것은 GROUP BY와 같으며, 윈도우 함수는 이를 'PARTITION BY'라는 구로 수행합니다.
   차이점은 자른 후 집약하지 않으므로 출력 결과의 레코드수가 입력되는 테이블의 레코드 수와 같다는 것입니다.
   ※ 위의 GROUP BY와 같은 결과가 아닌 집약 전의 데이터가 출력 됩니다.

---

쿼리 조건
  - 나이가 많은 순서대로 순위를 구하시오

윈도우함수를 이용한 쿼리

 

SELECT name, age, 
	RANK() OVER(ORDER BY age DESC) AS rnk
FROM Address

. 결과 값

name	age	rnk
하린	55	1
준	45	2
기주	32	3
민	32	3
인성	30	5
아린	25	6
하진	21	7

 

' 순위구하는 SQL (건너뛰기 없음)

SELECT name, age,
	DENSE_RANK() OVER(ORDER BY age DESC) AS dense_rnk
FROM Address

. 결과 값

name	age	rnk
하린	55	1
준	45	2
기주	32	3
민	32	3
인성	30	4
아린	25	5
하진	21	6

---

쿼리 조건

Sales 테이블을 참조하여, 아래의 조건을 추가 한후,  Sales2에 데이터를 입력하시오

이전 연도가 없을 경우 : NULL
이전 연도보다 매출이 올랐을 경우 : +
이전 연도보다 매출이 내렸을 경우 : -
이전 연도와 매출이 동일한 경우 : =


Sales 테이블

company(회사)	year(연도)	sale(매상 : 억)
A	2002	50
A	2003	50
A	2004	55
A	2007	55
B	2001	27
B	2005	28
B	2006	28
B	2009	30
C	2001	40
C	2005	39
C	2006	38
C	2010	35

윈도우함수를 이용한 쿼리

INSERT INTO Sales2
SELECT company,
		year,
        sale,
        CASE SIGN(sale - MAX(sale)
        						OVER(PARTITION BY company
                                ORDER BY year
                                ROW BETWEEN 1 PRECEDING
                                		AND 1 PRECEDING))
		WHEN 0 THEN '='
        WHEN 1 THEN '+'
        WHEN -1 THEN '-'
        ELSE NULLL END AS var
FROM Sales;

ROW BETWEEN 1 PRECEDING AND 1 PRECEDING 은 "현재 레코드에서 1개 이전부터 1개 이전까지의 레코드 범위"
를 나타냅니다. 따라서 직전의 1개의 레코드를 제한하게 됩니다.
"1개전 레코드"가 아니라 "2개 전 레코드"를 하고 싶다면, ROW BETWEEN 2 PRECEDING AND 2 PRECEDING 로 범위를
변경하면 됩니다.

결과값 - Sales2 테이블

company(회사)	year(연도)	sale(매상 : 억)	var(변화)
A	2002	50
A	2003	50	+
A	2004	55	+
A	2007	55	=
B	2001	27
B	2005	28	+
B	2006	28	=
B	2009	30	+
C	2001	40
C	2005	39	-
C	2006	38	-
C	2010	35	-

 

 

---

쿼리 조건
 - 직전 회사명, 직전 매상을 구하시오

 

윈도우 함수를 이용한 쿼리

SELECT company, year, sale,
	MAX(company)
    	OVER(PARTITION BY company
        	ORDER BY year
            ROWS BETWEEN 1 PRECEDING
            AND 1 PRECEDING) AS pre_company
	MAX(sale)
    	OVER(PARTITION BY company
        	ORDER BY year
            ROWS BETWEEN 1 PRECEDING
            AND 1 PRECEDING) AS pre_sale

결과값

company	year	sale	pre_company	pre_sale
A	2002	50
A	2003	52	A	50
A	2004	55	A	52
A	2007	55	A	55
B	2001	27
B	2005	28	B	27
B	2006	28	B	28
B	2009	30	B	28
C	2001	40
C	2005	39	C	40
C	2006	38	C	39
C	2010	35	C	38

 

---