1. 그룹함수
ROLLUP
ROLLUP은 GROUP BY의 컬럼에 대해서 subtotal을 만들어준다.
예시1)
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO);
Oracle에서 제공하는 DECODE구문을 사용해서 DEPNO가 NULL일때는 '전체합계', 아니면 DEPNO를 표시하도록 해주었다. MSSQL에서는 'IIF(조건, 참일때 값, 거짓일때 값)' 함수를 사용하면 된다.
예시2)
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
각 부서, 직업별 합계가 SUM_SAL 컬럼의 값으로 표시되었다. 각 부서별 합계는 JOB이 NULL인 행들에 표시되고, 전체 합계는 DEPTNO가 NULL인 행에 표시된다.
집계함수를 적용한 SUM_SAL 외에 다른 컬럼을 2개 이상 조회할때만 ROLLUP이 의미가 있다. 만약 DEPTNO, SUM_SAL로만 조회한다면 ROLLUP을 사용하더라도 DEPNO별로 SUM_SAL이 조회될 뿐이다.
MSSQL에서는 'GROUP BY {컬럼명, ...} WITH ROLLUP' 과 같이 사용한다.
GROUPING, GROUPING SETS
GROUPING
GROUPING 함수는 각종 소계, 합계 등을 구분하기 위해서 사용한다. 계산이 되면 1, 그렇지 않으면 0을 반환한다. 다시 말해 ROLLUP을 하는데, 소계나 합계 부분이 있는 행을 구분하기 위해서 1로 표시하는 것이다. ROLLUP을 사용하므로 GROUPING을 제외하고 컬럼이 2개 이상 조회해야 의미가 있다.
SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
이렇게 구분할 수 있는 값을 만들어 놓으면, 다음과 같이 합계 값을 구분하여 표기할 수 있다.
SELECT DEPTNO, DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT,
JOB, DECODE(GROUPING(JOB), 1, '부서합계') T_DEPT, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
GROUPING SETS
GROUPING SETS는 GROUP BY 문에 작성하는 컬럼의 순서와 관계없이 모든 값들을 구분하여 그룹화한 합계를 계산한다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);
CUBE
CUBE는 CUBE의 인자로 들어간 컬럼에 대해 모든 가능한 경우의 수로 부분합계를 구한다. 부하가 많이 걸릴 수 있으므로 유의.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);
직업별 합계, 부서별 합계, 직업-부서별 합계가 모두 조회되었다.
2. WINDOW 함수
윈도우 함수는 행과 행간의 관계를 정의하기 위해서 제공되는 함수이다. 윈도우 함수를 이용해서 순위, 합계, 평균, 행 위치 등을 변경할 수 있다. 쿼리의 결과 표에서 마치 창을 내듯이, 특정 부분만 보고 그 행들의 관계를 통해 추가 결과를 내겠다는 의미이다.
WINDOW 함수 기본 문법
SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (PARTITION BY 컬럼
ORDER BY WINDOWING절)
FROM 테이블;
문법 구조
- WINDOW_FUNCTION : 순위, 비율 등 WINDOW 함수 명칭을 쓴다.
- ARGUMENTS : 각 윈도우 함수의 인자들이다.
- PARTITION BY : 전체 집합을 기준을 갖고 소그룹으로 나눈다. 1~5행, 6~10행 등으로 수직으로 나눈다는 것이다.
- ORDER BY : 정렬기준
- WINDOWING : 행을 그룹화하는 기준을 의미한다.
WINDOWING 상세
- ROWS : 물리적 행의 집합을 정의한다.
- RANGE : 논리적 행의 집합을 정의한다.
- BETWEEN~AND : 윈도우의 시작과 끝의 위치를 정한다.
- UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫 번째 행임을 의미한다.
- UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 행임을 의미한다.
- CURRENT ROW : 윈도우의 시작 위치가 현재 행임을 의미한다.
예제1) 누적합계
SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER(ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) TOTSAL
FROM EMP;
SUM(SAL)의 값을 갖는 컬럼을 조회하는데, ROWS(물리적으로), UNBOUNDED PRECEDING(맨 처음행부터), CURRENT ROW(지금 행까지), BETWEEN A AND B(A와 B 사이) 조건으로 구한다. 따라서 각 행은 맨 처음 행부터 현재 행까지의 합계를 구하는 것이 되어 누적합계를 구한다.
순위 함수 RANK
SELECT ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM
FROM EMP;
RANK(), DENSE_RANK(), ROW_NUMBER() 세 순위 함수가 있다.
RANK() : 동일 순위를 같은 순위로 처리하고, 각 건으로 계산한다.
DENSE_RANK() : 동일 순위를 같은 순위로 처리하고, 1건으로 계산한다.
ROW_NUMBER() : 고유한 순위번호를 부여한다.
집계함수(AGGREGATE)
자주 쓰는 SUM, AVG, COUNT, MAX/MIN 도 윈도우 함수에 속한다!
행 순서 관련 함수
- FIRST_VALUE : 파티션에서 가장 처음 나오는 값을 구한다. MIN으로 대체할 수 있다.
- LAST_VALUE : 파티션에서 가장 나중에 나오는 값을 구한다. MAX로 대체할 수 있다.
- LAG : 이전 행을 가져온다.
- LEAD : 윈도우에서 원하는 위치의 행을 가져온다. 기본값은 1이다.
LAG 예제
SELECT DEPTNO, ENAME, SAL,
LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;
윈도우 함수로 PRE_SAL을 SAL 값으로 구하되, LAG(SAL)을 적용하여 이전 행의 값을 불러오는 것이다.
LEAD 예제
LAG와 반대로 다음의 행을 가져오는데, offset 값을 지정할 수 있는 것이라고 생각하면 된다. LEAD 함수의 두 번째 인자는 음수일 수 없다. 이전 행은 LAG로 가져와야 한다.
SELECT DEPTNO, ENAME, SAL,
LEAD(SAL, 2) OVER (ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;
비율 관련 함수
- CUME_DIST : 파티션 내에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다. 0~1값으로 구해진다.
- PERCENT_RANK : 파티션의 맨 처음 값을 0, 맨 나중 값을 1로 하여 백분율을 구한다.
- NTILE : 파티션별로 전체 건수를 인자 값으로 N 등분한 결과를 조회한다.
- RATIO_TO_REPORT : 파티션 내 전체 SUM에 대한 행 별 컬럼 값의 백분율을 소수점까지 조회한다.
SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC) AS PERCENT_SAL
FROM EMP;
SAL DESC로 했으니 SAL이 큰 값부터 0으로 나온다. 소수점 첫 번째 자리에서 구분 안되는 값은 동등 순위로 표시되는 것 같다.
SELECT DEPTNO, ENAME, SAL,
NTILE(4) OVER(ORDER BY SAL DESC) AS N_TILE
FROM EMP;
NTILE의 인자값인 4를 기준으로 동등분할 되었다.
참조
0. [도서] 영진닷컴 - SQL 개발자 이론서 + 기출문제