함수(Function)

함수(Function)는 데이터베이스에서 입력값을 처리하여 결과값을 반환하는 프로그램으로, 크게 밴더에서 제공하는 내장 함수(Built-in Function)와 사용자가 작성하는 사용자 정의 함수(User Defined Function)로 나눌 수 있습니다.

이번 시간에는 내장 함수를 알아보고자 합니다. 내장 함수는 단일 행 값을 입력하여 단일 값을 반환하는 단일 행 함수(Single-Row Function), 다중 행 값을 입력하여 단일 값을 반환하는 다중 행 함수(Multi-Row Function)으로 나눌 수 있습니다.

단일 행 함수

단일 행 함수의 유형은 문자형 함수, 숫자형 함수, 날짜형 함수, 형 변환 함수, NULL 관련 함수가 있습니다.

문자형 함수

문자형 함수는 문자 또는 문자열을 입력받아 처리하는 함수로, SELECT, WHERE, ORDER BY절에서 사용할 수 있습니다.

문자형 함수

숫자형 함수

숫자형 함수는 수치형 데이터를 입력받아 처리하는 함수입니다.

숫자형 함수

날짜형 함수

날짜형 함수는 DATE 타입의 데이터를 계산합니다.

날짜형 함수

형 변환 함수

형 변환 함수는 데이터 타입을 변환하기 위해 사용하는 함수입니다.

형 변환 함수

NULL 관련 함수

NULL 관련 함수눈 정해지지 않은 값을 의미하며, 과 관련된 함수는 다음과 같습니다.

NULL 관련 함수

다중 행 함수

다중 행 함수의 유형은 집계 함수, 그룹 함수, 윈도우 함수가 있습니다.

집계 함수

집계 함수는 특정 컬럼에 대한 행들의 값을 통계적으로 계산한 결과를 반환하는 함수입니다. SELECT절, HAVING절, GROUP BY절에 사용할 수 있으며, 값은 제외하고 계산됩니다.

집계 함수

그룹 함수

그룹 함수는 데이터를 그룹화하고 각 그룹 내의 데이터를 분석하는 데 사용되는 함수입니다. SELECT 문에서 사용되며 일반적으로 GROUP BY 구문과 함께 사용됩니다.

(1) ROLLUP 함수

ROLLUP 함수는 지정된 컬럼의 소계 및 총계를 구하기 위해 사용하는 그룹 함수입니다. 각 열을 기준으로 데이터를 그룹화하고, 각 그룹에 대해 집계 연산을 수행한 다음, 상위 수준에서도 그룹화 및 집계를 수행합니다.

ROLLUP 함수 구문

SELECT 컬럼명, 집계 함수
  FROM 테이블명
 GROUP BY ROLLUP(컬럼1, 컬럼2);

해당 그룹함수는 다음과 같은 행의 합집합과 같습니다.

ROLLUP 함수를 합집합으로 표현

GROUP BY ROLLUP(컬럼1, 컬럼2)
=
GROUP BY 컬럼1, 컬럼2
UNION ALL
GROUP BY 컬럼1
UNION ALL
전체 집합 결과

예를 들어, 다음과 같은 데이터가 있다고 가정해 봅시다.

ROLLUP 함수 예제 데이터

위의 데이터에서, 이름, 성별, 국가별 판매량을 계산하고 싶다고 가정해 봅시다. 이를 수행하기 위해 ROLLUP 함수를 사용할 수 있습니다. 다음 SQL 문은 이름, 성별, 국가를 기준으로 판매량을 계산하고, ROLLUP 함수를 사용하여 계층 구조를 만듭니다.

ROLLUP 함수 예제

SELECT 지역, 부서, SUM(금액)
  FROM 테이블명
 GROUP BY ROLLUP(지역, 부서);

이 구문을 사용하여, 지역별 금액 합계, 부서별 금액 합계, 전체 금액 합계를 각각 계산할 수 있습니다.

아래는 위의 SQL 문의 결과입니다.

ROLLUP 함수 예제 결과

(2) CUBE 함수

CUBE 함수는 다차원 그룹화를 수행하기 위한 기능입니다. ROLLUP 함수와 마찬가지로 GROUP BY 절에 사용되며, 여러 열을 기준으로 데이터를 그룹화하고 각 그룹 단위의 합계를 계산합니다. 하지만, ROLLUP 함수와 달리 CUBE 함수는 모든 가능한 조합에 대한 그룹 단위의 합계를 계산합니다.

CUBE 함수는 내부적으로 대상 컬럼의 순서를 변경하여 쿼리를 수행하기 때문에 다른 그룹 함수보다 시스템에 대한 부하가 크므로 사용시 주의해야 합니다.

