[SQL]PARTITION/WINDOW

2023. 9. 17. 10:33· SQL/DML - SELECT
목차
  1. 1. 분석 함수(Analytic Function)
  2. 2. PARTITION
  3. 2-1. PARTITION BY
  4. 2-2. LAG / LEAD
  5. 3. Window

작성일자 : 2023-07-12
수정일자 : 2023-07-23

Ver : 0.1.3

1. 분석 함수(Analytic Function)

  • 개별 행을 유지한 채 집계 값을 계산할 수 있는 함수이다.
    • 집계 함수 (Aggregate Functions)는 행 그룹 별로 값을 집계하고, 각 행 그룹을 단일 행 (1 row)으로 그룹화하여 리턴한다. 하지만 분석함수는 개별 행을 유지한 채로 값을 집계하기 때문에 원본 값과 집계 값을 함께 분석할 수 있다.
    • 분석 함수 실행시 대상이 되는 행의 범위를 윈도우(window)라고 하며, analytic_clause에 의해 각 행(current row) 별로 윈도우가 정의 된다.
  • 모든 join, where절, group by 절, having 절의 수행은 분석 함수 실행 전에 완료 된다. 따라서 분석함수는 select 절, order by 절 내에서만 사용할 수 있다.

2. PARTITION

2-1. PARTITION BY

  • PARTITION BY 절을 사용하여 GROUPO BY 절과 유사하게 논리적인 행의 그룹을 생성할 수 있으며, 각 행의 윈도우(실행 대상 행의 범위)는 파티션을 벗어날 수 없다.
SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno) AS SUM_SAL
  FROM emp a;
EMPNO ENAME HIREDATE DEPTNO SAL SUM_SAL
7782 CLARK 1981-06-09 00:00:00.000 10 2450 8750
7839 KING 1981-11-17 00:00:00.000 10 5000 8750
7934 MILLER 1982-01-23 00:00:00.000 10 1300 8750
7566 JONES 1981-04-02 00:00:00.000 20 2975 10875
7902 FORD 1981-12-03 00:00:00.000 20 3000 10875
7876 ADAMS 1987-05-23 00:00:00.000 20 1100 10875
7369 SMITH 1980-12-17 00:00:00.000 20 800 10875
7788 SCOTT 1987-04-19 00:00:00.000 20 3000 10875
7521 WARD 1981-02-22 00:00:00.000 30 1250 9400
7844 TURNER 1981-09-08 00:00:00.000 30 1500 9400
7499 ALLEN 1981-02-20 00:00:00.000 30 1600 9400
7900 JAMES 1981-12-03 00:00:00.000 30 950 9400
7698 BLAKE 1981-05-01 00:00:00.000 30 2850 9400
7654 MARTIN 1981-09-28 00:00:00.000 30 1250 9400
  • 분석 함수 활용 (다양한 집계 기준)
    • PARTITION BY 절만 변경하면 여러 집계 기준의 집계 값을 한번에 추출할 수 있다.
SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno) AS DEPTNO_SUM_SAL
      ,SUM(a.sal) OVER (PARTITION BY a.job) AS JOB_SUM_SAL
  FROM emp a;
EMPNO ENAME HIREDATE DEPTNO SAL DEPTNO_SUM_SAL JOB_SUM_SAL
7782 CLARK 1981-06-09 00:00:00.000 10 2450 8750 4150
7839 KING 1981-11-17 00:00:00.000 10 5000 8750 5000
7934 MILLER 1982-01-23 00:00:00.000 10 1300 8750 8275
7566 JONES 1981-04-02 00:00:00.000 20 2975 10875 4150
7902 FORD 1981-12-03 00:00:00.000 20 3000 10875 6000
7876 ADAMS 1987-05-23 00:00:00.000 20 1100 10875 4150
7369 SMITH 1980-12-17 00:00:00.000 20 800 10875 6000
7788 SCOTT 1987-04-19 00:00:00.000 20 3000 10875 8275
7521 WARD 1981-02-22 00:00:00.000 30 1250 9400 5600
7844 TURNER 1981-09-08 00:00:00.000 30 1500 9400 5600
7499 ALLEN 1981-02-20 00:00:00.000 30 1600 9400 5600
7900 JAMES 1981-12-03 00:00:00.000 30 950 9400 5600
7698 BLAKE 1981-05-01 00:00:00.000 30 2850 9400 4150
7654 MARTIN 1981-09-28 00:00:00.000 30 1250 9400 8275
  • 분석 함수 활용 (누적 집계)
    • 파티션 내에서 ORDER BY 절에 기술한 순서대로 현재 행까지의 값을 누적 집계 할수 있다.
      • PARTITION BY 절이 생략되면 전체 행을 대상으로 누적 집계 값을 계산한다. (애매한 부분이 있음 - 동일 값 집계 X)
SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal) AS SUM_SAL
  FROM emp a;

2-2. LAG / LEAD

오라클에서 이전 행의 값을 찾거나 다음 행의 값을 찾기 위해서는 LAG, LEAD 함수를 사용하면 된다.

LAG(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)
  - LEAD(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)
  • LAG 함수 : 이전 행의 값을 리턴
  • LEAD 함수 : 다음 행의 값을 리턴
  • expr : 대상 컬럼명
  • offset : 값을 가져올 행의 위치 기본값은 1, 생략가능
  • default : 값이 없을 경우 기본값, 생략가능
  • partition_by_clause : 그룹 컬럼명, 생략가능
  • order_by_clause : 정렬 컬럼명, 필수

기본 사용법

SELECT empno
     , ename
     , job
     , sal
     , LAG(empno) OVER(ORDER BY empno)  AS empno_prev
     , LEAD(empno) OVER(ORDER BY empno) AS empno_next
  FROM emp 
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
EMPNO ENAME JOB SAL EMPNO_PREV EMPNO_NEXT
7499 ALLEN SALESMAN 1600   7521
7521 WARD SALESMAN 1250 7499 7566
7566 JONES MANAGER 2975 7521 7654
7654 MARTIN SALESMAN 1250 7566 7698
7698 BLAKE MANAGER 2850 7654 7782
7782 CLARK MANAGER 2450 7698 7788
7788 SCOTT ANALYST 3000 7782 7844
7844 TURNER SALESMAN 1500 7788 7902
7902 FORD ANALYST 3000 7844  

현재 행 기준으로 두번째 이전 값을 표시

SELECT empno
     , ename
     , job
     , sal
     , LAG(empno, 2) OVER(ORDER BY empno) AS empno_prev
  FROM emp 
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
EMPNO ENAME JOB SAL EMPNO_PREV
7499 ALLEN SALESMAN 1600  
7521 WARD SALESMAN 1250  
7566 JONES MANAGER 2975 7499
7654 MARTIN SALESMAN 1250 7521
7698 BLAKE MANAGER 2850 7666
7782 CLARK MANAGER 2450 7654
7788 SCOTT ANALYST 3000 7698
7844 TURNER SALESMAN 1500 7782
7902 FORD ANALYST 3000 7788

가져올 행이 없는 경우 기본값(9999)를 가져온다.

SELECT empno
     , ename
     , job
     , sal
     , LAG(empno, 2, 9999) OVER(ORDER BY empno) AS empno_prev
  FROM emp 
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
EMPNO ENAME JOB SAL EMPNO_PREV
7499 ALLEN SALESMAN 1600 9999
7521 WARD SALESMAN 1250 9999
7566 JONES MANAGER 2975 7499
7654 MARTIN SALESMAN 1250 7521
7698 BLAKE MANAGER 2850 7666
7782 CLARK MANAGER 2450 7654
7788 SCOTT ANALYST 3000 7698
7844 TURNER SALESMAN 1500 7782
7902 FORD ANALYST 3000 7788

3. Window

  • 각 행마다 분석함수의 실행 대상이 되는 행의 범위(윈도우)를 세밀하게 지정한다.
  • 물리적인 행의 범위를 지정하거나 논리적인 값의 범위를 지정할 수 있다.
    • analytic_clause 내 order by 절의 컬럼 또는 표현식이 윈도우의 기준이 된다.
  • 각 행의 윈도우(실행 대상 행의 범위)는 파티션을 벗어날 수 없다.
  • Syntax : ROWS/RANGE BETWEEN start_point and end_point
    • Start_point & end_point : ROWS (시작행 / 종료행) / RANGE (값의 범위)
    • UNBOUNDED PRECEDING : 파티션의 첫 번째 행 / 파티션의 첫 번째 행의 값
    • N PRECEDING : (파티션 내에서) 현재 행을 기준으로 이전 N번째 행 / 현재 행의 값 - N
    • CURRENT ROW : 현재 행 / 현재 행의 값
    • N FOLLOWING : (파티션 내에서) 현재 행을 기준으로 이후 N번째 행 / 현재 행의 값 + N
    • UNBOUNDED FOLLOWING : 파티션의 마지막 행 / 파티션의 마지막 행의 값

