MySQL Select каждой n-ой строки

Question

[Александр]

Друзья, подскажите пожалуйста.
Есть таблица с тремя полями — id (auto_increment), date(timestamp) и value.
Каждые 10 секунд в БД добавляется одна запись value. Остальные поля прописываются автоматически.

Необходимо построить графики за час, сутки и тп…
Для построения графика за час необходимо выбрать 60 записей из 360. То есть берется одно значение за каждую минуту (каждое 6). Соответственно за сутки — 24 записи из 8640 (каждое 360). Ну и так далее…

Можно ли такой запрос реализовать средствами MySQL используя id или дату?

Answer 1

[Horzerus]

Если требуется прорядить данные, то можно использовать группировку по формату поля даты:

Например, для группировки по минутам:
GROUP BY DATE_FORMAT (`date`, "%y-%m-%d-%H-%i")
"%y%j%H-%i" — тоже самое, но так быстрее будет.

Comments

[Александр]

Про группировку не подумал как то сразу =)

select * from MAIN GROUP BY DATE_FORMAT (`date`, "%y%j%H-%i") desc limit 60

Получается как то так. Похоже на правду. =) Спасибо!
+-------+---------------------+
| id | date |
+-------+---------------------+
| 12593 | 2011-03-22 14:30:01 |
| 12588 | 2011-03-22 14:29:05 |
| 12583 | 2011-03-22 14:28:10 |
| 12577 | 2011-03-22 14:27:03 |
| 12572 | 2011-03-22 14:26:07 |
| 12566 | 2011-03-22 14:25:00 |
| 12561 | 2011-03-22 14:24:04 |
| 12556 | 2011-03-22 14:23:08 |
| 12550 | 2011-03-22 14:22:01 |
| 12545 | 2011-03-22 14:21:05 |
| 12540 | 2011-03-22 14:20:09 |
| 12534 | 2011-03-22 14:19:02 |
| 12529 | 2011-03-22 14:18:06 |
| 12524 | 2011-03-22 14:17:10 |
| 12518 | 2011-03-22 14:16:03 |
| 12513 | 2011-03-22 14:15:08 |

[Horzerus]

Ага. Там еще в формате даты лишний дефис по ошибке остался, так еще лучше будет — "%y%j%H%i"

[Александр]

Сегодня как раз получился тот случай, когда данные в БД не записывались. В итоге если делать запрос на вывод n-ого кол-ва записей, то данные охватывали больше чем сутки.

В общем нагуглился и наэкспериментировался. В итоге остановился на таком:

SELECT value, date from MAIN WHERE `date` > NOW() - INTERVAL 1 HOUR GROUP BY DATE_FORMAT (`date`, '%y%j%H%i%s')
Выводит все значения за последний час с точностью показания в 10 сек.

SELECT value, date from MAIN WHERE `date` > NOW() - INTERVAL 1 DAY GROUP BY DATE_FORMAT (`date`, '%y%j%H%i')
Выводит все значения за последние сутки с точностью показания в 1 минуту.

Что понравилось — исчез DESC, из-за которого данные выводились задом наперед. В данном случае порядок соблюден.
Всем большое спасибо за помощь! =)

Answer 2

[AlexeyK]

А каким образом вы собираетесь строить график по каждому 6му значению, если у вас 6 разных (наверное?) значений, а вы будете брать всего одно (даже не среднее арифметическое)?

Answer 3

[Alex XYZ]

select * from tbl where id%10=0 and datetime>=addtime(now(), '-0:1:00');

Выбрать данные за последнюю минуту, в которых id кретен 10. За сутки что-то похожее будет.

Answer 4

[Dzuba]

Поскольку такая таблица может иметь весьма приличный объем, отчего неизбежно пострадает скорость, я бы советовал реализовать всю логику в момент вставки новой записи, а именно:
1. Создать доп. таблицы для записей часа, суток и т.д., в которых хранить только записи за соответствующие моменты времени. Тогда доступ к ним будет весьма быстрым. Правда объем еще увеличится.
2. Если есть доступ к ПО, которое осуществляет запись в БД, проверять в нем величину соответствующего интервала. Если нет, или если требование «реализовать средствами MySQL» принципиально, то использовать триггер на вставку в исходную таблицу, в котором проверять условие и дублировать запись в таблицы для часа, суток и т.д.
При этом в доп.таблицы достаточно вставлять не саму запись, а только ее id.

Answer 5

[lashtal]

Каждую n-ную выбрать так (пример для каждой 60-й):

SELECT @row := @row + 1 row, t.id
FROM table t, (SELECT @row := 0) row
GROUP BY t.id
HAVING row % 60 = 0

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

На mysql это делать неверно, есть православный rrdtool, который и предназначен для работы с последовательностями значений во времени, автоматом получая данные в разрезе некоторого времени (напр. сутки, неделя, месяц, год).

Answer 6

[Jazzist]

