[MYSQL]CTE:Common Table Expression

potato·2024년 3월 29일

CTE Common Table Expression programmers sql예제 with절

DB

목록 보기

2/3

* 참고자료

위 문제를 진행하며 겪은 쿼리의 개선과정을 공유합니다.

1. 쿼리 설계 방식에 따른 경제성 비교

문제:

실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블에서 대장균 개체의 ID(ID)와 자식 개체 수(CHILD_COUNT)를 출력하는 SQL 쿼리를 작성하는 문제입니다. 문제를 진행하며 생성형AI인 Gemini 의 쿼리 개선 방식을 참고했습니다.

1. A 쿼리:

SELECT
    ID,
    (SELECT COUNT(ID) FROM ECOLI_DATA WHERE PARENT_ID = ED.ID) AS CHILD_COUNT
FROM
    ECOLI_DATA ED
ORDER BY
    ID ASC;

2. B 쿼리(+ Gemini):

WITH CTE AS(
    SELECT  PARENT_ID AS ID,
            COUNT(*) AS CHILD_COUNT
    FROM    ECOLI_DATA
    GROUP BY PARENT_ID
)
SELECT  ED.ID,
        COALESCE(CTE.CHILD_COUNT,0) AS CHILD_COUNT
FROM    CTE
RIGHT JOIN ECOLI_DATA ED ON CTE.ID = ED.ID
ORDER BY ED.ID ASC;

성능 비교:

테이블 레코드	A 쿼리	B 쿼리
적은 레코드 (10만 개)	⏱️ 빠름	⏱️ 빠름
대량의 레코드 (1억 개)	느림	⚡️ 빠름

분석:

적은 레코드:
- 두 쿼리의 성능이 비슷합니다.
- A 쿼리가 조금 더 간단하여 간편하게 사용할 수 있습니다.
대량의 레코드:
- B 쿼리가 A 쿼리보다 훨씬 빠릅니다.
- B 쿼리는 GROUP BY 연산을 사용하여 자식 개체 수를 미리 계산하여 테이블 스캔을 줄입니다.

결론:

적은 레코드: 두 쿼리 모두 사용 가능, A 쿼리는 간헐적 조회가 발생할 때 추천. 단, 대량의 레코드가 담긴 테이블에 주기적인 조회가 발생하는 경우 A 쿼리는 부적합
대량의 레코드: B 쿼리가 효과적임. 비지니스 측면에서 봤을 때 미생물 연구실 등에서 지속적으로 생성되는 데이터를 분석할 때 사용하기 좋음.

2. CTE (Common Table Expression) 에 간략한 대한 설명

CTE는 SQL에서 반복적으로 사용되는 서브쿼리를 저장하고 재사용할 수 있도록 하는 기능입니다. CTE를 사용하면 쿼리를 명확하고 간결하게 작성할 수 있습니다.

CTE 종류:

비재귀 CTE: 자기 자신을 참조하지 않는 CTE입니다. 재사용성이 적을 때 권장합니다.

예시:

WITH
    TEMP_TABLE AS (
        SELECT
            PARENT_ID AS ID,
            COUNT(*) AS CHILD_COUNT
        FROM
            ECOLI_DATA
        GROUP BY
            PARENT_ID
    )
SELECT
    ID,
    COALESCE(CHILD_COUNT, 0) AS CHILD_COUNT
FROM
    TEMP_TABLE
ORDER BY
    ID ASC;

재귀 CTE: 자기 자신을 참조하는 CTE입니다. Depth, Level 등을 표현할 때 권장합니다.

예시:

WITH RECURSIVE
    CHILD_COUNT_TREE (ID, PARENT_ID, CHILD_COUNT) AS (
        SELECT
            ID,
            PARENT_ID,
            1 AS CHILD_COUNT
        FROM
            ECOLI_DATA
        WHERE
            PARENT_ID IS NULL
        UNION ALL
        SELECT
            EC.ID,
            EC.PARENT_ID,
            CHILD_COUNT_TREE.CHILD_COUNT + 1
        FROM
            ECOLI_DATA EC
        INNER JOIN
            CHILD_COUNT_TREE
        ON
            EC.PARENT_ID = CHILD_COUNT_TREE.ID
    )
SELECT
    ID,
    CHILD_COUNT
FROM
    CHILD_COUNT_TREE
ORDER BY
    ID ASC;

3. 재귀 CTE와 비재귀 CTE 성능 비교 및 발전 방안

성능 차이:

데이터 규모:
- 적은 데이터: 비재귀 CTE가 더 빠릅니다.
- 대량 데이터: 재귀 CTE가 더 빠를 수 있습니다.
쿼리 패턴:
- 단순한 계산: 비재귀 CTE가 더 효율적입니다.
- 복잡한 계산: 재귀 CTE가 더 간결하고 효율적입니다.

비교 요약:

기준	비재귀 CTE	재귀 CTE
데이터 규모	적은 데이터: ⏱️ 빠름	대량 데이터: ⚡️ 빠를 수 있음
쿼리 패턴	단순 계산: 효율적	복잡 계산: 간결하고 효율적

발전 방안: