SQL_집계함수, 윈도우함수, 그룹함수

• 표준 SQL에는 집계(AGGREGATE)함수, 두 번째는 그룹(GROUP)함수, 세 번째는 윈도우(WINDOW)함수가 있다.

1. 집계함수(AGGREGATE FUNCTION)

• GROUP 함수의 한 부분으로 분류할 수 있음
• COUNT, SUM, AVG, MAX, MIN 등

2. 그룹함수(GRUOP FUNCTION)

• 데이터에 대한 결산 개념의 연산
• 소계, 총계 등

ROLLUP

• 소계를 구하는 함수
• 그룹화된 컬럼의 수가 N개면 소계는 N+1개(+1은 전체합계)
• ROLLUP은 계층구조이기 때문에 함수 내의 인자 순서가 바뀌면 결과도 바뀜

기본구문

SELECT 
	DNAME, 
	JOB, 
	COUNT(*) "Total Empl", 
	SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);

GROUPING 함수

• 합계를 표현하는 행은 1 아니면 0

SELECT DNAME,
       GROUPING(DNAME),
       JOB,
       GROUPING(JOB),
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB;

ROLLUP과 CASE

• 소계나 합계를 text값으로 표현할 수 있음

SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB);

CUBE

• 결합 가능한 모든 값에 대해 다차원 집계를 구함, 모든 데이터의 조합
• CUBE 함수의 인자가 N개라면 2^N만큼의 소계 생성
• 순서가 바뀌어도 데이터 결과는 동일함
• ROLLUP에 비해 시스템 연산 대상이 많음

SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "TotalEmpl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);

GROUPING SETS

• 특정 항목에 대한 소계를 구함
• GROUP BY를 여러번 반복하지 않아도 됨
• 순서가 바뀌어도 결과는 동일함

SELECT 
    DNAME,  
    JOB, 
    MGR, 
    SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));

3. 윈도우 함수(WINDOW FUNCTION)

윈도우 함수

• 행과 행간의 관계를 정의하기 위해 제공되는 함수
• 분석함수, 순위함수로도 알려져있음

WINDOW FUNCTION 기본구조

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

• ARGUMENT : 함수에 따라 0~N개의 인수가 저장될 수 있음
• PARTITON BY : 전체 집함을 기준에 의해 소그룹으로 나눌 수 있음
• ORDER BY : 어떤 항목에 대해 순위를 지정할지 기술함
• WINDOWING : 함수 대상의 범위지정(SQL 서버 지원안함)

윈도우 함수 종류

• RANK
  특정 항목 및 파티션에 대해서 순위를 계산
  동일한 순위는 동일한 값이 부여됨

SELECT JOB,
       ENAME,
       SAL,
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

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

SELECT 
    JOB,
    ENAME,
    SAL,
    RANK() OVER (ORDER BY SAL DESC) RANK,
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

• ROW_NUMBER : 동일한 순위 내에서 고유의 순위 부여

SELECT 
    JOB,
    ENAME,
    SAL,
    RANK() OVER (ORDER BY SAL DESC)       RANK,
    ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

일반 집계함수

• SUM : 파티션 별로 합계를 계산합니다.
• AVG : 파티션 별로 평균을 계산합니다.
• COUNT : 파티션 별로 행 수를 계산합니다.
• MAX와 MIN : 파티션 별로 최댓값과 최솟값을 계산합니다.

SELECT 
  MGR, 
	ENAME, 
	SAL, 
	SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

순서함수

• FIRST_VALUE
  파티션에서 가장 처음 나오는 값을 구할 수 있음
  MIN 함수를 사용해서 같은 결과를 구할 수 있음
  공동 등수를 인정하지 않고 처음 나온 행만 처리함

SELECT 
    DEPTNO,
    ENAME,
    SAL,
    FIRST_VALUE(ENAME) OVER
        (PARTITION BY DEPTNO 
				 ORDER BY SAL DESC 
				 ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP;

참고 - UNBOUNDED PRECEDING
PARTITION의 첫 번째 로우에서 윈도우가 시작합니다.

• LAST_VALUE
  파티션별 윈도우에서 가장 나중에 나온 값
  MAX 함수를 이용해 같은 결과 구할 수 있음 (SQL 서버 지원 안함)

SELECT DEPTNO,
       ENAME,
       SAL,
       LAST_VALUE(ENAME) OVER
           (PARTITION BY DEPTNO ORDER BY SAL DESC
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DEPT_POOR
FROM EMP;

• LAG 함수
  현재의 값을 기준으로 이전 값들 중 원하는 위치의 값을 가져옴

SELECT 
    ENAME, 
    HIREDATE, 
    SAL, 
    LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

• LEAD 함수
  LAG와는 달리 현재 값을 기준으로 이후에 원하는 위치의 값을 가져올 때
  SQL서버는 지원 안함

SELECT 
    ENAME, 
    HIREDATE, 
    LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) "NEXTHIRED"
FROM EMP;