[SQL 입문] 분석 함수

05 - 6 분석 함수

분석 함수는 행 그룹을 기반으로 집계해 이동 평균, 누계, 백분율, 그룹 내 상위 N개 결과를 계산합니다. 앞에서 배운 집계 함수와 다른 점은 각 그룹에 여러 행을 반환할 수 있다는 것입니다.

 

앞 또는 뒤의 행 참조하기 : LAG, LEAD 함수

LAG 함수는 현재 행에서 바로 앞의 행에 접근하고, LEAD 함수는 현재 행에서 바로 뒤의 행에 접근합니다. 물론 offset 인자에 전달한 값에 따라 이전 또는 이후 몇 번째 행의 데이터를 참조할지 결정할 수 있습니다.

-- LAG, LEAD 함수의 기본 형식
LAG[LEAD] (scalar_expression [, offset], [default]) OVER([paartition_by_clause] order_by_clause)

offset은 이전 또는 이후 몇 번째 행의 데이터를 참조할지 결정할 값입니다. 기본값은 1이므로 아무것도 전달하지 않으면 1칸 앞이나 1칸 뒤의 데이터를 참조합니다.

-- 현재 행 기준으로 앞 또는 뒤의 행 참조
SELECT
date,
LAG([close]) OVER(ORDER BY [close] ASC) AS lag_price,
[close],
LEAD([close]) OVER(ORDER BY [close] ASC) AS lead_price
FROM stock
WHERE symbol = 'MSFT'
AND date >= '2021-01-01' AND date < '2021-01-20'

-- 현재 행 기준으로 앞 또는 뒤의 행 참조 : offset = 2 설정
SELECT
date,
LAG([close], 2) OVER(ORDER BY [close] ASC) AS lag_price,
[close],
LEAD([close], 2) OVER(ORDER BY [close] ASC) AS lead_price
FROM stock
WHERE symbol = 'MSFT'
AND date >= '2021-01-01' AND date < '2021-01-20'

 

누적 분포 계산하기 : CUME_DIST 함수

CUME_DIST 함수는 그룹 내에서 누적 분포를 계산합니다. 다시 말해 그룹에서 지정한 값의 상대 위치를 계산합니다.

-- CUME_DIST 함수의 기본 형식
CUME_DIST() OVER([partition_by_clause] order_by_clause)

예를 들어 다음과 같이 Department 그룹별로 Rate 값의 누적 분포를 계산했을 때, 가장 높은 Rate는 1이고 그 하위 Rate는 1을 기준으로 몇 분위에 있는지를 나타냅니다.

Department	LastName	Rate	CumeDist
Document Control	Arifin	17.7885	1
Document Control	Norred	16.8269	0.8
Document Control	Kharatishvili	16.8269	0.8
Document Control	Chai	10.25	0.4
Document Control	Berge	10.25	0.4
Information Services	Trenary	50.4808	1
Information Services	Conroy	39.6635	0.9
Information Services	Ajenstat	38.4615	0.8
Information Services	Wilson	38.4615	0.8
Information Services	Sharma	32.4519	0.6
Information Services	Connelly	32.4519	0.6
Information Services	Berg	27.4038	0.4
Information Services	Meyyappan	27.4038	0.4
Information Services	Bacon	27.4038	0.4
Information Services	Bueno	27.4038	0.4

그룹 내 누적 분포 계산

CUME_DIST 함수는 0 초과 1 이하 버위의 이러한 값을 반환하며, 같은 값은 항상 같은 누적 분폿값으로 계산합니다. CUME_DIST 함수는 기본적으로 NULL값을 포함하며 가능한 한 가장 낮은 값으로 취급합니다.

-- CUME_DIST 함수로 누적 분포 계산
SELECT
symbol, sector,
ROUND(close_price, 0),
CUME_DIST() OVER(ORDER BY ROUND(close_price, 0) DESC) AS CUME_DIST
FROM nasdaq_company

 

상대 순위 계산하기 : PERCENT_RANK 함수

PERCENT_RANK 함수는 상대 순위를 계산할 수 있습니다. 또한 쿼리 결과 집합 또는 파티션 내의 상대 순위를 평가할 수도 있습니다. PERCENT_RANK 함수는 앞에서 배운 CUME_DIST 함수와 유사하지만 누적 분포가 아닌 분포 순위라는 점이 다릅니다.

