※ 다소 의역과 주관적 해석이 들어갈 수 있습니다.
common table express(CTE)는 단일 명령문 범위 내에서 존재하는 이름이 있는 임시 결과 집합이면서 나중에 해당 명령문에서 여러번 참조될 수 있습니다. 다음은 CTE를 사용하는 명령문을 작성하는 방법을 설명하고 있습니다.
CTE 최적화에 대한 정보는 다음을 참고하세요. Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.
Common Table Expressions
common table expression을 지정하려면 하나 이상의 콤마로 구분되는 하위 절 WITH 절을 사용하세요. 각 하위 절은 결과 집합을 만드는 서브쿼리를 제공하고 그 서브쿼리는 특정 이름과 연결됩니다. 다음 예제는 WITH 절 안에서 ct1 과 ct2 로 이름 지어진 CTE를 정의하고 WITH 절을 따르는 최상위 SELECT 에서 그것들을 참조합니다:
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
WITH 절을 포함하는 명령문에서 각 CTE 이름은 해당하는 CTE 결과 집합에 접근하기 위해 참조될 수 있습니다.
CTE 이름은 다른 CTE에서 참조될 수 있으며 다른 CTE를 기반으로 CTE를 정의할 수 있습니다.
재귀적 CTE를 정의하기 위해 자신을 참조할 수 있습니다. 재귀적 CTE의 일반적인 응용 분야로는 계층적 또는 트리 구조 데이터의 시리즈 생성과 순회가 있습니다.
CTE는 DML 명령문 구문의 선택적인 부분입니다. 그러한 명령문은 WITH 절을 사용하여 정의됩니다:
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
cte_name 은 단일 CTE의 이름을 지정하고 WITH 절을 포함하는 명령문에서 테이블 참조로 사용될 수 있습니다.
AS (subquery) 의 부분인 subquery 는 "CTE의 서브쿼리"로 불리며 CTE 결과 집합을 만들어 내는 것입니다. AS 다음 괄호는 필수입니다.
서브쿼리가 자식읜 이름을 참조한다면 이것은 재귀적인 CTE입니다. RECURSIVE 키워드는 WITH 절의 CTE가 재귀적이라면 무조건 포함되어야 합니다. 추가 정보는 다음을 참고하세요 Recursive Common Table Expressions.
주어진 CTE의 컬럼 이름은 다음과 같이 결정됩니다:
CTE 이름 다음에 괄호로 묶인 이름 목록이 있다면 이것들은 컬럼 이름입니다:
WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;이름 목록의 수는 결과 집합의 컬럼 수와 무조건 같아야 합니다.
괄호로 묶인 이름 목록이 없다면, AS (subquery) 부분 안에 첫번째 SELECT 의 select 목록에서 컬럼 이름이 나옵니다.
WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;
WITH 절은 다음 문맥에서 허용됩니다:
SELECT, UPDATE, DELETE 문의 시작 부분에서.
WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ...서브쿼리의 시작 부분에서(파생 테이블 서브쿼리를 포함해서):
SELECT ... WHERE id IN (WITH ... SELECT ...) ... SELECT * FROM (WITH ... SELECT ...) AS dt ...SELECT 명령문을 포함하는 명령문의 경우 SELECT 바로 앞에:
INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
동일한 레벨에서는 WITH 절이 하나만 허용됩니다. 같은 레벨에서 WITH 다음에 WITH 가 오는 것은 허용되지 않습니다. 다음은 허용되지 않아요:
WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
위 명령문을 가능하게 만들려면 콤마를 이용해서 하위 절을 분리하는 단일 WITH 절을 사용하세요:
WITH cte1 AS (...), cte2 AS (...) SELECT ...
하지만, 각각 다른 레벨에서 사용한다면 명령문은 여러 WITH 절을 포함할 수 있습니다:
WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;
WITH 절은 여러 CTE를 정의할 수 있지만 각 CTE는 그 절에서 유일한 CTE 이름을 가져야 합니다. 다음은 허용되지 않습니다:
WITH cte1 AS (...), cte1 AS (...) SELECT ...
위 명령문을 가능하게 하려면 유일한 이름으로 CTE를 정의하세요:
WITH cte1 AS (...), cte2 AS (...) SELECT ...
CTE는 자신을 참조하거나 다른 CTE를 참조할 수 있습니다:
자기 참조 CTE는 재귀적입니다.
같은 WITH 절에서 먼저 정의된 CTE는 다른 CTE에서 참조될 수 있지만 반대는 불가능합니다.
이러한 제약 조건은 cte1 이 cte2 를 참고하고 cte2가 cte1 을 참조하는 상호 재귀적 CTE를 배제합니다. 해당 참조 중 하나는 나중에 정의되는 CTE에 대한 것이어야 하는데, 이는 허용되지 않습니다.주어진 쿼리 블럭에서 CTE는 더 외부 레벨에 있는 쿼리 블럭에 정의된 CTE를 참조할 수 있습니다. 하지만 더 내부 레벨에 있는 쿼리 블럭에 정의된 CTE는 참조 불가합니다.
동일한 이름을 가진 개체에 대한 참조를 해결하기 위해 파생 테이블은 CTE를 숨깁니다; CTE는 기본 테이블, TEPORARY 테이블, 뷰를 숨깁니다.
[역주 : 여기서 숨긴다는 의미는 충돌을 피하기 위해 무시한다거나 덮어쓰는 것을 의미합니다.]
name resolution은 동일한 쿼리 블록에서 객체를 검색한 다음, 같은 이름을 가진 객체가 발견되지 않을 때까지 바깥 쪽 블록으로 차례를 진행하여 수행됩니다.
재귀적 CTE에 대한 추가 구문 고려사항은 Recursive Common Table Expressions를 참고하세요.
Recursive Common Table Expressions
재귀적 CTE는 자기 이름을 참조하는 서브 쿼리를 갖는 CTE입니다. 예를 들어:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
이 명령문을 실행하면 간단한 선형 시퀀스를 포함하는 단일 열이 생성되는 다음 결과가 나옵니다:
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+재귀적 CTE는 다음 구조를 가집니다:
WITH 절의 CTE가 자기 자신을 참조하는 경우 WITH 절은 WITH RECURSIVE 로 시작해야 합니다. (CTE가 자기 자신을 참조하지 않는 경우 RECURSIVE는 허용되지만 필수는 아닙니다.)
재귀저 CTE에 RECURSIVE를 생략하면 다음과 같은 에러가 발생합니다:ERROR 1146 (42S02): Table 'cte_name' doesn't exist재귀적 CTE는 UNION ALL 또는 UNION [DISTINCT] 로 구분되는 두 파트를 가지고 있습니다:
SELECT ... -- return initial row set UNION ALL SELECT ... -- return additional row sets첫번째 SELECT 는CTE의 초기 행들을 생성하지만 CTE 이름을 참조하지는 않습니다. 두번째 SELECT 는 추가적인 행을 생성하고 FROM 절에 있는 CTE 이름을 참조하여 재귀합니다. 이 부분에서 새로운 행을 만들지 않으면 재귀는 끝납니다. 따라서, 재귀적 CTE는 비재귀적 SELECT 파트와 따라오는 재귀적 SELECT 파트로 이루어져 있습니다.
각 SELECT 파트 자체가 여러 개의 SELECT 명령문의 합집합이 될 수 있습니다.CTE 결과 컬럼의 타입은 비재귀적 SELECT 파트의 컬럼 타입으로만 추론되며 모든 컬럼은 NULL이 가능합니다. 타입 결정에 대해 재귀적 SELECT 파트는 무시됩니다.
만약 비재귀적 과 재귀적 파트가 UNION DISTINCT 로 나눠진다면, 중독되는 행은 삭제됩니다. 이는 무한 루프를 피하기 위해 transitive closure를 수행하는 쿼리에 유용합니다.
재귀적 파트의 각각의 반복은 이전 반복에서 생성된 행에만 작동합니다. 만익 재귀적 파트가 여러 쿼리 블럭을 가지고 있다면 각 쿼리 블록의 반복은 지정되지 않은 순서로 스케쥴되며 각 쿼리 블럭은 이전 반복 작업이나 이전 반복 작업 이후 다른 쿼리 블록에서 생성된 행에 대해 작동합니다.
앞서 봤던 재귀적 CTE 서브쿼리에는 초기 행 집합을 생성하기 위해 단일 행을 검색하는 비재귀적 부분이 있습니다:
SELECT 1
그 CTE 서브쿼리른 아래와 같은 재귀적인 부분도 있습니다:
SELECT n + 1 FROM cte WHERE n < 5
매 반복마다, 위 SELECT 는 이전 행 집합의 n 값보다 1이 더 큰 개 값을 갖는 행을 생성합니다. 첫번째 반복은 초기 행 집합 (1)에 대해 동작하여 1+1=2 만듭니다; 두번 째 반복은 첫번째 반복의 행 집합 (2)에 대해 동작하고 2+1=3 을 만듭니다; 이런 식으로 진행됩니다. 재귀가 종료될 때까지, 즉 n이 5보다 작지 않을 때까지 이러한 과정이 계속됩니다.
CTE의 재귀적 부분이 비재귀적 부분보다 컬럽에 대해 더 넓은 값을 생성하는 경우, 데이터 truncation을 방지하기 위해 비재귀적 부분의 열을 넓혀야 할 수도 있습니다. 다음 명령문을 고려해보세요:
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 'abc' AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
nonstrict SQL mode에서 위 명령문은 다음 결과를 만듭니다:
+------+------+
| n | str |
+------+------+
| 1 | abc |
| 2 | abc |
| 3 | abc |
+------+------+
비재귀적 SELECT 이 컬럼 너비를 결정하기 때문에 str 컬럼의 모든 값이 'abc' 입니다. 따라서, 재귀적 SELECT 로 만들어지는 더 긴 str 값은 삭제됩니다.
strict SQL 모드에서, 위 명령문은 에러를 발생시킵니다:
ERROR 1406 (22001): Data too long for column 'str' at row 1
명령문이 잘림이나 오류를 만들지 않기 위해서는 비재귀적 SELECT 에서 CAST() 를 사용하여 str 열을 더 넓게 만듭니다.
WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
이제 위 명령문은 삭제 없이 다음 결과를 만듭니다:
+------+--------------+
| n | str |
+------+--------------+
| 1 | abc |
| 2 | abcabc |
| 3 | abcabcabcabc |
+------+--------------+
컬럼이 위치가 아닌 이름으로 접근되므로 재귀적 부분에 있는 컬럼이 다른 위치에 있는 비재귀적 파트에 있는 컬럼에 접근할 수 있습니다다. 다음 CTE에서 확인할 수 있습니다:
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 1 AS p, -1 AS q
UNION ALL
SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;
하나의 행에 있는 p 는 이전 행의 q 에서 파생되고 그 반대의 경우도 마찬가지이므로 출력의 각 연속된 행에서 양수 값과 음수 값 위치가 바뀝니다.
+------+------+------+
| n | p | q |
+------+------+------+
| 1 | 1 | -1 |
| 2 | -2 | 2 |
| 3 | 4 | -4 |
| 4 | -8 | 8 |
| 5 | 16 | -16 |
+------+------+------+
재귀적 CTE 서브쿼리에 몇가지 구문 제약이 적용됩니다:
재귀적 SELECT 파트는 다음 구문이 포함되엇는 안됩니다:
- SUM() 과 같은 집계함수
- 윈도우 함수
- GROUP BY
- ORDER BY
- DISTINCT
재귀적 CTE의 재귀적 SELECT 파트는 선택적인 OFFSET 절과 함께 LIMIT 절을 사용할 수 있습니다. 결과 집합에 미치는 영향은 가장 바깥쪽 SELECT 에서 LIMIT 을 사용하는 경우와 동일하지만, 재귀적 SELECT 와 함께 사용하면 요청된 수의 행이 생성되자마자 행 생성을 중지하므로 더 효율적입니다.
DISTINCT 금지는 UNION 에만 적용됩니다; UNION DISTINCT 는 허용됩니다재귀적 SELECT 파트는 CTE 를 오직 한번만 참조해야 하며 서브쿼리가 아닌 FROM 절에서만 참조해야 합니다. CTE 이외의 테이블을 참조하고 CTE 와 조인할 수 있습니다. 이와 같이 조인에서 사용되는 경우 CTE는 LEFT JOIN의 오른족에 있어서는 안 됩니다.
이러한 제약 조건은 이전에 언급한 MySQL 관련 제외 사항을 제외하고 SQL 표준에서 비롯됩니다.
재귀적 CTE의 경우 재귀적 SELECT 파트에 대한 EXPLAIN 출력 행은 Extra 컬럼에 Recursive 를 표시합니다.
EXPLAIN 에서 표시되는 비용 추정치는 반복당 비용을 나타내며며 총 비용과 상당히 다를 수 있습니다. 어디 지점에서 WHERE 절이 false가 될 지 알 수 없기때문에 옵티마이저는 반복 횟수를 예상할 수 없습니다.
CTE 실제 비용은 결과 세트 크기에 의해서도 영향을 받을 수 있습니다. 많은 행을 생성하는 CTE에는 메모리 내부 형식에서 디스크 내부 형식으로 변환하기에 충분히 큰 내부 임시 테이블이 필요할 수 있으며 성능 저하가 발생할 수 있습니다. 그렇다면 허용되는 메모리 내 임시 테이블 크기를 늘리면 성능이 향상될 수 있습니다; 다음을 참고하세요 Section 10.4.4, “Internal Temporary Table Use in MySQL”
Limiting Common Table Expression Recurtion
재귀적 SELECT 파트가 재귀 종료 조건을 가지는 것은 재귀적 CTE에서 중요합니다. 재귀적 CTE 폭주를 방지하기 위한 개발 기술로 실행 시간에 제한을 두어 강제로 종료할 수 있습니다:
_cte_max_recursion_depth 시스템 변수는 CTE의 재귀 단계의 횟수를 강제로 제한합니다. 서버는 시스템 변수 보다 더 많이 재귀하는 CTE 실행을 종료합니다.
max_execution_times 시스템 변수는 현재 세션 내에서 실행되는 SELECT 문에 대한 실행 시간 초과를 적용합니다.
MAXX_EXECUTION_TIME 옵티마이저 힌트는 해당 힌트가 나타나는 SELECT 문에 대해 쿼리별 실행 시간 초과를 적용합니다.
실수로 다음처럼 재귀 실행 종료 조건 없이 재귀적 CTE를 작성했다고 가정해보자:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT * FROM cte;기본적으로 cte_max_recursion_depth 는 1000으로 설정돼있고 1000 레벨 이상 재귀할 때 CTE는 종료됩니다. 애플리케이션은 요구사항에 맞게 세션 값을 조정할 수 있습니다:
SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
그 후 실행되는 모든 세션에 영향을 주는 글로벌 cte_max_recursion_depth 값 또한 설정할 수 있습니다
느리게 실행되어 재귀적으로 진행되는 쿼리나 cte_max_recursion_depth 값을 매우 높게 설정해야 하는 컨텍스트에서 발생하는 쿼리의 경우, 깊은 재귀를 방지하는 다른 방법은 세션마다 타임아웃을 설정하는 것입니다. 그러기 위해서는 다음 같은 명령문이 CTE 명령문 이전에 실행되어야 합니다:
SET max_execution_time = 1000; -- impose one second timeout
또는 CTE 문 자체에 최적화 힌트를 포함합니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
재귀 쿼리 내에서 LIMIT 를 사용하여 가장 바깥쪽 SELECT 에 반환되는 행의 최대 수를 지정할 수도 있습니다: 예를 들어:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;
위 작업 또는 추가로 시간 제한을 설정할 수 있습니다. 따라서 다음 CTE는 만 개의 행을 리턴하거나 1초(1000ms) 중 하나가 발생하면 종료합니다:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
만약 재귀적 쿼리가 시간 제한없이 무한 루프에 들어갔다면 다른 세션에서 KILL QUERY를 사용해서 종료할 수 있습니다. 세션 자체 내에서 쿼리를 실행하는 데 사용된 클라이언트 프로그램은 쿼리를 종료하는 방법을 제공할 수 있습니다. 예를 들어, mysql 에서 Control+C 입력은 현재 명령문을 중단합니다.
Recursive Common Table Expression Examples
이전에 언급했듯이, 재귀적 CTE는 수열 생성이나 계층적 탐색 또는 트리 구조 데이터에 자주 사용됩니다. 이번 섹션에서는 이러한 기술의 몇 가지 간단한 예를 보여줍니다.
Fibonacci Series Generation
Date Series Generation
Hierarchical Data Traversal
Fibonacci Series Generation
피보나치 수열을 0과 1(또는 1과 1) 두 수로 시작하고 그 뒤의 숫자는 이전 두 숫자의 합입니다. 재귀적 SELECT 로 생성된 각 행이 수열의 이전 두 수를 가지고 있다면 재귀적 CTE는 피보나치 수열을 만들 수 있습니다. 다음의 CTE는 0과 1을 처음 두 수로 사용하여 10개의 수열을 만듭니다:
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
CTE는 다음 결과를 만듭니다:
+------+-------+------------+
| n | fib_n | next_fib_n |
+------+-------+------------+
| 1 | 0 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 5 |
| 6 | 5 | 8 |
| 7 | 8 | 13 |
| 8 | 13 | 21 |
| 9 | 21 | 34 |
| 10 | 34 | 55 |
+------+-------+------------+
CTE 작동 방식:
n 은 n 번째 피보나치 숫자를 포함하는 행을 나타내는 열입니다. 예를들어 8번째 피보나치 숫자는 13입니다.
fib_n 열은 피보나치 숫자 n 을 표시합니다.
next_fib_n 컬럼은 숫자 n 다음의 피보나치 숫자를 표시합니다. 이 열은 다음 행에 다음 수열 값을 제공하므로 해당 행은 fib_n 열에 있는 이전 두 시리즈 값의 합을 생성할 수 있습니다.
재귀는 n 이 10에 도달하면 끝납니다.
앞선 출력은 전체 CTE 결과를 보여줍니다. 이것의 일부분만 선택하려면 최상위 SELECT 에 적절한 WHERE 절을 추가하세요. 예를 들어, 8번째 피보나치 숫자를 고를려면 다음 처럼하세요:
mysql> WITH RECURSIVE fibonacci ...
...
SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
| 13 |
+-------+Date Series Generation
CTE는 연속된 날짜를 생성할 수 있으며 이는 요약된 데이터에 나타나지 않는 날짜를 포함하여 일련의 모든 날짜에 대한 행을 포함하는 요약을 생성하는데 유용합니다.
판매 수치 테이플에 날짜 행이 포함되어 있다고 가정해보겠습니다:
mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date | price |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 | 50.00 |
| 2017-01-08 | 10.00 |
| 2017-01-08 | 20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 | 5.00 |
+------------+--------+
다음 쿼리는 각 날짜별 판매를 요약합니다:
mysql> SELECT date, SUM(price) AS sum_price
FROM sales
GROUP BY date
ORDER BY date;
+------------+-----------+
| date | sum_price |
+------------+-----------+
| 2017-01-03 | 300.00 |
| 2017-01-06 | 50.00 |
| 2017-01-08 | 180.00 |
| 2017-01-10 | 5.00 |
+------------+-----------+그러나 위 결과에는 포에 포함된 날짜 범위에 포함되지 않는 날짜에 대한 "구멍"이 포함되어 있습니다. 범위 내의 모든 날짜에 대한 결과는 날짜 집합을 만드는 재귀적 CTE와 판매 데이터를 LEFT JOIN 조인으로 하여 만들 수 있습니다.
다은은 날짜 범위 시리즈를 만드는 CTE 입니다:
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;
위 CTE는 다음 결과를 만듭니다:
+------------+
| date |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+
위 CTE 작동 방식:
비재귀적 SELECT 는 sales 테이블의 날짜 범위에서 가장 날짜를 생성합니다.
재귀적 SELECT 로 만들어지는 각 행은 이전 행에서 만들어진 날짜에서 하루를 더합니다.
재귀는 sales 테이블에서 가장 마지막 날짜에 도달한 후에 종료됩니다.
CTE를 sales 테이블과 LEFT JOIN 하여 범위 내 모든 날짜에 대한 판매 실적 행을 만들 수 있습니다:
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;
출력은 다음과 같습니다:
+------------+-----------+
| date | sum_price |
+------------+-----------+
| 2017-01-03 | 300.00 |
| 2017-01-04 | 0.00 |
| 2017-01-05 | 0.00 |
| 2017-01-06 | 50.00 |
| 2017-01-07 | 0.00 |
| 2017-01-08 | 180.00 |
| 2017-01-09 | 0.00 |
| 2017-01-10 | 5.00 |
+------------+-----------+
주의할 점:
쿼리가 비효율적입니까? 특히 재귀적 SELECT 에서 각 행에 대해 MAX() 서브쿼리를 실행하는 쿼리가 비효율적입니까? EXPLAIN 은 MAX() 를 포함하는 서브 쿼리가 한 번만 평가되고 결과가 캐시된다는 것을 보여줍니다.
COALESCE() 를 사용하면 판매 테이블에 판매 데이터가 없는 날에 sum_price 열에 NULL 이 표시되는 것을 방지할 수 있습니다.
Hierarchical Data Traversal
재귀적 CTE는 계층적 형태의 데이터를 순회하는데 유용합니다. 회사의 각 직원에 대해 직원 이름과 ID 번호, 직원 관리자의 ID를 보여주는 작은 데이터 세트를 생성하는 아래 명령문이 있습니다. 최상위 직원(CEO)는 매니저 ID가 NULL(no manager)입니다.
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
결과 데이터 집합은 다음과 같습니다:
mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id | name | manager_id |
+------+---------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
| 333 | Yasmina | NULL |
| 692 | Tarek | 333 |
| 4610 | Sarah | 29 |
+------+---------+------------+
각 직원의 관리 체인(CEO에서 직원까지의 경로)을 포함하는 조직도를 작성하려면, 재귀적 CTE를 사용하세요:
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
위 CTE는 다음 출력을 만듭니다:
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+
위 CTE 작동 방식:
비재귀적 SELECT 는 CEO 행(매니저 ID가 NULL )을 생성합니다.
path 컬럼은 재귀적 SELECT 로 만들어지는 path 값을 위한 공간 확보를 위해 CHAR(200) 으로 확장합니다.재귀적 SELECT 로 만들어지는 행들은 이전 행으로 만들어진 직원에게 직접적으로 보고하는 모든 직원들을 찾습니다. 만들어지는 각 직원 행마다 직원 ID와 이름, 직원 관리 체인 정보를 가지고 있습니다. 체인은 끝에 직원 ID가 추간된 관리자 체인입니다.
재귀는 보고할 직원이 나오지 않을 때 끝납니다.
특정 직원들의 path를 찾기 위해서 최상위 SELECT 에 WHERE 절을 추가하세요. 예를 들어, Tarek과 Sarah 에 대한 결과를 출력하기 위해 SELECT를 다음처럼 수정하세요:
mysql> WITH RECURSIVE ...
...
SELECT * FROM employees_extended
WHERE id IN (692, 4610)
ORDER BY path;
+------+-------+-----------------+
| id | name | path |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
| 692 | Tarek | 333,692 |
+------+-------+-----------------+Common Table Expressions Compared to Similar Constructs
CTE는 다음과 같은 면에서 파생 테이블과 유사합니다:
두 구성 모두 이름이 있습니다.
두 구성은 단일 명령문 범위에 대해 존재합니다.
이러한 유사성 때문에 CTE와 파생 테이블은 종종 서로 바꿔서 사용할 수 있습니다. 간단한 예로, 다음 명령문은 동일합니다.
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;
그러나 CTE은 파생 테이블에 비해 몇가지 장점이 있습니다:
파생 테이블은 쿼리 내에서 단 한번만 참조할 수 있지만 CTE는 여러 차례 참조할 수 있습니다. 파생 테이블 결과의 여러 인스턴스를 사용하려면 결과를 여러 번 파생해야 합니다.
CTE는 자기 참조가 가능합니다.
한 CTE가 다른 CTE를 참조할 수 있습니다.
테이블 정의가 명령문에 내장되어 있는 것보다 초반에 나타날 때 더 읽기 쉽습니다.
CTE는 CREATE [TEMPORARY] TABLE 로 테이블을 생성하는 것과 유사하지만 명시적으로 정의하거나 삭제할 필요가 없습니다. CTE를 사용하면 테이블 생성 권한이 필요없습니다.
'데이터베이스 > MySQL 8.4 Docs 번역' 카테고리의 다른 글
| [번역] 14.19.3 MySQL Handling of GROUP BY (0) | 2025.04.29 |
|---|---|
| [번역] Chapter 13 Data Types (0) | 2025.04.25 |
| [번역] 14.19.2 GROUP BY Modifiers (0) | 2025.04.22 |
| [번역] 15.2.13 SELECT Statement (0) | 2025.04.21 |