SQL 문제를 풀며 확립한 개념 몇개를 기록해보자. 첫번째로 with.
WITH절
WITH 절은 Common Table Expressions(CTEs)을 정의하는 데 사용됩니다. 이는 일시적인 테이블을 정의하여 쿼리의 다른 부분에서 재사용할 수 있게 합니다. 여러 개의 CTE를 정의할 때는 각 CTE를 쉼표(,)로 구분합니다.
쉽게 말하면, 복잡한 쿼리를 작은 단계로 나누어 이해하기 쉽게 하는 것 이다.
예를 들어보자. 어떤 문제에서 WITH 절을 사용하기 좋을까?
There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0 | 0 | start | 0.712 |
| 0 | 0 | end | 1.520 |
| 0 | 1 | start | 3.140 |
| 0 | 1 | end | 4.120 |
| 1 | 0 | start | 0.550 |
| 1 | 0 | end | 1.550 |
| 1 | 1 | start | 0.430 |
| 1 | 1 | end | 1.420 |
| 2 | 0 | start | 4.100 |
| 2 | 0 | end | 4.512 |
| 2 | 1 | start | 2.500 |
| 2 | 1 | end | 5.000 |
+------------+------------+---------------+-----------+
Output:
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0 | 0.894 |
| 1 | 0.995 |
| 2 | 1.456 |
+------------+-----------------+
출저: leetcode - 1661. Average Time of Process per Machine 문제
이 문제에서 우리는 processing_time을 구해야 한다. 문제에서 processing_time은 processing_time 의 average time, 즉 생산 시간의 평균시간이다.
그렇다면 기본적으로 주어진 데이터에서 우리는 두가지 가공처리를 해야 답을 얻을 수 있을 것이다.
1-생산 시간을 구한다
2-평균 시간을 구한다
이러한 복잡한 쿼리를 작은 단계로 나눌 때에 WITH을 사용하도록 하자.
With Processtimes AS (
Select machine_id, process_id,
MAX(CASE WHEN activity_type = 'end' THEN timestamp ELSE NULL END) -
MAX(CASE WHEN activity_type = 'start' THEN timestamp ELSE NULL END)
AS process_time
From Activity
Group by machine_id, process_id
),
Averagetimes AS (
Select machine_id,
Round(AVG(process_time),3) AS processing_time
From Processtimes
Group by machine_id
)
요런 식으로..
WITH절을 사용하다보면 중간에 쉼표(,)를 쓰는데, 이것도 중요하다. 요약하자면
- 쉼표 사용: WITH 절 내에서 여러 CTE를 정의할 때 각 CTE를 쉼표로 구분합니다.
- 마지막 CTE 이후 쉼표 생략: 마지막 CTE 이후에는 쉼표를 사용하지 않습니다. 이 위치에서는 주 쿼리가 와야 하기 때문입니다.
이렇게 된다. 마지막 CTE 에만 쉼표쓰지 않아야 한다. 쉼표쓰면 RUN했을때 틀리다고 나옴.
