У меня есть таблица 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
`?
В 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)
@GMB здесь должно быть так, как я: ... From (select * from doses) dd ...
Если вы хотите обновить данные, вы можете использовать оконные функции в 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. Однако этот комментарий был удален.
да у меня 5.7
MySQL не поддерживает повторное открытие целевой таблицы оператора обновления в коррелированном подзапросе, поэтому этот запрос вызывает синтаксическую ошибку: dbfiddle.uk/…. Это типичное ограничение синтаксиса
update
MySQL.