Pori_IT

문제

Find all numbers that appear at least three times consecutively. Return the result table in any order.

연속된 id에서 3번이상 연속되어 나오는 수를 구하기

테이블 & 예제

[Logs]

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

[Examples]

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

풀이

임시테이블과 LEAD함수를 활용하여 풀이를 진행했다.

임시 테이블에 LEAD를 활용해서 id, num의 이후 값들을 당겨서 가져오는 테이블을 생성한다.

후에 WHERE절을 활용하여 값이 같고, id의 차이가 1씩임을 확인해서 조건에 맞는 값들만 남겨주었다.

WITH temp AS (
    SELECT id,num,
    LEAD(id) OVER (ORDER BY id) AS id1,
    LEAD(num) OVER (ORDER BY id) AS num1,
    LEAD(id,2) OVER (ORDER BY id) AS id2,
    LEAD(num,2) OVER (ORDER BY id) AS num2
    FROM Logs
)
SELECT DISTINCT(num) AS ConsecutiveNums
FROM temp
WHERE (id1 - id) = 1 AND (id2 - id1) = 1 AND num = num1 AND num1 = num2;

위는 나의 풀이이고, 자기자신을 조인하는 방법을 설명해주어서 하나 더 가져왔다.

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

따로 JOIN을 작성하지 않고 FROM 절에 나열하는 방법도 JOIN이 됨을 알게되었다.

마지막으로 메모리, 속도의 측면에서 두 코드를 비교해보았다. (GPT의 도움을 받았다.)

[메모리]

: 나의 풀이가 임시 테이블 사용으로 메모리 최적화가 되었으며, 조인을 여러번 하는 경우에 메모리 사용이 증가한다고 한다.

[실행 속도]

: 속도 또한 테이블이 많이 조인됨에 따라서 속도가 저하되고, 특히 큰 테이블에서 조인은 연산이 느릴 수 있다고 한다. 따라서 대규모 처리에는 나의 방법이 조금 더 효율적이라고 말하고있다.

문제

어제보다 높은 기온을 갖는 날의 ID를 출력하기

테이블

[Weather]

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the column with unique values for this table.
There are no different rows with the same recordDate.
This table contains information about the temperature on a certain day.

[예제]

Input: 
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
Output: 
+----+
| id |
+----+
| 2  |
| 4  |
+----+

풀이1. - DATEDIFF

DATEDIFF를 사용해서 날짜의 차이를 계산한다.

DATEDIFF

날짜의 차이를 계산한다. 구조는 다음과 같다. DATEDIFF('구분자',시작일, 종료일)

코드

SELECT w1.id
FROM Weather w1 JOIN Weather w2
	ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

+) DATE_ADD, DATE_SUB

기본 구조는 DATE_ADD(기준 날짜, INTERVAL) 이다. 참고로 뺴고싶으면 DATE_SUB를 사용한다.

풀이2. LAG와 WITH AS, DATE_ADD 사용하기

OVER

아래 사용할 LAG, LEAD를 위해 OVER에 대해서 짚고 넘어간다.

OVER는 언제쓰는가? 각 행별로 특정 기준에 따라 함수를 적용하고 싶을 때 쓴다고 한다. OVER 절은 행의 범위를 지정해준다고한다.

기본 구조는 이러하다. [집계함수] OVER(ORDER BY [A]) OVER 안에는 ORDER BY 와 PARTITION BY 가 사용되는데 각각을 알아보면

• ORDER BY:  행 집합을 정의하는 기준
• PARTITION BY: 어떤 칼럼의 값을 기준으로 행 집합을 나눌 지 정의

둘은 같이 사용 가능하다.

LAG & LEAD

값을 특정 칸씩 뒤로 밀거나 앞으로 당겨오는 함수이다.

기본 구조는 LAG(column,diff,null대체) over (order by column) (LEAD도 동일하다.)이다.

그룹마다 함수를 다르게 적용하고자 한다면 PARTITION BY를 적용 가능하다.

WITH AS

with as 를 사용해서 서브쿼리를 사용한다. 기본 구조는 with 이름 as (서브쿼리)로 사용가능하며, 여러개의 사용도 가능하다.

다만 여러개 사용 시 with은 맨 처음 1회만 사용하며 , 를 사용해서 구분해서 추가한다.

코드

WITH pre_Weather AS
(
    SELECT
        id,recordDate, Temperature,
        LAG(temperature,1) OVER (ORDER BY recordDate) AS pre_temp,
        LAG(recordDate,1) OVER (ORDER BY recordDate) AS pre_record
    FROM
        Weather
)
SELECT
    id
FROM pre_Weather
WHERE temperature > pre_temp
    AND recordDate = DATE_ADD(pre_record, INTERVAL 1 DAY);

참고 및 출처

- 문제 : https://leetcode.com/problems/rising-temperature/

- 풀이 : https://github.com/poriz/leetcode/blob/main/0197-rising-temperature/0197-rising-temperature.sql

- OVER : https://velog.io/@wltn716/SQL-Over-%EC%A0%88

- LAG & LEAD : https://minor-research.tistory.com/64

- DATE_ADD & DATE_SUB : https://extbrain.tistory.com/58

- WITH AS : https://freehoon.tistory.com/188