Как найти предыдущую запись [n-per-group max (timestamp) <timestamp]?
У меня есть большая таблица, содержащая данные датчика временных рядов. Большой размер - от нескольких тысяч до 10 миллионов записей, поделенных между различными отслеживаемыми каналами. Для датчика определенного типа мне нужно рассчитать временной интервал между текущим и предыдущим показаниями, то есть найти наибольшую временную метку до текущей.
На ум приходят очевидные подходы, каждый из которых измеряется на Core i5 для канала из 40 тыс. Записей:
Коррелированный подзапрос
SELECT collect.*, prev.timestamp AS prev_timestamp
FROM data AS collect
LEFT JOIN data AS prev ON prev.channel_id = collect.channel_id AND prev.timestamp = (
SELECT MAX(timestamp)
FROM data
WHERE data.channel_id = collect.channel_id AND data.timestamp < collect.timestamp
)
WHERE collect.channel_id=14 AND collect.timestamp >= 0
ORDER BY collect.timestamp
Время (exec, fetch) 11сек, 21сек
План
+----+--------------------+---------+------+------------------------------+---------+---------+-------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+------+------------------------------+---------+---------+-------------------------+-------+--------------------------+
| 1 | PRIMARY | collect | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | const | 45820 | Using where |
| 1 | PRIMARY | prev | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 13 | const,func | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | data | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | nils.collect.channel_id | 2495 | Using where; Using index |
+----+--------------------+---------+------+------------------------------+---------+---------+-------------------------+-------+--------------------------+
Анти Присоединиться
SELECT d1.*, d2.timestamp AS prev_timestamp
FROM data d1
LEFT JOIN data d2 ON
d2.channel_id=14 AND
d2.timestamp < d1.timestamp
LEFT JOIN data d3 ON
d3.channel_id=14 AND
d3.timestamp < d1.timestamp AND
d3.timestamp > d2.timestamp
WHERE
d3.timestamp IS NULL AND
d1.channel_id=14
ORDER BY timestamp
Время 12сек, 21сек
План
+----+-------------+-------+------+------------------------------+---------+---------+-------+-------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------+---------+---------+-------+-------+--------------------------------------+
| 1 | SIMPLE | d1 | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | const | 45820 | Using where |
| 1 | SIMPLE | d2 | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | const | 47194 | Using index |
| 1 | SIMPLE | d3 | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | const | 47194 | Using where; Using index; Not exists |
+----+-------------+-------+------+------------------------------+---------+---------+-------+-------+--------------------------------------+
И я придумала другой образец, который я называюНаивный граф
SELECT current.*, prev.timestamp AS prev_timestamp FROM
(
SELECT data.*, @r1 := @r1+1 AS rownum from data
CROSS JOIN (SELECT @r1 := 0) AS vars
WHERE channel_id=14
ORDER BY timestamp
) AS current
LEFT JOIN
(
SELECT data.*, @r2 := @r2+1 AS rownum from data
CROSS JOIN (SELECT @r2 := 0) AS vars
WHERE channel_id=14
ORDER BY timestamp
) AS prev
ON current.rownum = prev.rownum+1
Время 1.1сек (это на самом деле самый быстрый!)
План
+----+-------------+------------+--------+------------------------------+---------+---------+-----+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+------------------------------+---------+---------+-----+-------+----------------+
| 1 | PRIMARY | <derived2> | ALL | | | | | 24475 | |
| 1 | PRIMARY | <derived4> | ALL | | | | | 24475 | |
| 4 | DERIVED | <derived5> | system | | | | | 1 | |
| 4 | DERIVED | data | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | | 45820 | Using where |
| 5 | DERIVED | | | | | | | | No tables used |
| 2 | DERIVED | <derived3> | system | | | | | 1 | |
| 2 | DERIVED | data | ref | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq | 5 | | 45820 | Using where |
| 3 | DERIVED | | | | | | | | No tables used |
+----+-------------+------------+--------+------------------------------+---------+---------+-----+-------+----------------+
Поскольку запрос, вероятно, выполняется на небольших платформах, таких как производительность RasPi, критичен, пара секунд является максимально приемлемой.
Мой вопрос: Последний подход хорош дляНаибольший-н-в-группа или есть лучшие? Ожидается ли, что коррелированный подзапрос будет таким же медленным, как и опытный?