CUBE 함수 구문은 다음과 같습니다.

CUBE 함수 구문

SELECT 컬럼명, 집계 함수
  FROM 테이블명
 GROUP BY CUBE(컬럼1, 컬럼2);

해당 그룹함수는 다음과 같은 행의 합집합과 같습니다.

CUBE 함수를 합집합으로 표현

GROUP BY CUBE(컬럼1, 컬럼2)
=
GROUP BY 컬럼1, 컬럼2
UNION ALL
GROUP BY 컬럼1
UNION ALL
GROUP BY 컬럼2
UNION ALL
전체 집합 결과

예를 들어, 다음과 같은 데이터가 있다고 가정해 봅시다.

CUBE 함수 예제 데이터

위의 데이터에서, 날짜, 지역, 부서별로 금액의 합계를 계산하려면 다음과 같은 SQL 문을 사용할 수 있습니다.

CUBE 함수 예제

SELECT 날짜, 지역, 부서, SUM(금액)
  FROM 테이블명
 GROUP BY CUBE(날짜, 지역, 부서);

이 구문을 사용하여, 지역별 금액 합계, 부서별 금액 합계, 전체 금액 합계를 각각 계산할 수 있습니다.

아래는 위의 SQL 문의 결과입니다.

CUBE 함수 결과

(3) GROUPING SETS 함수

GROUPING SETS 함수는 다중 그룹화를 수행하기 위한 기능입니다. ROLLUP 함수 및 CUBE 함수와 비슷하지만, GROUPING SETS 함수는 그룹화에 대한 집합을 명시적으로 지정할 수 있습니다.

GROUPING SETS 함수는 GROUP BY 절에 사용되며, 다중 열을 기준으로 데이터를 그룹화하고 각 그룹 단위의 합계를 계산합니다.

ROLLUP 함수나 CUBE 함수처럼 모든 가능한 조합을 계산하지 않고, 사용자가 지정한 그룹의 소계만 계산합니다. 또한 ROLLUP 함수와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있습니다.

GROUPING SETS 함수의 구문은 다음과 같습니다.

GROUPING SETS 함수 구문

SELECT 컬럼명, 집계 함수
  FROM 테이블명
 GROUP BY GROUPING SETS(컬럼1, 컬럼2);

해당 그룹함수는 다음과 같은 행의 합집합과 같습니다.

GROUPING SETS 함수를 합집합으로 표현

GROUP BY GROUPING SETS(컬럼1, 컬럼2)
=
GROUP BY 컬럼1
UNION ALL
GROUP BY 컬럼2

예를 들어, 다음과 같은 데이터가 있다고 가정해 봅시다.

GROUPING SETS 함수 예제 데이터

위의 데이터에서, 날짜, 지역, 부서별로 금액의 합계를 계산하려면 다음과 같은 SQL 문을 사용할 수 있습니다.

GROUPING SETS 함수 예제

SELECT 날짜, 지역, 부서, SUM(금액)
  FROM 테이블명
 GROUP BY GROUPING SETS((날짜), (지역), (부서));

위의 SQL 문에서, GROUPING SETS 구문은 그룹화에 대한 집합을 명시적으로 지정합니다. GROUPING SETS 구문 안에는 그룹화에 사용할 열을 나열하며, 각각을 괄호로 묶어서 집합으로 표시합니다.

아래는 위의 SQL 문의 결과입니다.

GROUPING SETS 함수 결과

윈도우 함수

윈도우 함수(Window Function)는 OVER 절을 사용하여 데이터 집합을 기반으로 계산되는 함수입니다. 집계 함수와 달리 그룹화하지 않고 개별 행에 대해 계산합니다. 각 행의 계산 결과는 해당 행의 윈도우(Window)에 속하는 모든 행의 값을 사용하여 계산됩니다.

윈도우 함수를 사용하면 데이터 집합에서 개별 행에 대한 값을 계산하는 것이 가능합니다. 일반적으로, 윈도우 함수는 데이터 집합에서 일부 행에 대한 통계를 계산하기 위해 사용됩니다. 윈도우 함수는 행과 행 사이에서 연산을 수행하고 결과 집합에 포함될 행을 결정하는 데 사용됩니다.

데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 추가된 기능으로 OLAP(On-Line Analytical Processing) 함수라고도 합니다.

윈도우 함수는 다음과 같은 구문을 가집니다.

윈도우 함수 구문

