MySQL 재귀 쿼리

1. MySQL 재귀 쿼리
2. MySQL에서 재귀 쿼리 구현

이 가이드에서는 MySQL의 재귀 쿼리에 대해 알아봅니다. SQL에서 재귀 쿼리를 작성하는 방법과 작동 방식은 이 가이드에서 더 나은 이해를 위해 설명됩니다.

MySQL 재귀 쿼리

SQL의 재귀 쿼리는 하위 쿼리입니다. 이름에서 알 수 있듯이 재귀적으로 작동합니다. 기본 케이스, 사용자 정의 이름 및 종료 조건이 있는 재귀 케이스가 있습니다.

with [Recursive] CTE(user_defined name) AS
(
    SELECT query (Non Recursive query or the Base query)
    UNION
    SELECT query (recursive query using the name [with a termination condition])
)
SELECT * from CTE_name;

위는 recursive SQL 쿼리의 의사 코드입니다. 그것에 대해 더 깊이 파고들어 보자.

와 절

with [Recursive] CTE(user_defined name) AS

SQL의 with 절은 SQL의 재귀 키워드와 함께 처음에 사용됩니다. 그런 다음 AS 키워드가 사용된 다음 사용자 정의 이름인 CTE가 사용됩니다.

이 키워드 구문은 쿼리의 기본 사례를 구성합니다.

‘CTE’는 ‘공통 테이블 표현식’이라고 하며, 나중에 ‘SELECT’ 문에서 사용할 사용자가 정의한 임시 명명된 결과 집합입니다.

기본 쿼리

SELECT query (Non Recursive query or the Base query)

이는 기본 쿼리라고 하는 두 쿼리 중 첫 번째 쿼리입니다. 비재귀 쿼리이며 재귀가 발생하는 기본 입력이 여기에 제공됩니다.

연합 조항

UNION

Union 연산자는 중간에 첫 번째 쿼리와 두 번째 쿼리를 병합하는 데 사용됩니다.

재귀 쿼리

SELECT query (recursive query using the name [with a termination condition])

이것은 with 절을 사용하여 이전에 생성한 CTE 테이블을 제공하고 true가 될 때 재귀를 종료하는 종료 조건도 제공해야 하는 재귀 쿼리입니다.

기본 및 재귀 쿼리는 위의 의사 코드에 표시된 것처럼 괄호 () 안에 작성됩니다.

SELECT * from CTE_name

이 쿼리는 이 재귀 기술을 사용하여 생성된 테이블을 봅니다.

MySQL에서 재귀 쿼리 구현

이 쿼리를 올바르게 구현하여 이 쿼리의 작동 방식을 이해해 봅시다.

with recursive number_printer AS
(
    SELECT 1 AS digit
    UNION
    SELECT digit+1 FROM number_printer WHERE digit<5
)
SELECT * FROM number_printer

위의 표는 프로그램 실행 결과입니다. 실행 방법은 다음과 같습니다.

쿼리는 with 절과 recursive 키워드를 사용하여 시작하고 print_number를 CTE로 사용했습니다. 코드를 실행하자마자 프로그램은 그것이 재귀 쿼리임을 이해하기 때문에 기본 사례를 찾습니다.

기본 쿼리에서 1부터 기본 사례를 시작했으며 여기에서 사용된 별칭은 digit입니다. 실행의 첫 번째 반복에서 출력은 기본 쿼리에서 반환된 결과입니다.

초기 레코드는 기본 쿼리의 출력입니다.

기본 쿼리에서 반환된 결과는 재귀 쿼리의 입력이 됩니다. 두 번째 반복에서 재귀 쿼리는 이전 쿼리의 출력 데이터를 사용하고 종료 조건을 확인합니다.

종료 조건이 충족되면 반복이 중지됩니다. 그렇지 않으면 세 번째 반복에 들어갑니다. 세 번째 반복은 두 번째 반복에서 반환된 출력을 입력으로 사용합니다.

이것은 재귀가 발생하는 유일한 논리입니다.

세 번째 반복이 실행되고 이 반복의 결과 출력이 네 번째 반복의 입력으로 사용됩니다. 그런 다음 네 번째 반복은 입력과 함께 기본 쿼리의 출력을 추가합니다.

종료 조건이 충족될 때까지 재귀적으로 계속 발생합니다.

여기서 number_printer라는 테이블이 매우 중요합니다. 이 쿼리를 재귀적으로 만들려면 테이블을 사용해야 합니다. 쿼리 완료를 위해 WHERE절을 사용하여 종료 조건을 작성합니다.

이제 재귀 쿼리에 대한 또 다른 예를 살펴보겠습니다.

예 2: 계층

계층 구조가 있는 조직이 있다고 가정합니다. 맨 위에는 매니저가 있고, 그 매니저의 직속 매니저 두 명이 있고, 이 두 매니저는 각자 직속 매니저가 한 명씩 있다.

이제 id, name 및 ManagerID와 같은 열이 있는 network라는 테이블을 만들었습니다. 이들은 각각 직원의 ID, 이름 및 관리자 ID를 정의합니다.

다음 표에 나와 있습니다.

위의 테이블에는 직원의 이름과 관리자 ID가 있지만 앞에서 설명한 계층 구조를 기반으로 이러한 관리자에게 적절하게 순위를 지정하려고 합니다. 이러한 이유로 다음 코드를 사용합니다.

WITH RECURSIVE hierarchy AS
( SELECT id, name, 1 AS level FROM network WHERE id = 1
UNION
SELECT n.id, n.name, h.level+1 FROM hierarchy h
JOIN network n on h.id = n.ManagerID
)
SELECT * FROM hierarchy

위의 재귀 쿼리 코드는 우리가 원하는 대로 문제를 해결할 것입니다. 이 코드가 무엇을 하는지 살펴보고 이해해 봅시다.

With 절은 구문에 따라 Recursive 키워드와 함께 사용되며 여기서 CTE는 계층 구조로 명명됩니다.

기본 쿼리에서 network 테이블에서 id 및 name을 선택하고 id가 1인 값을 가져옵니다. 그런 다음 AS 레벨 값을 정수 1로 설정합니다.

여기에서 수준은 계층 구조에서 관리자의 위치를 보여주기 위해 사용됩니다.

기본 쿼리가 완료되었습니다. 이제 코드는 UNION 절을 사용하여 기본 쿼리를 재귀 쿼리와 병합합니다.

재귀 쿼리는 network 테이블에서 id 및 name을 선택합니다. 여기에서 n은 네트워크 테이블의 별칭이며 여기서 이 점(.)은 입력으로 제공한 특정 테이블에서 가져올 값을 제한합니다.

h는 hierarchy 테이블의 별칭이며 level+1 절은 hierarchy 테이블에서 가져온 정수 값에 1을 추가합니다.

재귀 쿼리의 이 부분은 network 테이블에서 name과 id를 가져오고, 정수 값은 hierarchy에서 가져오며 1이 추가되고 출력은 level에 저장됩니다. . 이 쿼리는 관리자 수준을 업데이트하고 정의합니다.

쿼리의 다음 부분에서는 JOIN절이 사용되며 기본적으로 Inner join으로 작동합니다. 여기에서 network 테이블의 ManagerID는 hierarchy 테이블의 id와 일치하고 위의 쿼리와 내부 조인됩니다.

관리자 ID와 ID가 일치하면 관리자의 수준에 1을 더하여 해당 엔터티의 수준을 할당하는 논리입니다. 이런 식으로 감독 순위가 지정됩니다.