Оптимизация запроса для расчета разницы между датой и временем

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

+---------+----------+---------------------+---------------------+---------+
| id      | party_id | begintime           | endtime             | to_meas |
+---------+----------+---------------------+---------------------+---------+
| 1395035 |     9255 | 2010-09-26 00:34:02 | 2010-09-26 03:56:20 |       0 |
| 1395036 |     8974 | 2009-07-10 11:00:00 | 2009-07-10 21:30:00 |       0 |
| 1395037 |     8974 | 2009-07-10 23:14:00 | 2009-07-11 08:48:00 |       0 |
| 1395038 |     8975 | 2009-07-10 11:00:00 | 2009-07-10 21:30:00 |       0 |
| 1395039 |     8975 | 2009-07-10 23:14:00 | 2009-07-11 08:48:00 |       0 |
| 1395040 |     8974 | 2009-07-11 10:08:31 | 2009-07-12 18:49:51 |       0 |
| 1395041 |     8975 | 2009-07-11 10:08:31 | 2009-07-12 18:49:51 |       0 |
| 1395042 |     8974 | 2009-07-12 20:38:27 | 2009-07-13 20:33:21 |       0 |
| 1395043 |     8975 | 2009-07-12 20:38:27 | 2009-07-13 20:33:21 |       0 |
| 1395044 |     8974 | 2009-07-13 21:57:37 | 2009-07-15 08:25:45 |       0 |
| 1395045 |     8975 | 2009-07-13 21:57:37 | 2009-07-15 08:25:45 |       0 |
| 1395046 |     8974 | 2009-07-15 08:51:25 | 2009-07-16 10:29:13 |       0 |
| 1395047 |     8975 | 2009-07-15 08:51:25 | 2009-07-16 10:29:13 |       0 |
| 1395048 |     8974 | 2009-07-16 12:22:22 | 2009-07-17 14:39:10 |       0 |
| 1395049 |     8975 | 2009-07-16 12:22:22 | 2009-07-17 14:39:10 |       0 |
| 1395050 |     8976 | 2009-07-24 16:53:48 | 2009-07-25 08:47:29 |       0 |
| 1395051 |     8977 | 2009-07-24 16:53:48 | 2009-07-25 08:47:29 |       0 |
| 1395052 |     8978 | 2009-07-24 16:53:48 | 2009-07-25 08:47:29 |       0 |
| 1395053 |     8979 | 2009-07-24 16:53:48 | 2009-07-25 08:47:29 |       0 |
| 1395054 |     8976 | 2009-07-25 10:47:14 | 2009-07-26 09:41:44 |       0 |
+---------+----------+---------------------+---------------------+---------+
...

Мне нужно рассчитать время между begintime и предыдущим endtime и установить to_meas на 1, если эта разница> 30 минут. Вот моя попытка сделать это в MySQL:

update doses d set to_meas=1 where d.id in 
  (select a.id from party join (select * from doses) a 
  on party_id=a.party_id 
  left join (select * from doses) b 
  on party.id=b.party_id 
  and b.begintime=(select min(begintime) 
  from (select * from doses) c 
  where c.begintime > a.endtime) 
  and timestampdiff(minute, a.endtime, b.begintime) > 30 
  group by party.id);

Эта команда работает (квази-) вечно. Я пытался сделать это на питоне pandas:

conn = engine.connect()
sql =
    '''
    select doses.id, party_id, party.ml, begintime, endtime
    from doses join party on party.id=doses.party_id
'''
df = pd.read_sql(con=conn, sql=sql,
measure = df.groupby('party_id', as_index=False).apply(
    lambda x: x[pd.to_datetime(x['begintime']) - 
    pd.to_datetime(x.shift()['endtime']) > pd.to_timedelta('30 minutes')])
measure_ids = measure['id'].to_list()
measure_list = ','.join([str(x) for x in measure_ids])
conn.execute(
    'update doses set to_meas=true where id in(%s)' % measure_list)

Последний оператор выполняется около 10 секунд. Есть ли способ оптимизировать код SQL для работы так же быстро, как pandas`?

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
0
51
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

В MySQL 8.0 вы можете получить желаемый результат с помощью оконных функций, например:

select d.*,
    (begintime > lag(endtime) over(partition by pary_id order by endtime) + interval 30 minute) as to_meas
from doses d

В более ранних версиях:

select d.*,
    (
        begintime > (
            select max(endtime) + interval 30 minute
            from doses d1
            where d1.party_id = d.party_id and d1.endtime < d.endtime
        )
    ) as to_meas
from doses d

Я бы не рекомендовал хранить такую ​​производную информацию. Вы можете использовать запрос или создать представление. Но если вы действительно настаиваете на select:

update doses d
inner join (
    select id,
        (
            begintime > (
                select max(endtime) + interval 30 minute
                from doses d1
                where d1.party_id = d.party_id and d1.endtime < d.endtime
            )
        ) as to_meas
    from doses d
) d1 on d1.id = d.id
set d.to_meas = d1.to_meas

Вы можете обновить свои данные, используя существующие следующим образом:

Update doses d
   Set meas = 1
 Where begintime > (select max(dd.endtime) + interval '30' minute 
From doses dd where dd.begintime < d.begintime
  And dd.party_id = d.party_id)

MySQL не поддерживает повторное открытие целевой таблицы оператора обновления в коррелированном подзапросе, поэтому этот запрос вызывает синтаксическую ошибку: dbfiddle.uk/…. Это типичное ограничение синтаксиса update MySQL.

GMB 21.12.2020 02:47

@GMB здесь должно быть так, как я: ... From (select * from doses) dd ...

Michael 21.12.2020 03:27

Если вы хотите обновить данные, вы можете использовать оконные функции в update:

update doses d join
       (select d.*, 
              lag(d.endtime) over (partition by d.party_id order by d.endtime) as prev_endtime
        from doses d
       ) dd
       on d.id = dd.id and
          d.starttime > dd.prev_endtime + interval 30 minute
    set to_meas = 1;

Затем для этого запроса вам нужен индекс doses(party_id, endtime). Я предполагаю, что id уже объявлен как первичный ключ.

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

Обновлено:

В более старых версиях MySQL это можно сформулировать так:

update doses d join
       (select d.*, 
               (select d2.endtime
                from doses d2
                where d2.party_id = d.party_id and
                      d2.endtime < d.endtime
               ) as prev_endtime
        from doses d
       ) dd
       on d.id = dd.id and
          d.starttime > dd.prev_endtime + interval 30 minute
    set to_meas = 1;

У вас относительно мало строк на party_id, поэтому коррелированный запрос кажется разумным. Это также нуждается в индексе на (party_id, endtime).

В какой-то момент ОП прокомментировал мой ответ, что они использовали MySQL 5.7. Однако этот комментарий был удален.

GMB 21.12.2020 02:54

да у меня 5.7

Michael 21.12.2020 03:28

Другие вопросы по теме