서브쿼리와 집합 연산, MERGE

-- 주문 금액 합계가 100만 원을 넘는 고객만
SELECT c.customer_nm, s.total_amount
FROM   customer c
JOIN   (SELECT customer_id, SUM(amount) AS total_amount
        FROM   orders
        WHERE  status_cd = 'DONE'
        GROUP BY customer_id) s
  ON   s.customer_id = c.customer_id
WHERE  s.total_amount > 1000000;

집계 결과를 조건으로 쓰려면 인라인뷰나 HAVING이 필요합니다. 집계 전에는 존재하지 않는 값이기 때문입니다.

-- 행마다 실행될 수 있는 형태
SELECT o.order_id,
       o.amount,
       (SELECT c.customer_nm FROM customer c
        WHERE  c.customer_id = o.customer_id) AS customer_nm
FROM   orders o
WHERE  o.order_dt >= TRUNC(SYSDATE) - 7;

-- 조인으로 바꾸면 한 번에 처리됩니다
SELECT o.order_id, o.amount, c.customer_nm
FROM   orders o
LEFT JOIN customer c ON c.customer_id = o.customer_id
WHERE  o.order_dt >= TRUNC(SYSDATE) - 7;

아우터 조인으로 바꿔야 결과가 같습니다. 스칼라 서브쿼리는 짝이 없으면 NULL을 반환하므로 LEFT JOIN에 대응합니다. INNER JOIN으로 바꾸면 행이 사라질 수 있습니다.

-- 이관 전후 데이터가 같은지 양방향으로 확인
SELECT customer_id, customer_nm FROM customer_old
MINUS
SELECT customer_id, customer_nm FROM customer_new;

SELECT customer_id, customer_nm FROM customer_new
MINUS
SELECT customer_id, customer_nm FROM customer_old;

양쪽 모두 0건이어야 완전히 같습니다. 한 방향만 확인하면 신규 시스템에만 있는 데이터를 놓칩니다. 이관 검증에서 실제로 쓰는 방법입니다.

WITH recent_orders AS (
  SELECT customer_id, amount
  FROM   orders
  WHERE  order_dt >= ADD_MONTHS(TRUNC(SYSDATE), -3)
  AND    status_cd = 'DONE'
),
customer_summary AS (
  SELECT customer_id,
         COUNT(*)    AS order_cnt,
         SUM(amount) AS total_amount
  FROM   recent_orders
  GROUP BY customer_id
)
SELECT c.customer_nm,
       c.grade_cd,
       s.order_cnt,
       s.total_amount
FROM   customer_summary s
JOIN   customer c ON c.customer_id = s.customer_id
WHERE  s.total_amount > 1000000
ORDER BY s.total_amount DESC;

같은 내용을 인라인뷰 중첩으로 쓰면 안쪽부터 읽어야 해서 이해가 어렵습니다. 단계가 둘 이상이면 WITH 절을 권합니다.

MERGE INTO customer_summary t
USING (
  SELECT customer_id,
         COUNT(*)    AS order_cnt,
         SUM(amount) AS total_amount
  FROM   orders
  WHERE  order_dt >= TRUNC(SYSDATE) - 1
  GROUP BY customer_id
) s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET t.order_cnt    = s.order_cnt,
             t.total_amount = s.total_amount,
             t.upd_dt       = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (customer_id, order_cnt, total_amount, upd_dt)
  VALUES (s.customer_id, s.order_cnt, s.total_amount, SYSDATE);

ON 절의 컬럼은 UPDATE에서 바꿀 수 없습니다. 또 ON 조건에 맞는 대상 행이 여러 개면 ORA-30926 이 납니다. USING 쪽 결과에 중복 키가 없는지 먼저 확인합니다.