집계 함수
집계 함수 (Aggregate Functions)
행 그룹에 대한 요약 값을 계산하는 내장 집계 함수를 정리한 문서예요. GROUP BY, HAVING, 윈도우 함수와의 조합까지 차근차근 살펴볼게요.
출처: 문서
본문
집계 함수는 입력 행 집합에서 단일 결과를 계산해요. 보통 SELECT 문에서 GROUP BY 절과 함께 쓰이지만, GROUP BY 없이 모든 행을 대상으로 집계할 수도 있어요. 집계가 아닌 컬럼과 함께 GROUP BY 없이 SELECT에 쓰면 결과는 한 행이 돼요.
모든 표준 집계 함수는 NULL 값을 무시해요(count(*)는 예외). 모든 입력 값이 NULL이면 집계는 NULL을 반환하지만, count()(0 반환)와 total()(0.0 반환)는 예외예요.
Function Reference
| Function | Return Type | Description |
|---|---|---|
array_agg(X) |
BLOB (array) | X의 모든 값을 배열로 모아요 (Turso 확장) |
avg(X) |
REAL | X의 모든 non-NULL 값의 평균 |
count(X) |
INTEGER | X가 NULL이 아닌 행의 개수 |
count(*) |
INTEGER | 그룹의 전체 행 개수 |
group_concat(X) |
TEXT | X의 모든 non-NULL 값을 쉼표로 연결 |
group_concat(X, Y) |
TEXT | X의 모든 non-NULL 값을 Y로 연결 |
string_agg(X, Y) |
TEXT | group_concat(X, Y)의 별칭 |
max(X) |
X와 동일 | X의 최대 non-NULL 값 |
min(X) |
X와 동일 | X의 최소 non-NULL 값 |
sum(X) |
INTEGER 또는 REAL | X의 모든 non-NULL 값의 합. 모두 NULL이면 NULL 반환 |
total(X) |
REAL | X의 모든 non-NULL 값의 합. 항상 REAL, 모두 NULL이면 0.0 반환 |
Detailed Descriptions and Examples
아래 예제는 다음 테이블을 사용해요:
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
region TEXT,
product TEXT,
amount REAL,
quantity INTEGER
);
INSERT INTO sales VALUES
(1, 'North', 'Widget', 100.00, 5),
(2, 'North', 'Gadget', 250.00, 2),
(3, 'South', 'Widget', 150.00, 8),
(4, 'South', 'Gadget', NULL, 3),
(5, 'North', 'Widget', 200.00, NULL),
(6, 'South', 'Widget', 175.00, 6);
avg(X)
avg(X)
X의 모든 non-NULL 값의 평균을 REAL(부동소수점) 수로 반환해요. 모든 값이 NULL이면 NULL을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any numeric | 평균을 낼 표현식 |
Return type: REAL
SELECT avg(amount) FROM sales;
-- 175.0 (sum of non-NULL amounts / count of non-NULL amounts = 875.0 / 5)
SELECT region, avg(amount) AS avg_amount
FROM sales
GROUP BY region;
| region | avg_amount |
|---|---|
| North | 183.333333333333 |
| South | 162.5 |
avg(X)는 합과 개수 모두에서 NULL 값을 무시해요. 위 예제에서 South 지역의 non-NULL 금액은 두 개(150, 175)라서, 평균은 non-NULL 값만으로 계산돼요.
count(X) and count(*)
count(X)
count(*)
count(X)는 X가 NULL이 아닌 행의 개수를 반환해요. count(*)는 NULL 값이 있는 행을 포함해 그룹의 전체 행 개수를 반환하고요.
| Parameter | Type | Description |
|---|---|---|
| X | any | non-NULL 값을 셀 표현식 |
* |
special | NULL과 무관하게 모든 행을 셈 |
Return type: INTEGER
SELECT count(*) FROM sales;
-- 6 (all rows)
SELECT count(amount) FROM sales;
-- 5 (excludes the row where amount is NULL)
SELECT region, count(*) AS total_rows, count(amount) AS rows_with_amount
FROM sales
GROUP BY region;
| region | total_rows | rows_with_amount |
|---|---|---|
| North | 3 | 3 |
| South | 3 | 2 |
group_concat(X) and group_concat(X, Y)
group_concat(X)
group_concat(X, Y)
X의 모든 non-NULL 값을 하나의 문자열로 연결해요. 기본 구분자는 쉼표(,)이고, Y가 주어지면 대신 그 값이 구분자로 쓰여요.
| Parameter | Type | Description |
|---|---|---|
| X | any | 값을 연결할 표현식 |
| Y | TEXT | 구분자 문자열 (기본값: ",") |
Return type: TEXT
SELECT group_concat(product) FROM sales;
-- 'Widget,Gadget,Widget,Gadget,Widget,Widget'
SELECT group_concat(DISTINCT product) FROM sales;
-- 'Widget,Gadget'
SELECT region, group_concat(product, ' | ') AS products
FROM sales
GROUP BY region;
| region | products |
|---|---|
| North | Widget | Gadget | Widget |
| South | Widget | Gadget | Widget |
string_agg(X, Y)
string_agg(X, Y)
group_concat(X, Y)의 별칭이에요. PostgreSQL과의 호환성을 위해 제공돼요.
| Parameter | Type | Description |
|---|---|---|
| X | any | 값을 연결할 표현식 |
| Y | TEXT | 구분자 문자열 |
Return type: TEXT
SELECT region, string_agg(product, ', ') AS products
FROM sales
GROUP BY region;
| region | products |
|---|---|
| North | Widget, Gadget, Widget |
| South | Widget, Gadget, Widget |
max(X) and min(X)
max(X)
min(X)
max(X)는 X의 최대 non-NULL 값을 반환하고, min(X)는 최소 non-NULL 값을 반환해요. 값은 표준 SQLite 비교 규칙으로 비교돼요. 모든 값이 NULL이면 NULL을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any | 최대 또는 최소 값을 찾을 표현식 |
Return type: 입력 타입과 동일
SELECT max(amount), min(amount) FROM sales;
-- max: 250.0, min: 100.0
SELECT region, max(amount) AS highest, min(amount) AS lowest
FROM sales
GROUP BY region;
| region | highest | lowest |
|---|---|---|
| North | 250.0 | 100.0 |
| South | 175.0 | 150.0 |
max(X)나 min(X)를 집계 컨텍스트에서 인자 하나로 호출하면 집계 함수로 동작해요. 인자 두 개 이상으로 호출하면(예: max(a, b, c)) 스칼라 함수로 동작해 가장 큰 인자를 반환하죠.
sum(X) and total(X)
sum(X)
total(X)
두 함수 모두 X의 모든 non-NULL 값의 합을 반환해요. 반환 타입과 모든 값이 NULL일 때의 동작이 달라요.
| Parameter | Type | Description |
|---|---|---|
| X | any numeric | 합을 낼 표현식 |
Return type:
sum(X): 모든 non-NULL 입력이 정수이고 오버플로가 없으면 INTEGER, 아니면 REAL. 모든 값이 NULL이면 NULL 반환.total(X): 항상 REAL. 모든 값이 NULL이면 0.0 반환.
SELECT sum(amount), total(amount) FROM sales;
-- sum: 875.0, total: 875.0
SELECT sum(quantity), total(quantity) FROM sales;
-- sum: 24, total: 24.0
Difference between sum() and total()
핵심 차이는 그룹의 모든 값이 NULL일 때 나타나요:
CREATE TABLE empty_amounts (val REAL);
INSERT INTO empty_amounts VALUES (NULL), (NULL);
SELECT sum(val) FROM empty_amounts;
-- NULL
SELECT total(val) FROM empty_amounts;
-- 0.0
덕분에 빈 그룹이나 전부 NULL인 그룹에서도 수치 결과가 필요하다면 total()이 편리해요:
SELECT
region,
sum(amount) AS sum_amount,
total(amount) AS total_amount
FROM sales
GROUP BY region;
| region | sum_amount | total_amount |
|---|---|---|
| North | 550.0 | 550.0 |
| South | 325.0 | 325.0 |
-- total() is useful in arithmetic to avoid NULL propagation
SELECT total(amount) * 1.1 AS with_tax FROM sales;
-- 962.5
-- sum() with all NULLs would produce NULL, making the multiplication NULL too
sum(X)는 모든 입력이 정수이고 결과가 64비트 부호 정수 범위 안에 들면 정수 결과를 반환해요. 합이 오버플로하면 자동으로 REAL로 전환되고요. 항상 부동소수점 결과를 원한다면 total(X)를 쓰세요.
Using Aggregates with GROUP BY
GROUP BY 절은 행을 그룹으로 분할해요. 각 집계 함수는 그룹마다 독립적으로 계산돼요.
SELECT
region,
product,
count(*) AS order_count,
sum(amount) AS total_sales,
avg(amount) AS avg_sale,
min(amount) AS min_sale,
max(amount) AS max_sale
FROM sales
GROUP BY region, product;
| region | product | order_count | total_sales | avg_sale | min_sale | max_sale |
|---|---|---|---|---|---|---|
| North | Gadget | 1 | 250.0 | 250.0 | 250.0 | 250.0 |
| North | Widget | 2 | 300.0 | 150.0 | 100.0 | 200.0 |
| South | Gadget | 1 | NULL | NULL | NULL | NULL |
| South | Widget | 2 | 325.0 | 162.5 | 150.0 | 175.0 |
Filtering Groups with HAVING
HAVING 절은 집계 이후 그룹을 필터링해요. 집계 전에 행을 걸러낼 때는 WHERE, 집계 후에 그룹을 걸러낼 때는 HAVING을 쓰세요.
SELECT region, sum(amount) AS total_sales
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
HAVING sum(amount) > 400;
| region | total_sales |
|---|---|
| North | 550.0 |
Aggregates with DISTINCT
DISTINCT 키워드는 집계가 unique한 non-NULL 값만 고려하게 만들어요.
SELECT count(product) FROM sales;
-- 6 (all non-NULL product values)
SELECT count(DISTINCT product) FROM sales;
-- 2 (only 'Widget' and 'Gadget')
SELECT group_concat(DISTINCT product) FROM sales;
-- 'Widget,Gadget'
Aggregates as Window Functions
모든 표준 집계 함수는 윈도우 함수로 쓸 수 있어요. OVER 절과 함께 쓰면 행을 접지 않고 누적 또는 파티션 결과를 계산해요.
SELECT
id,
region,
amount,
sum(amount) OVER (PARTITION BY region ORDER BY id) AS running_total,
count(*) OVER (PARTITION BY region) AS region_count
FROM sales
ORDER BY region, id;
| id | region | amount | running_total | region_count |
|---|---|---|---|---|
| 1 | North | 100.0 | 100.0 | 3 |
| 2 | North | 250.0 | 350.0 | 3 |
| 5 | North | 200.0 | 550.0 | 3 |
| 3 | South | 150.0 | 150.0 | 3 |
| 4 | South | NULL | 150.0 | 3 |
| 6 | South | 175.0 | 325.0 | 3 |
Ordered-Set Aggregates (WITHIN GROUP)
WITHIN GROUP (ORDER BY ...)가 붙는 mode, percentile_cont, percentile_disc는 내장이고 기본으로 사용할 수 있어요 — 확장이 필요 없어요. 문법과 결과는 PostgreSQL과 일치해요. (표준 SQLite는 WITHIN GROUP을 지원하지 않아요.)
Ordered-set 집계는 ORDER BY 표현식의 값들을 그룹 안에서 정렬해 결과를 계산해요:
mode() WITHIN GROUP (ORDER BY sort_expr)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expr)
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expr)
ORDER BY 표현식이 집계되는 값이에요. percentile 함수에서는 WITHIN GROUP 앞의 인자가 백분위수 fraction이에요. ORDER BY 표현식의 NULL 값은 무시되고, non-NULL 값이 없으면 결과는 NULL이에요.
mode()
mode() WITHIN GROUP (ORDER BY X)
X의 가장 빈번한 값을 반환해요. 빈도가 같은 값이 여러 개면 가장 작은 값을 반환해요. 어떤 타입과도 동작하고 원래 타입으로 값을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any | 가장 빈번한 값을 구할 표현식 |
Return type: X와 동일
SELECT mode() WITHIN GROUP (ORDER BY product) FROM sales;
-- 'Widget' (the most frequent product)
SELECT region, mode() WITHIN GROUP (ORDER BY product) AS top_product
FROM sales
GROUP BY region;
| region | top_product |
|---|---|
| North | Widget |
| South | Widget |
mode()는 WITHIN GROUP과 함께 쓸 때만 유효해요; 없이 mode(X)를 호출하면 오류예요.
percentile_cont(fraction) and percentile_disc(fraction)
percentile_cont(fraction) WITHIN GROUP (ORDER BY X)
percentile_disc(fraction) WITHIN GROUP (ORDER BY X)
X의 fraction 번째 백분위수를 계산해요. fraction은 0.0과 1.0 사이예요:
percentile_cont는 연속적이고 보간된 값을 반환해요 (항상 REAL).percentile_disc는 이산 백분위수 위치의 실제 요소를 원래 타입으로 반환해요.
| Parameter | Type | Description |
|---|---|---|
| fraction | REAL | 백분위수 fraction, 0.0~1.0 |
| X | numeric (percentile_cont) / any (percentile_disc) |
백분위수를 계산할 값 |
Return type: percentile_cont는 REAL; percentile_disc는 X와 동일
-- Median, interpolated vs. an actual data point
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) FROM sales; -- 175.0
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY amount) FROM sales; -- 175.0
-- Quartiles per region
SELECT region,
percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
percentile_cont(0.75) WITHIN GROUP (ORDER BY amount) AS q3
FROM sales
WHERE amount IS NOT NULL
GROUP BY region;
fraction은 집계되는 행에 대해 상수여야 해요 — 리터럴, 상수 표현식, 또는 파라미터예요. 집계되는 컬럼을 참조할 수 없고, 범위를 벗어난 상수 fraction은 매치되는 행 수와 무관하게 보고돼요:
SELECT percentile_cont(2.0) WITHIN GROUP (ORDER BY amount) FROM sales;
-- Error: percentile value 2 is not between 0 and 1
Collation
텍스트 값은 적용 가능한 collation으로 정렬돼요 — ORDER BY 표현식의 명시적 COLLATE, 컬럼의 선언된 collation, 기본값은 BINARY — ORDER BY와 일관되게요.
Notes and limitations
ORDER BY표현식이 하나 필요해요. 여러 표현식,DESC,WITHIN GROUP안의NULLS FIRST/LAST는 아직 지원되지 않아요.- fraction 인자로 서브쿼리는 현재 지원되지 않아요.
- 이 집계들은
GROUP BY,FILTER (WHERE ...),HAVING, 서브쿼리, CTE, 조인, attach된 데이터베이스와 함께 동작해요. - percentile 확장은 비표준 두 인자 형태인
percentile_cont(Y, P)와percentile_disc(Y, P)도 제공해요 (아래 Extension Aggregate Functions 참고). 위의WITHIN GROUP형태가 SQL 표준 버전이고 확장이 필요 없어요.
Turso Extension: array_agg(X)
array_agg(X)는 Turso 확장이고 표준 SQLite에는 없어요. Turso에서는 추가 확장을 로드하지 않아도 기본으로 사용할 수 있어요.
array_agg(X)
X의 모든 값(NULL 포함)을 배열로 모아요. 그룹이 비어 있으면 NULL을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any | 배열로 모을 값의 표현식 |
Return type: BLOB (array)
SELECT array_agg(name) FROM users;
-- ["Alice","Bob","Charlie"]
SELECT department, array_agg(name) FROM employees GROUP BY department;
| department | array_agg(name) |
|---|---|
| Engineering | ["Alice","Bob"] |
| Sales | ["Charlie","Diana","Eve"] |
-- Control ordering with a subquery
SELECT array_agg(name) FROM (SELECT name FROM users ORDER BY name);
-- Combine with array_length
SELECT array_length(array_agg(name)) FROM users;
-- 3
더 많은 배열 함수는 Array Functions를 보세요.
Turso Extension: stddev(X)
stddev(X)는 Turso 확장이고 표준 SQLite에는 없어요. Turso에서는 추가 확장을 로드하지 않아도 기본으로 사용할 수 있어요.
stddev(X)
X의 모든 non-NULL 값의 모집단 표준 편차를 반환해요. non-NULL 값이 없으면 NULL을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any numeric | 표준 편차를 계산할 표현식 |
Return type: REAL
SELECT stddev(amount) FROM sales;
SELECT region, avg(amount) AS mean, stddev(amount) AS std_dev
FROM sales
GROUP BY region;
Extension Aggregate Functions
다음 집계 함수들은 percentile 확장을 통해 사용할 수 있어요. 사용 전에 로드하세요.
이 함수들은 percentile 확장이 필요해요. SELECT load_extension('./percentile');로 로드하거나 커넥션 설정으로 자동 로드하세요.
median(X)
median(X)
X의 모든 non-NULL 값의 중앙값(중간 값)을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| X | any numeric | 중앙값을 구할 표현식 |
Return type: REAL
SELECT median(amount) FROM sales;
-- 175.0
SELECT region, median(amount) AS median_amount
FROM sales
GROUP BY region;
percentile(Y, P)
percentile(Y, P)
Y의 모든 non-NULL 값의 P 번째 백분위수를 반환해요. 인접 값 사이에서 선형 보간을 사용해요.
| Parameter | Type | Description |
|---|---|---|
| Y | any numeric | 백분위수를 계산할 값 |
| P | REAL | 계산할 백분위수 (0.0~100.0) |
Return type: REAL
SELECT percentile(amount, 50) FROM sales; -- 50th percentile (median)
SELECT percentile(amount, 90) FROM sales; -- 90th percentile
SELECT percentile(amount, 25) FROM sales; -- 25th percentile (Q1)
percentile_cont(Y, P) and percentile_disc(Y, P)
percentile_cont(Y, P)
percentile_disc(Y, P)
이 두 인자 형태는 percentile 확장이 제공하는 편의 기능이에요. SQL 표준 표기인 percentile_cont(P) WITHIN GROUP (ORDER BY Y)는 내장이라 확장이 필요 없어요; 위의 Ordered-Set Aggregates를 보세요. percentile_cont는 연속(보간) 분포를 쓰고, percentile_disc는 이산 입력 값을 반환해요.
| Parameter | Type | Description |
|---|---|---|
| Y | any numeric | 백분위수를 계산할 값 |
| P | REAL | 백분위수 fraction (0.0~1.0) |
Return type: REAL
-- Continuous percentile (interpolates between values)
SELECT percentile_cont(amount, 0.5) FROM sales;
-- Discrete percentile (returns an actual input value)
SELECT percentile_disc(amount, 0.5) FROM sales;
P 범위 차이에 주의하세요: percentile(Y, P)는 P가 0100이고, 1.0이에요.percentile_cont와 percentile_disc는 P가 0.0
See Also
- Scalar Functions — 행별 함수
- Expressions — 연산자 문법, CAST, CASE, 서브쿼리
- SELECT — GROUP BY, HAVING, 윈도우 함수 문법
더 알아보기 (Learn more)
- 확장(Extensions) — percentile 확장 로드 방법
- 표현식(Expressions) — 연산자와 CAST 문법
- 데이터 타입 — 배열 타입과 array_agg 이해하기
- SQLite 호환성 — Turso 전용 집계 함수 지원 상태