SELECT 윈도우함수(인수) OEVER (
         [PARTITION BY 컬럼1 [, 컬럼2 ...]]
         [ORDER BY 컬럼1 [ASC|DESC] [, 컬럼2 [ASC, DESC] ...]]
         [WINDOWING절] )
  FROM 테이블명;

윈도우 함수를 사용하려면 OVER() 구문을 사용하여 윈도우를 정의해야 합니다. 윈도우는 연산을 수행할 데이터의 범위를 지정하며, 연산 범위는 WINDOWING절에서 ROWS나 RANGE로 정의됩니다. ROWS는 행 수로, RANGE는 값의 범위로 윈도우를 정의합니다. ROWS나 RANGE 구문 뒤에는 윈도우 크기를 나타내는 값이 올 수 있습니다.

WINDOWING절은 다음과 같은 구문을 가집니다.

WINDOWING절 구문

[ROWS|RANGE] BETWEEN 시작점 AND 끝점;

시작점은 다음과 같은 키워드를 사용할 수 있습니다.

끝점은 다음과 같은 키워드를 사용할 수 있습니다.

WINDOWING절을 명시하지 않는 경우 기본값으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 적용됩니다.

윈도우 함수의 유형에는 순위 함수, 집계 함수, 순서 함수, 비율 함수가 있습니다.

(1) 순위 함수

순위 함수는 데이터의 순위를 계산하여 출력합니다. 이 때, 데이터가 중복되는 경우 같은 순위를 가지게 됩니다. SQL에서는 다음과 같은 3가지의 순위 함수를 제공합니다.

예를 들어, 다음과 같은 SCORES 테이블이 있다고 가정해 봅시다.

순위 함수 예제 데이터

이 테이블에서 NAME 컬럼을 기준으로 정렬된 결과를 가지고, SCORE 컬럼의 누적 합계를 계산하려면 다음과 같이 윈도우 함수를 사용할 수 있습니다.

RANK 함수 예제

SELECT NAME,
       SCORE,
       RANK() OVER (ORDER BY SCORE DESC) AS RANK
  FROM SCORES;

위 쿼리는 SCORES 테이블에서 SCORE 컬럼을 기준으로 내림차순으로 정렬한 결과를 기반으로, 각 데이터의 순위를 계산합니다. RANK() 함수는 동일한 순위를 갖는 데이터가 있을 경우 같은 순위를 부여하고, 그 다음 순위는 건너뛰어 다음으로 높은 순위를 부여합니다. 예를 들어, 첫 번째와 두 번째 데이터는 같은 순위(2위)를 부여하고, 그 다음 데이터는 4위를 부여합니다. 결과는 다음과 같습니다.

RANK 함수 결과

다음은 DENSE_RANK() 함수를 사용하여 score 열을 기준으로 순위를 계산하는 쿼리의 예제입니다.

DENSE_RANK 함수 예제

SELECT NAME,
       SCORE,
       DENSE_RANK() OVER (ORDER BY SCORE DESC) AS RANK
  FROM SCORES;

위 쿼리는 RANK() 함수와 비슷하게, SCORES 테이블에서 SCORE 컬럼을 기준으로 내림차순으로 정렬한 결과를 기반으로, 각 데이터의 순위를 계산합니다. 하지만 DENSE_RANK() 함수는 동일한 순위를 갖는 데이터가 있을 경우 같은 순위를 부여하고, 그 다음 순위는 건너뛰지 않고 동일한 순위를 부여합니다. 예를 들어, 첫 번째와 두 번째 데이터는 같은 순위(2위)를 부여하고, 그 다음 데이터는 3위를 부여합니다. 결과는 다음과 같습니다.

DENSE_RANK 함수 결과

마지막으로, ROW_NUMBER() 함수를 사용하여 SCORE 컬럼을 기준으로 순위를 계산하는 쿼리의 예제를 살펴보겠습니다.

ROW_NUMBER 함수 예제

SELECT NAME,
       SCORE,
       ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS RANK
  FROM SCORES;

위 쿼리는 RANK() 함수와 DENSE_RANK() 함수와 달리, 각 데이터를 유일하게 구별하는 번호를 부여합니다. SCORES 테이블에서 SCORE 컬럼을 기준으로 내림차순으로 정렬한 결과를 기반으로, 각 데이터에 대해 1부터 순차적으로 번호를 부여합니다. 결과는 다음과 같습니다.

ROW_NUMBER 함수 결과

ROW_NUMBER() 함수는 특히 페이징 처리(paging)에 유용합니다. 페이징 처리란, 대량의 데이터를 페이지 단위로 나누어 출력하는 것을 의미합니다. 예를 들어, 위 쿼리 결과에서 첫 번째 페이지에는 1부터 2까지의 데이터를, 두 번째 페이지에는 3부터 4까지의 데이터를 출력할 수 있습니다.

