[SQLD 자격증 대비반] 챕터 13. 그룹 함수, 윈도우 함수

 

AGGREGATE FUCTION / 집계함수

• GROUP 함수의 한 부분으로 분류 가능
• COUN, SUM, AVG, MAX, MIN 모두 집계 함수이며 그 외에도 다양한 집계 함수 존재

GROUP FUNCTION / 그룹함수

• 데이터에 대한 결산 개념의 연산을 할 때 주로 사용
• ROLLUP
  • 칼럼으로 그룹을 만든 후 각 칼럼의 중간 합계를 만들기 위해 사용
  • 함수 내부 인자로 지정된 그룹화 칼럼은 소계를 생성하는 데 사용
  • 그룹화된 칼럼의 수가 N개면 소계는 N+1개 생성됨 (총계 포함)
  • 계층 구조이기 때문에 함수 내의 인자 순서가 바뀌면 결과도 바뀜
  • 계층 간 집계에 대해서는 LEVEL 별 순서를 정렬하지만 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않기 때문에 별도의 ORDER BY 절 사용 (CUBE 함수도 마찬가지)

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

• GROUPING 함수
  • ROLLUP이나 CUBE에 의해 그룹화된 칼럼의 소계가 계산된 결과를 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문
  • ROLLUP 함수에서 소계나 합계의 경우 NULL로 표시되는데 이를 어떤 그룹화된 값인지 표현하고 싶은 경우 CASE문을 사용하면 됨
  • CASE문은 ROLLUP 함수에서 사용자 정의 텍스트로 원하는 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);

• ROLLUP과 괄호
  • ROLLUP은 같은 형태를 띠고 있지만 괄호를 어디에 두느냐에 따라 다른 결과가 나옴
  • 괄호로 결합하여 두 칼럼을 하나의 집합 칼럼처럼 간주하여 사용하면 묶인 칼럼은 칼럼 별 집계를 따로 계산하지 않아도 됨

• 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 문장을 여러 번 반복하지 않아도 다양한 소계 집합 생성 가능
  • ROLLUP과 CUBE와 비슷한 결과를 얻을 수 있지만 좀 더 명시적으로 원하는 그룹 수준을 정할 수 있음

SELECT 
    DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
    DECODE(GROUPING(JOB), 1, 'All Jobs', JOB)            AS JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);

 

 

WINDOW FUNCTION / 윈도우 함수

• 데이터웨어하우스에서 발전한 기능으로 행과 행간의 관계를 나타내기 위해 사용
• 분석함수, 순위 함수로도 잘 알려져 있으며 순위나 합계, 평균, 행 위치 등 조작 가능
• GROUP BY 구문과 병행하여 중첩(NEST) 사용할 수 없지만, 서브쿼리에서는 사용 가능

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

• ARGUMENTS
  ▪️ 함수에 따라 0-N개의 인수가 지정될 수 있음
  ▪️ 윈도우 함수로 인해 결과 건수가 줄어들지는 않음
• PARTITION BY
  ▪️ 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음
  ▪️ GROUP BY는 분할 및 집약까지만 수행하지만 PARTITION은 분할까지만 수행
• ORDER BY
  ▪️ 어떤 항목에 대해 순위를 지정할지 기술
• WINDOWING
  ▪️ 함수의 대상이 되는 행 기준의 범위를 지정 가능
  ▪️ SQL Server에서는 지원 X
  ▪️ 사용할 수 있는 요소
  
  • ROWS : 윈도우 크기를 물리적 단위로 출력된 결과 행의 개수를 지정
  • RANGE : 논리적인 값에 의한 범위 지정
  • PRECEDING : 이전 범위를 의미
    UNBOUNDED PRECEDING : 첫 번째 행부터 윈도우가 시작됨을 지정
  • FOLLOWING : 이후 범위를 의미
    UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 행임을 의미
  • BETWEEN 시작 AND 끝 : 윈도우의 시작과 끝 위치 지정
  • CURRENT ROW : 윈도우의 시작 위치가 데이터가 출력된 현재 행임을 의미

윈도우 함수의 종류

 

▪️ 그룹 내 순위 함수

• RANK 함수
  • 특정항목 및 파티션에 대해서 순위 계산
  • 동일한 값은 동일한 순위를 부여하며 다음 순위는 누적된 순위로 계산
• DENSE_RANK
  • 동일한 순위는 하나의 건수로 계산
  • RANK 함수와 동일한 기능을 하지만 순위를 누적시키지 않고 순차대로 순위 부여
• ROW_NUMBER
  • 동일한 순위에 대해서 고유의 순위 부여
  • 동일한 값에 대해 다른 순위를 부여하지만 어떤 순서가 정해질지는 각 벤더 별로 결과가 다름
  • Oracle의 경우 ROWID가 적은 행이 우선순위가 높음
  • 세세하게 순서를 관리하려면 ORDER BY에 칼럼을 추가적으로 명시
• ex) 동일한 값의 경우
  RANK : 1 > 2 > 2 > 4 > 5
  DENSE_RANK : 1 > 2 > 2 > 3 > 4
  ROW_NUMBER : 1 > 2 > 3 > 4 > 5

▪️ 일반 집계 함수

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

▪️ 그룹 내 행 순서 함수

• FIRST_VALUE
  • 파티션에서 가장 처음 나오는 값을 구할 수 있음
  • SQL Server는 지원 X ➡️ MIN 함수를 사용해 같은 결과 구할 수 있음
  • 다른 함수와는 달리 공동 등수를 인정하지 않고 처음 나온 행만 처리하는 것이 특징
  • 공동 등수가 있는 경우에 정렬을 하고 싶다면 OVER 함수 내에 ORDER BY 사용하거나 INLINE VIEW 사용
• LAST_VALUE
  • 파티션에서 가장 나중에 나오는 값을 구할 수 있음
  • SQL Server는 지원 X ➡️ MAX 함수를 사용해 같은 결과 구할 수 있음
  • 공동 등수가 있는 경우에 정렬을 하고 싶다면 FIRST_VALUE와 마찬가지
• LAG
  • 이전의 행을 가지고 올 수 있음 (바로 이전, 특정 위치)
  • SQL Server 지원 X
• LEAD
  • 이후의 행을 가지고 올 수 있음 (바로 이후, 특정 위치)
  • SQL Server 지원 X

▪️ 그룹 내 비율 함수

• RATIO_TO_REPORT
  • 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율 조회
  • 결괏값의 범위는 0 < 결괏값 <= 1의 범위를 가지며 모든 값을 더하면 1
  • SQL Server 지원 X
• PERCENT_RANK
  • 파티션에서 제일 먼저 나온 것을 0으로, 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율 조회
  • 각 그룹의 순위를 퍼센트로 바꿔서 값을 표현
• CUME_DIST
  • 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회
  • 누적 분포상 위치를 0~1 사이의 값을 가짐
• NTILE
  • 입력되는 인자 값으로 N등분한 결과를 구할 때 주로 사용
  • 파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회

▪️ 개념 정리

• SQL은 컬럼과 컬럼간의 연산, 비교, 집계에 특화되어 있는 언어
• 윈도우 함수는 복잡한 서브쿼리 없이 간단히 행과 행간의 데이터에 대해 결과물 생성 가능
• 함수(칼럼) OVER (PARTITION BY 칼럼 ORDER BY 칼럼)
  • 함수 : 기존 함수 or 윈도우 함수
  • OVER : 필수 구문으로 내부에 파티션과 오더 절이 들어감