UNBOUNDED PRECEDING : 파티션의 첫 번째 행 / CURRENT ROW : 현재 행 -- 누적 집계

SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal --정렬 기준
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW) AS SUM_SAL
  FROM emp a;

N PRECEDING : (파티션 내에서) 현재 행을 기준으로 이전 N번째 행 / N FOLLOWING : (파티션 내에서) 현재 행을 기준으로 이후 N번째 행

SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal --정렬 기준
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS SUM_SAL
  FROM emp a;

RANGE

SELECT a.empno
      ,a.ename
      ,a.hiredate
      ,a.deptno
      ,a.sal
      ,SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal --정렬 기준
                        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW) AS SUM_SAL
  FROM emp a;

ROWS / RANGE BETWEEN UNBOUNDED PRECEDING = ROWS / RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW (명시해주는 것이 좋음)

저작자표시 (새창열림)
  1. 1. 분석 함수(Analytic Function)
  2. 2. PARTITION
  3. 2-1. PARTITION BY
  4. 2-2. LAG / LEAD
  5. 3. Window
'SQL/DML - SELECT' 카테고리의 다른 글
  • [SQL]CASE
  • [SQL]PIVOT/UNPIVOT
  • [SQL]GROUP BY
  • [SQL]ROWNUM
Unlimited Jun
Unlimited Jun
This space is my database for experiencing, thinking, studying, reading and working results
Unlimited Jun
JUST DO IT
Unlimited Jun
전체
오늘
어제

◀ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ   May   ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ▶
일 월 화 수 목 금 토
1 1 1
1 1 1 1 1 1 1
1 1 1 1 1 1 1
1 1 1 1 1 1 1
1 1 1 1 1 1 1
  • 분류 전체보기 (324)
    • JayJay (32)
      • Thoughts (20)
      • Experiences (9)
    • Books (20)
    • Project (6)
    • Side Project (18)
      • Football Analysis (15)
      • Game Analysis (1)
      • TQRDC (2)
    • Tableau (28)
      • Dashboards (8)
      • Desktop (8)
      • Cloud&Server (5)
    • SQL (53)
      • DDL (6)
      • DML - SELECT (10)
      • DML (4)
      • DCL (1)
      • Optimization (7)
      • Oracle (18)
      • MySQL (2)
      • Coding Test (0)
    • Python (68)
      • numpy (0)
      • pandas (16)
      • plotly (6)
      • matplotlib (19)
      • seaborn (4)
      • altair (6)
      • streamlit (1)
      • scipy (5)
    • R (9)
    • Salesforce (7)
      • SOQL (4)
    • Excel (4)
    • HTML (3)
    • Markdown (1)
    • IT 일반 (13)
    • Tool (33)
      • Data Analysis Tool (26)
    • Data Analysis (28)
      • Data & Statistics (6)
      • 빅 데이터 분석기사 (22)
    • Etc (1)

태그

  • 축구
  • pandas
  • 파이썬
  • Tableau
  • Python
  • 데이터분석
  • 시각화
  • SQL
  • 데이터시각화
  • oracle

인기 글

최근 댓글

블로그 메뉴

  • 홈
  • 태그
  • 방명록
  • 글쓰기
  • LinkedIn
  • Tableau Public
  • 블로그 관리
hELLO · Designed By 정상우.v4.2.1
Unlimited Jun
[SQL]PARTITION/WINDOW
상단으로

티스토리툴바

단축키

내 블로그

내 블로그 - 관리자 홈 전환
Q
Q
새 글 쓰기
W
W

블로그 게시글

글 수정 (권한 있는 경우)
E
E
댓글 영역으로 이동
C
C

모든 영역

이 페이지의 URL 복사
S
S
맨 위로 이동
T
T
티스토리 홈 이동
H
H
단축키 안내
Shift + /
⇧ + /

* 단축키는 한글/영문 대소문자로 이용 가능하며, 티스토리 기본 도메인에서만 동작합니다.