[DB] DML: CTE

13. CTE

이름을 갖는 중간 결과를 임시로 생성한다.
WITH절을 사용해서 중간 결과에 이름을 부여한다.

Format

WITH [RECURSIVE] 	cte_name [(column_list)] AS (subquery),
					cte_name [(column_list)] AS (subquery),
. . . .

Example

WITH TEMP AS (
        SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME
        FROM TEAM JOIN STADIUM USING (STADIUM_ID)
)
SELECT 	TEAM_NAME, STADIUM.NAME
FROM 	TEMP;

괄호 안에 있는 SELECT문을 TEMP라는 임시테이블에 저장하고, 그 안에서 TEAM_NAME, STADIUM.NAME을 출력한다.

SCHEDULE 테이블에서 STADIUM_ID, HOMETEAM_ID, AWAYTEAM_ID를 각각 경기장명, 홈팀명, 어웨이팀명으로 출력하시오.

WITH TEMP1_SCHEDULE AS
(
      SELECT S.STADIUM_ID, SCHE_DATE,
      TEAM_NAME AS HOMETEAM_NAME, AWAYTEAM_ID,
      HOME_SCORE, AWAY_SCORE
      FROM SCHEDULE S JOIN TEAM T ON S.HOMETEAM_ID = T.TEAM_ID
),
TEMP2_SCHEDULE AS
(
      SELECT T1.STADIUM_ID, SCHE_DATE,
      HOMETEAM_NAME, TEAM_NAME AS AWAYTEAM_NAME,
      HOME_SCORE, AWAY_SCORE
      FROM TEMP1_SCHEDULE T1 JOIN TEAM T ON T1.AWAYTEAM_ID = T.TEAM_ID
)
SELECT 	STADIUM_NAME 경기장명, SCHE_DATE,
		HOMETEAM_NAME 홈팀명, AWAYTEAM_NAME 어웨이팀명,
		HOME_SCORE, AWAY_SCORE
FROM 	TEMP2_SCHEDULE T2 JOIN STADIUM S ON T2.STADIUM_ID = S.STADIUM_ID;

처음에 SCHEDULE 테이블과 TEAM 테이블을 조인하고, 홈팀명을 뽑고 TEMP1_SCHEDULE 테이블에 넣는다.

다음으로 TEMP1_SCHEDULE 테이블과 TEAM 테이블을 조인하고, 홈팀명과 어웨이팀명을 뽑고 TEMP2_SCHEDULE 테이블에 넣는다.

마지막으로, TEMP2_SCHEDULE 테이블과 STADIUM 테이블을 조인하고, 경기장명, 홈팀명, 어웨이팀명 등등을 출력한다.

Recursive CTE

재귀적으로 연속해서 다음 튜플을 생성한다.

Format

WITH RECURSIVE cte_name [(column_list)] AS
(
    SELECT … /* non-recursive SELECT: return initial row */
    UNION ALL
    SELECT … /* recursive SELECT: return additional row set */
)
SELECT …

두번째 SELECT문의 WHERE 절에서 재귀 호출의 종료 조건이 서술될 수 있다.

Example

WITH RECURSIVE cte AS
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT 	*
FROM 	cte;

첫번째 SELECT문에서 1을 n이라고 하고, 1+1을 n<5가 될 때까지 계속한다.

데이터 타입 변환

Example

WITH RECURSIVE cte AS
(
    SELECT 1 AS n, ‘abc’ AS str
    UNION ALL
    SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 4
)
SELECT 	*
FROM 	cte;

이 경우 str이 CHAR(3) 이니까 나중에 재귀적으로 출력할 때 에러가 된다.

해결하기 위해서는 다음과 같이 str의 데이터 타입을 CAST 함수를 사용해서 임시로 변경한다.

WITH RECURSIVE cte AS
(
    SELECT 1 AS n, CAST(‘abc’ AS CHAR(30)) AS str
    UNION ALL
    SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 4
)
SELECT 	*
FROM 	cte;

날짜별 경기수를 검색하라.

WITH RECURSIVE DATES (DATE) AS
(
    SELECT 	CAST(MIN(SCHE_DATE) AS DATE)
    FROM 	SCHEDULE
    UNION ALL
    SELECT 	DATE + INTERVAL 1 DAY
    FROM 	DATES
    WHERE	DATE + INTERVAL 1 DAY <= ‘2012-03-31’
)
SELECT 		DATES.DATE, COALESCE(COUNT(SCHE_DATE),0) AS NO_OF_GAMES
FROM 		DATES LEFT JOIN SCHEDULE ON DATES.DATE = SCHEDULE.SCHE_DATE
GROUP BY 	DATES.DATE
ORDER BY 	DATES.DATE;

먼저, 원하는 범위 내의 모든 날짜를 출력한다.

CAST 함수를 사용해서 MIN(SCHE_DATE)를 DATE 타입으로 변환한다.

그 다음, DATE + INTERVAL 1 DAY 해서 현재 DATE에서 하루 플러스를 해준다.

COALESCE 함수를 사용해서 SCHE_DATE가 NULL값인 경우 0으로 출력한다.

LEFT JOIN에 의해서 게임이 없던 날에도 하나의 튜플이 생성됐다.