데이터베이스 기초 (4) - Aggregation, Subqueries, and Window Functions

데이터베이스 기초 시리즈의 4편입니다. 전체 목차는 0편에 있습니다.

이번 편은 2편에서 만든 게시판 database(users, posts, comments)를 그대로 사용합니다. 실습 환경 구성은 PostgreSQL 편을 참고합니다. 3편에서 JOIN과 함께 썼던 GROUP BY와 count(*)를 규칙부터 다시 정리하고, 서브쿼리, CTE, CASE, window function으로 조회 문법을 확장합니다.

GROUP BY 제대로

GROUP BY에는 규칙이 하나 있습니다. GROUP BY에 없는 column은 집계 함수로 감싸지 않는 한 SELECT에 올 수 없습니다. SELECT user_id, title, count(*) FROM posts GROUP BY user_id를 실행하면 column "posts.title" must appear in the GROUP BY clause or be used in an aggregate function 오류가 납니다. 이유는 그룹에 값이 여러 개 있기 때문입니다. user_id가 1인 그룹에는 title이 ‘첫 글입니다’와 ‘PostgreSQL 질문 있습니다’ 두 개가 있어서, 한 row로 접힌 결과에 어느 title을 넣을지 정할 수 없습니다.

집계 함수에는 count 외에 sum, avg, min, max가 있습니다. GROUP BY 없이 쓰면 table 전체가 하나의 그룹이 됩니다. 집계할 숫자 값이 필요하므로 본문 글자 수를 반환하는 length()를 사용하고, 평균은 round()로 소수 둘째 자리까지 반올림합니다.

SELECT count(*) AS post_count, sum(length(body)) AS total_chars,
       round(avg(length(body)), 2) AS avg_chars,
       min(length(body)) AS min_chars, max(length(body)) AS max_chars
FROM posts;

글 4건의 본문 길이 합은 35자, 평균은 8.75자, 최소는 7자, 최대는 10자입니다. 사용자별 글 수는 다음과 같이 구하고, 결과는 alice 2건, bob 1건, carol 1건입니다.

SELECT u.username, count(*) AS post_count
FROM posts p JOIN users u ON u.id = p.user_id
GROUP BY u.username
ORDER BY post_count DESC, u.username;

집계 결과에 조건을 걸 때는 HAVING을 씁니다. WHERE는 그룹으로 묶기 전의 개별 row에 거는 조건이고, HAVING은 묶은 후의 집계 결과에 거는 조건입니다. count(*) 같은 집계 값은 WHERE가 평가되는 시점에 아직 존재하지 않으므로 WHERE에 쓸 수 없습니다. 댓글이 2개 이상 달린 글은 다음과 같이 찾습니다.

SELECT post_id, count(*) AS comment_count
FROM comments
GROUP BY post_id
HAVING count(*) >= 2
ORDER BY post_id;

결과는 글 1번(2개)과 2번(3개)입니다. 댓글이 1개인 3번 글은 HAVING 조건에서 걸러집니다.

서브쿼리

서브쿼리는 쿼리 안에 괄호로 넣는 또 다른 SELECT이고, 쓰이는 위치에 따라 세 형태로 나뉩니다. 첫째는 WHERE IN입니다. 댓글을 단 적 있는 사용자를 찾으려면 comments에서 user_id 목록을 뽑아 IN에 넣습니다. 시드 데이터에서는 세 명 모두 댓글을 달았으므로 alice, bob, carol이 전부 반환됩니다.

SELECT username FROM users
WHERE id IN (SELECT user_id FROM comments)
ORDER BY id;

둘째는 스칼라 서브쿼리입니다. 값 하나를 반환하는 서브쿼리를 비교식에 그대로 씁니다. 본문이 전체 평균(8.75자)보다 긴 글을 찾으면 7자인 3번 글만 제외되고 1번, 2번, 4번 글이 반환됩니다.

SELECT title, length(body) AS body_chars FROM posts
WHERE length(body) > (SELECT avg(length(body)) FROM posts)
ORDER BY id;

셋째는 FROM 서브쿼리입니다. 서브쿼리의 결과를 table처럼 취급해 그 위에 다시 쿼리를 겁니다. 사용자별 글 수를 먼저 구하고 그 평균을 내면 1.33건이 나옵니다.

SELECT round(avg(post_count), 2) AS avg_posts_per_user
FROM (
    SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
) AS per_user;

WHERE IN과 같은 일을 하는 다른 표현으로 EXISTS가 있습니다. SELECT username FROM users u WHERE EXISTS (SELECT 1 FROM comments c WHERE c.user_id = u.id) ORDER BY u.id는 위의 IN 쿼리와 같은 결과를 반환합니다. 바깥 쿼리의 row마다 조건에 맞는 row가 서브쿼리에 존재하는지만 확인하는 방식입니다. 서브쿼리가 반환하는 값 자체는 쓰이지 않으므로, 관례적으로 상수 1을 SELECT합니다.

CTE로 쿼리 정리

FROM 서브쿼리가 중첩되면 쿼리를 안쪽부터 읽어야 해서 따라가기 어렵습니다. CTE(Common Table Expression)는 WITH로 중간 결과에 이름을 붙여 쿼리가 위에서 아래로 읽히게 만듭니다. 3편에서 글별 댓글 수를 LEFT JOIN과 GROUP BY로 구했는데, 같은 계산을 CTE로 다시 쓰면 집계 단계와 필터 단계가 분리됩니다.

WITH comment_counts AS (
    SELECT p.id, p.title, count(c.id) AS comment_count
    FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    GROUP BY p.id, p.title
)
SELECT title, comment_count FROM comment_counts
WHERE comment_count >= 2
ORDER BY comment_count DESC;

