[SQL] WITH 절

WITH 절?

● 서브쿼리 기능 대신 WITH 절로 가독성 있고 재사용하기 쉽게 함

● 어떤 기능을 제공하는가?

  - 임시 테이블 정의
  - 쿼리에서 마치 테이블처럼 사용할 수 있게 함
  - 공통 테이블 표현식(CTE)
  - 서브쿼리와 비슷한 역할을 하지만 가독성과 재사용성에서 더 좋음

- 예시 사용법

WITH player_total_hours AS (
  SELECT player_id, SUM(hours_played) AS total_hours
  FROM player_activity
  GROUP BY player_id
)
SELECT pa.player_id, q.quest_name, q.difficulty, pa.hours_played
FROM player_activity pa
INNER JOIN quests q ON pa.quest_id = q.quest_id
INNER JOIN player_total_hours pth ON pa.player_id = pth.player_id
WHERE pth.total_hours >= 5;

 

- 위 테이블은 각 플레이어가 퀘스트의 난이도를 표시하고, 총 플레이 시간이 5시간 이상인 플레이어만
  필터링하려는 쿼리

  1) WITH 절로 player_total_hours 임시 테이블 생성
            → 플레이어별로 총 플레이 시간 계산
  2) 임시 테이블을 JOIN으로 다시 player_activity 테이블과 결합
            → 플레이 시간 5시간 이상인 플레이어 필터링

 

가독성, 재사용이 좋다 했는데 무조건 사용하는 게 좋은가

● Node.js 에서 실제로 코드를 실행했을 때

const ids = await db.query(
'SELECT player_id FROM player_activity GROUP BY player_id HAVING SUM(hours_played) >= 5'
); // 여기선 그냥 HAVING을 이용해 쿼리로 필터링 
const playerIds = ids.map(id => id.player_id);

  - 쿼리를 실행하면 플레이어 ID를 가져오는 쿼리 실행 후 그 결과를 구축한 게임 서버에서
    특정 조건에 맞는 플레이어 ID를 추출한 후 playerIds와 같은 배열로 저장

   → 후처리(Post-processing)를 한 번 더 함 = 성능적 병목 발생    

 

  - 이러한 구현은 개발자 입장에서는 쉬운 이해가 가능하지만...

  - 네트워크에 두 번 요청하고 이는 곧 백엔드 서버와 DB 간의 네트워크 왕복(Round Tip) 발생
  - 즉, 네트워크 레이턴시가 늘어남

    → 이는 곧 게임 서버 메모리 사용량 증가, 쿼리 성능 저하 발생

 

● 결론 :
 - 쿼리를 쪼개서 코딩하는 것보다는 WITH 절로 한 번의 쿼리로 해결할 수 있다

 - 공통 테이블 표현식(CTE=WITH절)은 메모리에서 처리되므로 대용량 데이터에서는 사용 x