[SQLD] Grouping 함수, Window 함수 정리 (1)

* 추후 SQLD, SQLP 응시 계획이 있긴 하다. 그거와 별도로 SQL을 좀 더 다루는 연습을 할겸 Grouping 함수, Window 함수를 정리.

Rank 함수

Rank : 순위를 추출, 동순위일 경우 인원수 만큼 넘기고 다음 순위로 넘어감 (2위가 2명이면 다음은 4위)
Dense_Rank : 순위를 추출, 동순위여도 인원수 만큼 넘기지 않고 바로 다음 순위 부여(2위가 99명이어도 다음은 3위)

SELECT 
    EMPLOYEE_ID, 
    SALARY,
    RANK() OVER (ORDER BY SALARY DESC),
    DENSE_RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES;

 

비율 함수

Percent_rank : 상위 몇 % 인지 추출, 파티션 내의 비율을 따질때 사용한다.

SELECT 
    EMPLOYEE_ID, 
    DEPARTMENT_ID, 
    SALARY,
    ROUND(PERCENT_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC),1) AS PERCENT_SAL
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC, PERCENT_SAL ASC;

NTILE : N-분위에 대해 출력, 정확하게는 N개의 그룹으로 나눠 어디에 속하는가를 따질 때 사용

SELECT 
    EMPLOYEE_ID, 
    DEPARTMENT_ID, 
    SALARY,
    NTILE(3) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS N_TILE
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC, N_TILE ASC;

 

그룹함수

Rollup : 부분합을 출력할 때 사용, 문제는 진짜 NULL과 부분합에 의해 발생한 NULL을 명백하기 구분하기 어려움
(실제로 employees data는 부서 데이터가 없는 null이 존재하여, roll up을 쓰면 유감)

SELECT DECODE(DEPARTMENT_ID, NULL, '전체합계', DEPARTMENT_ID), SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY ROLLUP(DEPARTMENT_ID);

Rollup은 2개 이상의 분류 기준에도 적용 가능은 함

// 부서별, 직업별 계산 가능
SELECT
    DEPARTMENT_ID, 
    DECODE(JOB_ID, NULL, '부분합계', JOB_ID) AS JOB_ID,
    SUM(SALARY)
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ASC;

 

Grouping : rollup에서, 부분합 줄인지 null인지 구분하기 위해 GROUPING으로 부분합 라인 명시
실제로는 0이면 부분합 줄이 아니고 1이면 부분합 줄로 처리된다. 이를 텍스트로 구분하기 위해 decode 사용.

SELECT
    DEPARTMENT_ID, 
    DECODE(GROUPING(DEPARTMENT_ID), 1, '전체합계') AS TOT,
    JOB_ID,
    DECODE(GROUPING(JOB_ID), 1, '부서합계') AS TOT_DEPT,
    SUM(SALARY)
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ASC;

grouping sets :  항목별 부분합만 표시

SELECT
    DEPARTMENT_ID,
    JOB_ID,
    ROUND(AVG(SALARY), 0)
FROM
    EMPLOYEES
GROUP BY GROUPING SETS(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ASC, JOB_ID ASC;

SELECT * FROM EMPLOYEES;