Решение 1. Минутный timestamp нацело делится на 60? Можно написать условие сравнения для MySQL.

Решение 2. Есть-ли возможность писать дополнительный ключ? Если да — значит существует еще одно ресурсоэкономное решение.

Answer 7

[rvller]

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

Comments

[Александр]

Хм, видимо да, так будет точнее, особенно на графиках за сутки, неделю и тп.

[rvller]

Только при таком подходе производительность — слабое место.

[slang]

И ещё, я бы советовал использовать не среднее арифметическое, а медиану

Answer 8

[Анатолий]

Судя по задаче данных у Вас будет очень много. Я бы советовал добавить полей и ими оперировать

То есть, если значения времени в записях БД выглядят так:

22:19:00
22:19:10
22:19:20
22:19:30
22:19:40
22:19:50
22:20:00
22:20:10

то мы берем только
22:19:00
22:20:00

Тут лайк сдохнет, стоит добавить поле «00 ли в конце?» и выбирать по нему.
Опять де если выборка по датам, то даты разбить на части и по этим частям выбирать.
Эти варианты более зромоздкие, но скорости прибавить точно.

Comments

[Александр]

«00» не всегда может быть =(

[Анатолий]

Ну я просто привел пример, заметил что писали выше. Просто выбрать ключевые части и записывать их избыточно, и уж по ним потом и делать выборку.

Answer 9

[Scioner]

select field1, field2 from table
where mod(id, 60)=0

select field1, field2 from table
where mod(id, 360)=0

select field1, field2 from table
where mod(id, 1880)=0

etc…

Comments

[Scioner]

ещё можно предварительно выбрать айдишник, с которого надо начинать отсчёт и делать так:
select field1, field2 from table
where mod(id-start_id, 60)=0.

тогда статистика будет абсолютно точной

[Horzerus]

Ну опираться на «синхронизацию по id» совсем ненадежно.
Это примерно из ситуаций, когда меняют время на сервере назад, чтобы узнать что там было :)

[Scioner]

если нельзя опираться на id, то на что вообще можно опираться?

тут, насколько я понимаю, подразумевается целостность данных.

[Horzerus]

Ну автор же уточнил (http://habrahabr.ru/qa/6007/#answer_25724), что надо прореживать по времени. Поэтому нет никакой уверенности, что id будут кратными чему-то… Значит надо использовать именно поле времени для выборки

[Scioner]

нет никакой уверенности, что id будут кратными чему-то

Каждые 10 секунд в БД добавляется одна запись value

вы всегда пишете ответ, не читая сам вопрос?

[Александр]

select field1, field2 from table
where mod(id, 60)=0

Такой вариант работает. Но может возникнуть ситуация, когда данные не будут собираться например часов 12, из-за чего статистика получится неточной.

[Scioner]

поэтому я и посоветовал сначала получать айдишник, с которого надо начинать отсчёт.

т.е. делаем так:
select min(id) from table
where datetime>%НУЖНОЕ_ВРЕМЯ%

сохраняем результат, потом делаем так:
select field1, field2 from table
where datetime>%НУЖНОЕ_ВРЕМЯ% and
mod(id-%СОХРАНЁННЫЙ_РЕЗУЛЬТАТ%, 60)=0

можно сделать и вложенным запросом, но это медленней намного

[Horzerus]

Пожалуйста:
22:19:00
22:19:10
…
22:20:00
22:20:10
…

<провал в записях>
22:20:10

[Horzerus]

Не дописал, отправилось «само» :)
Пожалуйста:
22:19:00
22:19:10
…
22:20:00
22:20:10
(тут был провал в записях)
23:10:50
23:11:00
23:11:10

т.е. на границе групп данных может что-то теряться.
Но согласен, проблемные ситуации не оговаривались в условии

[Александр]

Это я неправильно вопрос сформулировал, сорри =) Про пропуски поступления данных не подумал сразу =)

Answer 10

[Александр]

Перефразирую точнее…
Значение value — показание температуры.
При построении часового графика необходимо 60 показаний. Из БД мы берем одно значение за каждую минуту этого часа.
То есть, если значения времени в записях БД выглядят так:

22:19:00
22:19:10
22:19:20
22:19:30
22:19:40
22:19:50
22:20:00
22:20:10

то мы берем только
22:19:00
22:20:00

То что произошло за эту минуту можно не учитывать.

Comments

[Horse]

select * where time like "%:00"

[Horzerus]

Если те, значения, которые надо выбрать всегда вначале часа/минуты/дня то можно при помощи функций для дат и времени проверять в WHERE, например: MINUTE(date) = 0

[Александр]

select * where time like "%:00" — сработал, но после осознал, что значение секунд не всегда может быть 00 =)

Answer 11

[Alex XYZ]

Про rrdtools мне очень понравилось. Достаточно быстро нашёл ссылку: Использование rrdtool для мониторинга сети. Мега вещь!!! Не знал. Спасибо за наводку.