집계 함수

집계 함수 (Aggregate Functions)

이번에는 여러 행을 한 번에 묶어 하나의 결과를 계산해주는 **집계 함수(aggregate function)**를 배워볼게요. COUNT, SUM, AVG, MAX, MIN 같은 것들이 대표적이에요. 날씨 테이블을 그대로 사용해 "가장 추웠던 날은 몇 도였는지" 같은 질문에 답해보면서 익혀볼게요.

출처: PostgreSQL 공식 문서 — Aggregate Functions

집계 함수란 (What is an aggregate)

대부분의 다른 관계형 데이터베이스 제품들처럼 PostgreSQL도 집계 함수를 지원해요. 집계 함수는 여러 입력 행으로부터 하나의 결과를 계산해요. 예를 들어, 행 집합에 대해 개수(count), 합계(sum), 평균(avg), 최댓값(max), 최솟값(min)을 구해주는 집계들이 있어요.

예를 들어, 어디에서든 가장 낮은 기온(음, 최저 온도 중 최댓값) 기록을 찾고 싶다면 이렇게 할 수 있어요:

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

만약 그 기록이 어떤 도시(들)에서 발생했는지 알고 싶다면, 이런 쿼리를 시도해볼 수도 있어요:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     -- WRONG

하지만 이건 동작하지 않아요. 집계 max는 WHERE 절에서 쓸 수 없기 때문이에요. (이 제한이 있는 이유는, WHERE 절이 어떤 행을 집계 계산에 포함할지를 결정하니까 당연히 집계 함수가 계산되기 전에 평가되어야 하기 때문이에요.) 다만 흔히 그렇듯이 쿼리를 다시 쓰면 원하는 결과를 얻을 수 있어요. 여기서는 **서브쿼리(subquery)**를 쓰면 돼요:

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

이건 괜찮은데, 서브쿼리가 바깥 쿼리에서 일어나는 일과는 별개로 자기만의 집계를 계산하는 독립적인 계산이기 때문이에요.

GROUP BY와 함께 쓰기 (Aggregates with GROUP BY)

집계 함수는 GROUP BY 절과 함께 쓸 때 매우 유용해요. 예를 들어, 각 도시의 관측 횟수와 관측된 최저 온도의 최댓값을 구할 수 있어요:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

도시마다 하나의 출력 행이 나오죠. 각 집계 결과는 그 도시와 일치하는 테이블 행들에 대해 계산돼요. 이렇게 그룹핑된 행들을 HAVING을 써서 필터링할 수도 있어요:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

이건 모든 temp_lo 값이 40 미만인 도시에 대해서만 같은 결과를 보여줘요. 마지막으로, 이름이 "S"로 시작하는 도시에만 관심이 있다면 이렇게 할 수 있어요:

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

(1) LIKE 연산자는 패턴 매칭을 수행하며, 자세한 내용은 9.7절에서 다뤄요.

WHERE와 HAVING의 차이 (WHERE vs HAVING)

집계와 SQL의 WHERE·HAVING 절 사이의 상호작용을 이해하는 게 중요해요. WHERE와 HAVING의 근본적인 차이는 이래요:

  • **WHERE**는 그룹과 집계가 계산되기 전에 입력 행을 고릅니다. 즉 어느 행이 집계 계산에 들어갈지를 제어해요.
  • **HAVING**은 그룹과 집계가 계산된 후에 그룹 행을 고릅니다.

따라서 WHERE 절에는 집계 함수가 들어가면 안 돼요. 집계로 "어느 행이 집계의 입력이 될지"를 정하려는 건 말이 안 되니까요. 반면 HAVING 절은 항상 집계 함수를 포함해요. (엄밀히 말하면 집계를 쓰지 않는 HAVING을 쓸 수도 있지만, 거의 쓸모가 없어요. 같은 조건을 WHERE 단계에서 더 효율적으로 처리할 수 있으니까요.)

앞선 예제에서는 도시 이름 제한을 WHERE에 적용했는데, 이건 집계가 필요 없는 조건이라서 그래요. 그 제한을 HAVING에 넣는 것보다 더 효율적이에요. WHERE 검사를 통과하지 못하는 모든 행에 대해 그룹핑과 집계 계산을 할 필요가 없어지거든요.

FILTER로 행 고르기 (Filtering with FILTER)

집계 계산에 들어갈 행을 고르는 또 다른 방법은 FILTER를 쓰는 거예요. 이건 집계마다 붙는 옵션이에요:

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)

FILTER는 WHERE와 아주 비슷하지만, 자기가 붙어 있는 그 특정 집계 함수의 입력에서만 행을 제거한다는 점이 달라요. 여기서 count 집계는 temp_lo가 45 미만인 행만 세지만, max 집계는 여전히 모든 행에 적용되므로 46이라는 기록도 찾아내는 거예요.

더 알아보기 (Learn more)