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

박진우·2022년 6월 5일
2과목SQLDWindow Function윈도우 함수
0

SQLD

목록 보기
15/21

💡 윈도우 함수(Window Function)

행마다 계산한 값을 같이 보여주는 것이 필요한 경우 사용한다.
 
즉 여러 행 간의 관계 정의 함수라고 할 수 있다.

WINDOW FUNCTION 개요

기존 관계형 데이터베이스는 컬럼과 컬럼간의 연산, 비교, 집계 등은 쉬운 반면, 행과 행간의 관계를 정의하거나 비교, 연산하는 것은 하나의 SQL 문으로 처리하기 어렵다.
절차형 프로그램을 작성하거나, INLINE VIEW 를 이용하여 복잡한 SQL 문을 작성해야 했던 것을 부분적으로라도 행과 행간의 관계를 쉽게 정의하기 위해 사용하는 함수가 윈도우 함수(WINDOW FUNCTION) 이다.



  • 행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수를 이름이다.

  • 분석 함수(ANALYTIC FUNCTION)이나 순위 함수(RANK FUNCTION) 라고도 부른다.

  • 기존 집계 함수, 새로이 WINDOW 함수 전용 존재한다.

  • 서브쿼리 사용 가능, 중첩(NEST)해서 사용 불가하다.

  • OVER문구가 키워드로 필수 포함된다

  • WINDOWING함수 2가지 종류(BETWEEN 사용 타입, BETWEEN 미사용 타입)

  • SQL>> 

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

◽ WINDOW FUNCTION 종류


▪ 알아볼 윈도우함수의 종류

순위함수 집계함수 행순서함수 비율함수
RANK
DENSE_RANK
ROW_NUMBER		 SUM, AVG
MAX, MIN
COUNT		 FIRST_VALUE
LAST_VALUE
LAG, LEAD		 CUME_DIST
PERCENT_RANK
NTILE
RATIO_TO_REPORT

◽ 그룹 내 순위(Rank) 함수

▪ RANK 함수

이름 그대로 각 행의 순위를 보여주는 함수이며,ORDER BY 를 포함한 QUERY 문에서 특정 컬럼에 대한 순위를 구하는 함수이다.

  • ORDER BY 를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.

  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구한다.

  • 동일한 값에 대해서는 동일한 순위를 부여한다.
    • 여러 개의 행이 같은 순위인 경우, 다음 랭크는 이어진 숫자(+1)가 아니다.
      1등인 행이 2개 있다면 다음 행은 3등이 되는 것이다.
  • SQL>>
SELECT JOB, ENAME, SAL,
        
       RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK,
       RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
        
  FROM EMP;

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

                                   -결과-

  • 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다.

  • 동일 SALARY 에 대해서 같은 순위 부여한다.

  • ORDER BY SAL DESC 와 PARTITION BY JOB ORDER BY SAL DESC 가 충돌 하여 ORDER BY SAL DESC 기준으로 정렬한다.

▪ DENSE_RANK 함수

DENSE_RANK 함수는 RANK 함수와 다르게 순서 있는 집합에서 각 행의 순위를 구하는 함수이다.

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

  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구한다.

DENSE_RANK() OVER (
PARTITION BY A, B,...
ORDER BY A [ASC | DESC], B,..
)

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

사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력하라.

                                   -결과-

  • ANALYST 의 SAL 은 값이 동일하기 때문에 같은 순위를 부여한다. 이 다음 순위가 RANK 함수는 4, DENSE_RANK 함수는 3 으로 달라지는 것을 통해 차이점을 알 수 있다.

▪ ROW_NUMBER 함수

동일한 값이라도 고유한 순위를 부여한다는 점이 RANK,DENSE_RANK와 다르다.

  • 각 파티션이 연속적인 정수를 할당받기 때문에, 파티션이 바뀔 때마다 번호가 초기화되고 파티션 안에서는 번호가 중복되지 않는다.

  • 페이징(pagination)에서 쓰인다.
  • SQL>>
SELECT JOB, ENAME, SAL, RANK( ) 
 OVER (ORDER BY SAL DESC) ALL_RANK, RANK( ) 
 OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

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

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

