[SQL] With ( Common Table Expression)(작성중)

와; with 내용이 많아도 너무 많다
레퍼런스 정독 하는 스따일인데.. 이건 개념익히고 세부적인 내용은 문제풀어가며 익혀야할것같다
게다가 재귀를 구현하는 방식이 Mysql 과 Oracle 이 살짝 다르다
재귀가 중요한 부분이기 때문에... DB Engine 별로 재귀부분 각각 정리해야할 것 같다 ...
그래도 재귀 with 사용하는 예시를 보다보니까 dfs 전위탐색,후위탐색 같이 동작하는것을 보고
SQL 만으로 구현 가능한 범위가 내 예상보다 더 넓다는 생각을 했다.
내용이 많아서 오늘 끝내진 못하겠네 ㅎㅎ Window function은 언제보나 ~

공통 테이블 표현식 (CTE : Common Table Expression )

개념 자체는 대표적은 3개의 DB 엔진 모두 동일하다.
서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다. 특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.

오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만, 11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.

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;

cte_name단일 공통 테이블 표현식의 이름을 지정하고 WITH절을 포함하는 명령문에서 테이블 참조로 사용할 수 있습니다.

두 번째 공통 테이블 표현식을 정의할때, 첫번째 with 문에서 정의한 공통테이블 명을 사용할수 있다.
( 첫번째 공통 테이블 표현을 정의할 때는 두번째 공통테이블명을 사용할 수 없다.

# 표현식 
WITH [RECURSIVE]
     공통테이블명 AS (공통 테이블 표현식) ,
    [ 공통테이블면2 AS (공통테이블 표현식2)]
SELECT … 

재귀 공통 테이블 표현식

재귀 공통 테이블 표현식은 자체 이름을 참조하는 하위 쿼리가 있는 표현식입니다.

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

#표현식 
WITH RECURSIVE 
   공통 테이블 명 AS 
   ( 
   SELECT …                                                     # 초기 값 
   UNION [ALL, DISTINCT] 
   SELECT … FROM 공통 테이블명 (자기자신) WHERE 반목문 탈출 조건.         # 추가 값 ( 반복문 탈출 조건)
   ) 
SELECT …

재귀 SELECT
문에서 일부 표현식이 제한됩니다
- 집계 함수 ex ) SUM
- ORDER BY
- GROUP BY
- DISTINCT

공통 테이블 표현식 재귀 제한

SELECT재귀 CTE의 경우 재귀 부분에 재귀를 종료하는 조건이 포함되어 있는 것이 중요합니다 . 폭주하는 재귀 CTE를 방지하기 위한 개발 기술로 실행 시간에 제한을 두어 강제 종료할 수 있습니다.

- 시스템 변수 는 cte_max_recursion_depth CTE의 재귀 수준 수에 대한 제한을 적용합니다. 서버는 이 변수의 값보다 더 많은 수준을 반복하는 모든 CTE의 실행을 종료합니다.
- max_execution_time 시스템 변수는 SELECT현재 세션 내에서 실행된 명령문에 대한 실행 시간 초과를 적용합니다.
- 옵티마이저 힌트 는 그것이 나타나는 명령문에 MAX_EXECUTION_TIME 대해 쿼리당 실행 시간 초과를 적용합니다 .

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

SET max_execution_time = 1000; -- impose one second timeout


WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

출처

https://dev.mysql.com/doc/refman/8.0/en/with.html

mysql 재귀 : https://horang98.tistory.com/10

오라클 : https://heodolf.tistory.com/72?category=887834

[SQL] WITH문을 이용한 계층형쿼리 ( With. CONNECT BY절)

https://goldswan.tistory.com/36

[Oracle]계층형 쿼리 사용법(START WITH, CONNECT BY PRIOR, ORDER SIBLINGS BY)

https://logical-code.tistory.com/39

[SQL] WITH절과 순환 서브쿼리