sql, group by를 연산자와 함께 사용하기

연속된 숫자를 그룹핑하여 그룹간 첫 번째와 마지막을 구하기 1285. Find the Start and End Number of Continuous Ranges

• https://leetcode.com/problems/find-the-start-and-end-number-of-continuous-ranges/
• 칼럼이 하나(log_id)인 테이블이 있다. 레코드가 늘어나면 칼럼의 값은 이전의 레코드보다 크다. 값이 1씩 증가하는 레코드 묶음을 하나의 그룹으로 만든다. 그리고 해당 그룹의 처음 값과 마지막 값을 구한다.

Input: 
Logs table:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+
Output: 
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+

문제의 해소

• 시작하는 레코드는 자신의 값에서 1을 뺀 값이 테이블 내에 존재해서는 안된다.
• 종료하는 레코드는 자신의 값에서 1을 더한 값이 테이블 내에 존재해서는 안된다.

with tb(id, s, e) as(
    select *   
    from logs l
    left join logs s on l.log_id = s.log_id + 1
    left join logs e on l.log_id = e.log_id - 1
)
select * from tb;

• 결과 :

{"headers": 
    ["id", "s", "e"],
 "values": 
    [[1, null, 2],
    [2, 1, 3],
    [3, 2, null],
    [7, null, 8],
    [8, 7, null],
    [10, null, null]]}

• s와 e를 합친다.

with tb(id, s, e) as(
select 
        *   
    from logs l
    left join logs s on l.log_id = s.log_id + 1
    left join logs e on l.log_id = e.log_id - 1
)
select
    s.id 'start_id'
    , e.id 'end_id'
from (
    select 
        (@idx1:=@idx1+1) idx
        , t.id
    from tb t
        ,(select @idx1:=0) i
    where t.s is null 
) s
join (
    select 
        (@idx2:=@idx2+1) idx
        , t.id
    from tb t
        ,(select @idx2:=0) i
    where t.e is null 
) e
on s.idx = e.idx

훨씬 쉬운 방법

• 성능은 나쁘지 않았으나 쿼리가 다소 길고 이해하기 어렵다. 그래서 다른 사람이 쿼리를 살펴봤다.
• 완전 쉬운 방법이 있었다!

+------------+-----+-------------+
| log_id     | idx |log_id - idx |
+------------+-----+-------------+
| 1          |1    |0            |
| 2          |2    |0            |
| 3          |3    |0            |
| 7          |4    |3            |
| 8          |5    |3            |
| 10         |6    |4            |
+------------+-----+-------------+

• 숫자의 순서대로 인덱스를 부여한다. 그리고 log_id와 인덱스를 뺀다. 뺀 값이 동일한 레코드가 바로 같은 그룹이 된다.
• 각각의 그룹(위에서는 0, 3, 4)에서 log_id의 최댓값과 최솟값을 구한다.
• 그룹핑에 대한 완전 새로운 접근이었다!
• 더하여, group by에 연산자(-)까지 넣었다.

select 
    min(log_id) as start_id
    , max(log_id) as end_id
from(
    select 
        log_id
        , row_number() over(order by log_id) as num 
        from logs
) a
group by log_id - num