[PostgreSQL] FILTER 절 조건별 카운트를 한 번의 스캔으로 나눠 센다

설치·접속: PostgreSQL 설치와 접속

부제: "전체 주문 수 / 대량주문 수 / 단품주문 수 / 평균 수량"을 서브쿼리 여러 개 없이 한 줄로 뽑고 싶을 때

-- 집계 함수마다 다른 조건을 걸어 한 번의 테이블 스캔으로 나눠 센다
SELECT count(*)                             AS total,
       count(*) FILTER (WHERE qty >= 4)     AS bulk,
       count(*) FILTER (WHERE qty = 1)      AS single,
       round(avg(qty), 2)                   AS avg_qty
FROM orders;

 total | bulk | single | avg_qty
-------+------+--------+---------
   200 |  108 |     21 |    3.55
(1 row)

FILTER (WHERE ...)는 그 집계 함수에만 적용되는 조건이다. 조건마다 서브쿼리를 따로 만들거나 테이블을 여러 번 스캔할 필요 없이, 한 번 훑으면서 열마다 다른 필터로 나눠 센다. CASE WHEN을 sum() 안에 넣는 예전 방식(sum(case when qty>=4 then 1 else 0 end))보다 의도가 훨씬 또렷하게 읽힌다. 조건별 매출, 상태별 건수, 기간별 비교 같은 "한 줄 요약 리포트"에 특히 잘 맞는다. GROUP BY와 함께 쓰면 그룹마다 조건별 수치를 펼칠 수 있다.

이렇게도 쓴다

상품별로 대량주문 건수와 단품 비율을 함께 낸다. (조합: GROUP BY)

SELECT product_id,
       count(*) AS n,
       count(*) FILTER (WHERE qty >= 4) AS bulk,
       round(100.0 * count(*) FILTER (WHERE qty = 1) / count(*), 1) AS single_pct
FROM orders GROUP BY product_id ORDER BY product_id;

 

조건별 합계(매출 분해)를 sum에 건다.

SELECT sum(qty) AS total_qty,
       sum(qty) FILTER (WHERE qty >= 4) AS bulk_qty
FROM orders;

 

여러 기간을 한 행에서 나란히 비교한다.

SELECT count(*) FILTER (WHERE ordered_at >= now() - interval '7 day')  AS last_7d,
       count(*) FILTER (WHERE ordered_at >= now() - interval '30 day') AS last_30d
FROM orders;

 

조건에 맞는 고유값만 센다. (조합: count(DISTINCT ...))

SELECT count(DISTINCT user_id) FILTER (WHERE qty >= 4) AS bulk_buyers
FROM orders;

 

FILTER 없이 표준 SQL로 쓰면 이렇게 된다(비교용). (조합: CASE)

SELECT sum(CASE WHEN qty = 1 THEN 1 ELSE 0 END) AS single FROM orders;

 

불리언 집계에도 조건을 건다. (조합: bool_or)

SELECT user_id, bool_or(qty >= 5) FILTER (WHERE product_id < 10) AS big_early
FROM orders GROUP BY user_id LIMIT 5;