(2) 집계 함수

윈도우 함수의 집계 함수는 파티션 별 윈도우 내에서 데이터를 그룹화하여 통계적으로 계산한 결과를 반환하는 함수입니다. 전체 데이터셋을 기준으로 계산하는 일반적인 집계 함수와는 달리 더욱 세부적인 집계 결과를 얻을 수 있습니다.

윈도우 함수 중 집계 함수로는 다음과 같은 함수가 있습니다.

다음과 같은 SCORES 테이블이 있다고 가정해 봅시다.

위 테이블에서 윈도우 함수의 집계 함수를 사용하여 SUBJECT를 그룹으로 묶어 각 그룹의 집계 결과를 구할 수 있습니다.

윈도우 함수 중 집계 함수의 예제

SELECT NAME,
	     SUBJECT,
	     SCORE,
	     SUM(SCORE) OVER (PARTITION BY SUBJECT) AS SUBJECT_TOTAL_SCORE,
	     AVG(SCORE) OVER (PARTITION BY SUBJECT) AS SUBJECT_AVG_SCORE,
	     MIN(SCORE) OVER (PARTITION BY SUBJECT) AS SUBJECT_MIN_SCORE,
	     MAX(SCORE) OVER (PARTITION BY SUBJECT) AS SUBJECT_MAX_SCORE,
	     COUNT(SCORE) OVER (PARTITION BY SUBJECT) AS SUBJECT_COUNT_SCORE
  FROM SCORES;

결과는 다음과 같습니다.

(3) 순서 함수

윈도우 함수 중 순서 함수는 각 행의 이전이나 다음 행의 값을 가져오거나, 윈도우 내에서의 첫 번째나 마지막 값을 가져오는 함수입니다.

윈도우 함수 중 순서 함수로는 다음과 같은 함수가 있습니다.

다음과 같은 SALES 테이블이 있다고 가정해 봅시다.

위 테이블에에서 순서 함수를 사용해 보겠습니다.

윈도우 함수 중 순서 함수의 예제

SELECT ID,
       "DATE",
       AMOUNT,
       LEAD(AMOUNT) OVER (ORDER BY "DATE") AS NEXT_AMOUNT,
       LAG(AMOUNT) OVER (ORDER BY "DATE") AS PREV_AMOUNT,
       FIRST_VALUE(AMOUNT) OVER (ORDER BY "DATE") AS FIRST_AMOUNT,
       LAST_VALUE(AMOUNT) OVER (ORDER BY "DATE") AS LAST_AMOUNT
  FROM SALES;

결과는 다음과 같습니다.

파티셔닝을 하지 않았기 때문에 전체 행을 하나로 그룹화 합니다. LEAD와 LAG 함수는 직관적으로 알 수 있을 것입니다. FIRST_VALUE와 LAST_VALUE의 경우 이해가 어려울 수 있는데 앞서 말한 WINDOWING절의 기본값이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이기 때문에 이러한 결과가 나왔습니다. 처음 행부터 현재 행까지가 하나의 윈도우가 되기 때문에 FIRST_AMOUNT는 항상 첫 번째 행의 AMOUNT인 100이, LAST_AMOUNT는 현재 행의 AMOUNT가 출력됩니다.

(4) 비율 함수

윈도우 함수 중 비율 함수는 파티션 내 백분율을 계산하거나 비율에 따라 n등분 할 수 있는 함수입니다.

윈도우 함수 중 비율 함수에는 다음과 같은 함수가 있습니다.

다음과 같은 SALES 테이블이 있다고 가정해 봅시다.

위 테이블에에서 비율 함수를 사용해 보겠습니다.

윈도우 함수 중 비율 함수의 예제

SELECT ID,
       "DATE",
       AMOUNT,
       RATIO_TO_REPORT(AMOUNT) OVER () AS AMOUNT_SUM_RATIO,
       PERCENT_RANK() OVER (ORDER BY "DATE") AS AMOUNT_RATIO,
       CUME_DIST() OVER (ORDER BY "DATE") AS AMOUNT_CUM_DIST
FROM SALES;

RATIO_TO_REPORT 함수는 SUM값을 그룹하기 위한 컬럼을 인수로 설정하고, OVER() 구문에서 ORDER BY절을 사용하지 않습니다.

반면 PERCENT_RANK와 CUME_DIST 함수는 인수가 없으며, OVER() 구문에서 ORDER BY절을 통한 정렬이 필요합니다.

결과는 다음과 같습니다.