Loading...
MySQL 9.5 Reference Manual 9.5의 15.2.20 WITH (Common Table Expressions)의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
common table expression(CTE)는 단일 statement의 scope 내에 존재하는 이름이 있는 일시적인 결과 집합이며, 그 statement 내에서 이후에 (필요하다면 여러 번) 참조할 수 있습니다. 다음 설명에서는 CTE를 사용하는 statement를 작성하는 방법을 설명합니다.
CTE 최적화에 대한 정보는 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”를 참조하십시오.
common table expression을 지정하려면, 하나 이상의 콤마로 구분된 subclause를 갖는
WITH 절을 사용합니다. 각 subclause는 결과 집합을 생성하는 하위 쿼리를 제공하고, 하위 쿼리에 이름을 연결합니다. 다음 예제에서는
WITH 절에서 cte1 및
cte2 라는 이름의 CTE를 정의하고, 그 후속 top-level
SELECT에서 이를 참조합니다:
1WITH 2 cte1 AS (SELECT a, b FROM table1), 3 cte2 AS (SELECT c, d FROM table2) 4SELECT b, d FROM cte1 JOIN cte2 5WHERE cte1.a = cte2.c;
WITH 절을 포함하는 statement에서, 각 CTE 이름은 해당 CTE 결과 집합에 접근하기 위해 참조될 수 있습니다.
CTE 이름은 다른 CTE에서 참조될 수 있으며, 이를 통해 CTE를 다른 CTE를 기반으로 정의할 수 있습니다.
CTE는 자신을 참조하여 재귀 CTE를 정의할 수 있습니다. 재귀 CTE의 일반적인 용도는 수열 생성과 계층적 또는 트리 구조 데이터의 탐색입니다.
common table expression은 DML statement 구문에서 선택적 부분입니다. 이는
WITH 절을 사용하여 정의됩니다:
1with_clause: 2 WITH [RECURSIVE] 3 cte_name [(col_name [, col_name] ...)] AS (subquery) 4 [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
_cte_name_은 단일 common table expression의 이름이며,
WITH 절을 포함하는 statement에서 테이블 참조로 사용할 수 있습니다.
AS (subquery)의 subquery 부분은 “CTE의 하위 쿼리”라고 하며, CTE 결과 집합을 생성하는 부분입니다. AS 뒤의 괄호는 필수입니다.
하위 쿼리가 자신의 이름을 참조하는 경우 common table expression은 재귀적입니다.
WITH 절의 CTE 중 하나라도 재귀적인 경우에는 RECURSIVE 키워드를 포함해야 합니다. 자세한 내용은
Recursive Common Table Expressions를 참조하십시오.
주어진 CTE에 대한 컬럼 이름 결정은 다음과 같이 이루어집니다:
1WITH cte (col1, col2) AS 2( 3 SELECT 1, 2 4 UNION ALL 5 SELECT 3, 4 6) 7SELECT col1, col2 FROM cte;
목록의 이름 개수는 결과 집합의 컬럼 개수와 같아야 합니다.
AS (subquery) 부분 내 첫 번째
SELECT의 select 리스트에서 가져옵니다:1WITH cte AS 2( 3 SELECT 1 AS col1, 2 AS col2 4 UNION ALL 5 SELECT 3, 4 6) 7SELECT col1, col2 FROM cte;
WITH 절은 다음 context에서 허용됩니다:
1WITH ... SELECT ... 2WITH ... UPDATE ... 3WITH ... DELETE ...
1SELECT ... WHERE id IN (WITH ... SELECT ...) ... 2SELECT * FROM (WITH ... SELECT ...) AS dt ...
1INSERT ... WITH ... SELECT ... 2REPLACE ... WITH ... SELECT ... 3CREATE TABLE ... WITH ... SELECT ... 4CREATE VIEW ... WITH ... SELECT ... 5DECLARE CURSOR ... WITH ... SELECT ... 6EXPLAIN ... WITH ... SELECT ...
동일 level에서는 하나의
WITH 절만 허용됩니다. 동일 level에서
WITH 뒤에 다시
WITH가 오는 것은 허용되지 않으므로, 다음 예는 허용되지 않습니다:
1WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
이 statement를 허용되도록 만들려면, subclause를 콤마로 구분하는 단일
WITH 절을 사용해야 합니다:
1WITH cte1 AS (...), cte2 AS (...) SELECT ...
그러나 서로 다른 level에 등장한다면, 하나의 statement에 여러
WITH 절을 포함할 수 있습니다:
1WITH cte1 AS (SELECT 1) 2SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;
WITH 절은 하나 이상의 common table expression을 정의할 수 있지만, 각 CTE 이름은 해당 절 내에서 고유해야 합니다. 다음 예는 허용되지 않습니다:
1WITH cte1 AS (...), cte1 AS (...) SELECT ...
이 statement를 허용되도록 만들려면, CTE를 서로 다른 이름으로 정의하십시오:
1WITH cte1 AS (...), cte2 AS (...) SELECT ...
CTE는 자신 또는 다른 CTE를 참조할 수 있습니다:
self-referencing CTE는 재귀적입니다.
CTE는 동일한
WITH 절 내에서 이전에 정의된 CTE는 참조할 수 있지만, 이후에 정의된 CTE는 참조할 수 없습니다.
이 제약은 cte1이 cte2를 참조하고, cte2가
cte1을 참조하는 상호 재귀 CTE를 허용하지 않습니다. 이들 중 하나는 이후에 정의된 CTE를 참조해야 하며, 이는 허용되지 않습니다.
동일한 이름을 가진 객체에 대한 참조를 해석할 때, 파생 테이블은 CTE를 숨기고, CTE는 기본 테이블,
TEMPORARY 테이블, 뷰를 숨깁니다. 이름 해석은 동일 쿼리 블록에서 객체를 검색하는 것부터 시작하여, 이름을 가진 객체가 발견되지 않는 동안 바깥 블록으로 차례로 진행됩니다.
재귀 CTE에 특화된 구문 고려 사항에 대해서는 Recursive Common Table Expressions를 참조하십시오.
재귀 common table expression은 자신의 이름을 참조하는 하위 쿼리를 가진 CTE입니다. 예를 들면 다음과 같습니다:
1WITH RECURSIVE cte (n) AS 2( 3 SELECT 1 4 UNION ALL 5 SELECT n + 1 FROM cte WHERE n < 5 6) 7SELECT * FROM cte;
이 statement를 실행하면, 단일 컬럼에 단순 선형 수열이 포함된 다음과 같은 결과가 생성됩니다:
1+------+ 2| n | 3+------+ 4| 1 | 5| 2 | 6| 3 | 7| 4 | 8| 5 | 9+------+
재귀 CTE는 다음과 같은 구조를 가집니다:
WITH 절은
WITH 절 내의 어떤 CTE라도 자신을 참조하는 경우
WITH RECURSIVE로 시작해야 합니다. (어떤 CTE도 자신을 참조하지 않는다면, RECURSIVE는 사용해도 되고 생략해도 됩니다.)재귀 CTE에서 RECURSIVE를 지정하는 것을 잊으면, 다음과 같은 오류가 발생할 수 있습니다:
1ERROR 1146 (42S02): Table 'cte_name' doesn't exist
UNION ALL
또는 UNION [DISTINCT]로 구분되는 두 부분으로 구성됩니다:1SELECT ... -- return initial row set 2UNION ALL 3SELECT ... -- return additional row sets
첫 번째
SELECT는 CTE에 대한 초기 row 집합을 생성하며, CTE 이름을 참조하지 않습니다. 두 번째
SELECT는 추가 row를 생성하고, FROM 절에서 CTE 이름을 참조함으로써 재귀를 수행합니다. 재귀는 이 부분이 새로운 row를 더 이상 생성하지 않을 때 종료됩니다. 따라서 재귀 CTE는 비재귀
SELECT 부분 뒤에 재귀
SELECT 부분이 이어지는 형태로 구성됩니다.
각
SELECT 부분 자체는 여러
SELECT statement를 union한 것일 수 있습니다.
CTE 결과 컬럼의 타입은 비재귀
SELECT 부분의 컬럼 타입에서만 추론되며, 컬럼은 모두 널 허용입니다. 타입 결정 시 재귀
SELECT 부분은 무시됩니다.
비재귀 부분과 재귀 부분이
UNION DISTINCT로 구분되는 경우, 중복 row는 제거됩니다. 이는 전이 폐쇄(transitive closure)를 수행하는 쿼리에서 무한 루프를 피하기 위해 유용합니다.
재귀 부분의 각 반복은 이전 반복에서 생성된 row에 대해서만 동작합니다. 재귀 부분에 여러 쿼리 블록이 있는 경우, 각 쿼리 블록의 반복은 미정의 순서로 스케줄되며, 각 쿼리 블록은 이전 반복 종료 이후 자신 또는 다른 쿼리 블록이 생성한 row에 대해 동작합니다.
앞서 제시된 재귀 CTE 하위 쿼리는 초기 row 집합을 생성하기 위해 단일 row를 가져오는 다음과 같은 비재귀 부분을 가지고 있습니다:
1SELECT 1
CTE 하위 쿼리는 다음과 같은 재귀 부분도 가지고 있습니다:
1SELECT n + 1 FROM cte WHERE n < 5
각 반복에서 이
SELECT는 이전 row 집합의 n 값보다 1 큰 새 값을 가진 row를 생성합니다. 첫 번째 반복은 초기 row 집합(1)에서 동작하고 1+1=2를 생성합니다. 두 번째 반복은 첫 번째 반복의 row 집합(2)에서 동작하고 2+1=3을 생성합니다. 이 과정은 재귀가 종료될 때까지 계속되며, 이는 n이 더 이상 5보다 작지 않을 때 발생합니다.
재귀 부분이 비재귀 부분보다 폭이 넓은 컬럼 값을 생성하는 경우, 데이터 잘림을 피하기 위해 비재귀 부분의 컬럼 폭을 넓힐 필요가 있을 수 있습니다. 다음 statement를 고려해 보십시오:
1WITH RECURSIVE cte AS 2( 3 SELECT 1 AS n, 'abc' AS str 4 UNION ALL 5 SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 6) 7SELECT * FROM cte;
nonstrict SQL 모드에서는, statement는 다음과 같은 output을 생성합니다:
1+------+------+ 2| n | str | 3+------+------+ 4| 1 | abc | 5| 2 | abc | 6| 3 | abc | 7+------+------+
str 컬럼 값은 모두
'abc'인데, 이는 비재귀
SELECT가 컬럼 폭을 결정하기 때문입니다. 결과적으로 재귀
SELECT에서 생성된 더 긴 str 값은 잘립니다.
strict SQL 모드에서는, statement가 다음과 같은 오류를 생성합니다:
1ERROR 1406 (22001): Data too long for column 'str' at row 1
이 문제를 해결하여 statement가 잘림이나 오류를 발생시키지 않도록 하려면, 비재귀
SELECT에서
CAST()를 사용해 str 컬럼을 더 넓게 만드십시오:
1WITH RECURSIVE cte AS 2( 3 SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str 4 UNION ALL 5 SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 6) 7SELECT * FROM cte;
이제 statement는 잘림 없이 다음과 같은 결과를 생성합니다:
1+------+--------------+ 2| n | str | 3+------+--------------+ 4| 1 | abc | 5| 2 | abcabc | 6| 3 | abcabcabcabc | 7+------+--------------+
컬럼은 위치가 아니라 이름으로 접근되므로, 재귀 부분의 컬럼은 비재귀 부분에서 위치가 다른 컬럼에 접근할 수 있습니다. 다음 CTE가 이를 보여 줍니다:
1WITH RECURSIVE cte AS 2( 3 SELECT 1 AS n, 1 AS p, -1 AS q 4 UNION ALL 5 SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5 6) 7SELECT * FROM cte;
한 row에서 p는 이전 row의
q에서 유도되고, 반대로 q는 이전 row의 p에서 유도되기 때문에, output의 각 연속적인 row에서 양수와 음수 값의 위치가 서로 바뀝니다:
1+------+------+------+ 2| n | p | q | 3+------+------+------+ 4| 1 | 1 | -1 | 5| 2 | -2 | 2 | 6| 3 | 4 | -4 | 7| 4 | -8 | 8 | 8| 5 | 16 | -16 | 9+------+------+------+
재귀 CTE 하위 쿼리 내에는 몇 가지 구문 제약이 적용됩니다:
재귀
SELECT 부분에는 다음 구문 요소를 포함할 수 없습니다:
SUM()과 같은 집계 함수
윈도 함수
GROUP BY
ORDER BY
DISTINCT
재귀 CTE의 재귀
SELECT 부분은 LIMIT 절과 선택적 OFFSET 절도 사용할 수 있습니다. 결과 집합에 대한 효과는 가장 바깥 SELECT에서 LIMIT을 사용하는 것과 동일하지만, 재귀 SELECT와 함께 사용할 경우 요청된 row 수가 생성되는 즉시 row 생성이 중단되므로 더 효율적입니다.
DISTINCT에 대한 금지 사항은
UNION 멤버에만 적용됩니다;
UNION DISTINCT는 허용됩니다.
SELECT 부분은 CTE를 한 번만 참조해야 하며, 하위 쿼리가 아닌 FROM 절에서만 참조해야 합니다. CTE 이외의 테이블을 참조할 수 있으며, 이를 CTE와 조인할 수 있습니다. 이런 방식으로 조인하는 경우, CTE는 LEFT JOIN의 오른쪽에 올 수 없습니다.이러한 제약은 앞서 언급된 MySQL 특정 제외 사항을 제외하면 SQL 표준에서 나온 것입니다.
재귀 CTE에 대해,
EXPLAIN output에서 재귀
SELECT 부분에 대한 row는
Extra 컬럼에 Recursive를 표시합니다.
EXPLAIN에 표시되는 비용 추정치는 반복당 비용을 나타내며, 이는 총 비용과 상당히 다를 수 있습니다. 옵티마이저는 WHERE 절이 어느 시점에서 false가 되는지 예측할 수 없기 때문에 반복 수를 예측할 수 없습니다.
CTE 실제 비용은 결과 집합 크기의 영향을 받을 수도 있습니다. 많은 row를 생성하는 CTE는 내부 임시 테이블이 메모리에서 디스크 형식으로 변환될 만큼 커질 수 있으며, 이로 인해 성능 저하가 발생할 수 있습니다. 그런 경우 허용되는 in-memory 임시 테이블 크기를 늘리면 성능이 향상될 수 있습니다. 자세한 내용은 Section 10.4.4, “Internal Temporary Table Use in MySQL”를 참조하십시오.
재귀 CTE의 경우, 재귀
SELECT 부분이 재귀를 종료하도록 하는 조건을 포함하는 것이 중요합니다. 폭주하는 재귀 CTE를 방지하기 위한 개발 기법으로, 실행 시간에 제한을 두어 강제로 종료시키는 방법이 있습니다:
cte_max_recursion_depth 시스템 변수는 CTE의 재귀 level 수에 대한 제한을 강제합니다. 이 변수 값보다 더 많은 level로 재귀하는 CTE를 서버는 실행을 종료합니다.
max_execution_time 시스템 변수는 현재 세션에서 실행되는
SELECT statement의 실행 타임아웃을 강제합니다.
MAX_EXECUTION_TIME 옵티마이저 힌트는 해당
SELECT statement에 대해 쿼리별 실행 타임아웃을 강제합니다.
재귀 CTE가 재귀 실행 종료 조건 없이 잘못 작성되었다고 가정해 봅시다:
1WITH RECURSIVE cte (n) AS 2( 3 SELECT 1 4 UNION ALL 5 SELECT n + 1 FROM cte 6) 7SELECT * FROM cte;
기본적으로,
cte_max_recursion_depth의 값은 1000이며, CTE가 1000 level을 초과하여 재귀할 때 CTE 실행을 종료하게 됩니다. 애플리케이션은 자신의 요구 사항에 맞게 세션 값을 변경할 수 있습니다:
1SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion 2SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
또한, 글로벌
cte_max_recursion_depth 값을 설정하여 이후에 시작되는 모든 세션에 영향을 줄 수 있습니다.
느리게 실행되거나 재귀하는 쿼리, 또는
cte_max_recursion_depth 값을 매우 높게 설정할 필요가 있는 context에 대해서는, 또 다른 방법으로 세션별 타임아웃을 설정하여 깊은 재귀를 방지할 수 있습니다. 이를 위해 CTE statement를 실행하기 전에 다음과 같은 statement를 실행합니다:
1SET max_execution_time = 1000; -- impose one second timeout
또는 CTE statement 자체에 옵티마이저 힌트를 포함할 수도 있습니다:
1WITH RECURSIVE cte (n) AS 2( 3 SELECT 1 4 UNION ALL 5 SELECT n + 1 FROM cte 6) 7SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte; 8 9WITH RECURSIVE cte (n) AS 10( 11 SELECT 1 12 UNION ALL 13 SELECT n + 1 FROM cte 14) 15SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
또한 재귀 쿼리 내에서 LIMIT을 사용해 가장 바깥
SELECT에 반환될 최대 row 수를 제한할 수 있습니다. 예를 들면 다음과 같습니다:
1WITH RECURSIVE cte (n) AS 2( 3 SELECT 1 4 UNION ALL 5 SELECT n + 1 FROM cte LIMIT 10000 6) 7SELECT * FROM cte;
이는 시간 제한을 설정하는 것과 함께 또는 대신 사용할 수 있습니다. 따라서 다음 CTE는 만 개의 row를 반환하거나 1초(1000 millisecond) 동안 실행된 후 중 먼저 발생하는 조건을 충족하면 종료됩니다:
1WITH RECURSIVE cte (n) AS 2( 3 SELECT 1 4 UNION ALL 5 SELECT n + 1 FROM cte LIMIT 10000 6) 7SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
실행 시간 제한 없이 재귀 쿼리가 무한 루프에 빠지는 경우, 다른 세션에서
KILL QUERY를 사용해 이를 종료할 수 있습니다. 해당 세션 내에서는, 쿼리를 실행하는 클라이언트 프로그램이 쿼리를 종료하는 방법을 제공할 수 있습니다. 예를 들어,
mysql에서는 Control+C를 입력하면 현재 statement가 중단됩니다.
앞서 언급했듯이, 재귀 common table expression(CTE)은 수열 생성과 계층적 또는 트리 구조 데이터의 탐색에 자주 사용됩니다. 이 섹션에서는 이러한 기법에 대한 몇 가지 간단한 예를 보여 줍니다.
Fibonacci 수열은 두 수 0과 1(또는 1과 1)에서 시작하며, 이후의 각 수는 이전 두 수의 합입니다. 재귀 common table expression은 재귀
SELECT에서 생성되는 각 row가 수열의 바로 이전 두 숫자에 접근할 수 있다면 Fibonacci 수열을 생성할 수 있습니다. 다음 CTE는 처음 두 숫자로 0과 1을 사용하여 10개의 숫자로 이루어진 수열을 생성합니다:
1WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS 2( 3 SELECT 1, 0, 1 4 UNION ALL 5 SELECT n + 1, next_fib_n, fib_n + next_fib_n 6 FROM fibonacci WHERE n < 10 7) 8SELECT * FROM fibonacci;
CTE는 다음과 같은 결과를 생성합니다:
1+------+-------+------------+ 2| n | fib_n | next_fib_n | 3+------+-------+------------+ 4| 1 | 0 | 1 | 5| 2 | 1 | 1 | 6| 3 | 1 | 2 | 7| 4 | 2 | 3 | 8| 5 | 3 | 5 | 9| 6 | 5 | 8 | 10| 7 | 8 | 13 | 11| 8 | 13 | 21 | 12| 9 | 21 | 34 | 13| 10 | 34 | 55 | 14+------+-------+------------+
CTE 동작 방식:
n은 row에 n번째 Fibonacci 숫자가 포함되어 있음을 나타내는 표시용 컬럼입니다. 예를 들어, 8번째 Fibonacci 숫자는 13입니다.
fib_n 컬럼은 Fibonacci 숫자 n을 표시합니다.
next_fib_n 컬럼은 숫자 n 다음의 Fibonacci 숫자를 표시합니다. 이 컬럼은 다음 row에 다음 수열 값을 제공하여, 그 row가 자신의 fib_n 컬럼에서 이전 두 수열 값의 합을 생성할 수 있도록 합니다.
재귀는 n이 10에 도달하면 종료됩니다. 이는 출력 결과를 작은 row 집합으로 제한하기 위한 임의의 선택입니다.
앞의 output은 전체 CTE 결과를 보여 줍니다. 그 중 일부만 선택하려면, top-level
SELECT에 적절한 WHERE 절을 추가하십시오. 예를 들어, 8번째 Fibonacci 숫자를 선택하려면 다음과 같이 합니다:
1mysql> WITH RECURSIVE fibonacci ... 2 ... 3 SELECT fib_n FROM fibonacci WHERE n = 8; 4+-------+ 5| fib_n | 6+-------+ 7| 13 | 8+-------+
common table expression은 연속된 날짜 수열을 생성할 수 있으며, 이는 요약 데이터에 수열 내 모든 날짜(요약된 데이터에 나타나지 않는 날짜 포함)에 대한 row를 생성하는 데 유용합니다.
판매 숫자 테이블에 다음과 같은 row가 있다고 가정합니다:
1mysql> SELECT * FROM sales ORDER BY date, price; 2+------------+--------+ 3| date | price | 4+------------+--------+ 5| 2017-01-03 | 100.00 | 6| 2017-01-03 | 200.00 | 7| 2017-01-06 | 50.00 | 8| 2017-01-08 | 10.00 | 9| 2017-01-08 | 20.00 | 10| 2017-01-08 | 150.00 | 11| 2017-01-10 | 5.00 | 12+------------+--------+
다음 쿼리는 일자별 판매를 요약합니다:
1mysql> SELECT date, SUM(price) AS sum_price 2 FROM sales 3 GROUP BY date 4 ORDER BY date; 5+------------+-----------+ 6| date | sum_price | 7+------------+-----------+ 8| 2017-01-03 | 300.00 | 9| 2017-01-06 | 50.00 | 10| 2017-01-08 | 180.00 | 11| 2017-01-10 | 5.00 | 12+------------+-----------+
그러나 이 결과에는 테이블이 span하는 날짜 범위 안에 있지만 테이블에 나타나지 않는 날짜에 대한 “구멍”이 있습니다. 해당 범위의 모든 날짜를 나타내는 결과는, 해당 날짜 집합을 생성하는 재귀 CTE와 판매 데이터에 대한 LEFT JOIN을 사용하여 생성할 수 있습니다.
다음은 날짜 범위 수열을 생성하는 CTE입니다:
1WITH RECURSIVE dates (date) AS 2( 3 SELECT MIN(date) FROM sales 4 UNION ALL 5 SELECT date + INTERVAL 1 DAY FROM dates 6 WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales) 7) 8SELECT * FROM dates;
CTE는 다음과 같은 결과를 생성합니다:
1+------------+ 2| date | 3+------------+ 4| 2017-01-03 | 5| 2017-01-04 | 6| 2017-01-05 | 7| 2017-01-06 | 8| 2017-01-07 | 9| 2017-01-08 | 10| 2017-01-09 | 11| 2017-01-10 | 12+------------+
CTE 동작 방식:
비재귀
SELECT는
sales 테이블이 span하는 날짜 범위 중 가장 낮은 날짜를 생성합니다.
재귀
SELECT에서 생성된 각 row는 이전 row에서 생성된 날짜에 하루를 더합니다.
재귀는 날짜가
sales 테이블이 span하는 날짜 범위 중 가장 높은 날짜에 도달하면 종료됩니다.
CTE와 sales 테이블 간에 LEFT JOIN을 수행하면, 해당 범위의 각 날짜에 대한 row가 있는 판매 요약 결과가 생성됩니다:
1WITH RECURSIVE dates (date) AS 2( 3 SELECT MIN(date) FROM sales 4 UNION ALL 5 SELECT date + INTERVAL 1 DAY FROM dates 6 WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales) 7) 8SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price 9FROM dates LEFT JOIN sales ON dates.date = sales.date 10GROUP BY dates.date 11ORDER BY dates.date;
output은 다음과 같습니다:
1+------------+-----------+ 2| date | sum_price | 3+------------+-----------+ 4| 2017-01-03 | 300.00 | 5| 2017-01-04 | 0.00 | 6| 2017-01-05 | 0.00 | 7| 2017-01-06 | 50.00 | 8| 2017-01-07 | 0.00 | 9| 2017-01-08 | 180.00 | 10| 2017-01-09 | 0.00 | 11| 2017-01-10 | 5.00 | 12+------------+-----------+
몇 가지 주의 사항:
특히 재귀
SELECT에서 각 row마다 실행되는
MAX() 하위 쿼리 때문에 쿼리가 비효율적인 것은 아닐까요?
EXPLAIN에 따르면,
MAX()를 포함하는 하위 쿼리는 한 번만 평가되며, 결과는 캐시됩니다.
COALESCE()를 사용하면 sales 테이블에 판매 데이터가 없는 날의 sum_price 컬럼에 NULL이 표시되는 것을 방지할 수 있습니다.
재귀 common table expression은 계층형 데이터를 탐색하는 데 유용합니다. 다음 statement는, 회사의 각 직원에 대해 직원 이름과 ID 번호, 그리고 해당 직원의 manager ID를 보여 주는 작은 데이터 집합을 생성합니다. 최상위 직원(CEO)은 manager ID가 NULL입니다(매니저 없음).
1CREATE TABLE employees ( 2 id INT PRIMARY KEY NOT NULL, 3 name VARCHAR(100) NOT NULL, 4 manager_id INT NULL, 5 INDEX (manager_id), 6FOREIGN KEY (manager_id) REFERENCES employees (id) 7); 8INSERT INTO employees VALUES 9(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL) 10(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina) 11(692, "Tarek", 333), 12(29, "Pedro", 198), 13(4610, "Sarah", 29), 14(72, "Pierre", 29), 15(123, "Adil", 692);
결과 데이터 집합은 다음과 같이 보입니다:
1mysql> SELECT * FROM employees ORDER BY id; 2+------+---------+------------+ 3| id | name | manager_id | 4+------+---------+------------+ 5| 29 | Pedro | 198 | 6| 72 | Pierre | 29 | 7| 123 | Adil | 692 | 8| 198 | John | 333 | 9| 333 | Yasmina | NULL | 10| 692 | Tarek | 333 | 11| 4610 | Sarah | 29 | 12+------+---------+------------+
각 직원에 대한 관리 체인(즉, CEO에서 직원에 이르는 경로)을 포함한 조직도를 생성하려면, 재귀 CTE를 사용합니다:
1WITH RECURSIVE employee_paths (id, name, path) AS 2( 3 SELECT id, name, CAST(id AS CHAR(200)) 4 FROM employees 5 WHERE manager_id IS NULL 6 UNION ALL 7 SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) 8 FROM employee_paths AS ep JOIN employees AS e 9 ON ep.id = e.manager_id 10) 11SELECT * FROM employee_paths ORDER BY path;
CTE는 다음과 같은 output을 생성합니다:
1+------+---------+-----------------+ 2| id | name | path | 3+------+---------+-----------------+ 4| 333 | Yasmina | 333 | 5| 198 | John | 333,198 | 6| 29 | Pedro | 333,198,29 | 7| 4610 | Sarah | 333,198,29,4610 | 8| 72 | Pierre | 333,198,29,72 | 9| 692 | Tarek | 333,692 | 10| 123 | Adil | 333,692,123 | 11+------+---------+-----------------+
CTE 동작 방식:
SELECT는 CEO에 대한 row(매니저 ID가
NULL인 row)를 생성합니다.path 컬럼은 재귀
SELECT에서 생성되는 더 긴
path 값을 수용할 수 있도록
CHAR(200)으로 폭이 넓혀집니다.
재귀
SELECT에서 생성되는 각 row는 이전 row에서 생성된 직원에게 직접 보고하는 모든 직원을 찾습니다. 각 직원에 대해, row에는 직원 ID와 이름, 그리고 직원의 관리 체인이 포함됩니다. 체인은 매니저의 체인 끝에 직원 ID를 추가한 것입니다.
재귀는 직원에게 더 이상 자신에게 보고하는 직원이 없을 때 종료됩니다.
특정 직원 또는 여러 직원에 대한 path를 찾으려면, top-level
SELECT에 WHERE 절을 추가하십시오. 예를 들어, Tarek과 Sarah에 대한 결과를 표시하려면 해당
SELECT를 다음과 같이 수정합니다:
1mysql> WITH RECURSIVE ... 2 ... 3 SELECT * FROM employees_extended 4 WHERE id IN (692, 4610) 5 ORDER BY path; 6+------+-------+-----------------+ 7| id | name | path | 8+------+-------+-----------------+ 9| 4610 | Sarah | 333,198,29,4610 | 10| 692 | Tarek | 333,692 | 11+------+-------+-----------------+
common table expression(CTE)은 몇 가지 측면에서 파생 테이블과 유사합니다:
두 구문 구성 요소 모두 이름이 있습니다.
두 구문 구성 요소 모두 단일 statement의 scope에서만 존재합니다.
이러한 유사성 때문에, CTE와 파생 테이블은 종종 서로 교환하여 사용할 수 있습니다. 간단한 예로, 다음 statement는 서로 동일합니다:
1WITH cte AS (SELECT 1) SELECT * FROM cte; 2SELECT * FROM (SELECT 1) AS dt;
그러나 CTE는 파생 테이블보다 몇 가지 장점을 가지고 있습니다:
파생 테이블은 쿼리 내에서 한 번만 참조할 수 있습니다. CTE는 여러 번 참조할 수 있습니다. 파생 테이블 결과를 여러 번 사용하려면, 그 결과를 여러 번 파생해야 합니다.
CTE는 self-referencing(재귀적)일 수 있습니다.
하나의 CTE는 다른 CTE를 참조할 수 있습니다.
statement의 맨 앞에 정의가 나타나는 CTE는 statement 내부에 내포된 파생 테이블보다 더 읽기 쉬울 수 있습니다.
CTE는
CREATE [TEMPORARY] TABLE로 생성된 테이블과 유사하지만, 명시적으로 정의하거나 drop할 필요가 없습니다. CTE의 경우, 테이블을 생성할 권한이 필요하지 않습니다.
15.2.19 VALUES Statement
15.3 Transactional and Locking Statements