집계와 윈도우 함수

SELECT c.grade_cd,
       COUNT(*)    AS order_cnt,
       SUM(o.amount) AS total_amount
FROM   orders o
JOIN   customer c ON c.customer_id = o.customer_id
WHERE  o.status_cd = 'DONE'          -- 접기 전에 행을 거릅니다
GROUP BY c.grade_cd
HAVING SUM(o.amount) > 10000000      -- 접은 뒤 그룹을 거릅니다
ORDER BY total_amount DESC;

WHERE는 집계 전, HAVING은 집계 후입니다. 개별 행 조건을 HAVING에 쓰면 불필요하게 많은 행을 집계한 뒤 버리게 되어 느려집니다. 걸러낼 수 있는 것은 최대한 WHERE에서 거릅니다.

-- 서브쿼리로 풀면 테이블을 두 번 읽습니다
SELECT o.order_id, o.customer_id, o.amount,
       (SELECT SUM(x.amount) FROM orders x
        WHERE  x.customer_id = o.customer_id) AS customer_total
FROM   orders o;

-- 윈도우 함수는 한 번의 스캔으로 끝냅니다
SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM   orders;

OVER 절이 윈도우 함수의 표시입니다. PARTITION BY 는 GROUP BY에 해당하는 구간 나누기이고, 생략하면 전체가 하나의 구간이 됩니다.

SELECT customer_id, order_id, order_dt, amount
FROM (
  SELECT customer_id, order_id, order_dt, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_dt DESC, order_id DESC
         ) AS rn
  FROM   orders
  WHERE  status_cd = 'DONE'
)
WHERE rn = 1;

ORDER BY에 order_id 를 함께 넣은 이유는 같은 시각의 주문이 있을 때 결과가 매번 달라지는 것을 막기 위해서입니다. 정렬 기준이 유일하지 않으면 실행할 때마다 다른 행이 뽑힐 수 있습니다.

SELECT month_ym,
       total_amount,
       LAG(total_amount) OVER (ORDER BY month_ym) AS prev_amount,
       total_amount - LAG(total_amount) OVER (ORDER BY month_ym) AS diff,
       ROUND(
         (total_amount - LAG(total_amount) OVER (ORDER BY month_ym))
         / NULLIF(LAG(total_amount) OVER (ORDER BY month_ym), 0) * 100
       , 1) AS growth_pct
FROM (
  SELECT TO_CHAR(order_dt, 'YYYY-MM') AS month_ym,
         SUM(amount) AS total_amount
  FROM   orders
  WHERE  status_cd = 'DONE'
  GROUP BY TO_CHAR(order_dt, 'YYYY-MM')
)
ORDER BY month_ym;

첫 행은 이전 행이 없어 LAG가 NULL을 반환합니다. 증감률 계산에서 NULLIF로 0 나누기를 막은 것과 함께, 첫 행 처리를 어떻게 보여 줄지 정해야 합니다.

SELECT order_dt, amount,
       SUM(amount) OVER (
         ORDER BY order_dt
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM   orders
WHERE  status_cd = 'DONE';

OVER 절에 ORDER BY를 넣으면 처음부터 현재 행까지가 기본 범위가 되어 누적 합계가 됩니다. ROWS BETWEEN 을 명시하면 의도가 분명해집니다.

SELECT NVL(c.grade_cd, '전체')   AS grade_cd,
       NVL(o.status_cd, '소계')  AS status_cd,
       SUM(o.amount)             AS total_amount,
       GROUPING(c.grade_cd)      AS is_grade_total,
       GROUPING(o.status_cd)     AS is_status_total
FROM   orders o
JOIN   customer c ON c.customer_id = o.customer_id
GROUP BY ROLLUP (c.grade_cd, o.status_cd)
ORDER BY c.grade_cd, o.status_cd;

GROUPING 함수는 그 행이 소계 행인지(1) 실제 데이터 행인지(0) 알려 줍니다. 원래 값이 NULL인 경우와 소계라서 NULL인 경우를 구분해야 하므로 NVL만으로는 부족합니다.