보고서 쿼리는 행을 요약하는 일에서 시작한다
지금까지는 주로 "어떤 행을 가져올 것인가"를 다뤘다. 실무 화면은 여기서 한 단계 더 나아간다. 관리자 대시보드는 오늘 매출, 상품별 판매 수량, 고객별 주문 횟수, 월별 성장률을 보여 달라고 요구한다. 이때 필요한 것이 집계와 윈도 함수다.
집계 함수는 여러 행을 하나의 값으로 요약한다. PostgreSQL에서 자주 쓰는 기본 집계 함수는 다음과 같다.1
COUNT(*): 행 수를 센다.COUNT(column): 해당 열이NULL이 아닌 행만 센다.SUM(column): 합계를 구한다.AVG(column): 평균을 구한다.MIN(column),MAX(column): 최솟값과 최댓값을 구한다.
COUNT를 제외한 대부분의 집계 함수는 대상 행이 없으면 0이 아니라 NULL을 반환한다. 보고서에서 빈 값을 0으로 보여야 한다면 coalesce를 함께 사용한다.
SELECT
coalesce(sum(total_amount), 0) AS total_sales
FROM orders
WHERE status = 'paid'
AND ordered_at >= date '2026-05-01'
AND ordered_at < date '2026-06-01';이 쿼리는 2026년 5월에 결제 완료된 주문의 총매출을 구한다. 조건에 맞는 주문이 하나도 없더라도 결과를 NULL이 아닌 0으로 보여 준다.
AI 생성 이미지
GROUP BY로 기준별 요약 만들기
GROUP BY는 같은 기준을 가진 행을 묶은 뒤, 묶음마다 집계 값을 계산한다. 예를 들어 일별 매출은 주문을 날짜별로 묶고 각 날짜 안에서 금액을 합산하면 된다.
SELECT
ordered_at::date AS order_date,
count(*) AS order_count,
sum(total_amount) AS daily_sales,
avg(total_amount) AS avg_order_amount
FROM orders
WHERE status = 'paid'
GROUP BY ordered_at::date
ORDER BY order_date;결과의 한 행은 주문 한 건이 아니라 "하루"를 뜻한다. 이것이 집계 쿼리를 읽을 때 가장 중요한 감각이다. GROUP BY ordered_at::date를 쓰는 순간 결과 단위가 주문에서 날짜로 바뀐다.
고객별 주문 횟수도 같은 방식으로 계산할 수 있다.
SELECT
c.customer_id,
c.name,
count(o.order_id) AS paid_order_count,
coalesce(sum(o.total_amount), 0) AS paid_total_amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY paid_total_amount DESC;여기서는 LEFT JOIN을 사용했다. 주문이 없는 고객도 결과에 남기기 위해서다. 이때 COUNT(*)를 쓰면 고객 행 자체가 세어져 주문이 없어도 1이 나올 수 있다. 그래서 주문 테이블의 식별자인 count(o.order_id)를 사용한다.
집계 쿼리를 작성할 때는 SELECT에 있는 일반 열이 GROUP BY에도 있어야 한다. 위 쿼리에서 c.name을 보여 주려면 GROUP BY c.customer_id, c.name처럼 묶는 기준에 포함해야 한다. 집계 함수 안에 들어간 sum(o.total_amount) 같은 표현은 GROUP BY에 넣지 않는다.
WHERE와 HAVING은 적용 시점이 다르다
WHERE와 HAVING은 모두 조건을 걸지만 적용 시점이 다르다. WHERE는 집계 전에 원본 행을 거른다. HAVING은 GROUP BY로 묶고 집계 값을 계산한 뒤 그룹을 거른다.2
예를 들어 "결제 완료 주문만 대상으로 고객별 매출을 계산하되, 총매출이 100만 원 이상인 고객만 보고 싶다"면 다음처럼 쓴다.
SELECT
customer_id,
count(*) AS order_count,
sum(total_amount) AS total_sales
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING sum(total_amount) >= 1000000
ORDER BY total_sales DESC;이 쿼리의 실행 흐름을 말로 풀면 다음과 같다.
orders에서 읽는다.WHERE status = 'paid'로 결제 완료 주문만 남긴다.customer_id별로 묶는다.고객별 주문 수와 매출 합계를 계산한다.
HAVING으로 총매출 100만 원 이상 그룹만 남긴다.매출이 큰 순서로 정렬한다.
다음 쿼리는 잘못된 예다.
SELECT customer_id, sum(total_amount) AS total_sales
FROM orders
WHERE sum(total_amount) >= 1000000
GROUP BY customer_id;WHERE 단계에서는 아직 고객별 합계가 계산되지 않았다. 그래서 집계 함수 조건은 HAVING에 둬야 한다. 반대로 status = 'paid'처럼 원본 행에 바로 판단할 수 있는 조건은 WHERE에 두는 편이 낫다. 불필요한 행을 먼저 줄일 수 있기 때문이다.
집계마다 다른 조건을 적용하고 싶다면 FILTER도 유용하다.
SELECT
customer_id,
count(*) AS total_orders,
count(*) FILTER (WHERE status = 'paid') AS paid_orders,
count(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id;한 번의 그룹화로 전체 주문 수, 결제 주문 수, 취소 주문 수를 함께 계산한다.
윈도 함수는 행을 유지한 채 계산한다
GROUP BY는 여러 행을 하나의 행으로 줄인다. 반면 윈도 함수는 각 행을 유지하면서 관련 행들을 참고해 값을 계산한다. 그래서 주문 목록을 그대로 보여 주면서 고객별 주문 순번, 이전 주문과의 차이, 누적 매출을 함께 표시할 수 있다.
윈도 함수는 항상 OVER 절과 함께 사용한다. PARTITION BY는 계산 범위를 나누고, ORDER BY는 그 범위 안의 순서를 정한다.3
SELECT
order_id,
customer_id,
ordered_at,
total_amount,
row_number() OVER (
PARTITION BY customer_id
ORDER BY ordered_at
) AS customer_order_no
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at;이 쿼리는 고객별 결제 주문 순번을 붙인다. PARTITION BY customer_id 때문에 고객마다 순번이 1부터 다시 시작한다.
상품별 매출 순위를 구할 때는 ROW_NUMBER와 RANK의 차이를 알아야 한다.
WITH product_sales AS (
SELECT
p.product_id,
p.name,
sum(oi.quantity * oi.unit_price) AS sales_amount
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
GROUP BY p.product_id, p.name
)
SELECT
product_id,
name,
sales_amount,
row_number() OVER (ORDER BY sales_amount DESC) AS row_no,
rank() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales
ORDER BY sales_rank, product_id;ROW_NUMBER는 같은 매출이어도 서로 다른 번호를 붙인다. RANK는 같은 매출이면 같은 순위를 주고 다음 순위를 건너뛴다. 예를 들어 1위가 두 개면 다음 순위는 3위가 된다. "정확히 10개 행만 보여 주기"에는 ROW_NUMBER, "공동 순위를 인정하기"에는 RANK가 어울린다.
윈도 함수 결과로 필터링하려면 한 번 감싸야 한다. 윈도 함수는 WHERE보다 나중에 계산되기 때문이다.
WITH ranked_products AS (
SELECT
p.product_id,
p.name,
sum(oi.quantity * oi.unit_price) AS sales_amount,
rank() OVER (ORDER BY sum(oi.quantity * oi.unit_price) DESC) AS sales_rank
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
GROUP BY p.product_id, p.name
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 10
ORDER BY sales_rank;LAG, LEAD, 누적 합계로 변화 추적하기
LAG는 이전 행의 값을 가져오고, LEAD는 다음 행의 값을 가져온다. 월별 매출에서 전월 대비 증감을 구할 때 자주 사용한다.
WITH monthly_sales AS (
SELECT
date_trunc('month', ordered_at)::date AS month,
sum(total_amount) AS sales_amount
FROM orders
WHERE status = 'paid'
GROUP BY date_trunc('month', ordered_at)::date
)
SELECT
month,
sales_amount,
lag(sales_amount) OVER (ORDER BY month) AS prev_month_sales,
sales_amount - lag(sales_amount) OVER (ORDER BY month) AS diff_from_prev_month
FROM monthly_sales
ORDER BY month;첫 달은 이전 달이 없으므로 prev_month_sales가 NULL이다. 보고서에서 0으로 표시하고 싶다면 coalesce를 사용할 수 있지만, 의미상 "이전 달이 없음"과 "이전 달 매출이 0"은 다르다. 분석 쿼리에서는 이 차이를 무심코 지우지 않는 것이 좋다.
누적 매출은 집계 함수 SUM을 윈도 함수처럼 사용해 계산한다.
WITH daily_sales AS (
SELECT
ordered_at::date AS order_date,
sum(total_amount) AS sales_amount
FROM orders
WHERE status = 'paid'
GROUP BY ordered_at::date
)
SELECT
order_date,
sales_amount,
sum(sales_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sales
FROM daily_sales
ORDER BY order_date;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 "처음 행부터 현재 행까지"를 뜻한다. 누적값을 계산할 때는 이 범위를 명시하는 습관이 좋다. 정렬 기준에 같은 값이 있을 때 기본 프레임이 예상과 다르게 동작하는 일을 줄일 수 있다.
GROUPING SETS, ROLLUP, CUBE로 여러 요약을 한 번에 만들기
보고서에는 여러 수준의 합계가 함께 필요하다. 예를 들어 일자별·카테고리별 매출, 일자별 전체 매출, 카테고리별 전체 매출, 전체 매출을 모두 보여 달라는 요구가 나온다. 각각 쿼리를 따로 작성할 수도 있지만 PostgreSQL은 이를 한 번에 계산하는 문법을 제공한다.
GROUPING SETS는 원하는 묶음 기준을 직접 나열한다.
SELECT
o.ordered_at::date AS order_date,
p.category,
sum(oi.quantity * oi.unit_price) AS sales_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
GROUP BY GROUPING SETS (
(o.ordered_at::date, p.category),
(o.ordered_at::date),
(p.category),
()
)
ORDER BY order_date NULLS LAST, category NULLS LAST;()는 전체 합계를 뜻한다. 결과에서 order_date나 category가 NULL인 행은 소계나 총계일 수 있다. 다만 실제 데이터에도 NULL 카테고리가 있을 수 있으므로, 엄격히 구분하려면 GROUPING() 함수를 함께 사용한다.
SELECT
o.ordered_at::date AS order_date,
p.category,
grouping(o.ordered_at::date) AS g_order_date,
grouping(p.category) AS g_category,
sum(oi.quantity * oi.unit_price) AS sales_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
GROUP BY GROUPING SETS (
(o.ordered_at::date, p.category),
(o.ordered_at::date),
(p.category),
()
);grouping(...) 결과가 1이면 해당 열이 현재 요약 기준에서 빠졌다는 뜻이다. 이를 이용하면 애플리케이션에서 상세, 소계, 총계를 구분하기 쉽다.1
ROLLUP은 계층형 합계에 적합하다.
GROUP BY ROLLUP (o.ordered_at::date, p.category)이 문법은 대략 다음 묶음을 만든다.
(order_date, category)(order_date)()
즉 날짜 안의 카테고리별 매출, 날짜별 소계, 전체 합계를 만든다.
CUBE는 가능한 모든 조합을 만든다.
GROUP BY CUBE (o.ordered_at::date, p.category)이는 (order_date, category), (order_date), (category), ()를 모두 계산한다. 축이 두세 개일 때는 편리하지만, 축이 많아지면 결과 행 수가 빠르게 늘어난다. 대시보드에 정말 필요한 요약 수준만 선택하는 것이 좋다.
집계 쿼리 검증 습관
집계와 윈도 함수는 문법보다 결과 단위를 잘못 잡는 실수가 더 많다. 다음 순서로 확인하면 오류를 줄일 수 있다.
결과 한 행이 무엇을 뜻하는지 먼저 정한다.
WHERE로 집계 대상 원본 행을 확인한다.GROUP BY기준이 결과 단위와 일치하는지 본다.COUNT(*)와COUNT(column)중 무엇이 맞는지 확인한다.순위와 누적값은
OVER의PARTITION BY,ORDER BY를 소리 내어 읽어 본다.소계와 총계는 실제
NULL과 요약 행의NULL을 구분한다.
보고서와 대시보드는 대부분 집계와 윈도 함수의 조합으로 만들어진다. GROUP BY가 행을 요약한다면, 윈도 함수는 요약 기준을 참고하면서도 행의 맥락을 유지한다. 이 차이를 이해하면 매출, 순위, 누적값, 전월 대비 변화 같은 실무 지표를 SQL 안에서 직접 계산할 수 있다.
참고
1PostgreSQL 18 Documentation - Aggregate Functions
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.