[Oracle] 그룹 함수 over, partition by

분석함수란?

SELECT
    분석함수 OVER([PARTITION BY 칼럼] [ORDER BY 칼럼] [WINDOWING 절])
FROM 테이블;

분석함수는 이미 사용해보셨을 겁니다. COUNT(), AVG(), SUM() 등과 같이 GROUP BY 절과 함께 사용했던 함수들도 있고, RANK(), ROW_NUMBER(), DENSE_RANK(), LEAD(), LAG(), NTITLE()  등 ORACLE 버전이 올라가면서 많이 생겨나고 있습니다. 이런 분석함수 뒤에 ANALYTIC CLAUSE( OVER 절)을 통해서 행 그룹의 정의를 지정하고 각 그룹당 결과값을 반복하여 출력하는 것 입니다. 여기서 행 그룹은 칼럼에 대한 행들이고 행 그룹의 범위(WINDOW)를 PARTITION BY, ORDER BY, WINDOWING으로 조절이 가능합니다.

 

위의 문제를 예시로 봤을때,

SUM(SAL)        --> 분석함수 SUM을 사용했고 SAL 칼럼에 대한 행들이 행 그룹입니다.
OVER               --> 분석절이라고 합니다. 분석함수에 대한 조절을 OVER절 안에서 합니다.
PARTITION BY --> GROUP BY와 동일하게 그룹지어 결과를 출력 합니다.

ORDER BY       --> PARTITION BY로 정의된 WINDOW 내에서 행들의 정렬순서를 정의해줍니다.

SELECT

    DEPTNO

    ,EMPNO

    ,ENAME

    ,JOB

    ,SAL

    ,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS SUM_SAL

FROM EMP;

위의 문제에서 ORDER BY절을 사용해서 각각 PARTITION 안에서 정렬작업을 할 수 있습니다.

 

 

OVER 절 실행 순서

OVER절에서는 ORDER BY절을 사용합니다. 그래서 가장 마지막에 실행됩니다.

 

★ OVER()함수란?

OVER함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있습니다.

 

★ 전통 SQL 사용

SELECT YYMM, PRICE FROM ( SELECT YYMM, SUM(TOT_PRICE) AS PRICE FROM TABLE1 GROUP BY YYMM ORDER BY YYMM DESC )

 

★ OVER 함수 이용

SELECT YYMM, SUM(TOT_PRICE) OVER(ORDER BY YYMM DESC) AS PRICE FROM TABLE1

 

 

★ COUNT(*)OVER() 사용

실제 데이터와 함께 해당 테이블의 전체 로우 컬럼을 쉽고 편리하게 추출할 수 있다.

 

SELECT MENU_ID, MENU_NAME, COUNT(*) AS TOTALCOUNT FROM MENU_MG

위의 쿼리를 실행하면 다음과 같은 오류 메시지가 나온다.
ORA-00937: not a single-group group function

 

다음 쿼리로 쉽게 전체 카운트를 추출할 수 있다.

SELECT MENU_ID, MENU_NAME, COUNT(*)OVER() AS TOTALCOUNT FROM MENU_MG

 

★ OVER() 함수

 

COUNT(*)OVER() : 전체행 카운트
COUNT(*)OVER(PARTITION BY 컬럼) : 그룹단위로 나누어 카운트