사원데이터에서 급여가 높은 순서의 RANK, ROW_NUMBER 결과를 조회

                                   -결과-

  • 같은 SALARY 에서는 어떤 순서가 정해질지 알수 없다.(Oracle 의 경우 rowid 가 적은 행이 먼저 나온다. )

  • 순서를 지정하기 위해서는 ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME) 과 같이 ORDER BY 절을 이용하여 표기해야 한다.

  • DBMS(Oracle rowid) 에 의해 정렬을 원지 않는다면, 명시적으로 ORDER BY 추가 할 것

◽ 일반집계 함수(Aggregate function)

튜플에 관심있는 것이 아니라 몇개의 튜플에 관심이 있는 경우, 집계함수를 사용하며, 집계함수와 일반 애트리뷰트를 같이 출력할 수 없다.

  • SUM : 결과 행들의 합 을 구한다.

  • AVG : 결과 행들의 평균 을 구한다.
SELECT AVG(Midterm) AS 중간평균  FROM ENROL WHERE Cno = ‘A1997’;

등록테이블에서 과목번호가 A1997인 과목의 중간교사의 평균을 구하라.

SELECT count(distinct sno), avg(final) from A where cno = 'SQLD';
SELECT cno, count(*), avg(final) FROM  A GROUP BY cno

등록테이블에서 과목번호가 'SQLD'인 과목의 기말고사의 평균을 구하라.

  • MAX : 결과 행들 중 MAX값 을 출력한다.

  • MIN : 결과 행들 중MIN값 값을 출력한다.
      
SELECT  e.cno, e.ename , MIN(final), MAX(final)
  FROM enrol e, student s
  WHERE e.sno = s.sno AND s.year = 2 
  GROUP BY cno ORDER BY cno DESC

2학년 학생들이 수강하는 과목들의 과목 번호, 과목명 , 기말고사 최대/최소값을 과목번호 순으로 구하여라.  

  • COUNT : 결과 행들의 개수를 구한다. (중복된 값도 모두 COUNT)
SELECT COUNT(*) AS 학생수  FROM STUDENT;

학생 테이블의 전체 튜플의 개수가 몇개인지 출력

SELECT COUNT(cno) AS 학생수  FROM STUDENT;

중복을 제거하지 않은 cno 의 튜플의 개수 출력

◽그룹 내 행 순서 함수

▪ FIRST_VALUE 함수

  • FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

  • SQL Server 에서는 지원하지 않는 함수이다.

  • MIN 을 활용해도 같은 결과 얻는다.

  • SQL>>
SELECT DEPTNO, ENAME, SAL,
              
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
              
FROM EMP;

부서별 직원을 연봉이 높은 순서대로 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력

  • 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.

  • ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

  • FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리한다.

  • DEPTNO = 20 파티션의 최고 연봉은 3000 이고, SCOTT 과 FORD 가 대상 ➡️ 이중 하나인 SCOTT 만 처리된다.

  • 명시적인 정렬을 위해서 INLINE VIEW 나, OVER () 내의 ORDER BY 절 에 칼럼을 추가 한다.

❓RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

▪ LAST_VALUE 함수

  • LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

  • SQL Server 에서도 지원한다.

  • MAX 을 활용해도 같은 결과 얻는다.

  • SQL>>
SELECT DEPTNO, ENAME, SAL,
              
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS  DEPT_POOR
              
FROM EMP;

부서별 직원을 연봉이 높은 순서대로 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력

❓ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

▪ LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
즉 현재 읽혀진 데이터의 이전 값을 알아내는 함수

  • SQL Server 에서는 지원하지 않는다.

  • SQL >> 
SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER ( 
ORDER BY HIREDATE ) AS PREV_SAL 
FROM EMP 
WHERE JOB ='SALESMAN';

직원을 입사일자 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력

  • LAG(인수1,인수2,인수3 )

  • 인수1 : 입력 칼럼

  • 인수2 : 몇 번째 앞의 행 을 가져올지 결정(DEFAULT 1)

  • 인수3 : 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력 (✅ NVL/ISNULL함수 와 동일)

▪ LEAD 함수

LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. 즉 이후 값을 알아내는 함수

  • SQL>>
SELECT ENAME, HIREDATE, LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED" 
FROM EMP ;

직원을 입사일자 기준으로 정렬하고, 바로 다음에 입사한 직원의 입사일자를 출력

  • LEAD(인수1,인수2,인수3 )

  • 인수1 - 입력 칼럼

  • 인수2 - 몇 번째 후 의 행을 가져올지 결정(DEFAULT 1)

  • 인수3 - 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력

  • LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰인다. (DEFAULT 1)

