Блог

Главная - Вопросы по программированию - SQL, сгруппируйте несколько строк в 2 строки и сделайте одну строку

SQL, сгруппируйте несколько строк в 2 строки и сделайте одну строку

#sql

#sql

Вопрос:

У меня есть такая таблица

     Id   |    Name     |         Date    
    1         John         2020-10-24 23:51:00.000  
    1         John         2020-10-25 08:00:00.000        
    2         Edward       2020-10-24 23:55:00.000          
    2         Edward       2020-10-25 08:02:00.000           
    2         Edward       2020-10-25 15:50:00.000          
    2         Edward       2020-10-26 00:02:00.000
    3         Mark         2020-10-25 08:05:00.000
    3         Mark         2020-10-25 15:53:00.000

Даты могут быть изменены, я хочу сгруппировать их в две строки. Диапазон дат может составлять не более 15 часов.

Мой ожидаемый результат

  Id            |  Name     |       StartDate                 |    EndDate
 1               John         2020-10-24 23:51:00.000        2020-10-25 08:00:00.000
 2               Edward       2020-10-24 23:55:00.000        2020-10-25 08:02:00.000
 2               Edward       2020-10-25 15:50:00.000        2020-10-26 00:02:00.000
 3               Mark         2020-10-25 08:05:00.000        2020-10-25 15:53:00.000

Комментарии:

1. Какова ваша база данных (например, MySQL, SQL Server, Oracle и т. Д.)?

2. Что такое 15 часов? Максимальный разрыв между двумя строками или максимальная разница между начальной и конечной датой (после группировки)?

3. База данных — это sql server. 15 часов — это максимальная разница между начальной и конечной датой.

Ответ №1:

Предполагая, что каждая дата начала всегда имеет соответствующую конечную дату, мы можем попробовать:

 WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) rn
    FROM yourTable
)

SELECT
    Id,
    Name,
    MAX(CASE WHEN (rn - 1) % 2 = 0 THEN Date END) AS StartDate,
    MAX(CASE WHEN (rn - 1) % 2 = 1 THEN Date END) AS EndDate
FROM cte
GROUP BY
    Id,
    Name,
    (rn - 1) / 2;

Здесь мы используем логику поворота вместе с модулем ROW_NUMBER , чтобы выяснить, какие записи даты приходят «первыми» и соответствуют дате начала, и почему они приходят «последними» и соответствуют дате окончания.

Ответ №2:

Один из методов состоит в том, чтобы предположить, что первая строка является начальной, а затем взять все остальные строки:

 select t.id, t.name, t.date as start_date, t.next_date as end_date
from (select t.*,
             lead(date) over (partition by id order by date) as next_date,
             row_number() over (partition by id order by date) as seqnum
      from t
     ) t
where seqnum % 2 = 1;

Это позволяет избежать внешней агрегации, которая должна быть более производительной, чем решение, которое использует group by .