77강 SQL 초석 다지기 : PostgreSQL Common Table Expressions

PostgreSQL 기초강의, PostgreSQL 기초강좌, 커먼테이블

77강 SQL 초석 다지기 : PostgreSQL Common Table Expressions (CTE)   

 

SQL 초석 다지기 77강 시작합니다.

 

이번 시간에는

▶ PostgreSQL Common Table Expressions

에 대해서 알아보는 시간입니다.

 

이번 시간에는 PostgreSQL CTE(공통 테이블 표현식)를 사용하여 복잡한 쿼리를 단순화하는 방법에 대해 알아봅니다.

---

Introduction to PostgreSQL common table expressions (CTE)   

공통 테이블 표현식은 SELECT, INSERT, UPDATE 또는 DELETE를 포함한 다른 SQL 문 내에서 참조할 수 있는 임시 결과 집합입니다.

공통 테이블 표현식은 쿼리 실행 중에만 존재한다는 점에서 일시적입니다.

다음은 CTE 생성 구문을 보여줍니다:

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement; 

 

위 구문에서:

• 먼저 CTE의 이름 다음에 선택적 열 목록(column_list)을 지정합니다.
• 둘째, WITH 절의 본문 안에서 결과 집합을 반환하는 쿼리를 지정합니다. CTE 이름 뒤에 열 목록(column_list)을 명시적으로 지정하지 않으면 CTE_query_definition의 선택 목록이 CTE의 열 목록이 됩니다.
• 셋째, CTE를 SELECT, INSERT, UPDATE 또는 DELETE가 될 수 있는 문의 TABLE이나 VIEW처럼 사용합니다.

공통 테이블 표현식 또는 CTE는 일반적으로 PostgreSQL에서 복잡한 조인 및 하위 쿼리를 단순화하는 데 사용됩니다.

---

PostgreSQL CTE examples   

이해를 돕기 위해 CTE를 사용하는 몇 가지 예를 들어 보겠습니다.

 

A simple PostgreSQL CTE example   

우리는 샘플 데이터베이스의 film과 rental 테이블을 시연에 사용할 것입니다.

다음 예를 참조하십시오:

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) length    
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM 
    cte_film
WHERE
    length = 'Long'
ORDER BY 
    title;  

일부를 보면,

결과

이 예제에서는 먼저 WITH 절을 사용하여 cte_film이라는 일반적인 테이블 표현식을 다음과 같이 정의했습니다:

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 
                THEN 'Short'
            WHEN length >= 30 AND length < 90 
                THEN 'Medium'
            WHEN length >=  90 
                THEN 'Long'
        END) length    
    FROM
        film
)  

공통 테이블 표현식은 두 부분으로 구성됩니다:

• 첫 번째 부분은 cte_film인 CTE의 이름을 정의합니다.
• 두 번째 부분에서는 식을 행으로 채우는 SELECT 문을 정의합니다.

그런 다음 SELECT 문에서 cte_film CTE를 사용하여 길이가 'Long'인 필름만 반환했습니다.

---

Joining a CTE with a table example   

다음 예에서는 rental  및 staff 테이블을 사용합니다:

다음 문장은 CTE를 테이블과 결합하는 방법을 보여줍니다:

WITH cte_rental AS (
    SELECT staff_id,
        COUNT(rental_id) rental_count
    FROM   rental
    GROUP  BY staff_id
)
SELECT s.staff_id,
    first_name,
    last_name,
    rental_count
FROM staff s
    INNER JOIN cte_rental USING (staff_id);  

이 예에서는 다음을 수행합니다:

• 먼저, CTE는 직원 ID와 대여 횟수를 포함하는 결과 집합을 반환합니다.
• 그런 다음 staff_id 열을 사용하여 직원 테이블을 CTE와 결합합니다.

다음은 출력입니다:

결과

---

Using CTE with a window function example   

다음 명령문은 RANK() window function과 함께 CTE를 사용하는 방법을 보여줍니다:

WITH cte_film AS  (
    SELECT film_id,
        title,
        rating,
        length,
        RANK() OVER (
            PARTITION BY rating
            ORDER BY length DESC) 
        length_rank
    FROM 
        film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;  

위 예에서는 다음을 수행합니다:

• 먼저, 우리는 각 영화 등급에 대해 길이별로 영화 순위를 반환하는 CTE를 정의합니다.
• 두 번째로, 우리는 길이 순위가 1인 영화만 골랐습니다.

다음 그림은 출력을 보여줍니다:

결과

RANK() window function대해서는 함수 강좌에서 다루게 될 것입니다.

---

PostgreSQL CTE advantages   

다음은 일반적인 테이블 표현식 또는 CTE를 사용할 때의 몇 가지 이점입니다:

• 복잡한 쿼리의 가독성을 향상합니다. CTE를 사용하여 복잡한 쿼리를 보다 체계적이고 읽기 쉬운 방식으로 구성할 수 있습니다.
• 재귀 쿼리를 만들 수 있습니다. 재귀 쿼리는 자체를 참조하는 쿼리입니다. 재귀 쿼리는 조직도 또는 재료 명세서와 같은 계층적 데이터를 쿼리 할 때 유용합니다.
• window function과 함께 사용합니다. CTE를 window function과 함께 사용하여 초기 결과 세트를 만들고 다른 선택 문을 사용하여 이 결과 세트를 추가로 처리할 수 있습니다.

---

이번 강의에서는 복잡한 쿼리를 단순화하기 위한 PostgreSQL CTE 또는 공통 테이블 표현식에 대해 배웠습니다.

 

CTE는 2005년경부터 나온 걸로 아는데, 전 아직도 서브쿼리가 더 익숙합니다.

 

다음 강의로 찾아뵐게요. 

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.