MAX(컬럼)OVER() : 전체행 중에 최고값
MAX(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최고값

MIN(컬럼)OVER() : 전체행 중에 최소값
MIN(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최소값

SUM(컬럼)OVER() : 전체행 합
SUM(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 합

AVG(컬럼)OVER() : 전체행 평균
AVG(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 평균

STDDEV(컬럼)OVER() : 전체행 표준편차
STDDEV(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 표준편차

RATIO_TO_REPORT(컬럼)OVER() : 현재행값/SUM(전체행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.
RATIO_TO_REPORT(컬럼)OVER(PARTITION BY 컬럼) : 현재행값 / SUM(그룹행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

======================================================================

 

COUNT(expr) OVER(analytic_clause)

- 같거나 작은 조건에 대한 갯수 반환

 

/* 부서번호가 50인 부서 지원에 대해 각 직원의 급여보다 같거나 적게 받는 사람에 대한 누적 합을 반환. */

SELECT employee_id, salary

, COUNT(*) over(ORDER BY salary) AS "Count"

FROM employees

WHERE department_id = '50';

 

SUM(expr) OVER(analytic_clause)

-- 같거나 작은 값들에 대한 누적

/* 특정 값을 누적하여 결과를 보여준다. */

SELECT employee_id, salary

, SUM(salary) over(ORDER BY employee_id)

FROM employees

WHERE department_id = '50';

 

/* 위 예제에 더해 부서별 누적 결과를 함께 보고자 한다. */

SELECT employee_id, department_id, salary

, SUM(salary) over(ORDER BY department_id, employee_id)

, SUM(salary) over(partition by department_id order by employee_id)

FROM employees;

 

 

RANK() OVER()

--순위

SELECT SAL_SNO, SAL_YYMM, SAL_TOTAL,

RANK() OVER(ORDER BY SAL_TOTAL) AS "CONT"

FROM TB_SALARY

WHERE SAL_YYMM = '201101';

 

DENSE_RANK 함수

- 값의 그룹에서 값의 순위를 계산합니다. RANK와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1만 증가하여 반환.

 

SELECT employee_id, department_id, salary

, DENSE_RANK() over(PARTITION BY department_id ORDER BY salary DESC)

FROM employees

WHERE department_id = '50'

 

 

★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)

SELECT T.MAIN_CODE , T.GOODS_SEQ , T.OFFER_MASTER_SEQ , MODEL_CD , (ROW_NUMBER() OVER(PARTITION BY T.MAIN_CODE ORDER BY T.MAIN_CODE, T.GOODS_SEQ, T.OFFER_MASTER_SEQ)) RANK -- , (ROW_NUMBER() OVER(PARTITION BY 중복조회컬럼 ORDER BY 정렬컬럼1, 정렬컬럼2, ...)) FROM TMP_TABLE T

 

결과

MAIN_CODE GOODS_SEQ OFFER_MASTER_SEQ MODEL_CD RANK

--------------------------------------------------------------

1 1 1 AAA 1

1 1 2 AAA 2

1 2 4 BBB 3

1 2 5 BBB 4

2 1 1 AAA 1

2 1 4 AAA 2

2 2 5 BBB 3

2 2 6 BBB 4

2 3 7 CCC 5

2 3 9 CCC 6

 

 

★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기.

TEAM_CD SCORE PALY_DATE

------------------------------

AAA 90 2010/08/01

AAA 50 2010/08/02

AAA 60 2010/08/03

AAA 50 2010/08/04

BBB 50 2010/08/01

BBB 90 2010/08/02

BBB 95 2010/08/03

BBB 100 2010/08/04

 

방법 1. RANK() OVER

SELECT T.TEAM_CD, T.SCORE, RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE FROM TMP_TABLE T

 

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 3 2010/08/02

AAA 60 5 2010/08/03

AAA 50 6 2010/08/02

AAA 50 6 2010/08/04

BBB 50 6 2010/08/01

 

방법 2. ROW_NUMBER() OVER

SELECT T.TEAM_CD, T.SCORE, ROW_NUMBER() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE FROM TMP_TABLE T

 

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 4 2010/08/02

AAA 60 5 2010/08/03

AAA 50 6 2010/08/02

AAA 50 7 2010/08/04

BBB 50 8 2010/08/01

 

방법 3. DENSE_RANK() OVER

SELECT T.TEAM_CD, T.SCORE, DENSE_RANK() OVER(ORDER BY SCORE DESC) RANK, T.PLAY_DATE FROM TMP_TABLE T

 

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 3 2010/08/02

AAA 60 4 2010/08/03

AAA 50 5 2010/08/02

AAA 50 5 2010/08/04

BBB 50 5 2010/08/01

 

 

 

★ 분석용 함수

RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용)

DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함)

ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공

CUME_DIST - 분산값

PERCENT_RANK - 백분율

NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시

FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환.

LAST_VALUE - 정렬된 값중에서 마지막 값을 반환.

 

★ OVER() 에 사용되는 OPTION

1. PARTITION BY
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.