PostgreSQL Tutorial 08. Common Table Expressions

jwKim·2023년 3월 3일

🎯 PostgreSQL Tutorial

목록 보기

8/9

01. PostgreSQL CTEs(common table expression)

01-01. 개요

CTE는 다른 SQL문(SELECT, INSERT, DELETE 등) 내에서 사용할 수 있는 임시 결과 세트이다.

조인이나 서브쿼리가 중첩되면 코드 길이도 길어지고 가독성이 떨어지기 때문에 CTE를 사용하여 임시 테이블을 생성하여 서브쿼리 결과를 재활용하며 WITH를 키워드로 사용한다.

-- CTE 문법
WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

CTE의 이름 지정한다.
WITH 절에서 사용하며 임시 결과 세트로 도출할 쿼리를 선언한다. 컬럼을 지정하지 않으면 CTE 쿼리 내에서 선택한 컬럼이 자동으로 선택된다.
CTE는 SELECT, INSERT, UPDATE, DELETE 문에서 테이블이나 뷰처럼 사용된다.

[ VIEW ]

SQL에서 view란?

view는 SELECT문과 같다고 생각하면 되며, 한 개 이상의 테이블을 사용하여 생성된 가상 테이블이다.

디스크에 물리적으로 저장되지 않기 때문에 가상 테이블이라고 불린다.

view를 통해 제한적으로 데이터를 보이게 하는데 사용되며, 사용자는 주어진 view를 통해 기본 테이블을 제한적으로 사용한다.

데이터 딕셔너리(DB 운영에 필요한 스키마, 사용자, 권한 등의 정보가 있는 테이블)의 데이터를 조회하는 데에 주로 사용된다.(INSERT, DELETE, UPDATE도 가능하긴 함)

참고한 사이트

[DB] View란?

[ SQL의 조건문 ]

SQL문에서 조건문을 사용하기 위해서는 CASE WHEN ~ END구문을 사용하면 된다. 사용 방법은 다음과 같다.

CASE
$\quad$ WHEN 조건 1 THEN 결과 1
$\quad$ WHEN 조건 2 THEN 결과 2
$\quad$ ...
$\quad$ (ELSE 결과 N)
END

ELSE는 쓰지 않아도 되지만 NULL 값이 있는 경우를 대비하여 어떤 것이라도 넣어주는 것이 좋다고 함

참고한 사이트
[PostgreSQL] 조건에 따라 다른값 보여주기 (CASE WHEN ~ ELSE ~ END)

01-02. 예제

-- 예제 1) film 테이블의 length컬럼값 변경한 임시 테이블 생성
WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) AS length    
    FROM
        film
)

SELECT *
FROM
	cte_film
;

⇒ CTE를 선언하는 부분을 보면, film 테이블에서 film_id, title, length를 고르는데, length는 조건문을 활용하여 값을 변경한다. 그 결과로 나오는 테이블을 ‘cte_film’으로 이름을 지정함

<rental 테이블에서 staff_id 별 대여 횟수를 반환하는 CTE 생성

-- 예제 2) 테이블과의 조인
WITH cte_rental AS (
    SELECT 
				staff_id,
        COUNT(rental_id) AS rental_count
    FROM   
				rental
    GROUP BY 
				staff_id
)
SELECT *
FROM
		cte_rental
;

-- 예제 2) 테이블과의 조인
WITH cte_rental AS (
    SELECT 
				staff_id,
        COUNT(rental_id) AS rental_count
    FROM   
				rental
    GROUP BY 
			staff_id
)
SELECT 
	s.staff_id,
	s.first_name,
	s.last_name,
	cr.rental_count
FROM
	staff AS s
INNER JOIN cte_rental AS cr
	on cr.staff_id = s.staff_id
;

[ window function ]

window function은 집계함수와 비슷하게 동작하는데, 집계함수는 결과로 하나의 행만 반환하는 반면, window function은 여러 개의 행을 반환하는 것이 큰 차이점이다. 사용법은 다음과 같다.

EX) 제품 품목에 따른 가격 평균 내기
AVG (price) OVER (
PARTITION BY group_name)

⇒ 집계함수를 사용하고 뒤에 그룹화 할 기준 컬럼을 명시한다.

-- 예제 3) window fuction과 함께 사용하기
WITH cte_film AS (
	SELECT
		title,
		rating,
		length,
		RANK() OVER(
			PARTITION BY rating
			ORDER BY length DESC) AS length_rank
	FROM
		film
)

SELECT *
FROM
	cte_film
;

⇒ 그룹화 기준은 rating으로 하여 length의 순위를 매긴다.

02. Recursive query using CTEs

02-01. 개요

recursive 쿼리는 조직구조, BOM(SAP 시스템에서 자재 품목을 기입해 놓은 리스트)와 같은 계층 구조에서 유용하게 사용한다. 사용 방법은 다음과 같다.

WITH RECURSIVE cte_name AS(
    CTE_query_definition -- non-recursive
    UNION [ALL]
    CTE_query_definion  -- recursive
) 

SELECT * 
FROM 
	cte_name;

non_recursive : CTE의 기본 구조를 잡는 부분
recursive : UNION (ALL)을 사용하여 결합함. cte_name 자체를 사용한다.
이전 반복에서 반환 되는 행이 없으면 recursive 중지

[ recursive의 의미 ]

recursive는 ‘반복적으로 자기 자신을 활용하는 것’을 의미한다. 다음은 recursive의 의미를 이해하기 위한 파이썬 코드이다.
def recursive_function(num):
    print(num)
    recursive_function(num+10)

recursive_function(1)

>1
11
21
31
...
recursive_function() 함수에는 숫자를 입력값으로 받아 출력하고 10을 더하여 함수 자기 자신을 다시 호출하는 방식으로 작동한다. recursive_function()처럼 함수 내부에서 자기 자신을 반복적으로 호출하는 구조를 recursive라고 한다.

recursive CTE가 동작하는 방법은 다음과 같다.

non_recursive 부분에서 결과 세트를 생성(0번째로 간주하므로 R0으로 표기)
R0의 결과를 R1의 입력값으로 사용, R1의 결과를 R2의 입력값으로 사용.... Ri의 결과를 Ri+1의 입력값으로 사용(반환되는 결과가 없을 때 까지)
모든 출력 값을 UNION (ALL)을 통해 통합

02-02. 예제

예제에서 사용할 employees 테이블은 아래와 같다. 20명의 직원 데이터가 있다. 직원 id가 있고, 직속 상사인 매니저의 id가 있다. 부하직원이 있는 직원 데이터를, 직원과 매니저 모두 반환해보자.

-- 예제 1) 특정 직원의 부하직원 검색
WITH RECURSIVE subordinates AS (
	SELECT
		employee_id,
		manager_id,
		full_name
	FROM
		employees
	WHERE
		employee_id = 2
	
	UNION
	
	SELECT
		e.employee_id,
		e.manager_id,
		e.full_name
	FROM
		employees AS e
	INNER JOIN subordinates AS s 
		ON s.employee_id = e.manager_id
) 

SELECT *
FROM
	subordinates;