분석 함수는 데이터 집합의 각 행에 대해 집계 또는 통계적 계산을 수행하지만, 원본 행을 그대로 유지하며 결과를 제공하는 함수이다. 일반적인 집계 함수(예: SUM, AVG)는 데이터의 결과를 축약하지만, 분석 함수는 원본 데이터의 각 행에 대해 추가 정보를 제공한다.
1. LEAD() , LAG()
LEAD() 함수는 현재 행 기준으로 다음 행의 값을 가져오는 데 사용된다. 데이터의 연속성을 분석하거나 다음 값을 기준으로 계산이 필요할 때 사용한다.
반대로 LAG() 함수는 이전 행의 값을 가져오는데 사용된다.
LEAD(<컬럼명>, <오프셋>, <기본값>) OVER (PARTITION BY <분할 컬럼> ORDER BY <정렬 기준>)
USE sqlDB SELECT name, addr, height AS [키],height-(LEAD(height,1,0) OVER(ORDER BY height DESC)) AS [다음 사람과 키 차이] FROM userTb1
LEAD() 함수를 사용하면 다음 행의 데이터를 가져와 연산할 수 있다.
2. FIRST_VALUE()
FIRST_VALUE() 함수는 현재 윈도우(범위)에서 첫 번째 값을 반환한다. 데이터를 정렬한 후 윈도우의 첫 번째 값을 가져오기 때문에 그룹 내에서 시작 값을 확인하거나 특정 조건에 맞는 첫 번째 데이터를 추출할 때 사용한다.
FIRST_VALUE(<컬럼명>) OVER ( [PARTITION BY <분할 컬럼>] [ORDER BY <정렬 기준>] [ROWS or RANGE BETWEEN <범위>] ) AS <별칭>
SELECT addr, name, height AS 키, height - (FIRST_VALUE(height) OVER(PARTITION BY addr ORDER BY height DESC)) AS [지역별 가장 큰 키와 차이] FROM userTb1
지역별로 가장 키가 큰 사람과의 차이를 구한다. OVER 문장의 PARTITION BY addr 에서 지역별로 그룹화한다. 또 ORDER BY height DESC 에 의해서 키로 내림차순 정렬 후에, FIRST_VALUE(height) 로 각 지역별 첫번째 값을 추출하게 된다.
3. CUME_DIST()
CUME_DIST() 함수는 특정 행의 값이 데이터 집합에서 누적 분포의 위치(백분위수) 를 계산하는 데 사용된다. 결과 값은 0과 1 사이의 실수로 반환되며, 데이터 집합에서 현재 행의 값보다 작거나 같은 값의 비율을 나타낸다.
CUME_DIST() 를 사용하면 직원별 연봉이 소속 부서 중에서 몇 퍼센트 안에 드는지와 같은 경우에 활용할 수 있다.
CUME_DIST() OVER ( [PARTITION BY <분할 컬럼>] ORDER BY <정렬 기준> )
SELECT addr, name, height AS [가입일], (CUME_DIST() OVER (PARTITION BY addr ORDER BY height DESC)) * 100 AS [누적인원 백분율%] FROM userTb1
위의 결과는 현 지역에서 자신과 키가 같거나, 자신보다 큰 인원의 백분율을 구한 결과이다.
결과를 보면 조관우는 전체 경기 인원 2명 중에서 자신보다 키가 크거나 같은 사람이 1명(자신 포함) 이므로 50%가 된다. 또 조용필은 2명 중에서 자신보다 키가 크거나 같은 사람이 2명이므로 100%가 출력된다.
4. PERCENTILE_CONT()
PERCENTILE_CONT() 함수는 정렬된 데이터 집합에서 특정 백분위수 값을 계산하는 윈도우 함수이다. 이 함수는 연속형(Continuous) 백분위수를 계산하며, 값 사이를 선형적으로 보간하여 정확한 값을 반환한다. 데이터의 중간값(중위수) 또는 특정 위치의 값을 찾는 데 유용하며, 특히 통계 분석과 데이터 분포 분석에서 자주 사용된다.
PERCENTILE_CONT(<백분위수 값>) WITHIN GROUP (ORDER BY <컬럼명>) OVER ([PARTITION BY <분할 컬럼>])
SELECT DISTINCT addr , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY height) OVER (PARTITION BY addr) AS [지역별 키의 중앙값] FROM userTb1
위의 결과는 각 지역별로 키의 중앙값을 계산한 결과이다. PERCENTILE_CONT() 의 인자로는 0.0 ~ 1.0 사이의 백분율 값이 올 수 있다. 0.5를 입력하면 중앙값이 나온다. WITHIN GROUP에는 정렬할 컬럼을 지정한다. 위의 결과에선 키로 정렬 후 그 중앙값을 찾는다.
5. PIVOT/UNPIVOT 연산자
PIVOT과 UNPIVOT은 데이터를 변환하는 기능으로, 데이터를 열과 행 간에 변환하는 데 사용한다. PIVOT은 행 데이터를 열로 변환하며, UNPIVOT: 열 데이터를 행으로 변환한다.
PIVOT ( <집계 함수>(<집계 대상 열>) FOR <행을 열로 변환할 열> IN ([열1], [열2], ...)
USE tempDB CREATE TABLE pivotTest( uName NCHAR(3), season NCHAR(2), amount INT ) INSERT INTO pivotTest VALUES ('김범수','겨울',10),('윤종신','여름',15), ('김범수','가을',25),('김범수','봄',3), ('김범수','봄',37), ('윤종신','겨울',40), ('김범수','여름',14), ('김범수','가을',22), ('윤종신','여름',64)
pivotTest 테이블을 생성 후 데이터를 입력한다.
SELECT * FROM pivotTest
이 데이터를 pivot 을 수행한다.
SELECT * FROM pivotTest PIVOT (SUM(amount) FOR season IN ([봄],[여름],[가을],[겨울])) AS resultPivot
pivot 연산자를 사용해 행 데이터인 봄,여름,가을,겨울을 컬럼으로 변환할 수 있다.
Share article