count(column)은 NULL이 아닌 값만 세므로, LEFT JOIN에서 댓글 짝이 없는 글은 0으로 집계됩니다. comment_counts라는 중간 결과를 먼저 정의하고, 본 쿼리는 그것을 일반 table처럼 조회합니다. 집계가 이미 끝난 중간 결과이므로 HAVING 대신 WHERE로 거를 수 있습니다. 결과는 2번 글(3개)과 1번 글(2개)입니다. CTE는 쉼표로 이어서 여러 개를 정의할 수 있고 뒤의 CTE가 앞의 CTE를 참조할 수 있어서, 복잡한 쿼리를 단계별로 조립하기에 적합합니다.

CASE와 NULL 함수

CASE WHEN은 조건에 따라 다른 값을 만드는 표현식입니다. 댓글 수가 0이면 숫자 대신 ‘없음’을 표시하는 column을 만들어 봅니다.

SELECT p.title,
       CASE WHEN count(c.id) = 0 THEN '없음'
            ELSE count(c.id)::TEXT END AS comment_display
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title
ORDER BY p.id;

댓글이 없는 4번 글만 ‘없음’으로 표시됩니다. CASE의 각 분기는 같은 타입을 반환해야 하므로 count 결과를 ::TEXT로 문자열로 바꿨습니다. WHEN은 여러 개를 이어 쓸 수 있고, 어느 조건에도 맞지 않으면 ELSE 값이, ELSE가 없으면 NULL이 반환됩니다. 한편 COALESCE는 인자를 앞에서부터 확인해 처음 만나는 NULL 아닌 값을 반환하는 함수로, LEFT JOIN에서 짝이 없어 NULL이 된 자리를 기본값으로 채울 때 씁니다.

SELECT p.title, COALESCE(cc.comment_count, 0) AS comment_count
FROM posts p
LEFT JOIN (
    SELECT post_id, count(*) AS comment_count FROM comments GROUP BY post_id
) cc ON cc.post_id = p.id
ORDER BY p.id;

FROM 서브쿼리 cc에는 댓글이 있는 글 1, 2, 3번만 있으므로 4번 글의 comment_count 자리는 NULL이 되고, COALESCE가 이를 0으로 바꿉니다. 1편에서 본 대로 NULL이 낀 비교는 true가 아니라 NULL이 되므로, 이후 계산이나 비교에 쓸 값이라면 COALESCE로 채우고 시작하는 편이 안전합니다.

window function 기초

GROUP BY 집계는 여러 row를 한 row로 줄이지만, window function은 row 수를 유지한 채 각 row 옆에 계산 결과를 붙입니다. 함수 뒤에 OVER가 붙으면 window function이고, PARTITION BY가 계산 범위의 경계를 정합니다. 예를 들어 posts 조회에 count(*) OVER (PARTITION BY user_id)를 붙이면 결과는 4 row 그대로이고, alice의 글 2건 옆에는 2가, bob과 carol의 글 옆에는 1이 붙습니다. 같은 계산을 GROUP BY로 했다면 결과가 3 row로 줄었을 것입니다.

ROW_NUMBER()는 PARTITION BY로 나눈 범위 안에서 ORDER BY 순서대로 1부터 번호를 붙입니다. 사용자별 최신 글 1건을 뽑는 정형화된 패턴은 CTE와 결합해 번호가 1인 row만 남기는 것입니다.

WITH ranked AS (
    SELECT user_id, title,
           ROW_NUMBER() OVER (PARTITION BY user_id
                              ORDER BY created_at DESC, id DESC) AS rn
    FROM posts
)
SELECT user_id, title FROM ranked WHERE rn = 1 ORDER BY user_id;

시드의 글 4건은 한 INSERT 문으로 입력되어 created_at이 모두 같기 때문에, id를 두 번째 정렬 기준으로 두어 순서를 확정했습니다. 결과는 alice의 ‘PostgreSQL 질문 있습니다’, bob의 ‘중고 키보드 팝니다’, carol의 ‘이번 주 모임 공지’입니다. window function은 WHERE에 직접 쓸 수 없어서 이렇게 CTE나 FROM 서브쿼리로 감싼 뒤 거릅니다. 비슷한 함수로 RANK()가 있는데, ORDER BY 기준 값이 같은 row에는 같은 순위를 주고 그만큼 다음 순위를 건너뜁니다. 글 수가 2, 1, 1건인 세 사용자에게 글 수 순위를 매기면 1, 2, 2가 됩니다.

MySQL 차이

MySQL은 8.0부터 CTE와 window function을 지원하므로, 이 편의 WITH, ROW_NUMBER, OVER, PARTITION BY 쿼리는 MySQL 8.4에서도 그대로 동작합니다. GROUP BY 규칙도 같습니다. MySQL은 ONLY_FULL_GROUP_BY 모드가 5.7부터 기본으로 켜져 있어서, GROUP BY에 없는 column을 SELECT에 쓰면 PostgreSQL과 같은 종류의 오류가 납니다. 문법 차이는 두 가지입니다. ::TEXT는 PostgreSQL 전용 표기라서 MySQL에서는 CAST(count(c.id) AS CHAR)로 바꿔야 합니다. 그리고 MySQL의 length()는 byte 수를 반환하므로 글자 수를 세려면 char_length()를 써야 합니다.

다음 편

다음 5편에서는 데이터가 많아졌을 때 느려지는 쿼리를 B-tree index의 원리와 EXPLAIN ANALYZE 읽는 법으로 진단합니다.