윈도우 함수
- 서로 다른 행의 비교나 연산을 위해 만든 함수
- GROUP BY를 쓰지않고 그룹 연산 가능
- LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK
SELECT 윈도우함수([대상]) OVER ([PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC|DESC]
[ROWS|RANGE BETWEEN A AND B]- PARTITION BY 절
출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼 - ORDER BY 절
RANK의 경우 필수 (정렬 컬럼 및 정렬 순서에 따라 순위 변화)
SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용 - ROWS|RANGE BETWEEN A AND B
연산 범위 설정
ORDER BY 절 필수
그룹함수의 형태
- SUM, COUNT, AVG, MIN, MAX 등
- OVER 절을 사용하여 윈도우 함수로 사용 가능
- 반드시 연산할 대상을 그룹함수의 입력값으로 전달
SELECT SUM(대상) OVER ([PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC|DESC]
[ROWS|RANGE BETWEEN A AND B]SUM OVER()
- 전체 총 합, 그룹별 총 합 출력 가능
// 에러
// 각 직원 정보와 급여 총 합(그룹함수 결과)을 동시에 출력 시도
SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) FROM EMP;
//해결 1 : 서브쿼리 사용
SELECT EMPNO, ENAME, SAL, DEPTNO, (SELECT SUM(SAL) FROM EMP) AS TOTAL
FROM EMP
// 해결 2 : 윈도우 함수 사용
SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) OVER() AS TOTAL
FROM EMP;AVG OVER()
- SUM과 동일하게 사용
// 각 직원 정보와 해당 직원이 속한 부서의 평균 급여 출력
SELECT EMPNO, ENAME, SAL, DEPTNO,
AVG(SAL) OVER(PARTITION BY DEPTNO) AS AVG_SAL
FROM EMP;MIN/MAX OVER()
- SUM과 동일하게 사용
// 각 직원 정보와 해당 직원이 속한 부서의 최대급여를 함께 출력
SELECT EMPNO, ENAME, SAL, DEPTNO,
MAX(SAL) OVER(PARTITION BY DEPTNO) AS 부서별급여총합
FROM EMP;COUNT OVER()
- SUM과 동일하게 사용
윈도우 함수의 연산 범위
집계 연산 시 행의 범위 설정 가능
- ROWS, RANGE 차이
- ROWS : 값이 같더라도 각 행씩 연산
- RANGE : 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산
// RANGE_TEST 테이블에서의 범위 설정에 따른 누적합 // 1. RANGE 범위 전달 (default) // 값이 같을 경우 같은 범위로 취급하여 동시 연산 SELECT R.*, SUM(SAL) OVER(ORDER BY SAL) FROM RANGE_TEST R;
// 2. ROWS 범위 설정 시 // 각 행 별로 연산 수행 SELECT R.*, SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RESULT1 FROM RANGE_TEST R;
- BETWEEN A AND B
- 시작점 정의
CURRENT ROW : 현재행부터
UNBOUNDED PRECEDING : 처음부터 (default)
N PRECEDING : N 이전부터- 마지막 시점 정의
CURRENT ROW : 현재행까지 (default)
UNBOUNDED FOLLOWING : 마지막까지
N FOLLOWING : N 이후까지// 3. BETWEEN A AND B 수정 시 // UNBOUNDED PRECEDING AND 1 FOLLOWING // 각 행마다 누적합 계산 시 처음부터 다음 행까지 연산 SELECT R.*, SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS RESULT3 FROM RANGE_TEST R;
순위 관련 함수
RANK (순위)
- RANK WITHIN GROUP
특정값에 대한 순위 확인
윈도우함수가 아닌 일반함수
SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼);
// EMP에서 급여가 3000이면 전체 급여 순위가 열마?
SELECT RANK(3000) WITHIN GROUP(ORDER BY SAL DESC) AS RANK_VALUE
FROM EMP;- RANK() OVER()
전체 중/특정 그룹 중 값의 순위 확인 (동순위 처리)
ORDER BY절 필수
순위를 구할 대상을 ORDER BY 절에 명시
그룹 내 순위 구할 시 PARTITION BY 절 사용
SELECT RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);
// 각 직원 급여의 전체 순위 (큰 순서대로)
SELECT ENAME, DEPTNO, SAL,
RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1
FROM EMP; DENSE_RANK
- 누적 순위
- 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
- 1등이 5명이더라도 그 다음 순위는 2등
ROW_NUMBER
- 연속된 행 번호
- 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴
LAG, LEAD
- 행 순서대로 각각 이전값(LAG), 이후값(LEAD) 가져오기
- ORDER BY 절 필수
SELECT LAG(컬럼, N)
OVER([PARTITION BY 컬럼]
ORDER BY 컬럼 ASC|DESC);
// EMP에서 바로 이전 입사자와 급여 비교
SELECT ENAME, HIREDATE, SAL
LAG(SAL) OVER(ORDER BY HIREDATE) AS 바로직전상사급여
FROM EMP;FIRST_VALUE, LAST_VALUE
- 정렬 순서대로 정해진 범위에서의 처음, 마지막 값 출력
- 순서와 범위 정의에 따라 최소값, 최대값 리턴 가능
SELECT ENAME, DEPTNO, SAL,
FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS MIN_VALUE,
FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS MAX_VALUE
FROM EMP;
NTILE
- 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
- 그룹번호가 리턴됨
- ORDER BY 필수
SELECT NTILE(N) OVER([PARTITION BY 컬럼]
ORDER BY 컬럼 ASC|DESC
// NTILE을 사용한 그룹 분리
SELECT ENAME, SAL, DEPTNO,
NTILE(2) OVER (ORDER BY SAL) AS GROUP_NUMBER
FROM EMP;
비율관련 함수
RATIO_TO_REPORT
- 각 값의 비율 리턴 (전체 또는 특정 그룹 내 비율)
- ORDER BY 사용 불가
RATIO_TO_REPORT(대상) OVER([PARTITION BY ...])CUME_DIST
- 각 값의 누적 비율 리턴
- ORDER BY 필수
CUME_DIST() OVER([PARTITION BY 컬럼] ORDER BY 컬럼)PERCENT_RANK
- PERCENTILE(분위수) 출력
- 전체 COUNT 중 상대적 위치 출력 (0~1범위)
- ORDER BY 필수
PERCENT_RANK() OVER([PARTITION BY ...] ORDER BY ...])
홍쌤의 데이터랩
SQLD 2과목 PART2. SQL 활용 완벽 정리 (2024년 신유형 반영) 강의에 대해
공부 및 개인적으로 정리한 글 입니다.
