[프로그래머스]Oracle SQL_6(Lv4)/UNION ALL과 CONNECT BY

1. 오프라인/온라인 판매 데이터 통합하기

https://school.programmers.co.kr/learn/courses/30/lessons/131537?language=oracle

WITH JOIN_TABLE AS (
                    SELECT 
                        USER_ID, 
                        PRODUCT_ID, 
                        SALES_AMOUNT, 
                        SALES_DATE
                    FROM ONLINE_SALE 
        
                    UNION ALL
    
                    SELECT 
                        NULL AS USER_ID, 
                        PRODUCT_ID, 
                        SALES_AMOUNT, 
                        SALES_DATE
                    FROM OFFLINE_SALE )
SELECT
    TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS "SALES_DATE",
    PRODUCT_ID,
    USER_ID,
    sales_amount
FROM JOIN_TABLE
WHERE TO_CHAR(SALES_DATE,'YYYY-MM') = '2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

1-1. POINT : UNION ALL에서 컬럼의 수가 부족할 때

UNION ALL을 사용할 때 특징이 컬럼의 수/ 순서/ 데이터 타입이 일치해야 한다고 했었죠.

하지만이 문제에서 OFFLINE_SALE 테이블은 USER_ID가 없습니다. 따라서 만들어 줘야해요

문제에서 OFFLINE_SALE 테이블의 USER_ID값은 NULL로 표시해달라고 했기때문에 이 조건에 맞게

컬럼을 만들어 줍니다.

SELECT 
      NULL AS USER_ID, 
      PRODUCT_ID, 
      SALES_AMOUNT, 
      SALES_DATE
FROM OFFLINE_SALE

이 테이블을 가지고 UNION ALL을 사용하게 되면 행으로 데이터를 합칠 수 있습니다.

---

2. 입양 시각 구하기(2)

https://school.programmers.co.kr/learn/courses/30/lessons/59413?language=oracle

WITH hours AS (
    SELECT LEVEL - 1 AS HOUR
    FROM dual
    CONNECT BY LEVEL <= 24
)
SELECT 
    h.HOUR,
    NVL(COUNT(a.ANIMAL_ID), 0) AS COUNT
FROM hours h
LEFT JOIN ANIMAL_OUTS a
    ON TO_CHAR(a.DATETIME, 'HH24') = TO_CHAR(h.HOUR, 'FM00')
GROUP BY h.HOUR
ORDER BY h.HOUR;

2-1. POINT1 : 시간 0~23 생성하기

ANIMAL_OUTS 테이블에서 DATETIME의 시간을 추출하면 모든 시간이 나오지 않습니다

하지만 문제에서는 0부터 23시까지, 각 시간대별로 입양이 몇건이나 발생했는지를 나타내야합니다

따라서 실제 데이터에 없는 시간대(건수 0)를 포함하기 위해 CONNECT BY로 0~23 시간대를 생성하고,

이를 LEFT JOIN하여 누락 시간을 0으로 채워야하죠

WITH hours AS (
    SELECT LEVEL - 1 AS HOUR
    FROM dual
    CONNECT BY LEVEL <= 24
)

 

📌  CONNECT BY를 활용한 0~23 생성하기 요약

• DUAL
  → 오라클이 제공하는 1행짜리 더미 테이블을 시작점으로 삼음.
  (출발점이 필요하니까 DUAL에서 시작하는 것)
• CONNECT BY LEVEL <= 24
  = 반복문 역할 (LEVEL이라는 자동 증가 값을 조건에 맞을 때까지 생성)
  → 즉, "24번 반복해라"라는 조건.
• SELECT LEVEL - 1
  → 생성된 LEVEL 값(1~24)을 그대로 쓰는 게 아니라 -1 해줘서
  최종적으로 0~23까지 출력되도록 함.

• DUAL : 오라클에서 임시 1행짜리 테이블

오라클에서 제공하는 더미(dummy) 테이블.

실제 데이터는 1행 1열밖에 없는 아주 작은 테이블.
보통 "임시로 1줄만 필요할 때" 사용해요.

• LEVEL : CONNECT BY 쓸 때 자동으로 생기는 "계층 단계 번호"

CONNECT BY를 사용할 때 자동으로 생기는 가상 컬럼.
현재 몇 번째 단계인지(깊이)를 나타냄.

• CONNECT BY : 조건을 만족할 때까지 행을 재귀적으로 생성

 오라클의 계층형 쿼리(Hierarchical Query) 문법.

• 보통 "부모-자식 관계"를 따라가며 트리 구조 데이터를 조회하는 용도로 사용합니다
• 기본 문법
  • START WITH : 트리의 시작 노드를 지정
  • CONNECT BY : 부모-자식 관계 정의
  • PRIOR : 부모행을 가리킬 때 사용

SELECT ...
FROM 테이블
START WITH 조건 -- 루트(시작점)
CONNECT BY PRIOR 부모컬럼 = 자식컬럼;

 

실무에서는 복잡한 트리 대신 "숫자 1~N까지" 뽑는 용도로 자주 씁니다.

 

---

2-2. POINT2 : JOIN에서 ON의 기능

1. 기본적인 JOIN의 동작 구조

SQL에서 JOIN은 두 테이블의 카테시안 곱(Cartesian Product, 곱집합)을 만들고 → ON 조건으로 필터링하는 개념입니다

SELECT *
FROM A
JOIN B
  ON A.id = B.id;

실제로는:

1. A의 모든 행 × B의 모든 행 = 전체 조합
2. 그중에서 A.id = B.id인 경우만 남김
3. 남은 결과가 최종 테이블

즉,ON에  ABS(A.price - B.price) <= 1000 같은 조건을 넣었다면:

1. A의 행 × B의 행 전체 조합을 만든 뒤
2. 가격 차이가 1000 이하인 조합만 남김
3. 그 결과가 최종 테이블에 들어감

즉, JOIN은 WHERE 전에 실행되는 "조건부 매칭"이라고 보면 됩니다.

 

그래서 문제에서 조건을 적용해 준겁니다 -> ON TO_CHAR(a.DATETIME, 'HH24') = TO_CHAR(h.HOUR, 'FM00')

---

2-3. POINT3 : LEFT JOIN

LEFTJOIN은 왼쪽 테이블은 모두 반환, 오른쪽은 매칭되는 값만 반환하고 없으면 NULL을 출력합니다

즉, 왼쪽 테이블 전체와 교집합이 나오고 매칭이 안되는 데이터는 NULL로 채워지죠

 

정리하자면 ANIMAL_OUTS가 전부 다 있는 게 아니기 때문에 LEFT JOIN을 써서 0~23시를 다 채운 겁니다.