.gif&blockId=11b00c82-b138-8050-a58b-dcbceda2ef8f)
윈도우 함수
분석 함수(Analytic Functions): SQL 쿼리의 성능을 한 단계 높이는 강력한 도구, 단순히 데이터를 처리하는 것을 넘어, 데이터 분석과 쿼리 최적화에 있어 핵심적인 역할을 함.
•
Row별로 세부적인 계산을 가능하게 한다.
•
전체 데이터에 걸쳐 다양한 통계와 계산을 유연하게 수행할 수 있게 도와준다.
•
데이터베이스를 사용한 온라인 분석처리 용도 ︎ OLAP(Online Analytical Processing)함수
︎ OLAP(Online Analytical Processing)함수•
GROUP BY절 사용 불가 ︎ PARTITION BY로 구간 설정하여 사용
︎ PARTITION BY로 구간 설정하여 사용•
PARTITION BY는 GROUP BY와 같은 집약기능이 없기때문에 레코드가 줄어들지 않음. 그저 함수 적용 범위를 묶는 역할
•
윈도우 함수는 반드시 OVER과 함께 사용
•
기본 분석함수 사용법
SELECT ANALYTIC_FUNCTION(args) OVER (
[PARTITION BY 컬럼]
[ORDER BY 컬럼]
[WINDOWING절(ROWS | RANGE BETWEEN)]
)
FROM 테이블명;
SQL
복사
◦
ANALYTIC FUNCTION: 분석함수명(입력인자)
◦
OVER: 분석함수임을 나타내는 키워드
◦
PARTITION BY: 계산 대상 그룹 지정 >>GROUP BY와 비슷한 역할. 이 구문이 없으면 전체 데이터에 대해 계산이 적용됨.
◦
ORDER BY: 대상 그룹에 대한 정렬 수행
◦
WINDOWING 절 : 분석함수의 계산 대상 범위 지정
(ORDER BY절에 종속적. 기본 생략 구문 : 정렬된 결과의 처음~현재행까지)
분석/윈도우 함수 | 설명 |
RANK() | 동일한 값이 있을 경우 동일한 순위를 부여하고, 그 다음 순위를 건너뜀 |
DENSE_RANK() | 동일한 값에 동일한 순위를 부여하지만, 그 다음 순위를 건너뛰지 않음 |
ROW_NUMBER() | 각 행에 대해 고유한 순번을 부여 |
NTILE(N) | 결과를 N개의 동일한 크기로 나누고 각 행에 그룹 번호를 부여 |
LEAD(열, N, 결측값 채울 값) | 현재 행을 기준으로 이후 행의 값을 참조 |
LAG(열, N, 결측값 채울 값) | 현재 행을 기준으로 이전 행의 값을 참조 |
FIRST_VALUE() | 파티션 또는 그룹 내에서 정렬된 결과에서 첫 번째 값을 반환 |
LAST_VALUE() | 파티션 또는 그룹 내에서 정렬된 결과에서 마지막 값을 반환 |
CUME_DIST() | 누적 분포를 계산하여 특정 값 이하의 행이 집합에서 차지하는 비율을 반환 |
PERCENT_RANK() | 현재 행이 전체 집합에서 차지하는 백분위 순위를 반환 |
NTH_VALUE() | 파티션 또는 그룹에서 정렬된 결과 중 N번째 값을 반환 |
소계 : 전체가 아닌 어느 한 부분만을 셈한 합계
합계 ≒ 계 : 한데 합하여 계산함
총계 : 전체를 한데 모아서 계산함.
순위
•
RANK() : 중복 가능, 공동 순위만큼 건너뜀
•
DENSE_RANK() : 중복 가능, 공동 순위가 있더라도 순차적으로 순위 생성
•
ROW_NUMBER() : 중복 없는 순위, 행 번호
SELECT val
-- 행 번호
, ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'
-- 순위
, RANK() OVER (ORDER BY val) AS 'rank'
-- 순위 : 순차적
, DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM table
SQL
복사
◦
ORDER BY에 순위의 기준을 지정한다.
◦
인자가 들어가지 않는다.
•
NTILE(N) : 파티션 분할, 지정된 N 만큼의 등급으로 나누어 각 등급 번호를 출력
SELECT NTILE(2)
OVER(ORDER BY height DESC) "반번호", userName, addr, height
FROM userTBL;
SQL
복사
◦
전체 인원을 키순 정렬 후 2개의 그룹으로 분할
다음 행의 값 찾기
•
LAG(열, N=1, 결측값 채울 값) : 해당 열을 N칸 미루기
•
LEAD(열, N=1, 결측값 채울 값) : 해당 열을 N칸 당기기
•
ORDER BY에서 사용할 열과 대상 열이 SELECT문에서 존재해야 한다.
SELECT Id
, RecordDate -- ORDER 열
, Temperature -- 대상 열
-- 미루기
, LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
-- 당기기
, LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
FROM table
SQL
복사
→ PARTITION BY에 그룹할 기준을 지정한다.
→ ORDER BY에 정렬 기준을 지정한다.
