이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

기초개념 › DB

GROUP BY와 집계

GROUP BY는 무엇을 묶고, COUNT와 SUM은 무엇을 세는가

COUNT(*)와 SUM(price)는 여러 행을 값 하나로 줄이는 집계 함수입니다. COUNT(*)는 행이 몇 개인지, SUM(price)는 price를 모두 더하면 얼마인지를 돌려줍니다. GROUP BY member는 SELECT 문 뒤에 붙여서, member 값이 같은 행끼리 한 묶음으로 만들고 묶음마다 집계를 한 번씩 하게 합니다. 묶음 하나는 결과의 한 줄이 됩니다. 이 실습은 주문 5건이 든 orders 테이블에 같은 집계를 하면서, GROUP BY member를 지우고, 묶지 않은 열 id를 SELECT에 더 적고, WHERE 조건을 넣어 가며 결과가 몇 줄이고 어떤 모양인지를 봅니다.

실행으로 대조 · H2 2.4.240에서 여섯 가지 조합을 실행해 대조, 규칙은 MySQL 문서와 대조

이 실습에 나오는 개념

GROUP BY 절

SELECT 문 뒤에 GROUP BY 열 이름으로 적어, 그 열의 값이 같은 행끼리 한 묶음(그룹)으로 만드는 절이다. 집계 함수는 묶음마다 한 번씩 계산되고, 묶음 하나가 결과의 한 줄이 된다. 그래서 결과의 행 수는 원래 행의 수가 아니라 묶음의 수이다. GROUP BY를 적지 않고 집계 함수를 쓰면 조건에 맞는 모든 행이 한 묶음이 되어 결과가 한 행이다. WHERE로 행을 거르는 일은 묶기 전에 일어나므로, WHERE는 어느 행을 묶을지를 정한다.

집계 함수

여러 행의 값을 모아 값 하나를 돌려주는 함수이다. COUNT(개수), SUM(합계), AVG(평균), MAX(최댓값), MIN(최솟값)이 대표적이다. GROUP BY가 있으면 그룹마다 한 번씩 계산하고, 없으면 조건에 맞는 행 전체를 한 그룹으로 보고 계산한다. 그룹이 만들어진 뒤에야 계산할 수 있으므로 WHERE에는 쓸 수 없고 HAVING, SELECT, ORDER BY에 쓴다. COUNT(*)를 뺀 집계 함수는 NULL인 값을 건너뛰고 계산한다.

COUNT와 SUM

COUNT(*)와 SUM(열)은 대표적인 집계 함수이다. COUNT(*)는 묶음 안의 행이 몇 개인지를 돌려주며, 값이 NULL인 열이 있어도 행으로 센다. COUNT(열)은 그 열이 NULL이 아닌 행만 센다. SUM(열)은 묶음 안에서 그 열의 값을 모두 더한 값을 돌려준다. GROUP BY가 있으면 묶음마다, 없으면 모든 행에 대해 한 번 계산한다.

묶지 않은 열과 ONLY_FULL_GROUP_BY

집계를 하는 SELECT에서 GROUP BY에 적지도 않았고 집계 함수 안에 있지도 않은 열을 묶지 않은 열이라고 부른다. 묶음 하나가 결과의 한 줄이 되는데 묶음 안에는 그 열의 값이 여럿일 수 있어서, 한 줄에 무엇을 적을지 정할 수 없다. MySQL은 SQL 모드 ONLY_FULL_GROUP_BY가 켜져 있으면(8.4에서 기본으로 켜져 있다) 묶지 않은 열을 SELECT, HAVING, ORDER BY에 쓴 문장을 거부한다. GROUP BY 없이 집계 함수를 쓴 문장에서도 같다. 다만 GROUP BY에 적은 열이 그 열의 값을 하나로 정해 주는 경우(예: 기본 키)는 허용하고, 값을 하나로 줄이려면 MAX 같은 집계 함수 안에 넣거나 ANY_VALUE를 쓴다. 데이터베이스마다 이 규칙을 검사하는 방식이 다르다.

이렇게 바꿔 보세요

