Percentile 확장
Percentile 확장 (Percentile Extension)
percentile 확장은 분포에 대한 백분위 점수와 중앙값을 계산하는 네 개의 집계 함수를 제공해요. SQLite 3.51.0부터 amalgamation에 포함되지만 기본적으로는 비활성화되어 있어요.
본문
1. 개요
percentile 확장은 분포에 대한 백분위 점수와/또는 중앙값을 계산하는 네 개의 집계 함수를 제공해요.
1.1. 사용 가능 여부
percentile 확장은 SQLite 버전 3.51.0(2025-11-04)부터 amalgamation의 일부예요. 하지만 percentile 확장은 기본적으로 비활성화되어 있으며, -DSQLITE_ENABLE_PERCENTILE 컴파일 옵션을 사용해 컴파일 시 활성화해야 해요.
SQLite 3.51.0 이전에는 percentile 확장이 별도의 소스 코드 파일에 있었고, 독립적으로 컴파일되어 로드 가능한 확장으로 취급되어야 했어요.
2. Percentile이 구현하는 집계 함수
percentile 확장은 아래에 설명된 집계 SQL 함수를 구현해요. 이들 함수가 사용하는 알고리즘은 O(N) 공간과 O(NlogN) 시간을 사용하며, 여기서 N은 NULL이 아닌 입력의 수예요.
2.1. median(Y) 집계 함수
median(Y) 함수는 NULL이 아닌 모든 입력 Y의 중앙값을 계산하는 집계 함수예요. median()에 대한 Y 입력 중 NULL이 아니면서 숫자 값이 아닌 것이 있으면 오류가 발생해요. NULL이 아니고 숫자인 입력이 없으면 median()의 결과는 NULL이에요.
중앙값은 모든 입력을 정렬했을 때 입력 수가 홀수면 중앙 요소의 값이에요. 입력 수가 짝수면 중앙값은 두 중앙 입력의 평균이에요.
median(Y) 함수는 percentile(Y,50)과 동등해요.
2.2. percentile(Y,P) 집계 함수
percentile(Y,P) 집계 함수는 NULL이 아닌 입력의 P 퍼센트보다 크거나 같고, 입력의 100-P 퍼센트보다 작거나 같은 값 X를 계산해요. 매개변수 P는 0.0과 100.0 사이의 숫자여야 해요. P의 값은 집계의 모든 항에 대해 같아야 하며 NULL이면 안 돼요. Y 입력은 NULL이거나 숫자여야 해요. Y의 NULL 값은 무시돼요. 숫자가 아닌 NULL이 아닌 Y 입력은 오류를 발생시켜요.
percentile() 함수는 NULL이 아닌 입력을 정렬한 다음, 첫 번째부터 마지막까지 P 퍼센트에 가장 가까운 입력 하나 또는 여러 개를 계산해 동작해요. 반환값은 두 개의 가장 가까운 입력의 가중 평균이에요.
2.3. percentile_cont(Y,P) 집계 함수
percentile_cont(Y,P) 함수는 P 값이 0.0부터 100.0이 아니라 0.0부터 1.0의 범위를 가진다는 점을 제외하면 percentile(Y,P)과 같이 동작해요. 따라서 percentile_cont(Y,P)의 결과는 percentile(Y,P*100)과 같아요.
percentile_cont() 함수는 SQL 표준에 정의되어 있어요. 하지만 단순한 함수 호출 "percentile_cont(Y,P)" 대신, SQL 표준 문법은 다음과 같아요.
SELECT percentile_cont(P) WITHIN GROUP (ORDER BY Y) FROM tab;
이는 정확히 같은 의미인 다음 문과 동일한 것을 표현하기에 너무 많은 문법이에요.
SELECT percentile_cont(Y,P) FROM tab;
SQLite는 SQL 표준 문법을 지원하지만, amalgamation이 아닌 정식 소스에서 -DSQLITE_ENABLE_ORDERED_SET_AGGREGATES=1 컴파일 옵션을 사용해 컴파일된 경우에만 그래요. 그 컴파일 옵션이 없으면 더 간단한 "percentile_cont(Y,P)" 형태만 지원돼요. 장황한 SQL 표준 형식에는 장점이 없고 가독성에 큰 단점이 있으며, SQLITE_ENABLE_ORDERED_SET_AGGREGATES 컴파일 옵션은 SQLite 라이브러리를 더 크게 만들기 때문에, 그 옵션은 대부분의 빌드에서 제외돼요.
저자는 이 함수 이름의 "_cont" 접미사가 "continuous"(연속)의 약어라고 생각하며, 이는 실제 백분위 순위에 가장 가까운 두 입력 값의 가중 평균이 반환값이라는 사실을 반영해요. 이 이름은 SQLite 개발자가 고른 것이 아니라 SQL 표준이에요.
2.4. percentile_disc(Y,P) 집계 함수
percentile_disc(Y,P) 함수는 가장 가까운 사용 가능한 입력의 가중 평균을 하는 대신 항상 입력 값 중 하나(두 가지 가능한 선택 중 더 작은 값)를 반환한다는 점을 제외하면 percentile_cont(Y,P)과 같이 동작해요. percentile_disc(Y,P) 함수는 SQL 표준에 정의되어 있어요. percentile_cont()와 마찬가지로 장황한 ordered-set 집계 문법이 필요한데, 그 문법은 SQLite가 SQLITE_ENABLE_ORDERED_SET_AGGREGATES 컴파일 옵션을 사용해 컴파일될 때만 SQLite에서 지원돼요.
저자는 이 함수 이름의 "_disc" 접미사가 "discrete"(이산)의 약어라고 생각해요. 이 이름은 SQLite 개발자가 고른 것이 아니라 SQL 표준이에요.
3. 설계 요구사항
다음 요구사항들이 percentile 확장을 정의해요.
-
percentile(Y,P) 함수는 정확히 두 개의 인수를 취하는 집계 함수예요.
-
percentile(Y,P)에 대한 P 인수가 집계의 모든 행에서 같지 않으면 오류가 발생해요. 앞 문장의 "같다"는 값이 0.001 미만으로 다르다는 뜻이에요.
-
percentile(Y,P)에 대한 P 인수가 0.0 이상 100.0 이하 범위의 숫자 이외의 것으로 평가되면 오류가 발생해요.
-
percentile(Y,P)에 대한 어떤 Y 인수가 NULL이 아니면서 숫자가 아닌 값으로 평가되면 오류가 발생해요.
-
percentile(Y,P)에 대한 어떤 Y 인수가 양의 무한대나 음의 무한대로 평가되면 오류가 발생해요. (SQLite는 항상 NaN 값을 NULL로 해석해요.)
-
percentile(Y,P)의 Y와 P 모두 CASE WHEN 표현식을 포함한 임의의 표현식일 수 있어요.
-
percentile(Y,P) 집계는 최소 1,000,000(1백만) 행의 입력을 처리할 수 있어야 해요.
-
Y에 대한 NULL이 아닌 값이 없으면 percentile(Y,P)는 NULL을 반환해요.
-
Y에 대한 NULL이 아닌 값이 정확히 하나 있으면 percentile(Y,P)는 그 하나의 Y 값을 반환해요.
-
NULL이 아닌 Y 값이 N개(N은 2 이상) 있고 Y 값이 작은 것부터 큰 것 순으로 정렬되어 있으며, 0부터 N-1까지 그래프를 그려 J 위치의 그래프 높이가 J번째 Y 값이고 인접한 Y 값 사이에 직선이 그려진다면, percentile(Y,P) 함수는 P*(N-1)/100 위치에서의 그래프 높이를 반환해요.
-
percentile(Y,P) 함수는 항상 부동 소수점 숫자 또는 NULL을 반환해요.
-
percentile(Y,P)는 단일 C99 소스 코드 파일로 구현되며, sqlite3_load_extension() 인터페이스로 SQLite에 로드할 수 있는 공유 라이브러리나 DLL로 컴파일돼요.
-
별도의 median(Y) 함수는 percentile(Y,50)과 동등해요.
-
별도의 percentile_cont(Y,P) 함수는 percentile(Y,P*100.0)과 같은 결과를 반환해요. 즉, percentile_cont()의 두 번째 인수의 값은 percentile()처럼 0부터 100이 아니라 0부터 1의 범위여야 해요.
-
별도의 percentile_disc(Y,P) 함수는 가장 가까운 두 입력 값의 가중 평균을 반환하는 대신 다음으로 낮은 값을 반환한다는 점을 제외하면 percentile_cont(Y,P)와 비슷해요. 따라서 percentile_disc(Y,P)는 항상 입력 중 하나였던 값을 반환해요.
-
median(), percentile(Y,P), percentile_cont(Y,P), percentile_disc(Y,P) 모두 창 함수(window function)로 사용할 수 있어요.