[SQLD] SQL 기본 및 활용 Section 02. SQL 활용 (2) 그룹 함수부터

---

4. 그룹 함수 (Group Function)

 

---

01. ROLLUP

• GROUP BY의 칼럼에 대해 Subtotal을 만들어 줌
• ROLLUP함수는 뒤에 오는 칼럼의 순서에 따라 서브토탈을 다르게 만들 수 있다. 즉, 칼럼 순서가 중요하다
• ROLLUP(COL1, COL2)일 경우 COL1별 합계, COL1별 COL2 합계, 전체 합계가 조회되며, 반대의 경우에는 COL2별 합계, COL2별 COL1합계, 전체 합계가 조회됨

 

CREATE TABLE DEPT
(
    DEPTNO NUMBER(10) PRIMARY KEY,
    DEPTNAME VARCHAR2(20)
);

INSERT INTO DEPT VALUES (1000, '총무부');
INSERT INTO DEPT VALUES (1001, '기획부');
INSERT INTO DEPT VALUES (1002, '생산부');
INSERT INTO DEPT VALUES (1003, '기술부');

CREATE TABLE EMP
(
    EMPNO NUMBER(10) PRIMARY KEY,
    DEPTNO NUMBER(10) NOT NULL,
    ENAME VARCHAR2(30),
    JOB VARCHAR2(20),
    SALARY NUMBER(30),
    CONSTRAINT D_PK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);

INSERT INTO EMP VALUES (1, 1001, '김수철', 'STAFF', 3000);
INSERT INTO EMP VALUES (2, 1003, '고기환', 'MANAGER', 4500);
INSERT INTO EMP VALUES (3, 1001, '한상철', 'DIRECTOR', 6000);
INSERT INTO EMP VALUES (4, 1002, '이유민', 'MANAGER', 5000);
INSERT INTO EMP VALUES (5, 1002, '최지혜', 'STAFF', 4300);
INSERT INTO EMP VALUES (6, 1001, '박상현', 'STAFF', 3700);
INSERT INTO EMP VALUES (7, 1000, '구광한', 'STAFF', 4500);
INSERT INTO EMP VALUES (8, 1003, '서지우', 'STAFF', 4700);
INSERT INTO EMP VALUES (9, 1001, '유이현', 'MANAGER', 5200);

SELECT DEPT.DEPTNO, EMP.JOB, SUM(EMP.SALARY) AS "임금 합계" FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY ROLLUP(DEPT.DEPTNO, EMP.JOB)
ORDER BY DEPT.DEPTNO;

 

 

 

 

---

02. GROUPING

• ROLLUP, CUBE, GROUPING SETS 함수들에서 만들어지는 그룹을 구분하기 위해 만들어진 함수
• 합계값이 계산되는 경우 1, 아닌 경우 0을 리턴함

 

SELECT
    DECODE(GROUPING(DEPT.DEPTNO), 1, '합계', DEPT.DEPTNO),
    DECODE(GROUPING(EMP.JOB), 1, '합계', EMP.JOB),
    SUM(EMP.SALARY) AS "임금 합계"
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY ROLLUP(DEPT.DEPTNO, EMP.JOB)
ORDER BY DEPT.DEPTNO;

 

 

GROUPING함수와 DECODE를 조합해 사용하면 위의 SQL문을 더 직관적으로 짤 수 있음

 

 

 

---

03. GROUPING SETS

• GROUP BY에 나오는 칼럼의 순서에 상관없이 개별적으로 모두 GROUPING해 보여주는 함수

 

SELECT
    DECODE(GROUPING(DEPT.DEPTNO), 1, '합계', DEPT.DEPTNO),
    DECODE(GROUPING(EMP.JOB), 1, '부서합계', EMP.JOB),
    SUM(EMP.SALARY) AS "임금 합계"
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY GROUPING SETS(DEPT.DEPTNO, EMP.JOB)
ORDER BY DEPT.DEPTNO;

 

 

GROUPING SETS를 이용하면 DEPTNO와 JOB을 각각 그룹지은 결과를 얻을 수 있음

 

 

 

---

04. CUBE

• 칼럼에서 결합 가능한 모든 집계를 계산
• 다차원 집계를 제공해 다양하게 데이터를 분석할 수 있도록 함
• 예를 들어 부서와 직업을 CUBE로 사용하면 부서별 집계, 직업별 집계, 부서별 직업별 집계, 전체 집계가 조회됨

 

SELECT
    DECODE(GROUPING(DEPT.DEPTNO), 1, '합계', DEPT.DEPTNO),
    DECODE(GROUPING(EMP.JOB), 1, '부서합계', EMP.JOB),
    SUM(EMP.SALARY) AS "임금 합계"
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY CUBE(DEPT.DEPTNO, EMP.JOB)
ORDER BY DEPT.DEPTNO;

 

 

 

🔍CUBE와 GROUPING SETS의 관계

• GROUPING SETS로 CUBE함수를 똑같이 구현할 수 있다
• GROUPING SETS는 뒤에 나오는 칼럼을 각각 그룹핑해 집계를 해주므로 CUBE(A, B)일 때 GROUPING SETS(A, B, (A, B), ())라고 해주면 CUBE함수와 똑같은 결과를 얻을 수 있다

SELECT DEPTNO, JOB, SUM(SALARY) FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

SELECT DEPTNO, JOB, SUM(SALARY) FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB, (DEPTNO, JOB), ());

 

CUBE

 

GROUPING SETS

 

---

5. 윈도우 함수 (Window Function)

 

---

01. 윈도우 함수

