CTE for postgresql and sqlalchemy by 스포카

저희 서비스는 가게마다 웹에서 접속할 수 있는 어드민을 제공하는데, 프렌차이즈가 아닌 하나의 독립적인 가게들일 경우 정보를 가져와 나타내는 데는 굳이 CTE 를 쓸 필요가 없지만 프렌차이즈일 경우 본사와 지점들로 나누어져 있어서 본사와 지점들 정보를 다 가져오기 위해서 CTE 를 사용하게 되었습니다.

그럼 postgresql 의 CTEReadme 에 나와 있는 예제와 sqlalchemy core 로 변환하는 것까지 살펴보겠습니다.

CTE란?

Common table expression 의 약자로 ‘공통 테이블 식’입니다.

CTE 특징

WITH절 같은 SELECT 문에서 효과적으로 테이블 식을 정의 할 수 있습니다.
CTE는 VIEW의 사용방법과 비슷하지만, VIEW보다 편리합니다.
VIEW와 달리 사전에 CTE를 정의할 필요가 없습니다.
개체로 저장되지 않고, 쿼리 지속시간에만 존재합니다.
CTE는 재귀 쿼리를 사용할 수 있습니다.
재귀 CTE는 여러행을 반환 가능합니다.
동일 문에서 결과 테이블을 여러번 참조 가능합니다.

재귀 CTE 예제

아래 예제는 ‘A’부서 하위에 있는 부서만 추출하는 예제입니다.

일단 재귀 CTE를 이용한 쿼리를 사용하려면 ‘WITH RECURSIVE’ 키워드를 추가해야 합니다.

Table ‘department’ 인접 리스트로 조직 구조를 나타냅니다.

CREATE TABLE department (
id INTEGER PRIMARY KEY, -- department ID
    parent_department INTEGER REFERENCES department, 
    -- upper department ID
    name TEXT -- department name
);

INSERT INTO department (id, parent_department, "name")
VALUES
(0, NULL, 'ROOT'),
(1, 0, 'A'),
(2, 1, 'B'),
(3, 2, 'C'),
(4, 2, 'D'),
(5, 0, 'E'),
(6, 4, 'F'),
(7, 5, 'G');

screen

부서 구조:

ROOT-+->A-+->B-+->C
 | |
 | +->D-+->F
 +->E-+->G

A의 하위 부서를 추출, 다음과 같은 재귀 쿼리를 사용할 수 있습니다.

WITH RECURSIVE subdepartment AS
(
    -- non-recursive term
    SELECT * FROM department WHERE name = 'A'

    UNION ALL

    -- recursive term
    SELECT d.* FROM department AS d
    JOIN subdepartment 
    AS sd ON (d.parent_department = sd.id)
)
SELECT * FROM subdepartment ORDER BY name;

위의 쿼리는 다음과 같이 설명할 수 있습니다.

중간 테이블(Intermediate table), 작업 테이블(work table), 결과 테이블(result table)이 있습니다.

초기화
1. 비재귀 구간을 실행 (SELECT * FROM department WHERE name = ‘A’)
2. ResultTable = WorkTable = (‘A’) 결과 테이블과 작업 테이블에 결과를 배치합니다.
3. IntermediateTable = () 중간 테이블을 비웁니다.
재귀 쿼리 실행
1. (SELECT d.* FROM WT AS d JOIN subdepartment AS sd ON d.parent_department = sd.id) 하위 부서와 작업 테이블을 바꾸고, 재귀 구간을 실행합니다.
2. 중간 테이블에 쿼리 결과를 할당합니다.
3. 결과 테이블 및 작업 테이블에 중간테이블 추가합니다.
4. 중간 테이블을 비웁니다.
재귀가 끝났는지 확인
1. 2번 과정의 중간테이블이 비어 있으면 재귀의 실행이 종료되고, 결과 테이블은 반환됩니다.
2. 중간테이블이 비어 있지 않으면 다시 2번의 과정으로 돌아갑니다.

“subdepartment”는 재귀 표현을 포함하고 있는 CTE입니다. 먼저 비재귀항이 평가되고, 다음 재귀항이 평가됩니다. 재귀항은 평가하고 처리하는 데이터가 없을 때까지 결과가 반복적으로 이전 결과에 추가됩니다. 끝으로 마지막 SELECT가 실행되고 데이터는 결과 집합에서 추출됩니다.

CTE의 한계점

SEARCH 및 CYCLE 절은 구현되지 않습니다.
상호 재귀는 허용되지 않습니다.
UNION ALL의 마지막 SELECT만 재귀 이름을 포함할 수 있습니다.
재귀와 재귀스캔(RecursiveScan) 계획의 비용은 항상 0입니다

sqlalchemy 로 변환

sqlalchemy 에서 필요한 모듈들을 불러옵니다.

from sqlalchemy import Table, Column, Text, Integer, MetaData, select
 
metadata = MetaData()

department 테이블을 정의합니다.

department = Table('department', metadata,
Column('id',Integer),
Column('parent_department',Integer),
Column('name',Text))

WITH 절부터 시작되는 CTE 부분의 비재귀항을 subdepartment로 만듭니다. 재귀 사용을 위해 .cte( recursive=True) 부분을 붙여줍니다.

subdepartment = select([
department.c.id,
department.c.parent_department,
department.c.name]).where(department.c.name == 'A') \
.cte(recursive=True)

department 와 subdepartment 에 각각 alias를 붙여줍니다.

subd_alias = subdepartment.alias()
department_alias = department.alias()

CTE 부분의 재귀항과 비재귀 항을 union all 해주는 subdepartment를 만듭니다. (이 부분이 postgresql 예제 쿼리에서 봤던 WITH RECURSIVE subdepartment 전체를 나타내는 부분이라 할 수 있습니다.)

subdepartment = subdepartment.union_all(
select([
department_alias.c.id,
department_alias.c.parent_department,
department_alias.c.name]) \
.where(department_alias.c.parent_department == subd_alias.c.id))

마지막으로 결과 쿼리를 출력하기 위한 statement를 만듭니다.

statement = select([
subdepartment.c.id,
subdepartment.c.parent_department,
subdepartment.c.name]).order_by(subdepartment.c.name)

원문: CTEReadme

참조: 공통 테이블 식 사용 ,공통 테이블 식을 사용하는 재귀 쿼리

#스포카 #개발 #개발자 #서버개발 #개발팀 #꿀팁 #인사이트 #조언