FILTER 절

FILTER 절 (FILTER Clause)

FILTER 절은 SELECT 문에서 집계 함수 뒤에 선택적으로 붙을 수 있어요. WHERE 절이 행을 필터링하는 것처럼, 집계 함수로 들어가는 데이터 행을 필터링하되 특정 집계 함수에 국한해서 적용할 수 있어요. 피벗(pivot) 작업을 할 때 특히 깔끔한 문법을 제공한답니다.

출처: 문서

본문

FILTER 절은 SELECT 문에서 집계 함수 뒤에 선택적으로 붙을 수 있어요. 이는 WHERE 절이 행을 필터링하는 것과 같은 방식으로 집계 함수로 들어가는 데이터 행을 필터링하되, 특정 집계 함수에 국한해서 적용해요.

서로 다른 필터로 여러 집계를 평가하거나 데이터셋의 피벗 뷰를 만들 때 등 여러 상황에서 유용해요. FILTER는 아래에서 다루는 더 전통적인 CASE WHEN 접근 방식과 비교할 때 데이터 피벗에 더 깔끔한 문법을 제공해요.

또한 일부 집계 함수는 NULL 값을 필터링하지 않기 때문에, CASE WHEN 접근 방식으로는 올바른 결과가 나오지 않을 때도 FILTER 절을 쓰면 유효한 결과를 얻을 수 있어요. 이는 first와 last 함수에서 발생하는데, 단순히 데이터를 재집계하지 않고 컬럼으로 재배향(re-orient)하려는 비집계 피벗 연산에서 바람직해요. FILTER는 또한 list와 array_agg 함수를 사용할 때 NULL 처리를 개선해요 — CASE WHEN 접근 방식은 목록 결과에 NULL 값을 포함하는 반면, FILTER 절은 이를 제거하기 때문이에요.

예시 (Examples)

다음을 반환해 보세요:

• 전체 행 수
• i <= 5인 행 수
• i가 홀수인 행 수

SELECT
    count() AS total_rows,
    count() FILTER (i <= 5) AS lte_five,
    count() FILTER (i % 2 = 1) AS odds
FROM generate_series(1, 10) tbl(i);

조건을 만족하는 행을 단순히 세는 것은 FILTER 절 없이도 불리언 sum 집계 함수로 가능해요. 예: sum(i <= 5).

서로 다른 집계 함수를 사용할 수 있고, 여러 WHERE 표현식도 허용돼요:

SELECT
    sum(i) FILTER (i <= 5) AS lte_five_sum,
    median(i) FILTER (i % 2 = 1) AS odds_median,
    median(i) FILTER (i % 2 = 1 AND i <= 5) AS odds_lte_five_median
FROM generate_series(1, 10) tbl(i);

FILTER 절은 데이터를 행에서 컬럼으로 피벗하는 데도 사용할 수 있어요. 이는 정적 피벗이에요 — SQL에서 컬럼이 런타임 전에 정의되어야 하기 때문이에요. 하지만 이런 종류의 문은 호스트 프로그래밍 언어에서 동적으로 생성해 DuckDB의 SQL 엔진을 활용해 메모리보다 큰 데이터를 빠르게 피벗할 수 있어요.

먼저 예제 데이터셋을 생성해 보세요:

CREATE TEMP TABLE stacked_data AS
    SELECT
        i,
        CASE WHEN i <= rows * 0.25  THEN 2022
             WHEN i <= rows * 0.5   THEN 2023
             WHEN i <= rows * 0.75  THEN 2024
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL
             END AS year
    FROM (
        SELECT
            i,
            count(*) OVER () AS rows
        FROM generate_series(1, 100_000_000) tbl(i)
    ) tbl;

데이터를 연도별로 "피벗"(각 연도를 별도 컬럼으로 이동)해 보세요:

SELECT
    count(i) FILTER (year = 2022) AS "2022",
    count(i) FILTER (year = 2023) AS "2023",
    count(i) FILTER (year = 2024) AS "2024",
    count(i) FILTER (year = 2025) AS "2025",
    count(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

이 문법은 위의 FILTER 절과 같은 결과를 만들어요:

SELECT
    count(CASE WHEN year = 2022 THEN i END) AS "2022",
    count(CASE WHEN year = 2023 THEN i END) AS "2023",
    count(CASE WHEN year = 2024 THEN i END) AS "2024",
    count(CASE WHEN year = 2025 THEN i END) AS "2025",
    count(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

하지만 CASE WHEN 접근 방식은 NULL 값을 무시하지 않는 집계 함수를 사용할 때는 예상대로 작동하지 않아요. first 함수가 이 범주에 속하므로, 이 경우 FILTER가 선호돼요.

데이터를 연도별로 "피벗"(각 연도를 별도 컬럼으로 이동)해 보세요:

SELECT
    first(i) FILTER (year = 2022) AS "2022",
    first(i) FILTER (year = 2023) AS "2023",
    first(i) FILTER (year = 2024) AS "2024",
    first(i) FILTER (year = 2025) AS "2025",
    first(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

CASE WHEN 절의 첫 평가가 NULL을 반환할 때마다 NULL 값을 만들어내요:

SELECT
    first(CASE WHEN year = 2022 THEN i END) AS "2022",
    first(CASE WHEN year = 2023 THEN i END) AS "2023",
    first(CASE WHEN year = 2024 THEN i END) AS "2024",
    first(CASE WHEN year = 2025 THEN i END) AS "2025",
    first(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

집계 함수 문법 (FILTER 절 포함) (Aggregate Function Syntax)

더 알아보기 (Learn more)

• 집계 함수 — count, sum, avg 등.
• GROUP BY 절 — 그룹핑과 집계.