[DataBase] WITH절 알아보기

요즘 한참 글을 안쓰다 여유가 생겨 글을 써보고자 한다

최근 진행중인 프로젝트에서 시계열 데이터를 다루기 위해

timescaledb를 사용하고 db 설계부터 클라이언트 로직을 구현하고 있다

 

테이블 연관관계과 복잡해지고 필요한 데이터를 뽑기위해

쿼리문을 작성하는데 서브쿼리, 조인문을 많이 사용하게 되었다

 

그러던중 복잡한 쿼리문을 비교적 간단하게 만들어주고

성능도 높일 수 있는 WITH 절을 알게되고 사용해 보았다

 

1. WITH절 이란?

with절은 SQL 에서 쿼리를 작성할 때 하나의 서브쿼리 또는 임시 테이블처럼 활용할 수 있는 기능이다

 

임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷한데 차이점이 있다

 

VIEW는 한번 만들어놓으면 DROP할때까지 없어지지 않지만

WITH절의 같은경우 한번 실행할 쿼리문내에 정의되어 있을경우

그 쿼리문안에서만 실행된다는 차이점이 있다

 

2. WITH절을 사용하는 방법

ex1)

with {테이블 명} as (
 
    with절로 저장하고 싶은 SQL 쿼리문
    
)
 
select * from {with절로 저장한 테이블명};

 

ex2)

WITH EXAMPLE AS
(
 SELECT 'WITH절' AS STR1
 FROM DUAL
)

SELECT * FROM EXAMPLE

 

위에 쿼리는 WITH절을 설명하기 위해 아주 간단하게 작성했다

 

EXAMPLE 이라는 WITH문을 하나 만들고

그 안에 출력 쿼리를 작성하며 된다

 

저렇게 보면 왜 쓰나 싶을 수 있어서 같은 값을 뽑는데

WITH문을 쓴것과 JOIN을 쓴것을 아래에서 비교해 보겠다

 

WITH문)

WITH ranked_records AS (
    SELECT 
        ht.chunk_name, ht.hypertable_name,
        ROW_NUMBER() OVER(PARTITION BY ht.hypertable_name ORDER BY ht.range_start DESC) as rn
    FROM 
        timescaledb_information.chunks AS ht
)
SELECT * FROM ranked_records WHERE rn = 1;

 

JOIN문)

SELECT timescaledb_information.chunks.*
FROM timescaledb_information.chunks
JOIN (
    SELECT hypertable_name, MAX(range_start) AS max_range_start
    FROM timescaledb_information.chunks
    GROUP BY hypertable_name
) AS max_records ON timescaledb_information.chunks.hypertable_name = max_records.hypertable_name 
                  AND timescaledb_information.chunks.range_start = max_records.max_range_start;

 

쿼리가 복잡해지면 복잡해질 수록 WITH문의 위력이 나오는것 같다

 

3. WITH절 사용하는 이유

WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우

그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능을 높일 수 있는데

WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있다

자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로

쿼리의 성능향상에 도움이 된다