◽그룹 내 비율 함수

  • 칼럼 값에 대한 백분률 ➡️ RATIO_TO_REPORT

  • 행의 순서에 대한 (0~1사이 값) 백분률 ➡️ PERCENT_RANK

  • 1/(파티션) 전체 건수로 표현하는 백분률 ➡️ CUME_DIST

▪ RATIO_TO_REPORT 함수

  • 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구한다.

  • 0 < 백분율 < 1, 개별 RATIO 의 합 = 1
  • SQL>>
SELECT ENAME, SAL,
  ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
  FROM EMP
 WHERE JOB = 'SALESMAN';

JOB 이 SALESMAN 인 사원을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력

  • 실행 결과에서 전체 값은 1650+1250+1250+1500=5600 이 되고, RATIO_TO_REPORT 함수 연산의 분모로 사용된다.

  • 개별 RATIO 의 전체 합을 구하면 1이 되는 것을 확인할 수 있다. 0.29+0.22+0.22+0.27=1



▪ PERCENT_RANK 함수

  • 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것으로 1로해서 값이 아닌 행의 순서별 백분율을 구한다.

  • 0 < 백분율 < 1
  • SQL>>
SELECT DEPTNO, ENAME, SAL, 
PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R 
FROM EMP ;

같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력

  • DEPTNO 10 은 3건이므로 2개의 구간이 되어, 0과 1 사이를 2개로 나누면
    ➡️ 0, 0.5, 1

  • DEPTNO 20 은 5건이므로 4개의 구간이 되어, 0과 1 사이를 4개로 나누면
    ➡️ 0, 0.25, 0.5, 0.75, 1

  • DEPTNO 30 은 6건이므로 5개의 구간이 되어, 0과 1 사이를 5개로 나누면
    ➡️ 0, 0.2, 0.4, 0.6, 0.8, 1

  • SCOTT-FORD, WARD-MARTIN 은 ORDER BY SAL DESC 구문에 의해 급여가 같으므로 같은 ORDER 로 취급한다.



▪ CUME_DIST 함수

  • 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한
    누적 백분율 구한다.

  • 0 < 백분율 ≤ 1
  • SQL>>
SELECT DEPTNO, ENAME, SAL,
  
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
  
FROM EMP;

같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력

  • 다른 WINDOW 함수의 경우 동일 순서이면 앞 행의 함수 결과 값을 따르는데, CUME_DIST 의 경우는 동일 순서이면 뒤 행의 함수 결과값을 기준으로 한다.



▪ NTILE 함수

파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 구한다.

  • SQL>>
SELECT ENAME, SAL,
  
   NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
  
FROM EMP;

전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류

◽윈도우 함수 문법(WINDOW FUNCTION SYNTAX)

  • 구조 >>
SELECT WINDOW_FUNCTION (ARGUMENTS)

OVER ( [PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절] )

FROM 테이블 명
  • WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.

  • ARGUMENTS(인수) : 함수에 따라 0 ~ N개 의 인수가 지정될 수 있다.

  • PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹 으로 나눌 수 있다.

  • ORDER BY 절 : 어떤 항목에 대해 순위를 기정할 지 ORDER BY 절을 기술한다.

  • ROWS : 물리적 인 ROW 단위로 행 집합을 지정 ( 현재행을 기준으로 몇개의 행을 포함하는지 )

  • RANGE : 논리적 인 상대번지로 행 집합을 지정 ( 현재행을 기준으로 어떤 값의 범위를 포함하는지 )

  • BETWEEN ~ AND 절 : 윈도우의 시작 과 끝 위치를 지정한다.

  • UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우 에서 윈도우가 시작

  • UNBOUNDED FOLLOWING : PARTITION의 마지막 로우 에서 윈도우가 시작

  • CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우

  • WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다 ROWS➡️ 물리적 인 결과 행의 수를, RANGE➡️ 논리적 인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.
profile
박진우
이전 포스트

[SQLD] 2과목 SQL 기본 및 활용 - 서브쿼리, 그룹함수

다음 포스트

[SQLD] 2과목 SQL 기본 및 활용 - DCL

0개의 댓글