• 처음 설정으로 — member가 같은 행끼리 세 묶음이 되고, 묶음마다 한 줄씩 3행이 나옵니다.
• GROUP BY를 지우면 — 묶음이 테이블 전체 하나뿐이어서 결과가 한 행입니다.
• id를 SELECT에 더 적으면 — 묶은 열도 집계도 아닌 id는 적을 수 없고 오류가 납니다.
• WHERE 조건을 넣으면 — 묶기 전에 행이 걸러져서 lee 묶음의 개수와 합계가 달라집니다.
• GROUP BY 없이 id를 적으면 — 집계와 함께 id를 적으면, 묶음이 하나뿐이어도 오류가 납니다.
• GROUP BY 없이 WHERE만 넣으면 — 걸러진 4행이 한 묶음이 되어 결과가 한 행입니다.

처음 설정으로 실행하면

1. CREATE TABLE과 INSERT: DB가 orders 테이블을 만들고 행 5개를 넣습니다. 열은 id(주문 번호), member(주문한 사람), price(금액)입니다. kim이 2건, lee가 2건, park이 1건 주문했습니다. 아래 문장은 이 5행을 읽습니다.
2. SELECT member, COUNT(*), SUM(price) FROM orders GROUP BY member: orders의 행을 member 값이 같은 것끼리 묶고, 묶음마다 COUNT(*)와 SUM(price) 두 가지 집계를 한 번씩 계산해 한 줄씩 보여 달라는 문장입니다. COUNT(*)는 묶음 안의 행 수이고 SUM(price)는 묶음 안의 price를 모두 더한 값입니다. 묶고 세는 코드를 우리는 쓰지 않았습니다. DB가 이 문장을 받아 합니다.
3. GROUP BY member: DB가 5개 행을 member 값이 같은 것끼리 묶습니다. member가 kim인 행(id 1, 2), member가 lee인 행(id 3, 4), member가 park인 행(id 5)이 각각 한 묶음이 되어, 묶음은 3개입니다. 묶음 안의 행 순서나 묶는 방법은 DB가 정합니다.
4. kim 묶음(id 1, 2)에서 COUNT(*)는 묶음 안의 행 수라 2이고, SUM(price)는 price를 모두 더한 1000 + 2000 = 3000입니다. 묶음마다 한 줄이 나오므로 (kim, 2, 3000) 한 줄을 결과에 넣습니다.
5. lee 묶음(id 3, 4): COUNT(*)는 2이고 SUM(price)는 500 + 1500 = 2000입니다. (lee, 2, 2000) 한 줄을 결과에 넣습니다.
6. park 묶음(id 5): COUNT(*)는 1이고 SUM(price)는 3000입니다. (park, 1, 3000) 한 줄을 결과에 넣습니다.
7. SELECT의 결과: 결과는 3행입니다. 주문은 5건이었지만 결과의 행 수는 행의 수가 아니라 묶음의 수입니다. ORDER BY member 때문에 member 순서로 나옵니다.

실행 결과

• GROUP BY member는 member 값이 같은 행끼리 한 묶음으로 만들고, 묶음마다 COUNT(*)와 SUM(price)를 한 번씩 계산해 한 줄씩 냅니다. 주문 5행이 kim, lee, park 세 묶음이 되어 결과는 3행입니다.
• 결과의 행 수는 원래 행의 수가 아니라 묶음의 수입니다. 묶음마다 한 줄이 나오므로 SELECT에는 묶은 열(member)과 묶음마다 값 하나가 나오는 집계(COUNT, SUM)를 적습니다.

코드

CREATE TABLE orders (id INT PRIMARY KEY, member VARCHAR(20), price INT);
INSERT INTO orders VALUES (1, 'kim', 1000), (2, 'kim', 2000),
    (3, 'lee', 500), (4, 'lee', 1500), (5, 'park', 3000);

SELECT member, COUNT(*), SUM(price)
FROM orders
-- GROUP BY: member 값이 같은 행끼리 한 묶음으로 만든다
GROUP BY member
ORDER BY member;

이 모형의 전제

