다중행 함수-그룹 함수, 윈도우 함수

다중행 함수

데이터베이스 함수는 다음과 같이 나뉜다.

• 내장 함수(Built-in Function)
  • 단일행 함수(Single-Row Function)
    • 문자형 함수, 숫자형 함수, 날짜형 함수, 변환형 함수, NULL 관련 함수, CASE 문
  • 다중행 함수(Multi-Row Function) ← 일반적으로 Where 절에 사용할 수 없음
    • 집계 함수
      • NULL 제외하고 집계
      • 여러 행으로부터 하나의 결과값을 반환
      • Select 절에서만 사용 가능
    • 그룹 함수(Group by 절)
      • ROLLUP, CUBE, GROUPING SETS + GROUPING(컬럼) (아래 참조)
    • 윈도우 함수
      • OVER과 함께 사용
      • PARTITION BY(그룹 옵션), ORDER BY 컬럼 [ASC/DESC] (정렬 옵션), [RANGE\ROWS] BETWEEN [범위] AND [범위]
• 사용자 정의 함수(User Defined Function)

---

다중행 함수 - 그룹 함수

• 여러 컬럼의 데이터를 가지고 한 번에 처리해 결과값 1건을 반환하는 함수 (= 복수행 함수)
• 일반적으로 NULL 값을 제외하고 계산함
• WHERE 절에 사용할 수 없음

ROLLUP

• 주어진 그룹핑의 소계(subtotal)를 표현

  ROLLUP(a, b)
  → (a, b), (a), 0레벨의 소계

  • 0레벨은 전체 소계를 말하는 것

• GROUP BY ROLL UP()

CUBE

• 소계 + 총계를 표현 (컬럼별로 다 계산)
  • ROLLUP 결과에 총계가 추가됨

    CUBE(a, b)
    → (a, b), (a), (b), 0레벨의 소계

GROUPING SETS

• ROLLUP, CUBE와 달리 계층 구조가 나타나지 않아, 인자(컬럼)의 순서가 달라져도 결과는 동일함

  GROUPING SETS((a, b), (a), (b), ())
  → (a, b), (a), (b), 0레벨의 소계

• GROUPING
  • GROUPING SETS를 지원하는 역할 (매개변수 하나)
  • 해당 데이터가 ROLLUP, CUBE 또는 GROUPING SETS를 이용할 때 만들어진 로우인지, 아니면 일반 로우의 데이터인지를 판별함
  • 해당 컬럼의 데이터가 NULL인 경우에는 1, NULL이 아닌 경우에는 0 반환
• GROUPING_ID
  • 매개변수로 들어온 컬럼들의 NULL처리 여부를 구분해 NULL이면 1(2진수)로 반환 (매개변수 여러개)

---

다중행 함수 - 윈도우 함수

• OVER이 필수

순위 함수
: RANK, DENSE_RANK, ROW_NUMBER
일반 집계 함수
: SUM, MIN, MAX, AVG, COUNT
그룹 내 행 순서 함수
: FIRST_VALUE, LAST_VALUE, LAG, LEAD
그룹 내 비율 함수
: RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE
** 대다수는 SQL에 지원되지 않음

• Range
  함수 적용될 행의 범위
  윈도우 함수의 OVER 절 내에 사용되고 ROWS와 함께 사용될 수 있음

• Rows
  윈도우 함수가 적용되는 행 범위 설정에 쓰임
  ex)
  SUM(col1) OVER (ORDER BY col2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
  → 현재 행부터 이전 두 개 행까지

• Partition by
  윈도우 함수를 파티션 별로 분할할 때 쓰임
  ex)
  SUM(col1) OVER (PARTITION BY col2)
  → col2의 고유한 값을 기준으로 데이터를 파티션으로 나누고, 각 파티션 내에서 SUM 함수가 계산됨

순위 함수

• RANK
  : 동일한 값에 동일한 순위 부여. 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력 (동일 순위, 건너뜀)
  ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수

• DENSE_RANK
  : RANK와 작동법은 동일, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않음 (동일 순위, 건너뛰지 않음)

• ROW_NUMBER
  : 중복 값들에 대해서도 순차적인 순위를 표시 (같은 값이라도 순위 다르게 메김, 공동 등수 인정 x) (동일 순위 X, 건너뛰지 않음)

SELECT COL1, COL2, COL3, RANK() OVER (ORDER BY COL1) AS ranking FROM table

그룹 내 행 순서 함수

• FIRST_VALUE
  : 파티션 별 윈도우에서 가장 먼저 나온 값 구함
  공동 등수 인정 x
  MIN 함수와 결과 동일
  (LAST_VALUE는 파티션 별 윈도우에서 마지막에 나온 값을 구하며 공동 등수 인정 안 하며 MAX 함수와 결과 동일)

• LAG
  : 이전 몇 번째 행의 값을 가져옴 (인자 최대 3개)
  두번째 인자 -> 몇번째 앞 행 가져오는지 (default=1)
  세번째 인자 -> 가져올 값 없을 때 사용되는 default 값
  이전 값 없는 경우 0
  (LEAD는 이후 몇 번째 행을 가져옴. LAG와 사용 방식 동일but 이후 값 없는 경우 null)

  LEAD (column_name, offset, default) OVER (PARTITION BY partition_column ORDER BY sort_column)
  ✅ column_name : 다음 행의 값을 가져올 컬럼
  ✅ offset : 몇 번째 다음 행의 값을 가져올지를 지정 (default=1)
  ✅ default : 다음 행이 없을 경우 반환할 기본 값

그룹 내 비율 함수

• RATIO_TO_REPORT
  : 파티션 내 SUM에 대한 행별 컬럼 값의 백분율을 소수점으로 산출함 (SQL Server X)

• PERCENT_RANK
  : 파티션별 순위에 대한 백분률을 소수점으로 산출함 (SQL Server X)

• CUME_DIST
  : 해당 파티션에서 누적 백분율 (SQL Server X)

• NTILE
  : 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급 구함