SQL - 윈도우함수, 파티션

윈도우 함수 - 레코드(행) 사이의 관계를 쉽게 정의하기 위한 함수

 

SELECT WINDOW함수(ARGUMENTS) OVER( [PARTITION BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING]) FROM 테이블명

 

ARGUMENTS : 인수(컬럼명 등 작업이 이뤄지는 대상)

PARTITION BY : 테이블의 레코드들을 쪽내느 기준

ORDER BY : 쪼개진 레코드들 내에서 혹은 전체 테이블에서 레코드들을 어떤 기준으로 정렬할지

WINDOWING : 함수의 연산 대상이 되는 레코드들의 범위를 지정

 

WINDOW함수의 종류

- 그룹  내 집계함수 : COUNT, SUM, MIN, MAX, AVG 등

- 그룹 내 순위(RANK)함수

- 그룹 내 비율 관련 함수

- 그룹 내 행 순서 함수

 

RANK 함수

- RANK : 동일한 순위에 대해 동일한 순위를 부여한다. 동일한 순위를 하나의 건수로 계산하지 않는다.

    EX) 1등 다음에 공동 2등이 두명인데 다음으로 4등이 옴

- DENSE_RANK : 동일한 순위에 대해 동일한 순위를 부여한다. 동일한 순위는 하나의 건수로 계산한다.

    EX) 1등 다음에 공동 2등이 두명인데 다음으로 3등이 옴

- ROW_NUMBER : 동일한 순위에 대해 고유한 순위를 부여한다.

    EX) 1등 다음에 공동 2등이 두명인데 두명다 2등이 되는게 아니라 한명은 2등 한명은 3등으로 고유의 순위가 메겨지고      다음으로 4등이 옴

 

참고)

- 순위함수를 사용할 경우 ORDER BY를 입력해 오름차순, 내림차순으로 출력이 가능하다. ORDER BY 없이 자동으로 정렬되는것이 아니다.

- RANK 함수는 동일한 등수의 레코드가 많을 경우 등수가 생략될 수 있다.

- DENSE_RANK는 동일한 동수의 레코드가 많아도 등수가 생략되지 않는다.

 

EX)

SELECT 매장코드, 지역명, 메뉴명, 판매량,

               DENSE_RANK() OVER( PARTITION BY 지역명 ORDER BY 판매량 DESC) AS 판매순위

FROM 판매

 

비율 관련 함수

- PERCENT_RANK : 값이 아닌, 순서를 대상으로, 파티션 내에서의 순서별 백분율을 조회함

- NTILE(n) : 파티션별로 전체 건수를 n등분한 값을 반환한다. n=5이면 5등분한 값을 반환한다.

    EX) SELECT 이름, NTILE(7) OVER ( ORDER NY 점수 DESC ) AS 그룹순위 FROM 성적

    위의 SQL문을 실행하면 성적을 기준으로 7개의 그룹으로 나눠 성적이 높은순으로 그룹 순위가 정해진다.

- CUME_DIST : 파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대해 누적 백분율을 조회, 누적 분포 상에 0~1값을 가짐

 

행 순서 함수

- FIRST_VALUE : 파티션 내에서 가장 처음 나오는 값 반환, MIN과 동일한 결과

- LAST_VALUE : 파티션 내에서 가장 마지막에 나오는 값 반환, MAX와 동일한 결과

- LAG(컬럼명, 레코드위치차이값) : 특정 위치에서 이전 행을 가져온다.

- LEAD(컬럼명, 레코드위치차이값, NULL일 경우 대체값) : 특정 위치에서 다음 행을 가져온다, DEFAULT는 1이다.

 

위의 SQL문을 실행 했을때 ㄱ, ㄴ 에 들어갈 것은 무엇인지 보자.

먼저 LAG(매장코드, 2) OVER( ORDER BY 매장코드 DESC ) A 의 결과는 다음과 같다

매장코드의 내림차순 상태에서 각 행의 위로 2번 갔을때 매장코드가 A에 들어가는 거다. 따라서 ㄱ에는 E가 들어간다.

 

다음으로 LEAD(매장코드, 2) OVER( PARTITION BY 지역명 ORDER BY 매장코드) B의 결과는 다음과 같다.

 

지역명에 따라서 나누었고 매장코드의 오름차순으로 정렬했다. 각 행에서 밑으로 두칸 갔을때 매장코드가 B에 들어간다. 따라서 ㄴ에는 E가 들어간다

 

 

WINDOWING 

- 함수의 연산 대상이 되는 레코드의 범위를 정함

- 행을 선택할때는 ROWS, 값의 범위를 선택할때는 RANGE 사용

- ROWS : 행의 범위를 지정 

    UNBOUNDED PRECEDING : 첫번째 행

    EX) ROWS UNBOUNDED PRECEDING - 윈도우 함수의 연산을 맨 위엥서 현재행(CURRENT ROW)까지

   

    PRECDEING : CURRENT ROW의 이전 행

    EX) ROWS 3 PRECEDING - 윈도우 함수의 연산은 세 칸 위 행부터 현재행까지 포함해서 계산   

 

    CURRENT ROW : 현재 행

   

    FOLLOWING : CURRENT ROW 의 다음 행

    EX) ROWS 2 FOLLOWING - 윈도우 함수의 연산을 현재행(CURRENT ROW)부터 두 칸 아래 행까지 포함해서 계산

    

    UNBOUNDED FOLLLOWING : 마지막 행

    EX) 윈도우 함수의 연산을 현재행에서 마지막 행까지 포함해서 계산

 

- RNAGE : 값의 범위를 지정해줄 대 사용

    RANGE 숫자 PRECEDING : 현재 행의 값을 기준으로 작은 값 중에서 숫자이하로 차이가 나는 행들을 선택해서 계산한      다.

    EX) RANGE 150 PRECEDING >>> 현재행의 값보다 작고 150이하로 차이나는 값들을 대상으로 계산

   

    RANGE 숫자 FOLLOWING : 현재 행의 값을 기준으로 큰 값 중에서 숫자이하로 차이가 나는 행들을 선택해서 계산한      다.

    EX) RANGE 150 FOLLOWING >>> 현재행의 값보다 크고 150이하로 차이나는 값들을 대상으로 계산

 

    RANGE BETWEEN A AND B : A부터 B까지 윈도우 적용

    EX) RANGE BETWEEN 100 AND 200 >>> 100~200인 값들을 대상으로 계산

           RANGE BETWEEN 150 PRECEDING AND 120 FOLLOWING >>> 현재행의 값보다 작은 행중에 150이하로 차이            가 나고 현재행의 값보다 큰 행중에서 150이하로 차이가 나는 행들을 선택

 

 

테이블 파티션

- 대용량의 테이블을 여러개의 데이터 파일에 분리하여 저장하는 것

- 물리적으로 분리된 데이터 파일에 저장되어 입력/수정/삭제/조회 성능이 향상되고, 독립적 관리가 가능

- 조회의 범위를 줄이는 효과가 있어 성능이 향상됨

- RANGE PARTITION : 값의 범위를 기준으로 파티션을 나눠 저장하는 방법

    EX) 성적이 80점 이상인 레코드와 미만인 레코드를 별도로 저장

- LIST PARTITION : 특정 값을 기준으로 분할

    EX) 특정 값이 100일때와 200일때와 같이 특점 값에 따라 다른 데이터파일에 저장

- HASH PARTITION : 데이터베이스 관리 시스템이 자체적으로 해시함수를 사용해 분할하고 관리하는 방식

 

 

출저) [SQLD 자격증] 8만명이 유튜브에서 검증한 메타코드M의 대표강의 | Udemy