Database/SQL

[SQL] LEFT JOIN, UNION ALL, WITH

5ole 2024. 1. 21. 18:41

 

작년 10월부터 시작해서 약 4개월 동안 현재 진행중인 프로젝트로 정신없게 보내고 있다. 투입되고 정말 단 하루도 안 바쁜 적이 없었던 것 같은데 이번 프로젝트는 많은 삽질(?)이 동반되었기에 프로젝트가 마무리되면 이후에 회고해보면 좋을 것 같다. 

 

그 전에 프로젝트에서 사용하고 있는 주요 SQL문과 사용 이유를 한번 정리해두고자 한다. 

( Oracle, PostgreSQL을 병행해 사용중입니다 )

 

1. LEFT OUTER JOIN

 

 

먼저 데이터 마트는 팩트 테이블을 중심으로 여러 디멘젼 테이블을 결합하는 형식으로 사용한다.

( 팩트 테이블은 트랜잭션과 유사하게 사실이 기록된 것이며 집계가 되도록 숫자 데이터로 이루어져있는 테이블, 

디맨젼 테이블은 참고되는 마스터 데이터로 데이터를 분류하기 위한 속성값으로 사용되는 테이블이다.)

 

현재 프로젝트에서는 팩트 테이블에서의 값의 온전히 보장되어야한다고 보고 팩트 테이블에 모두 LEFT OUTER JOIN으로 디멘젼 테이블들을 조인해 가져오고 있다. 디멘젼과 팩트를 조인할 때는 특수한 경우가 아니고서야 주로 LEFT OUTER JOIN을 사용한다.

 

SELECT F.A
    ,F.B
    ,DIM.C
    ,DIM.D
    ,DIM2.E
    ,DIM2,F
FROM FACT AS F
LEFT OUTER JOIN DIMENSION AS DIM
ON FACT.A = DIM.A
LEFT OUTER JOIN DIMENSION2 AS DIM2
ON FACT.A = DIM2.A

 

 

추가로, 디멘젼 값이 매핑되지 않은 값들은 NULL로 뜨게 되는데 요청사항으로 이런 값들은 "미정의" 라는 이름으로 화면에 띄워주어 팩트와 디멘젼이 매핑되지 않은 값들을 쉽게 찾을 수 있으면 좋겠다는 것이었다.

디멘젼이 텍스트값으로 되어있는 경우의 값에만 ISNULL함수를 사용해 "미정의"를 출력할 수 있도록 한다.

 

SELECT F.A
    ,F.B
    ,CASE WHEN DIM1.C IS NULL THEN "미정의" ELSE DIM1.C END AS DIM_C
    ,CASE WHEN DIM1.D IS NULL THEN "미정의" ELSE DIM1.D END AS DIM_D
    ,CASE WHEN DIM2.D IS NULL THEN "미정의" ELSE DIM2.D END AS DIM_E
    ,CASE WHEN DIM2.F IS NULL THEN "미정의" ELSE DIM2.F END AS DIM_F
FROM FACT AS F
LEFT OUTER JOIN DIMENSION AS DIM1
ON FACT.A = DIM1.A
LEFT OUTER JOIN DIMENSION2 AS DIM2
ON FACT.A = DIM2.A

 

 

2. UNION ALL

 

프로젝트 오픈 시점까지 데이터가 없었는데 실 데이터가 들어왔을 때는 예상보다 너무 큰 데이터라 한정된 시간동안 데이터 추출을 끝낼 수 없는 상황이 생겼다. 그래서 ROW단으로 가져오던 데이터를 BI 대시보드에서 쓰는 형식으로 집계해서 가져올 수 있도록 쿼리를 변경하게 되었다.

 

UNION ALL 는 여러 개의 쿼리 결과를 하나의 결과로 출력하는 집합 연산자로 위쪽 쿼리와 아래쪽 쿼리를 붙여 그대로 출력해준다.

