[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