H2 2.4.240(메모리 DB, JDBC)에서 실행해 대조했습니다. 여섯 가지 조합을 모두, 화면의 SQL 전체(CREATE TABLE, INSERT, SELECT와 주석 줄)를 글자 그대로 문장 하나씩 실행해서, SELECT가 돌려준 행 수와 모든 행의 값, 오류가 났는지와 그 오류의 종류를 모형과 대조했습니다. 오류가 난 두 조합은 H2의 오류 코드 90016(id 열이 GROUP BY 목록에 있어야 한다는 메시지)이었고, 화면에는 그 종류만 한국어로 적었습니다. 단계 문장이 기대는 값도 H2에서 따로 확인했습니다: 행 수 5와 전체 합계 8000, price가 1000 이상인 행의 id(1, 2, 4, 5), 이름별 id 목록(kim은 1과 2, lee는 3과 4, park은 5)과 WHERE를 넣었을 때의 목록(lee는 4만), 이름별 서로 다른 id 수, 묶음이 3개라는 것, MAX(id)를 적은 문장이 오류 없이 실행된다는 것입니다. 예상과 달랐던 것: H2는 id를 적은 문장을 해석하는 단계(prepareStatement)에서는 오류를 내지 않았고, 실행하면서 한 묶음 안에 서로 다른 id가 있을 때 오류를 냈습니다. 그래서 park 행만 남기거나 묶음마다 id가 하나씩뿐이게(id 1, 3, 5) 행을 남기면 같은 모양의 문장이 오류 없이 실행되었습니다. 화면의 오류 단계 문장은 이 관찰을 따라 쓴 것입니다. 정의와 규칙은 문서를 열어 확인했습니다. MySQL 8.4 문서 14.19.1절 Aggregate Function Descriptions에서 집계 함수는 값의 집합에 대해 동작하고, GROUP BY 없이 쓰면 모든 행을 한 그룹으로 묶은 것과 같으며, COUNT(*)는 NULL이 있어도 행 수를 센다고 합니다. 14.19.3절 MySQL Handling of GROUP BY에서는 SQL 모드 ONLY_FULL_GROUP_BY가 기본으로 켜져 있고, GROUP BY에 적지 않았고 함수적으로 종속되지도 않은 열을 SELECT에 쓴 문장을 거부하며, GROUP BY 없이 집계 함수를 쓴 문장에서도 같다고 합니다. 15.2.13절 SELECT 문에서는 WHERE 조건에 맞는 행이 선택되고 WHERE에는 집계 함수를 쓸 수 없다고 합니다. H2는 문법 페이지(grammar.html)에 SELECT 설명이 없어 Commands 페이지의 SELECT를 열었습니다. GROUP BY는 식으로 결과를 묶고, GROUP BY 없이 집계 함수를 쓰면 모든 행이 함께 묶이며, 논리적인 실행 순서에서 WHERE가 GROUP BY보다 앞이라고 합니다. 네 문서 모두 웹 페이지를 요약해 주는 도구로 열었고 원문을 한 글자씩 확인한 것은 아닙니다. MySQL에서의 실행 대조는 하지 않았습니다. 따라서 MySQL이 같은 문장에서 같은 결과와 오류를 내는지는 실행으로 확인하지 않았고, 문서에 따르면 MySQL은 id의 값이 몇 개인지 보지 않고 문장에 적힌 열의 종류만 보고 거부하므로 H2와 오류를 내는 조건이 다릅니다. 화면의 INSERT는 5행 모두 그대로 보입니다. SELECT 끝에 ORDER BY member를 붙인 것은 묶음을 member 순서로 보이게 하려는 것이고, GROUP BY member를 지우면 결과에 member가 없으므로 ORDER BY member도 함께 지웠습니다. 단계 문장은 결과가 만들어지는 원리를 순서대로 풀어 쓴 것입니다. 실제로 DB가 행을 어떤 방법으로 묶는지는 DB가 정하며, 실행으로 확인한 것은 결과입니다. 이 실습은 HAVING, 둘 이상의 열로 묶기, NULL이 든 열, COUNT(열)과 AVG, 조인과 함께 쓰는 집계, ANY_VALUE를 다루지 않습니다. 집계한 뒤 조건을 거는 HAVING과 SELECT가 처리되는 순서는 "SELECT가 처리되는 순서"에서, 행을 합치지 않고 순위를 붙이는 방법은 "윈도우 함수"에서, 조인한 결과를 집계하는 것은 "JOIN과 GROUP BY"에서 다룹니다.

앞 실습: 조인 · 다음 실습: 인덱스