[SQLD] SQL 활용 - 윈도우 함수

윈도우 함수 (WINDOW FUNCTION)

행과 행 간의 관계를 쉽게 정의하게 위해 만든 함수

• 윈도우 함수는 분석 함수나 순위 함수로도 알려져 있다.
• 윈도우 함수는 기존에 사용하던 집계 함수도 있고, 새로이 윈도우 함수 전용으로 만들어진 기능도 있다.
• 윈도우 함수는 다른 함수와 달리 중첩해서 사용은 못하지만, 서브쿼리에는 사용할 수 있다.

SELECT WINDOW_FUNCTION (ARGUMENTS)
	OVER ([PARTITION BY 칼럼][ORDER BY 절][WINDOWING 절])
    FROM 테이블명;
    
* OVER 문구 필수

• WINDOW_FUNCTION: 윈도우 함수
• AGUMENTS(인수): 함수에 따라 0~N개 인수가 지정될 수 있다.
• PARTITION BY절: 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
• WINDOWING 절: WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (SQL Server에서는 지원하지 않음)

 

 

순위 함수(RANK FUNCTI0N)

RANK 함수

순위를 구하는 함수.

특정 범위(PARTITION)내에서 순위를 구할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있다.

동일한 값에 대해서는 동일한 순위를 부여하게 된다.

// 사원 데이터에서 급여가 높은 순서와 JOB별로 급여가 높은 순서를 출력

SELECT JOB, ENAME, SAL,
		RANK() OVER (ORDER BY SAL DESC) ALL_RANK, -- 급여 높은 순
        RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -- JOB 별로 급여 높은 순
FROM EMP;

동일한 급여가 있다면 같은 순위를 부여한다.

PARTITION으로 구분한 JOB_RANK는 같은 업무 범위 내에서만 순위를 부여한다.

하나의 문장에 ORDER BY SAL DESC와 PARTITION BY JOB의 조건이 충돌 났기 때문에 JOB별로는 정렬 되지 않고, ORDER BY SAL DESC 조건으로 정렬 된다.

 

DENSE_RANK 함수

RANK와 흡사하지만, 동일한 순위를 하나의 건수로 취급한다.

RANK는 1,2,3 순위로 표기하지만, DENSE_RANK는 1,1,3 순위를 부여한다. (1위가 두개 이니 2위 없이 3위로 넘어감)

SELECT JOB, ENAME, SAL,
		RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
        DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK,
FROM EMP;

 

ROW_NUMBER 함수

RANK, DENSE_RANK가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해,

ROW_NUMBER는 동일한 값이라도 고유한 순위를 부여한다.

 

SELECT JOB, ENAME, SAL,
		RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
        ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

ROW_NUMBER는 동일한 순위를 배제하기 위해 유니크한 순위를 정한다.

같은 값에 대해서 어떤 결과가 먼저 나올지 순서가 정하고 싶다면 ORDER BY를 같이 기재하라.

 

 

집계 함수

집계 함수	설명
COUNT	행의 수 출력
SUM	총 합계
AVG	평균
MAX	최댓값
MIN	최솟값

 

 

행 순서 함수

• FIRST_VALUE 함수: 처음 나오는 값을 구한다
• LAST_VALUE 함수: 가장 나중에 나오는 값을 구한다
• LAG 함수: 이전 몇번째 행을 가져온다
• LEAD 함수: 특정 위치의 행을 가져온다

 

비율 함수

• RATIO_TO_REPORT 함수: 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 조회
• PERCENT_RANK 함수: 파티션의 값 중 가장먼저 나온 것을 0 나중을 1을 기준으로 행별로 백분율 조회
• CUME_DIST 함수: 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 조회
• NTILE 함수: 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과

 

📌 참고자료

https://velog.io/@zinu/SQLD-2%EA%B3%BC%EB%AA%A9-SQL-%EA%B8%B0%EB%B3%B8-%EB%B0%8F-%ED%99%9C%EC%9A%A9-%EC%9C%88%EB%8F%84%EC%9A%B0-%ED%95%A8%EC%88%98

[SQLD] 2과목 SQL 기본 및 활용 - 윈도우 함수