흔한 함정: 집계에서의 NULL

집계 함수는 NULL을 제외합니다

흔한 함정: COUNT(*)와 COUNT(col)

SELECT COUNT(*)         FROM users;      -- 1000
SELECT COUNT(email)     FROM users;      -- 950  (50 users have NULL email)

버그: AVG는 NULL을 0으로 세지 않습니다

SELECT AVG(rating) FROM reviews;        -- 5
SELECT AVG(COALESCE(rating, 0)) FROM reviews;   -- 3.33

빈 테이블 = NULL 집계 결과

SELECT COALESCE(SUM(total), 0) AS revenue
FROM orders
WHERE user_id = 999999;       -- safe even when user has no orders

NULL 열의 SUM

NULL 그룹화 키와 GROUP BY

SELECT country, COUNT(*) FROM users
GROUP BY country;
-- → ('US', 100), ('CA', 30), (NULL, 5)

NULL이 있을 때 고유 값 세기

INSERT INTO t (c) VALUES (1),(2),(NULL),(NULL),(2);
SELECT COUNT(DISTINCT c) FROM t;   -- 2 (NULL not counted)

여러 열의 DISTINCT

SELECT COUNT(*) FROM (
  SELECT DISTINCT a, b FROM t
) sub;

선택적 테이블에 대한 암시적 조심스러운 조인

SELECT u.id, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;          -- COUNT(o.id) gives 0 for users with no orders

조건부 개수에 FILTER 사용하기

SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refund_count
FROM orders;

집계 함수는 항상 NULL로 테스트하기

복습

빠른 확인