[MySQL] UNION / UNION ALL 결과의 고급 활용 예제

UNION 또는 UNION ALL로 만든 결과를
👉 다시 조인하거나,
👉 집계(GROUP BY, SUM 등) 하거나,
👉 조건 필터링(WHERE, HAVING) 하는 고급 실무형 쿼리 예제.
이건 리포트, 로그 통합, 활동 분석 등에서 아주 자주 등장하는 패턴.

---

✅ UNION / UNION ALL 결과의 고급 활용 예제

---

🎯 시나리오: 활동 기록 통합 + 분석

다음과 같은 3개의 로그 테이블이 있다고 가정하자:

테이블	설명
logins(user_id, login_time)	로그인 로그
orders(user_id, order_date, amount)	주문 로그
inquiries(user_id, inquiry_time)	고객 문의 로그

---

✅ 1. UNION ALL로 활동 로그 통합

SELECT user_id, login_time AS activity_time, '로그인' AS activity, NULL AS amount
FROM logins

UNION ALL

SELECT user_id, order_date AS activity_time, '주문' AS activity, amount
FROM orders

UNION ALL

SELECT user_id, inquiry_time AS activity_time, '문의' AS activity, NULL AS amount
FROM inquiries;

🔹 이 쿼리는 로그 3개를 통합된 형태로 합친 것
🔹 activity_time, activity로 통일
🔹 amount는 주문만 존재하므로 NULL 처리

---

✅ 2. 위 결과를 서브쿼리로 감싸서 필터링 하기

SELECT *
FROM (
  -- 위의 UNION ALL 결과 전체
  SELECT user_id, login_time AS activity_time, '로그인' AS activity, NULL AS amount
  FROM logins
  UNION ALL
  SELECT user_id, order_date AS activity_time, '주문' AS activity, amount
  FROM orders
  UNION ALL
  SELECT user_id, inquiry_time AS activity_time, '문의' AS activity, NULL AS amount
  FROM inquiries
) AS activity_log
WHERE activity_time >= CURDATE() - INTERVAL 30 DAY
ORDER BY activity_time DESC;

📌 최근 30일 이내 활동만 필터링

---

✅ 3. UNION 결과를 GROUP BY + 집계 함수로 분석

예: 유저별 활동 횟수 & 총 주문금액

SELECT user_id,
       COUNT(*) AS total_actions,
       SUM(CASE WHEN activity = '주문' THEN amount ELSE 0 END) AS total_spent,
       MAX(activity_time) AS last_activity
FROM (
  SELECT user_id, login_time AS activity_time, '로그인' AS activity, NULL AS amount
  FROM logins
  UNION ALL
  SELECT user_id, order_date AS activity_time, '주문' AS activity, amount
  FROM orders
  UNION ALL
  SELECT user_id, inquiry_time AS activity_time, '문의' AS activity, NULL AS amount
  FROM inquiries
) AS all_logs
GROUP BY user_id
HAVING total_spent > 100000;

📌 포인트:

• SUM(CASE WHEN … THEN …)로 특정 활동에만 금액 합산
• HAVING으로 고객 세그먼트 필터링 (100,000원 이상 소비 고객)

---

✅ 4. UNION 결과를 다른 테이블과 JOIN하기

예: users 테이블과 JOIN해서 사용자 이름 붙이기

SELECT u.name, a.*
FROM (
  SELECT user_id, order_date AS activity_time, '주문' AS activity, amount
  FROM orders
  UNION ALL
  SELECT user_id, inquiry_time, '문의', NULL
  FROM inquiries
) AS a
JOIN users u ON a.user_id = u.id
ORDER BY u.name, a.activity_time DESC;

📌 JOIN 대상이 되는 테이블은 서브쿼리 밖에서 사용 가능

---

✅ 5. CTE(Common Table Expression, WITH 절)로 구조 정리

WITH activity_log AS (
  SELECT user_id, login_time AS activity_time, '로그인' AS activity, NULL AS amount FROM logins
  UNION ALL
  SELECT user_id, order_date, '주문', amount FROM orders
  UNION ALL
  SELECT user_id, inquiry_time, '문의', NULL FROM inquiries
)

SELECT user_id, activity, COUNT(*) AS cnt
FROM activity_log
GROUP BY user_id, activity;

📌 WITH절 사용 시 쿼리가 더 깔끔하고 재사용 가능해짐 (MySQL 8.0 이상)

---

✅ 실무 활용 요약 패턴

목적	패턴 예시
전체 활동 내역 모으기	UNION ALL
활동 로그 + 사용자 JOIN	UNION ALL 결과를 users와 JOIN
특정 활동 필터	WHERE activity = '주문'
특정 조건 금액 필터	HAVING SUM(...) > ...
일별/유저별 집계	GROUP BY + DATE(activity_time)

---