SQL: DATEDIFF, SUBDATE 활용 방법의 차이 (feat. join문제)

 

01 날짜차: DATEDIFF, SUBDATE의 이용법 알기

---

문제 URL: Rising Temperature - LeetCode

SELECT W1.id
FROM Weather AS W1
WHERE W1.temperature > (
    SELECT W2.temperature
    FROM Weather AS W2
    WHERE DATEDIFF(W1.recordDate, W2.recordDate)=1
);

= 다음 코드는 join이지만 조인 조건(on)을 주지 않았다.

SELECT w1.id
FROM Weather AS W1
JOIN Weather AS W2
WHERE DATEDIFF(W1.recordDate, W2.recordDate) = 1
AND W1.temperature > W2.temperature;

=

SELECT W1.ID
FROM WEATHER AS W1
JOIN WEATHER AS W2
ON W2.RECORDDATE = SUBDATE(W1.RECORDDATE, 1)
WHERE W1.TEMPERATURE > W2.TEMPERATURE;

 

02 각각의 실행과정을 기록한다.

---

SELECT W1.id
FROM Weather AS W1
WHERE W1.temperature > (
    SELECT W2.temperature
    FROM Weather AS W2
    WHERE DATEDIFF(W1.recordDate, W2.recordDate)=1
);

코드 1. JOIN 없이 서브쿼리를 사용하여 하루 전보다 기온이 올라간 날을 찾아야 한다.

1. DATEDIFF(큰 수, 작은 수)

2. 서브 쿼리는 W1.recordDate와 하루 차이인 W2.recordDate인 기온을 필터링한다.

3. 즉, W1(최근)의 날짜보다 하루 전의 기온을 반환한다.

 

SELECT w1.id
FROM Weather AS W1
JOIN Weather AS W2
WHERE DATEDIFF(W1.recordDate, W2.recordDate) = 1
AND W1.temperature > W2.temperature;

코드 2. JOIN을 활용하여 하루 전보다 기온이 올라간 날을 찾아야 한다.

1. JOIN 다음 바로 두 개의 조건(날짜차, 기온차)을 WHERE절로 지정하여 조인+필터링한다.

2. 조인 조건의 대체: ON(조인 조건)절 없이도 WHERE 절에서 날짜 차이를 검증함으로써 두 테이블 간의 관계를 정의할 수 있다. WHERE 절에서 날짜 관계를 명시적으로 설정했기 때문에, 별도의 ON 조건이 필요하지 않게 됐다.

SELECT W1.id
FROM Weather AS W1
JOIN Weather AS W2
ON DATEDIFF(W1.recordDate, W2.recordDate) = 1
WHERE W1.temperature > W2.temperature;

3. SQL 쿼리의 가독성과 명확성을 위해 위와 같이 수정하는 것이 일반적이다.

1. ON 절

• 목적: 조인 조건을 정의하여 어떤 테이블의 행을 서로 연결할지를 결정한다.
• 작동 방식: 두 테이블을 조인할 때, ON 절에서 지정한 조건에 따라 두 테이블의 행이 연결된다. 이 단계에서 조건이 충족되는 행들만 조인 결과로 나타난다.

2. WHERE 절

• 목적: 조인된 결과에서 추가적인 필터링을 수행한다.
• 작동 방식: ON 절에서 조인된 결과에 대해 조건을 추가하여 최종적으로 반환할 행들을 결정한다. 여기서 지정한 조건에 따라 결과 집합에서 행을 선택한다.

 

SELECT W1.ID
FROM WEATHER AS W1
JOIN WEATHER AS W2
ON W2.RECORDDATE = SUBDATE(W1.RECORDDATE, 1)
WHERE W1.TEMPERATURE > W2.TEMPERATURE;

코드 3. JOIN, SUBDATE(큰 수, 날짜 차이)를 활용해도 같은 output을 볼 수 있다.

 

1. ON 절: 조인 조건이다.

• W2의 RECORDDATE가 W1의 RECORDDATE에서 하루 전 (SUBDATE(W1.RECORDDATE, 1))인 경우에만 조인한다.
• 즉, W1의 날짜가 W2의 날짜보다 하루 늦은 경우에 해당한다.

 

2. WHERE 절: 필터 조건이다.

• W1의 TEMPERATURE가 W2의 TEMPERATURE보다 높아야 한다.
• 즉, 하루 전의 온도(W2)보다 오늘의 온도(W1)가 높은 경우를 찾는다.

 

03 기타: join과 left join의 차이

---

문제 URL: Employee Bonus - LeetCode

1번 코드
SELECT EM.name, BO.bonus
FROM Employee AS EM
JOIN Bonus AS BO
ON EM.empId = BO.empId
WHERE BO.bonus < 1000 OR BO.bonus IS NULL;

2번 코드 (정답)
SELECT EM.name, BO.bonus
FROM Employee AS EM
LEFT JOIN Bonus AS BO
ON EM.empId = BO.empId
WHERE BO.bonus < 1000 OR BO.bonus IS NULL;

1. 두 쿼리의 차이는 JOIN 방식에 있다.

2. 첫 번째 쿼리 (INNER JOIN): JOIN은 기본적으로 INNER JOIN을 의미하며, Bonus 테이블에 매칭되는 레코드가 없는 Employee 레코드는 결과에서 제외된다. 따라서 bonus 값이 NULL인 레코드가 WHERE 조건에 따라 포함되지 않는다.

3. 두 번째 쿼리 (LEFT JOIN): LEFT JOIN은 Employee 테이블의 모든 레코드를 유지하며, Bonus 테이블에 매칭되는 레코드가 없는 경우에도 Employee 레코드가 결과에 포함된다. 따라서 bonus 값이 NULL인 레코드도 결과에 포함될 수 있다.

4. 결과적으로, 두 쿼리의 차이는 LEFT JOIN을 사용함으로써 bonus가 NULL인 레코드를 포함할 수 있다는 점에 있다.

 

 

04 기타: 하나의 테이블에 start, end기록이 모두 포함된다면?

---

문제 URL: Average Time of Process per Machine - LeetCode

SELECT A1.machine_id, ROUND(AVG(A2.timestamp-A1.timestamp),3) AS processing_time
FROM Activity AS A1
JOIN Activity AS A2 ON A1.machine_id = A2.machine_id
WHERE A1.process_id = A2.process_id
AND A1.activity_type = 'start'
AND A2.activity_type = 'end'
GROUP BY machine_id;

 

1. 스스로를 JOIN하고 조인 조건(ON)은 가장 포괄적인 범위의 기계_ID로 부여한다.

2. ACTIVITY_TYPE이 START인 A1, ACTIVITY_TYPE이 END인 A2 테이블을 만든다.

3. 기계_ID별로 그룹화를 시킨 다음 행 별로 시간 차를 계산한다.

4. 결과적으로, 인풋과 아웃풋 테이블 표 시각화는 다음과 같다.