• 행과 행 간의 관계를 정의하기 위해 제공되는 함수
• 특정 행 집합(윈도우)에 대해 계산을 수행할 수 있음
• 일반 집계 함수와 유사해 보일 수 있지만 일반 집계 함수는 전체 결과 집합에 대해 하나의 결과를 돌려주지만 윈도우 함수는 각 행에 대한 결과를 돌려줌
• OVER() 과 함께 사용되며 이 절 안에서 PARTITION BY, ORDER BY 혹은 추가적인 윈도우의 범위나 프레임을 정의할 수 있음

 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;

 

윈도우 함수 구조

구조	설명
ARGUMENTS(인수)	- 함수에 따라 0 ~ N개의 인수가 지정될 수 있음
PARTITION BY	- 전체 집합을 기준에 의해 소그룹으로 나눔
ORDER BY	- 어떤 항목에 대해 정렬
WINDOWING	- 행 기준의 범위를 정함 - ROWS는 물리적 결과의 행 수, RANGE는 논리적인 값에 의한 범위

 

 

WINDOWING

구조	설명
ROWS	- 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	- 논리적인 주소에 의해 행 집합을 지정
BETWEEN ~ AND	- 윈도우의 시작과 끝의 위치를 지정
UNBOUNDED PRECEDING	- 윈도우의 시작 위치가 첫번째 행임을 의미
UNBOUNDED FOLLOWING	- 윈도우의 마지막 위치가 마지막 행임을 의미
CURRENT ROW	- 윈도우 시작 위치가 현재 행임을 의미

 

CREATE TABLE TEST
(
    TESTNO NUMBER(10) PRIMARY KEY,
    AA NUMBER(5)
);

INSERT INTO TEST VALUES(1, 3);
INSERT INTO TEST VALUES(2, 6);
INSERT INTO TEST VALUES(3, 9);
INSERT INTO TEST VALUES(4, 12);
INSERT INTO TEST VALUES(5, 15);
INSERT INTO TEST VALUES(6, 18);

SELECT TESTNO, SUM(AA) OVER(ORDER BY TESTNO
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) FROM TEST;

 

 

TEST 테이블은 각각 3, 6, 9, 12, 15, 18의 행을 가지고 있다. 이 때 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 첫번째 행부터 현재 행까지 윈도우의 범위를 제약한다. 따라서 첫번째 행부터 현재 행까지의 AA의 합계를 보여준다

 

 

🔍 RANGE BETWEEN start_point AND end_point

• start_point는 end_point와 같거나 작은 값이 들어갈 수 있음
• Default값 => RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• UNBOUNDED PRECEDING - start_point에만 들어갈 수 있음
• UNBOUNDED FOLLOWING - end_point에만 들어갈 수 있음
• CURRENT ROW - start_point, end_point 둘 다 들어갈 수 있음

 

---

02. 순위 함수 (RANK Function)

• 윈도우 함수 중 특정 항목과 파티션에 대해 순위를 계산할 수 있는 함수

 

RANK

• 특정 항목 및 파티션에 대해 순위를 계산
• 동일한 순위는 동일한 값이 부여되고 동일한 값의 개수만큼 순위를 건너뜀

기준 테이블

 

SELECT ENAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMP;

 

 

SALARY를 기준으로 RANK 함수를 수행했을 때 SALARY가 높은 순으로 순위가 매겨진다. 이때 고기환과 구광한은 4500으로 같으며 순위 또한 5위로 같다. 그리고 다음 순위인 최지혜는 공동 5위로 인해 6위를 건너뛰고 7위가 된다

 

 

 

 

DENSE_RANK

• 동일한 순위를 하나의 건수로 계산

 

SELECT ENAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMP;

 

 

RANK 함수와 비슷하지만 공동 5위 이후에 6위를 건너뛰지 않고 최지혜는 6위가 된다

 

 

 

ROW_NUMBER

• 동일한 순위에 대해서 고유의 순위를 부여함

 

SELECT ENAME, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMP;

 

 

공동 5위에 상관없이 각 순위는 하나만 존재할 수 있도록 고유의 순위가 부여됐다.

 

 

 

---

03. 행 순서 관련 함수

• 첫번째 값이나 마지막 값 혹은 이전 행이나 지정된 위치의 행을 가져올 수 있음

행 순서	설명
FIRST_VALUE	- 파티션에서 가장 첫번째로 나오는 값을 구함 - MIN함수와 같은 효과
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구함 - MAX함수와 같은 효과
LAG	- 이전 행을 가지고 옴
LEAD	- 윈도우에서 특정 위치의 행을 가지고 옴 - 기본값은 1

 

SELECT
    ENAME,
    SALARY,
    DEPTNO,
    FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMP;

 

 

각 DEPTNO 그룹 즉, 같은 부서 그룹 중에 SALARY를 내림차순으로 정렬한 윈도우에서 가장 첫번째 있는 ENAME을 출력하도록 했다. 결과적으로 각 부서 그룹에서 급여가 가장 높은 사람이 각각 출력됐다.

 

 

 

SELECT ENAME, SALARY, DEPTNO, LAG(ENAME) OVER(ORDER BY SALARY DESC) AS PRE
FROM EMP;

 

각각 이전 행의 이름을 출력하도록 했다

 

 

 

---

04. 비율 관련 함수

• 누적 백분율, 순서별 백분율, 파티션을 n분할한  결과 등을 조회할 수 있음

 

비율 함수	설명
CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회 - 누적 분포상에 위치를 0~1사이의 값을 가짐
PERCENT_RANK	- 파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 해 값이 아닌 행의 순서별 백분율을 조회
NTILE	- 파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회
RATIO_TO_REPORT	- 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회