SQLD [윈도우 함수, TOP N 쿼리]

김예은·2024년 9월 9일
0

데이터베이스

목록 보기
7/10

윈도우 함수의 개요

기존 관계형 데이터베이스는 컬럼과 컬럼간의 연산, 비교, 집계 등은 쉬운 반면, 행과 행간의 관계를 정의하거나 비교, 연산하는 것은 하나의 SQL 문으로 처리하기 어렵다.

절차형 프로그램을 작성하거나, INLINE VIEW 를 이용하여 복잡한 SQL 문을 작성해야 했던 것을 부분적으로라도 행과 행간의 관계를 쉽게 정의하기 위해 사용하는 함수가윈도우 함수(WINDOW FUNCTION)이다.

윈도우 함수 문법(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➡️ 논리적 인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.

예시

SELECT 상품분류코드
       , AVG(상품가격) AS 상품가격
       , COUNT(*) OVER(ORDER BY AVG(상품가격)
                       RANGE BETWEEN 10000 PRECEDING
                       AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드;

유사개수 칼럼은 상품분류코드별 평균상품가격을 서로 비교하여 ~10000~+10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다.

윈도우 함수의 종류

순위함수

RANK 함수

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

  • ORDER BY 를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.
  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구한다.
  • 동일한 값에 대해서는 동일한 순위를 부여한다.
    • 여러 개의 행이 같은 순위인 경우, 다음 랭크는 이어진 숫자(+1)가 아니다.1등인 행이 2개 있다면 다음 행은 3등이 되는 것이다.

DENSE_RANK 함수

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

  • RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급한다.
  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구한다.
SELECT JOB, ENAME, SAL,

       RANK() OVER ( ORDER BY SAL DESC ) RANK,
       DENSE_RANK() OVER ( ORDER BY SAL DESC ) DENSE_RANK

FROM EMP ;


ROW_NUMBER 함수

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

  • 각 파티션이 연속적인 정수를 할당받기 때문에, 파티션이 바뀔 때마다 번호가 초기화되고 파티션 안에서는 번호가 중복되지 않는다.
  • 페이징(pagination)에서 쓰인다.
SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) RANK,
       ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

그룹 내 행 순서함수

FIRST_VALUE 함수

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

LAST_VALUE 함수

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

NTILE

  • 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위함 필수
  • 그룹 번호가 리턴됨
  • ORDER BY 필수
  • PARTITION BY를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리 가능

LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

즉 현재 읽혀진 데이터의 이전 값을 알아내는 함수

  • LAG(인수1,인수2,인수3 )
  • 인수1 : 입력 칼럼
  • 인수2 : 몇 번째 앞의 행 을 가져올지 결정(DEFAULT 1)
  • 인수3 : 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력 (✅ NVL/ISNULL함수 와 동일)

LEAD 함수

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

그룹 내 비율 함수

  • 칼럼 값에 대한 백분률 ➡️ RATIO_TO_REPORT
  • 행의 순서에 대한 (0~1사이 값) 백분률 ➡️ PERCENT_RANK
  • 1/(파티션) 전체 건수로 표현하는 백분률 ➡️ CUME_DIST

그룹 함수의 형태

  • SUM, COUNT, AVG, MIN, MAX 등
  • OVER 절을 사용하여 윈도우 함수로 사용 가능
  • 반드시 연산할 대상을 그룹함수의 입력값으로 전달

SUM OVER()

윈도우함수의 연산범위 : 집계 연산 시 행의 범위 설정 가능

  • ROWS, RANGE 차이

1) ROWS : 값이 같더라도 각 행씩 연산

2) RANGE : 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산

  • BETWEEN A AND B

A 시작점 정의

  • CURRENT ROW : 현재행부터
  • UNBOUNDED PRECEDING : 처음부터
  • N PRECEDING : N 이전부터

B 마지막 시점 정의

  • CURRENT ROW : 현재행부터
  • UNBOUNDED FOLLWOING : 마지막까지
  • N FOLLWOING : N 이후부터

TOP N 쿼리

페이징 처리를 효과적으로 수행하기 위해 사용

전체 결과에서 특정 N개 추출 ex) 빌보드 차트 3

  • 같은 순위의 것을 모두 가져오고 싶다면 WITH TIES 를 사용하고, ORDER BY로 정렬해야함

TOP N 추출 방법

  1. ROWNUM
  2. RANK
  3. FETCH
  • 윈도우 함수를 사용하여 상위 N개에 대한 값을 추출할 수 있으나 단일 Query로 표현 불가하다.

ROWNUM

출력된 데이터 기준으로 행 번호 부여

절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음(=연산 불가)

→ ROWNUM 할당 전에 먼저 순서대로 데이터를 정렬한 뒤 ROWNUM을 부여 후 추출하는 것이 좋다.

첫 번째 행이 증가한 이후 할당되므로 > 연산 사용불가

ROWNUM 잘못된 사용

SELECT *
FROM EMP
WHERE ROWNUM > 1; 
// 크다 조건 전달 불가

SELECT *
FROM EMP
WHERE ROWNUM = 4;
// 항상 불변하는 절대적 번호가 아니므로 '=' 연산자 단독전달불가

예제) EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE ROWNUM <= 5;
ORDER BY SAL DESC;

추출원리 : WHERE절에 의해 먼저 5개를 추출 뒤 이 결과가 집합에 대해 정렬 수행

** 해결 : 먼저 서브쿼리를 사용하여 SAL에 대해 내림차순 정렬을 해놓고 상위 5개 추출

SELECT *
	FROM(SELECT *
				FROM EMP
				ORDER BY SAL DESC)
WHERE ROWNUM <= 5;
ORDER BY SAL DESC;

FETCH 절

  • 출력될 행의 수를 제한하는 절
  • 단일 쿼리로도 정렬 순서대로 상위 N개에 대한 값을 추출할 수 있다.
  • ORDER BY 절 뒤에 사용
ORDER BY 컬럼 OFFSET N {ROW | ROWS} -- OFFSET : 건너뛸 행의 수 
	FETCH {FIRST | NEXT} N {ROW | ROWS} ONLY
  • OFFSET : 건너뛸 행의 수
    ex) 성적 높은순 1 등 제외, 나머지 3 명
  • N : 출력할 행의 수
  • FETCH : 출력할 행의 수를 전달하는 구문
  • FIRST : OFFSET 을 쓰지 않았을 때 처음부터 N 행 출력 명령
  • NEXT : OFFSET 을 사용했을 경우 제외한 행 다음부터 N 행 출력 명령
  • ROW | ROWS : 행의 수에 따라 하나일 경우 단수, 여러값이면 복수형(특별히 구분하지 않아도 됨)

OFFSET 옵션

  • FIRST : OFFSET 을 쓰지 않았을 때는 처음부터 N 행 출력
  • NEXT : OFFSET 을 사용할 경우 제외한 행 다음부터 N 행 출력

ONLY / WITH TIES

ONLY - 내가 지정한 딱 row limits 갯수만 리턴
WITH TIES - 내가 지정한 row limits 에서 맨 마지막 행과 same sort key인 row들도 줄줄이 보여줌

출처 : 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

profile
김예은
소프트웨어공학 / 정보통신공학
이전 포스트

SQLD 그룹함수(ROLLUP,CUBE,GROUPING SETS)

다음 포스트

SQLD [계층형 질의와 셀프조인 / PIVOT과 UNPIVOT]

0개의 댓글

관련 채용 정보