-- PERCENT_RANK 함수의 기본 형식
PERCENT_RANK() OVER([partition_by_clause] order_by_clause)

예를 들어 다음과 같이 Department 그룹으로 Rate 값의 분포 순위를 계산했을 때, 가장 높은 Rate는 1이고 그 하위 Rate는 1을 기준으로 분포 순위를 나타냅니다.

Department	LastName	Rate	CumeDist
Document Control	Arifin	17.7885	1
Document Control	Norred	16.8269	0.5
Document Control	Kharatishvili	16.8269	0.5
Document Control	Chai	10.25	0
Document Control	Berge	10.25	0
Information Services	Trenary	50.4808	1
Information Services	Conroy	39.6635	0.888888888888889
Information Services	Ajenstat	38.4615	0.666666666666667
Information Services	Wilson	38.4615	0.666666666666667
Information Services	Sharma	32.4519	0.444444444444444
Information Services	Connelly	32.4519	0.444444444444444
Information Services	Berg	27.4038	0
Information Services	Meyyappan	27.4038	0
Information Services	Bacon	27.4038	0
Information Services	Bueno	27.4038	0

그룹 내 분포 순위 계산

PERCENT_RANK 하수의 반환값 범위는 0 초과 1 이하입니다. 모든 그룹의 첫 번째 행은 PERCENT_RANK 함수의 반환값이 0이며 NULL값은 기본 포함되고 가능한 한 가장 낮은 값으로 취급됩니다. PERCENT_RANK 함수에서는 NULL도 유효한 데이터로 포함합니다.

-- PERCENT_RANK 함수로 상대 순위 계산
SELECT
symbol, sector,
ROUND(close_price, 0),
PERCENT_RANK() OVER(ORDER BY ROUND(close_price, 0) DESC) AS PERCENT_RANK
FROM nasdaq_company

 

중앙값 계산하기 : PERCENTILE_CONT, PERCENTILE_DISC 함수

PERCENTILE_CONT와 PERCENTILE_DISC는 모두 중앙값을 계산하는 함수입니다.

-- PERCENTILE_CONT, PERCENTILE_DISC 함수의 기본 형식
PERCENTILE_CONT [PERCENTILE_DISC] (numeric_literal)
	WITHIN GROUP (ORDER BY ordder_by_expression [ASC | DESC])
    OVER ([<prtition_by_clause>])

여기서 매개변수 numeric_literal에 전달하는 인자는 계산할 백분위 수이며 범위는 0.0 ~ 1.0 사이입니다. WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )는 정렬할 숫잣값 목록을 지정하며 기본 정렬 순서는 오름차순인 ASC입니다. PERCENTILE_CONT, PERCENTILE_DISC 함수는 데이터 집합에서 NULL값은 무시합니다. PERCENTILE_CONT 함수는 데이터셋에 있거나 없을 수 있는 적절한 값을 보간(interpolation)한 결과를 반환하며, PERCENTILE_DISC 함수는 정확한 위치의 실젯값인 중위수를 추출할 때 사용합니다.

-- 특정 열의 그룹별 중앙값 계산
SELECT
symbol,
sector,
close_price,
PERCENTILE_COUNT(0.5) WITHIN GROUP (ORDER BY close_price) OVER (PARTITION BY sector) AAS PT_COUNT,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY close_price) OVER (PARTITION BY sector) AAS PT_DISC,
FROM nasdaq_company
WHERE close_price IS NOT NULL

 

정렬된 첫 번째 / 마지막 행의 값 구하기 : FIRST_VALUE, LAST_VALUE 함수

FIRST_VALUE 함수는 정렬된 데이터에서 첫 번째 행의 값을 반환하며 LAST_VALUE 함수는 마지막 행의 값을 반환합니다.

-- 특정 열의 그룹별 가격차 검색 : FIRST_VALUE 함수로 최고가 반환
SELECT
symbol,
date,
[close],
[close] - (FIRST_VALUE([close]) OVER(PARTITION BY symbol ORDER BY [close] DESC))
FROM stock
WHERE date >= '2021-01-01' AND date < '2021-02-01'
ORDER BY symbol, date