CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    category   VARCHAR(50),
    amount     NUMERIC(12, 2),
    status     VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

실행 완료 또는 조회 결과가 표시됩니다.

SELECT
    COUNT(*)                AS 총행수,
    COUNT(amount)           AS 금액있는행수,
    COUNT(DISTINCT user_id) AS 고유사용자수,
    SUM(amount)             AS 총금액,
    AVG(amount)             AS 평균금액,
    MIN(amount)             AS 최소금액,
    MAX(amount)             AS 최대금액,
    SUM(COALESCE(amount, 0)) AS 널을0포함합계
FROM orders;

SELECT
    category,
    COUNT(*)    AS 주문건수,
    SUM(amount) AS 총매출,
    AVG(amount) AS 평균주문금액,
    MAX(amount) AS 최대주문금액
FROM orders
WHERE status != 'cancelled'
  AND created_at >= '2024-01-01'
GROUP BY category
HAVING SUM(amount) > 1000000
ORDER BY 총매출 DESC
LIMIT 10;

SELECT
    DATE_TRUNC('month', created_at) AS month,
    category,
    COUNT(*)    AS order_count,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY 1, 2
HAVING SUM(amount) >= 500000
ORDER BY 1, 4 DESC;

SELECT
    TO_CHAR(created_at, 'YYYY-MM') AS 월,
    COUNT(*)                       AS 주문건수,
    SUM(amount)                    AS 총매출,
    ROUND(AVG(amount), 0)          AS 평균주문금액
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '12 months'
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY 1;

SELECT
    category,
    COUNT(*)                          AS 총주문,
    COUNT(DISTINCT user_id)           AS 구매자수,
    SUM(amount)                       AS 매출,
    ROUND(SUM(amount) / COUNT(*), 0)  AS 건당평균매출
FROM orders
WHERE status != 'cancelled'
GROUP BY category
ORDER BY 매출 DESC;

SELECT
    u.id              AS user_id,
    u.name            AS 고객명,
    COUNT(o.id)       AS 주문횟수,
    SUM(o.amount)     AS 총구매금액,
    MAX(o.created_at) AS 최근구매일
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 3
ORDER BY 총구매금액 DESC
LIMIT 10;

SELECT
    EXTRACT(YEAR FROM created_at)  AS 연도,
    EXTRACT(MONTH FROM created_at) AS 월,
    SUM(amount)                    AS 매출
FROM orders
WHERE status = 'completed'
GROUP BY ROLLUP(
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at)
)
ORDER BY 1 NULLS LAST, 2 NULLS LAST;

SELECT
    CASE WHEN GROUPING(category) = 1 THEN '전체' ELSE category END AS 카테고리,
    CASE WHEN GROUPING(status)   = 1 THEN '전체' ELSE status   END AS 상태,
    COUNT(*) AS 건수,
    SUM(amount) AS 금액
FROM orders
GROUP BY ROLLUP(category, status)
ORDER BY 1, 2;

SELECT
    category,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY CUBE(category, status)
ORDER BY category NULLS LAST, status NULLS LAST;

SELECT
    category,
    COUNT(*)                                               AS 총주문수,
    COUNT(*) FILTER (WHERE status = 'completed')           AS 완료수,
    COUNT(*) FILTER (WHERE status = 'cancelled')           AS 취소수,
    COUNT(*) FILTER (WHERE status = 'pending')             AS 대기수,
    SUM(amount) FILTER (WHERE status = 'completed')        AS 완료매출,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status = 'completed') / COUNT(*),
        1
    ) AS 완료율
FROM orders
GROUP BY category
ORDER BY 총주문수 DESC;

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', created_at) AS cohort_month
    FROM users
    WHERE created_at >= '2024-01-01'
),
first_orders AS (
    SELECT
        user_id,
        MIN(DATE_TRUNC('month', created_at)) AS first_order_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT
    uc.cohort_month,
    COUNT(DISTINCT uc.user_id)  AS 가입자수,
    COUNT(DISTINCT fo.user_id)  AS 구매전환자수,
    ROUND(
        100.0 * COUNT(DISTINCT fo.user_id) / COUNT(DISTINCT uc.user_id),
        1
    ) AS 전환율
FROM user_cohorts uc
LEFT JOIN first_orders fo
    ON uc.user_id = fo.user_id
    AND uc.cohort_month = fo.first_order_month
GROUP BY uc.cohort_month
ORDER BY uc.cohort_month;

[orders 테이블의 행들]
   │
   ① FROM·WHERE: 대상 행 확보            (status·기간 등으로 행 필터 — 아직 그룹 아님)
   │
   ② 그룹 키 계산 → 버킷 분할            (GROUP BY 컬럼 값이 같은 행끼리 한 버킷)
   │      category='전자' → [행, 행, 행]
   │      category='도서' → [행, 행]
   │      category=NULL   → [행, 행]      (NULL끼리도 하나의 버킷)
   │
   ③ 버킷마다 집계함수로 fold            (COUNT·SUM·AVG를 버킷 안 행들에 적용)
   │      [행, 행, 행] → COUNT=3, SUM=1200
   │
   ④ HAVING: 집계 결과로 버킷 필터        (SUM(amount) > 1000000 같은 그룹 조건)
   │
   ⑤ 버킷당 1행 출력                      (그룹 키 + 집계값 — 개별 행은 사라짐)
   ▼
[category별로 한 행씩 남은 결과]

EXPLAIN (ANALYZE, BUFFERS)
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

 HashAggregate  (cost=... rows=8 ...) (actual time=...)
   Group Key: category
   Batches: 5  Memory Usage: 4096kB  Disk Usage: 24960kB   ← 스필 발생
   ->  Seq Scan on orders  ...