예시로 부서별로 가져오는 월급과 그 아래에 전체 부서의 월급을 출력하는 쿼리를 하나의 결과로 출력하는 것이다. 

 

SELECT DEPTNO, SUM(SAL)
	FROM EMP
	GROUP BY DEPTNO
UNION ALL
SELECT TO_NUMBER(NULL) AS DEPTNO, SUM(SAL)
	FROM EMP

 

UNION ALL 사용할 때는 4가지 주의사항이 있다.

  1. 위쪽 쿼리와 아래쪽 퀴리 컬럼의 개수가 동일해야한다. 
  2. 위쪽 쿼리와 아래쪽 쿼리 컬럼의 데이터 타입이 동일해야한다.
  3. 결과로 출력되는 컬럼명은 위쪽 쿼리의 컬럼명으로 출력된다.
  4. ORDER BY 절은 제일 아래쪽 쿼리에만 작성 가능하다.

 

예시 쿼리에서처럼

1. 전체 부서의 부서명은 존재하지 않지만 컬럼 개수를 맞춰주기 위해서 TO_NUMBER(NULL)로 임시 값을 주고,

2. DEPTNO의 데이터 유형이 숫자이기에 TO_NUMBER 함수를 사용해 숫자형으로 유형을 맞춰준다.

 

(+) 

참고로 UNION이라는 연산자도 존재하는데,  

UNION은 중복된 데이터를 하나의 고유한 값으로 출력하고, 첫번째 컬럼의 데이터 기준으로 내림차순 정렬해 출력한다.

UNION ALL은 출력 결과가 동일한 데이터가 있어도 중복은 제거하지 않는다. 

 

 

3. WITH AS 

 

UNION ALL을 사용하는 이유와 유사하게 전체 행 데이터를 가져오는데 시간이 오래 걸려, 대시보드에서 쓰는 형식으로 집계해서 가져오는데 사용하는 쿼리가 중복되는 경우가 생겼다.

 

WITH 절은 시간이 오래 걸리는 SQL이 반복되어 사용될 때 성능을 높이는 방법으로, 한번 수행된 쿼리를 임시 저장 영역(Temporary Tablespace)에 저장하고 해당 쿼리가 쓰일 때마다 임시 저장 영역에 저장된 데이터를 불러오는 원리로 사용된다. 

 

# WITH문 사용

WITH JOB_SUMSAL AS (
	SELECT JOB, SUM(SAL) AS 토탈
	FROM EMP
     	GROUP BY JOB )

SELECT JOB, 토탈
FROM JOB_SUMSAL
WHERE 토탈 > ( SELECT AVG(토탈)
    		FROM JOB_SUMSAL );

 

# SUBQUERY

SELECT JOB, SUM(SAL) AS 토탈
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) > ( SELECT AVG(SUM(SAL))
    			FROM EMP
           		GROUP BY JOB);

 

 

예시 구문은 쿼리가 짧아 큰 차이가 안나보이지만 최대한 중복을 줄여 가독성을 높이는 것이 좋기에 WITH절을 사용하는 것이 좋고, 가장 큰 이유는 쿼리 성능을 높이기 위해서이다.

만약 데이터가 대용량이라 추출 시간이 오래 걸리는데 하나의 쿼리에서 해당 테이블을 여러번 호출하게 된다면 호출할 때마다 시간은 배로 더 걸리게 된다. 위의 퀴리를 예시로 들었을 때, EMP 테이블의 데이터가 추출 시간이 20분이 걸린다면 서브쿼리를 사용한 출력값은 EMP 테이블을 2번 불러오기에 2배인 40분이 걸리게 된다.

해당 테이블이 쓰일 때마다 임시 저장 영역에 저장된 데이터를 불러와 한번만 수행될 수 있도록 해 쿼리 성능을 높일 수 있다.

 

 

(+) 참고 자료

초보자를 위한 sql 200제 - 유연수

 

 

 

저작자표시 비영리 변경금지 (새창열림)