행 그룹을 기반으로 집계해 이동 평균, 누계, 백분율, 그룹 내 상위 N개의 결과를 계산한다.
앞 또는 뒤 행 참조하기: LAG, LEAD 함수
하루 전 날짜와 오늘 날짜 데이터를 비교할 때, LAG 또는 LEAD 함수를 사용하면 간편하다.
LAG 함수는 현재 행에서 바로 앞의 행에 접근하고,
LEAD 함수는 현재 행에서 바로 뒤의 행에 접근한다.
물론, offset 인자에 전달한 값에 따라 이전 또는 이후 몇 번째 행의 데이터를 참조할지 결정할 수 있다.
| LAG, LEAD 함수의 기본 형식 |
| LAG(scalar_expression [, offeset], [default]) OVER ([partition_by_clause] order-by_clause) |
다음은 stock 테이블에서 symbol =' MSFT'인 데이터를 검색해 현재 행 기준으로 앞 또는 뒤의 행을 참조한다. 앞,뒤 데이터 비교를 쉽게 하기 위해 기준 열인 close를 가운데 두었다.
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'
결과
date lag_price close lead_price
2021-01-06 00:00:00.000 NULL 212.25 212.649993896484
2021-01-15 00:00:00.000 212.25 212.649993896484 213.020004272461
2021-01-14 00:00:00.000 212.649993896484 213.020004272461 214.929992675781
2021-01-12 00:00:00.000 213.020004272461 214.929992675781 216.339996337891
2021-01-13 00:00:00.000 214.929992675781 216.339996337891 216.440002441406
2021-01-19 00:00:00.000 216.339996337891 216.440002441406 217.490005493164
2021-01-11 00:00:00.000 216.440002441406 217.490005493164 217.690002441406
2021-01-04 00:00:00.000 217.490005493164 217.690002441406 217.899993896484
2021-01-05 00:00:00.000 217.690002441406 217.899993896484 218.289993286133
2021-01-07 00:00:00.000 217.899993896484 218.289993286133 219.619995117188
2021-01-08 00:00:00.000 218.289993286133 219.619995117188 NULL
close 열 기준으로 왼쪽은 현재 행의 앞, 오른쪽은 현재 행의 뒤에 있는 행을 참조함을 알 수 있다.
다음은 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'
결과
date lag_price close lead_price
2021-01-06 00:00:00.000 NULL 212.25 213.020004272461
2021-01-15 00:00:00.000 NULL 212.649993896484 214.929992675781
2021-01-14 00:00:00.000 212.25 213.020004272461 216.339996337891
2021-01-12 00:00:00.000 212.649993896484 214.929992675781 216.440002441406
2021-01-13 00:00:00.000 213.020004272461 216.339996337891 217.490005493164
2021-01-19 00:00:00.000 214.929992675781 216.440002441406 217.690002441406
2021-01-11 00:00:00.000 216.339996337891 217.490005493164 217.899993896484
2021-01-04 00:00:00.000 216.440002441406 217.690002441406 218.289993286133
2021-01-05 00:00:00.000 217.490005493164 217.899993896484 219.619995117188
2021-01-07 00:00:00.000 217.690002441406 218.289993286133 NULL
2021-01-08 00:00:00.000 217.899993896484 219.619995117188 NULL
누적 분포 계산하기: CUME_DIST 함수
그룹내 누적 분포를 계산한다. 즉, 그룹에서 지정한 값의 상대 위치를 계산한다.
| CUME_DIST 함수의 기본 형식 |
| CUME_DIST() OVER ([partition_by_clause] order-by_clause) |
예를 들어, Department 그룹별로 Rate 값의 누적 분포를 계산했을 때, 가장 높은 Rate는 1이고 그 하위는 1을 기준으로 몇 분위에 있는지를 나타낸다.
CUME_DIST 함수는 0초과 1이하 범위의 값을 반환하며, 같은 값은 항상 같은 누적 분푯값으로 계산한다. CUME_DIST 함수는 기본적으로 NULL 값을 포함하며 가능한 한 가장 낮은 값으로 취급한다.
SELECT
symbol, sector,
ROUND(close_price, 0),
CUME_DIST OVER (ORDER BY ROUND (close_price, 0) DESC) AS cume_dist
FROM nasdaq_company
결과
symbol sector (열 이름 없음) cume_dist
BTC-USD NULL 60776 0.00011213276519399
^DJI NULL 36328 0.000224265530387979
YM=F NULL 34697 0.000336398295581969
^N225 NULL 29535 0.000448531060775959
NAV Capital Goods 18600 0.000560663825969948
^IXIC NULL 15972 0.000672796591163938
상대 순위 계산하기: PERCENT_RANK 함수
PERCENT_RNAK 함수는 상대 순위를 계산한다. 또한 쿼리 결과 집합 또는 파티션 내의 상대 순위를 평가할 수 있다. CUME_DIST 함수와 유사하나 누적 분포가 아닌 분포 순위라는 점이 다르다.
| PERCENT_RANK 함수의 기본 형식 |
| PERCENT_RANK() OVER ([partition_by_clause] order-by_clause) |
모든 그룹의 첫번째 행은 0이고, 기본적으로 NULL 값을 포함하며 가능한 한 가장 낮은 값으로 취급한다. NULL도 유효 데이터로 취급한다.
SELECT
symbol, sector,
ROUND(close_price, 0),
PERCENT_RANK() OVER (ORDER BY ROUND (close_price, 0) DESC) AS PERCENT_RANK
FROM nasdaq_company
결과
symbol sector (열 이름 없음) PERCENT_RANK
BTC-USD NULL 60776 0
^DJI NULL 36328 0.000112145340361108
YM=F NULL 34697 0.000224290680722216
^N225 NULL 29535 0.000336436021083324
NAV Capital Goods 18600 0.000448581361444432
중앙값 계산하기: PERCENTILE_CONT, PERCENTILE_DISC 함수
| PERCENTILE_CONT, PERCENTILE_DISC 함수의 기본 형식 |
| PERCENTILE_CONT(numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC or DESC]) OVER ([partition_by_clause]) |
numeric_literal에 전달할 인자는 계산할 백분위 수이며 범위는 0.0~1.0 사이이다. WITHIN GROUP(ORDER BY order_by_exrpession [ASC or DESC])는 정렬할 숫잣값 목록을 지정하며 기본 정렬 순서는 오름차순인 ASC이다.
PERCENTILE_CONT, PERCENTILE_DISC 함수는 NULL 값을 무시한다.
PERCENTILE_CONT 함수는 데이터셋에 있거나 없는 적절한 값을 보간한 결과를 반환하며,
PERCENTILE_DISC 함수는 정확한 위치의 실젯값인 중위수를 추출할 때 사용한다.
SELECT
symbol, sector, close_price,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY close_price) OVER(PARTITION BY sector) As PT_COUNT,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY close_price) OVER(PARTITION BY sector) As PT_DISC
FROM nasdaq_company
WHERE close_price is NOT NULL
결과
symbol sector close_price PT_COUNT PT_DISC
EURUSD=X NULL 1.15553498268127 2030.52001953125 1820
^VIX NULL 16.4799995422363 2030.52001953125 1820
DBA NULL 19.2700004577637 2030.52001953125 1820
SI=F NULL 22.0209999084473 2030.52001953125 1820
정렬된 첫번째/마지막 행의 값 구하기: FIRST_VALUE, LAST_VALUE 함수
stock 테이블에서 특정 기간 동안 symbol 그룹별로 가장 높은 종가를 구한 뒤 해당 가격과 현재 가격의 차이가 얼마인지 검색한 쿼리이다.
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-1'
ORDER BY symbol, date
결과
symbol date close (열 이름 없음)
^CMC200 2021-01-05 00:00:00.000 654.447998046875 -106.835998535156
^CMC200 2021-01-06 00:00:00.000 696.783020019531 -64.5009765625
^CMC200 2021-01-07 00:00:00.000 749.794982910156 -11.489013671875
^CMC200 2021-01-08 00:00:00.000 761.283996582031 0
^CMC200 2021-01-11 00:00:00.000 654.158996582031 -107.125
'IT Story > SQL Story' 카테고리의 다른 글
| 하루 동안 상승/하락 종목 분석하기 (0) | 2022.12.06 |
|---|---|
| 주가 분석하기 (0) | 2022.12.06 |
| SQL 함수: 순위 함수 (0) | 2022.12.06 |
| SQL 함수: 수학 함수 (0) | 2022.12.05 |
| SQL 함수: 집계 함수 (0) | 2022.12.05 |