SQL CTE를 잘 활용하려면?
SQL문을 작성하면서 어떤 경우에 CTE를 사용하면 좋을지 궁금한 적이 있었나요? 이 글은 언제 CTE를 사용하면 좋을지, 그리고 어떻게 사용하면 좋을지를 다뤄볼 것입니다.
만약 여러분이 SQL CTEs에 대해 들어본 적이 있으시다면, 아마 CTE는 서브 쿼리와 함께 언급된다는 것을 눈치채셨을 수도 있습니다. 종종, 사람들은 CTE와 서브 쿼리 간의 차이가 없다고 생각합니다. 그리고 특정 결과를 얻기 위해 CTE를 사용할지 서브 쿼리를 사용할 지에 대한 의견도 분분하고요.
사실, CTE로 쓴 대부분의 쿼리문은 서브 쿼리로도 작성할 수 있습니다. 여기서 중요한 단어는 "대부분"인데요. 물론 경우에 따라 CTE만이 여러분이 원하는 결과를 얻어낼 수 있도록 도와주기도 합니다. 또, CTE와 서브 쿼리 중 골라 써도 무방한 경우도 있지만, CTE를 사용하는 게 좀 더 편할 때도 있어요.
이제부터 그 경우들에 대해 살펴보고 CTE를 활용한 쿼리문을 작성할 때 유념할 점도 함께 다뤄보려고 합니다.
CTEs가 무엇인가요?
CTEs. 풀어쓰면 'Common Table Expressions' 인데요. 쿼리를 통해 만들어낸 임시적인 데이터 세트입니다. CTEs의 문법은 다음과 같습니다.
WITH 테이블 이름 AS (테이블 만들 쿼리문)
CTEs는 WITH와 함께 쓰입니다. 위 문법을 살펴보시면 왜 그런지 아실 수 있습니다. 임시로 만든 테이블을 가지고 우리가 원하는 결과를 얻기 위한 쿼리문을 작성할 거니까요.
쿼리문을 더욱 직관적으로 읽기 좋게 도와주는 CTEs
예를 들어 이런 데이터가 있다고 가정해봅시다. 여러분은 다양한 직무의 프리랜서를 고용하는 회사에 근무하고 있습니다. 사내에는 아래와 같은 3개의 테이블(freelancer, hours_worked, job_position)에 프리랜서에 관한 다양한 정보를 저장하고 있고요.
첫 번째 테이블인 freelancer 테이블은 아래와 같은 데이터를 수집하고 있습니다.
id : 프리랜서의 아이디
first_name : 프리랜서의 이름
last_name : 프리랜서의 성
country : 프리랜서의 근무지
pay_rate : 시급
job_type_id : 직종 아이디 (job_position 테이블의 foreign key입니다)
두 번째 테이블인 hours_worked 테이블은 아래와 같은 열이 있습니다.
id : hours_worked 테이블 내 데이터의 고유 아이디
date : 일한 날짜
hours : 일한 시간
freelancer_id : 프리랜서의 아이디 (freelancer 테이블의 foreign key입니다)
마지막 테이블은 job_position입니다.
id : 직종 아이디
position_type : 직종 이름
첫 번째 예시에서는 CTEs를 사용함에 따라 쿼리문이 체계적으로 정리되고 읽기 좋게 구성될 수 있다는 것을 보여드리고자 합니다. 위 3개의 테이블을 활용하여 모든 프리랜서의 아이디, 이름, 시급, 그들의 직종, 그 직종의 평균 시급에 대해 알아보도록 합시다.
만약 서브 쿼리를 활용한다면, 여러분의 코드는 다음과 같이 작성될 것입니다.
SELECT
f.id,
f.first_name,
f.last_name,
f.pay_rate,
j.position_type,
sq.average_job_rate
FROM freelancer AS f
LEFT JOIN job_position AS j
ON f.job_type_id = j.id
LEFT JOIN
(SELECT
j.id,
AVG(f.pay_rate) AS average_job_rate
FROM freelancer AS f
LEFT JOIN job_position AS j
ON f.job_type_id = j.id
GROUP BY j.id) AS sq
ON j.id = sq.id;
이 쿼리문은 freelancer 테이블에선 id, first_name, last_name, pay_rate 열을 불러옵니다. 그리고 job_position 테이블에서는 position_type을 불러옵니다. 각각의 테이블에는 별칭이 주어졌고 (freelancer 테이블은 f, job_position 테이블은 j) freelancer 테이블의 job_type_id와 job_position 테이블의 id를 기준으로 조인해줬습니다.
여기까지는 괜찮은데, 직종별 평균 시급을 구하지 못했네요. 이번엔 테이블이 아닌 서브 쿼리를 조인했습니다. 이 서브 쿼리를 잘 살펴보면, freelancer 테이블과 job_position 테이블을 freelancer 테이블의 job_type_id와 job_position의 id로 조인해주었네요. 그래야만 직종 아이디 열을 불러올 수 있거든요.
그다음엔 AVG() 함수를 이용해 평균 시급을 계산할 겁니다. 그리고 그 열은 average_job_rate이라는 이름을 지어주었습니다. 집계 함수를 썼으니 GROUP BY를 사용해 직종 아이디별로 평균값을 나눠서 살펴봅니다. 이젠 서브 쿼리까지 다 준비가 되었으니, 메인 쿼리의 SELECT 문에 sq.average_job_rate 열을 입력해 주었습니다.
위의 쿼리문을 실행하면, 아래와 같은 결과를 얻게 됩니다.
| Id | first_name | last_name | pay_rate | position_type | average_job_rate |
| 1 | Carole | Queen | 60 | SQL Developer | 58.9 |
| 2 | Arthur | Strauss | 42 | SQL Developer | 58.9 |
| 3 | Cindy | Jones | 75 | SQL Developer | 58.9 |
| 4 | Paul | Hopkins | 25.5 | Content Writer | 24.618181 |
| 5 | Katherine | McLean | 32 | Content Writer | 24.618181 |
| 6 | Don | Jones | 18.5 | Content Writer | 24.618181 |
| 7 | Linda | Stones | 40.5 | Data Analyst | 39.45 |
| 8 | Trent | Arthurs | 12.5 | Data Analyst | 39.45 |
| 9 | Miriam | Jones | 52 | Data Analyst | 39.45 |
| 10 | Guy | Gudbois | 36 | SQL Developer | 58.9 |
| 11 | Michelle | Lebouef | 40 | SQL Developer | 58.9 |
| 12 | Jean-Paul | Lamontagne | 26 | Content Writer | 24.618181 |
| 13 | Camille | Barbet | 16 | Content Writer | 24.618181 |
| 14 | Jules | Fournier | 24.8 | Content Writer | 24.618181 |
| 15 | Paulette | Dubois | 32 | Content Writer | 24.618181 |
| 16 | Mohamed | Lambert | 38 | Content Writer | 24.618181 |
| 17 | Simone | Pomeroy | 36.7 | Data Analyst | 39.45 |
| 18 | Gaspard | Chevalier | 51 | Data Analyst | 39.45 |
| 19 | Anouk | De Jong | 80 | SQL Developer | 58.9 |
| 20 | Ambroos | Jansen | 75 | SQL Developer | 58.9 |
| 21 | Hendrika | De Vries | 41 | SQL Developer | 58.9 |
| 22 | Caetano | Van de Berg | 84.5 | SQL Developer | 58.9 |
| 23 | Grietje | Van Dijk | 55.5 | SQL Developer | 58.9 |
| 24 | Dael | Janssen | 18 | Content Writer | 24.618181 |
| 25 | Uday | Visser | 28 | Content Writer | 24.618181 |
| 26 | Hadwin | Bakker | 12 | Content Writer | 24.618181 |
| 27 | Liv | Meijer | 44 | Data Analyst | 39.45 |
우리가 원하던 결과를 얻었습니다. 하지만 쿼리문은 좀 찝찝하네요. 읽기 좀 불편하지 않나요? 제가 썼지만 제가 읽고 뜯어 분석하자니 어려워 보이네요. 지금도 그런데 며칠 후에 이 쿼리문을 수정하고 싶어서 다시 들여다볼 땐 얼마나 더 힘이 들까요? 그래도 이건 내가 썼으니까 어떻게든 이해하겠지만, 만약 다른 사람들이 이와 같은 쿼리문을 작성했다면요?
사람마다 다르겠지만 저는 JOIN안에 SELECT문을 넣는 것을 선호하지 않습니다. 제가 논리적이라고 여기는 쿼리의 흐름과는 반대로 작동하게 되거든요. 그리고 테이블을 조인할 때는 간략한 이름을 사용하는 것을 선호합니다. 그래야 서브 쿼리를 사용해도 쿼리문 흐름에 방해를 받지 않거든요.
그렇다면 위에서 작성한 쿼리문을 보다 읽기 좋게 수정해 볼 수 있을까요? 그럼요. 바로 CTE를 사용할 겁니다. 아래는 같은 결과를 반환할 쿼리문인지만 CTE를 활용한 쿼리문입니다. 한 번 살펴보세요.
WITH average_rate_per_job AS(
SELECT
j.id,
AVG(f.pay_rate) AS average_job_rate,
FROM freelancer AS f
LEFT JOIN job_position AS j
ON f.job_type_id = j.id
GROUP BY j.id)
SELECT
f.id,
f.first_name,
f.last_name,
f.pay_rate,
j.position_type,
a.average_job_rate
FROM freelancer AS f
LEFT JOIN job_position AS j
ON f.job_type_id = j.id
LEFT JOIN average_rate_per_job AS a
ON f.job_type_id = a.id;
분석하기에 위의 쿼리문이 좀 더 보기 좋고 쉬워 보이지 않나요? WITH문을 활용해 임시 테이블(CTE를 이해하기 쉽게 바꾸어 씀)을 생성했습니다. 제가 임시 테이블에게 준 이름은 average_rate_per_job입니다. 그 이름 다음엔 AS가 나왔고, 괄호 안에는 해당 쿼리의 결과로 임시 테이블을 생성해 달라고 요청하는 것입니다.
임시 테이블을 생성하기 위해 작성한 쿼리문은 우리가 제일 먼저 작성했던 쿼리문에서의 서브 쿼리문과 일치합니다. 하지만, 저는 CTE가 쿼리문을 체계적으로 더욱 잘 정리해준다고 생각합니다. 논리적인 흐름에서 보았을 때, 제일 먼저 직종별 평균 시급을 구하고 그 결과를 직원의 정보와 직원의 시급 데이터가 기록된 employee 테이블과 조인하는 게 더욱 간결해 보이거든요.
보시면 알다시피, 해당 쿼리문은 freelancer 테이블에서 id, first_name, last_name, pay_rate 열을 불러오고 job_position 테이블에서는 position_type 열을 불러옵니다. 그리고 CTE인 average_rate_per_job 테이블에서 average_job_rate 열을 불러오고요. 마지막으로 프리랜서 아이디를 기준으로 총 3개의 테이블을 조인합니다. CTE를 조인할 때도 일반적인 테이블을 조인할 때와 다를 바 없이 똑같은 방식으로 조인해줬습니다. 위 쿼리문을 실행하면 첫 번째 지저분한 쿼리문을 실행했을 때와 동일한 결과를 받아볼 수 있습니다.
서브 쿼리 문을 반복해서 쓰기보단 CTEs
다른 예시도 살펴보도록 하죠. 이번엔 프리랜서 아이디, 이름 그리고 그들의 평균 근무 시간 데이터를 사용할 겁니다. 또한, 모든 프리랜서의 평균 근무 시간도 계산하고, 그 2개의 평균값의 차이도 계산할 것입니다. 모든 프리랜서의 평균 근무 시간 대비 1시간 초과 근무한 프리랜서들만 뽑아서 살펴보려고 합니다.
서브 쿼리를 활용하면 어떤 식으로 쿼리문이 쓰이는지 살펴볼까요?
SELECT
f.id,
f.first_name,
f.last_name,
AVG(h.hours) AS daily_avg_freelancer,
(SELECT AVG(hours) FROM hours_worked) AS daily_avg_total,
AVG(h.hours) - (SELECT AVG(hours) FROM hours_worked) AS diff
FROM hours_worked AS h
LEFT JOIN freelancer AS f
ON h.freelancer_id = f.id
GROUP BY f.id, f.first_name, f.last_name
HAVING (AVG(h.hours) - (SELECT AVG(hours) FROM hours_worked) > 1 );
무언가 현란해 보이는 위 쿼리문은 freelancer 테이블에서 id, first_name, last_name을 불러옵니다. 그리고 각각의 프리랜서의 평균 근무 시간을 계산한 열을 daily_avg_freelancer이라고 이름을 지어줬네요. 그다음엔 서브 쿼리가 적혀있는데, 이 서브 쿼리는 모든 프리랜서의 평균 근무 시간을 계산합니다. 해당 값은 daily_avg_total 이란 이름을 지어주었습니다. 이젠 마지막으로 각각의 프리랜서 평균 근무 시간과 모든 프리랜서 평균 근무 시간의 차이를 계산할 겁니다. 즉, 모든 프리랜서 평균 근무 시간을 구하기 위해 작성한 서브 쿼리 문을 한 번 더 써야 한다는 얘기입니다. 우리가 알고 싶은 건 각각의 프리랜서 별 데이터니까 마지막엔 freelancer 테이블 내의 프리랜서 별 id, first_name, last_name을 기준으로 그룹핑해주었습니다.
마침내 모든 프리랜서의 평균 근무 시간 대비 1시간 초과 근무한 프리랜서들만 살펴보기 위한 조건을 작성하고자 합니다. HAVING 절을 사용해 둘의 차이가 1 보다 크다고 설정해 주었는데, 차이를 얘기하려니 서브 쿼리문을 또 가져와서 써야 하네요.
모든 프리랜서의 평균 근무시간을 가져와 계산하기 위해 매번 서브 쿼리를 작성하기보단 해당 서브 쿼리문의 별칭인 daily_avg_total만 입력해서 쿼리문을 쓸 수 있다면 얼마나 좋을까요? 맞습니다. CTE를 사용하면 됩니다.
아래 쿼리문을 보세요. 위의 쿼리문과 동일합니다. 다만, 유일한 차이점은 CTE를 사용했다는 점입니다.
WITH total_average AS(
SELECT
AVG(hours) AS daily_avg_total
FROM hours_worked),
avg_per_freelancer AS(
SELECT
f.id,
f.first_name,
f.last_name,
AVG(h.hours) AS daily_avg_freelancer
FROM hours_worked AS h
LEFT JOIN freelancer AS f
ON h.freelancer_id = f.id
GROUP BY f.id, f.first_name, f.last_name)
SELECT
*,
(daily_avg_freelancer - daily_avg_total) AS diff
FROM avg_per_freelancer, total_average
WHERE (daily_avg_freelancer - daily_avg_total) > 1;
WITH문을 써서 모든 프리랜서의 평균 근무시간을 가지고 있는 임시 테이블(total_average)을 생성해 주었습니다. 해당 CTE는 꽤나 간단한 SELECT 문으로 구할 수 있었습니다.
그다음의 avg_per_freelancer 은 바로 두 번째 CTE입니다. 꼭 기억하세요. 2개 이상의 CTE를 작성할 땐 WITH는 첫 번째 CTE 앞에 딱 1번만 적어주시면 됩니다. 그리고 여러 개의 CTE는 콤마로 구분해 주시면 되고요. 두 번째 CTE에서는 프리랜서의 아이디, 성, 이름, 그리고 그들의 각각 평균 근무 시간을 구했습니다.
서브 쿼리만 이용했을 때와는 큰 차이는 없습니다. 다만, 사소한 차이점은 CTE를 가지고 2개의 평균값 간의 차이를 계산한다는 점과 전체 쿼리문 내에 각각의 평균을 구하고 있다는 논리적인 흐름과 깔끔해 보이는 구성일 뿐입니다.
이제 2개의 평균값을 계산해볼까요? 모든 프리랜서의 평균 근무 시간을 불러오기 위해 매번 쿼리문을 작성했던 서브 쿼리와는 달리 CTE는 임시 테이블 명과 필요한 열 이름만 적어주면 됩니다. 그래서 'daily_avg_freelancer - daily_avg_total' 이라고만 적어줘도 두 평균값 간의 차이를 구할 수 있는 것이죠.
위의 쿼리문을 실행하면 다음과 같은 결과를 얻습니다.
| id | first_name | last_name | daily_avg_freelancer | daily_avg_total | diff |
| 2 | Arthur | Strauss | 10.333333 | 6.633333 | 3.7 |
| 6 | Don | Jones | 8 | 6.633333 | 1.366667 |
| 8 | Trent | Arthurs | 12 | 6.633333 | 5.366667 |
| 12 | Jean-Paul | Lamontagne | 8 | 6.633333 | 1.366667 |
| 16 | Mohamed | Lambert | 8.666666 | 6.633333 | 2.033333 |
| 18 | Gaspard | Chevalier | 10 | 6.633333 | 3.366667 |
| 19 | Anouk | De Jong | 12 | 6.633333 | 5.366667 |
| 22 | Caetano | Van de Berg | 7.666666 | 6.633333 | 1.033333 |
| 26 | Hadwin | Bakker | 7.833333 | 6.633333 | 1.2 |
| 27 | Liv | Meijer | 8.5 | 6.633333 | 1.866667 |
FROM 절에 있는 서브 쿼리 대신 CTEs
여러분이 이미 보셨을 수도, 아님 이미 사용해 보셨을 수도 있습니다. SQL Server와 Oracle에서는 FROM절에 들어간 서브 쿼리를 파생 테이블이라고도 부르는데요. FROM절에 서브 쿼리를 넣을 때는 서브 쿼리는 별칭을 가져야 합니다. 예를 들어, 아래의 쿼리문을 살펴보도록 합시다.
SELECT
hs.first_name,
hs.last_name,
hs.pay_rate * hs.hours_per_freelancer AS monthly_pay
FROM(
SELECT
f.id,
f.first_name,
f.pay_rate,
SUM(h.hours) AS hours_per_freelancer
FROM freelancer AS f
LEFT JOIN hours_worked AS h
ON f.id = h.freelancer_id
GROUP BY f.id, f.first_name, f.last_name, f.pay_rate) AS hs;
이 쿼리는 각각의 프리랜서 별 시급과 근무 시간을 바탕으로 그들의 월급을 계산합니다. 생각보다 어려운 계산이 아님에도 쿼리는 좀 복잡해 보이네요. 위의 쿼리문을 뜯어볼 건데, 서브 쿼리가 들어있는 FROM절부터 살펴보도록 하죠.
해당 서브 쿼리는 freelancer 테이블에서 프리랜서의 id, first_name, last_name, pay_rate열을 불러옵니다. 그리고 hours_worked 테이블에선 근무 시간의 데이터를 가져와 더함으로써 프리랜서 별 총 근무 시간을 계산하네요.
그리고 원하는 결과를 얻고자 freelancer 테이블이랑 hours_worked 테이블을 조인했습니다. 우리가 얻어내야 할 결과는 프리랜서 별 데이터이기 때문에 마지막에 id, first_name, last_name 열 기준으로 그룹핑을 하였고, 해당 서브 쿼리의 이름은 hs라고 지었습니다.
메인 쿼리 즉 외부 쿼리는 해당 서브 쿼리를 테이블처럼 활용합니다. 필요한 열을 선택한 후 프리랜서 별 시급과 프리랜서 별 근무 시간을 곱해 그들의 월급을 구했고 그 결과가 담긴 열을 monthly_pay라고 이름을 지었습니다.
만약 여러분이 FROM절에 서브 쿼리를 사용하실 거라면, CTE를 활용하는 것을 추천드립니다. 그렇게 하면 여러분의 쿼리문은 보다 짧아질 것이고, 덜 복잡할 것이고, 더욱 명확해질 것입니다. 위의 쿼리문을 CTE를 활용하면 어떻게 변하는지 살펴볼까요?
WITH hours_charged AS(
SELECT
f.id,
SUM(h.hours) AS hours_per_freelancer
FROM freelancer AS f
LEFT JOIN hours_worked AS h
ON f.id = h.freelancer_id
GROUP BY f.id)
SELECT
f.first_name,
f.last_name,
f.pay_rate * hc.hours_per_freelancer AS monthly_pay
FROM freelancer AS f
LEFT JOIN hours_charged AS hc
ON f.id = hc.id;
이번 CTE의 이름은 hours_charged입니다. 해당 테이블은 freelancer 테이블에서 id를, hours_worked 테이블에서 근무 시간 데이터를 가져옵니다. 프리랜서 별 총 근무시간을 살펴보고 싶기 때문에 마지막에 프리랜서 id 기준으로 그룹핑을 해줬습니다. CTE를 만들어줬으니 이제 새로운 SELECT 문에 CTE를 가져다가 사용할 수 있겠네요. 위의 쿼리문의 결과는 아래와 같습니다.
| first_name | last_name | monthly_pay |
| Carole | Queen | 1020 |
| Arthur | Strauss | 1302 |
| Cindy | Jones | 1425 |
| Paul | Hopkins | 102 |
| Katherine | McLean | 640 |
| Don | Jones | 444 |
| Linda | Stones | 121.5 |
| Trent | Arthurs | 300 |
| Miriam | Jones | 572 |
| Guy | Gudbois | 2052 |
| Michelle | Lebouef | 480 |
| Jean-Paul | Lamontagne | 624 |
| Camille | Barbet | 96 |
| Jules | Fournier | 446.4 |
| Paulette | Dubois | 128 |
| Mohamed | Lambert | 988 |
| Simone | Pomeroy | 440.4 |
| Gaspard | Chevalier | 1530 |
| Anouk | De Jong | 10560 |
| Ambroos | Jansen | 1275 |
| Hendrika | De Vries | 451 |
| Caetano | Van de Berg | 1943.5 |
| Grietje | Van Dijk | 166.5 |
| Dael | Janssen | 252 |
| Uday | Visser | 168 |
| Hadwin | Bakker | 282 |
| Liv | Meijer | 1122 |
CTEs 이름 짓기
비록 CTEs를 사용하면 쿼리문이 좀 더 정리되고 읽기 좋아진다는 점이 있지만 사실 그것도 쿼리문을 작성하는 우리에게 달렸습니다. 우리가 얼마나 깔끔하게 쿼리문을 깔끔하게 작성하냐에 따라 CTEs를 사용하는 것이 정말 빛이 될 수도 있으니까요. 그래서 지금부터는 CTEs 이름 짓는 방법에 대해 간략하게 살펴보려고 합니다.
CTE 내용과 관련 있는 이름으로 짓기
CTE의 이름을 지을 때 유의미한 이름을 지어주는 것은 상당히 중요합니다. 이름을 잘 지어주면 CTE의 이점을 더욱 돋보이게 해 줄 겁니다. 유의미한 이름을 지어주라는 것은 바로 여러분이 구하고자 CTE를 생성하는 이유를 고려한 이름을 지어주라는 것입니다. CTE 속 쿼리문과 전혀 상관없는 이름을 지어주지 마세요. 안 그러면 CTE가 도대체 어떠한 이유로 사용되었는지 쿼리문을 볼 때마다 분석해야 하니까요. 예를 들어보도록 하겠습니다. 위에서 CTE를 2개 만들었던 그 예시를 그대로 가져와 써보도록 할게요. CTE 이름을 보면 각각의 CTE가 대충 어떤 역할을 위해 존재하는지 추측해 볼 수 있습니다.
WITH total_average AS(
SELECT AVG(hours) AS daily_avg_total
FROM hours_worked),
avg_per_freelancer AS(
SELECT
f.id,
f.first_name,
f.last_name,
AVG(h.hours) AS daily_avg_freelancer
FROM hours_worked AS h
LEFT JOIN freelancer AS f
ON h.freelancer_id = f.id
GROUP BY f.id, f.first_name, f.last_name)
SELECT
*,
(daily_avg_freelancer - daily_avg_total) AS diff
FROM avg_per_freelancer, total_average
WHERE (daily_avg_freelancer - daily_avg_total) > 1;
만약 위의 쿼리문처럼 각각의 CTE의 특징을 나타내는 이름 대신 무작위로 a, b라고 이름을 지었다고 상상해봅시다. 어떨 것 같나요? 한 번 직접 살펴보시죠.
WITH a AS(
SELECT AVG(hours) AS daily_avg_total
FROM hours_worked),
b AS(
SELECT
f.id,
f.first_name,
AVG(h.hours) AS daily_avg_freelancer
FROM hours_worked AS h
LEFT JOIN freelancer AS f
ON h.freelancer_id = f.id
GROUP BY f.id, f.first_name, f.last_name)
위 쿼리문만 보고 각각의 CTE가 무엇을 하는지 바로 알아보실 수 있으신가요? 위의 CTE를 활용하여 새로운 값을 계산하는 쿼리를 한 번 보실게요.
SELECT
*,
(daily_avg_freelancer - daily_avg_total) AS diff
FROM b, a
WHERE (daily_avg_freelancer - daily_avg_total) > 1;
CTE의 내용과 무관한 이름을 지어주니까 위처럼 간단한 쿼리문도 도대체 무엇을 이야기하는 건지 알 수 없게 됩니다. 여기서 말하는 a, b가 무엇인지 쿼리에서 바로 알 수 있는 방법이 없으니까요!
CTE 이름끼리 서로 통일성 있게 짓기
여러분들이 눈치채셨으리라 믿지만 지금까지 이 글에서 쓰인 모든 CTE의 이름은 소문자로 쓰였습니다. 그리고 해당 이름이 2개 단어 이상으로 구성될 땐 단어 사이에 _ (언더바)를 넣어줬고요. 물론 여러분들도 꼭 저와 똑같이 해야 한다는 말은 아닙니다.
만약 대문자를 사용하고 싶으시다면 사용하셔도 됩니다. _ (언더바) 대신에 대문자를 사용해 단어 사이를 구분해 줄 수도 있습니다. 다만 제가 말씀드리고 싶은 건 어떤 방법으로 이름을 작성하시든 간에 여러 개의 이름을 지을 때마다 해당 이름들이 서로 통일성을 갖출 수 있도록 해주세요. 절대 아래 쿼리문처럼 CTE 이름을 중구난방으로 짓지만 말아주세요.
WITH Total_AveRage AS (...),
AVGPERfreelancer AS (...)
SELECT
*,
(daily_avg_freelancer - daily_avg_total) AS diff
FROM TOTAL_AVERAGE, avgperfreelancer
WHERE (daily_avg_freelancer - daily_avg_total) > 1;
Recursive 쿼리를 쓸 때는 무조건 CTEs
이미 recursive 쿼리에 대해 아시는 분들은 CTEs 만이 답이라는 것을 알고 계실 겁니다. CTE를 사용하지 않고서는 recursive 쿼리를 작성할 수가 없어요. 만약 SQL의 초급 내용 정도만 숙지하고 있는 분이시라면 recursive 쿼리가 무엇인지 궁금하실 겁니다. 해당 쿼리는 자기 자신을 참조해 실행되는 쿼리입니다. 자기 자신을 참조해 실행함으로써 부분적인 결과를 반환해내고 최종 결과를 얻어낼 때까지 그 과정을 계속 반복해요.
CTEs를 사용하면 좋은 점
CTEs를 사용할 때마다, 어떻게 이름을 짓는 것이 쿼리문 작성하는 것을 더욱 쉽게 만들지 고민해 보세요. 물론 이런 고민을 안 한다고 해서 쿼리가 잘못 실행되거나 쿼리문의 결과에 어떤 영향을 끼치는 것은 아니지만, 여러분이 CTE를 사용함으로써 얻는 이점을 놓치게 될 수도 있기 때문입니다. 바로 깔끔히 정리된 쿼리문과 한눈에 알아보기 쉬운 이점 말입니다.
아참, recursive 쿼리를 공부하고 싶다면 CTEs를 아는 것은 필수입니다.
원문: What are SQL CTE Best Practices?