Форум пользователей MySQL

Placeholder · 21.03.2012 20:19:58

Доброго времени суток. Не даёт покоя простая для понимания задача. Имеется таблица (показания прибора):
CREATE TABLE `test_grp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`val` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `test_grp` VALUES (1,'2011-12-31 20:01:00',0.08),(2,'2011-12-31 20:02:00',1.12),(3,'2011-12-31 20:03:00',2.34),(4,'2011-12-31 20:04:00',4.29),(5,'2011-12-31 20:05:00',7.67),(6,'2011-12-31 20:06:00',3.41),(7,'2011-12-31 20:07:00',2.15),(8,'2011-12-31 20:08:00',1.23),(9,'2011-12-31 20:09:00',8.79),(10,'2011-12-31 21:00:00',2.54),(11,'2011-12-31 21:02:00',1.36),(12,'2011-12-31 21:03:00',0.15),(13,'2011-12-31 21:12:03',15.81),(14,'2011-12-31 21:13:31',20.37),(15,'2011-12-31 21:13:48',16.72),(16,'2011-12-31 21:13:57',10.21),(17,'2011-12-31 21:14:23',1.89),(18,'2011-12-31 21:14:39',0.97),(19,'2011-12-31 21:15:29',0.31);
Выглядит так:
| id | time | val |
+---+---------------------+------+
| 1 | 2012-01-01 00:01:00 | 0.08 |
| 2 | 2012-01-01 00:02:00 | 1.12 |
| 3 | 2012-01-01 00:03:00 | 2.34 |
| 4 | 2012-01-01 00:04:00 | 4.29 |
| 5 | 2012-01-01 00:05:00 | 7.67 |
| 6 | 2012-01-01 00:06:00 | 3.41 |
| 7 | 2012-01-01 00:07:00 | 2.15 |
| 8 | 2012-01-01 00:08:00 | 1.23 |
| 9 | 2012-01-01 00:09:00 | 8.79 |
| 10 | 2012-01-01 01:00:00 | 2.54 |
| 11 | 2012-01-01 01:02:00 | 1.36 |
| 12 | 2012-01-01 01:03:00 | 0.15 |
| 13 | 2012-01-01 01:12:03 | 15.81 |
| 14 | 2012-01-01 01:13:31 | 20.37 |
| 15 | 2012-01-01 01:13:48 | 16.72 |
| 16 | 2012-01-01 01:13:57 | 10.21 |
| 17 | 2012-01-01 01:14:23 | 1.89 |
| 18 | 2012-01-01 01:14:39 | 0.97 |
| 19 | 2012-01-01 01:15:29 | 0.31 |
+----+---------------------+-------+
Нужно выделить группы "превышений" показаний прибора, например выше 3.0, о которых человекопонимаемым языком говорят так:
"превышение №1, началось '2012-01-01 00:04:00', закончилось '2012-01-01 00:06:00', достигнут максимум 7.67"
"превышение №3, началось '2012-01-01 01:12:03', закончилось '2012-01-01 01:13:57', достигнут максимум 20.37"
Пробую так

SELECT `id`,  MIN(`time`) AS from_time, MAX(`time`) AS to_time, MAX(`val`) AS max_val, COUNT(*) FROM `test_grp`

    GROUP BY `val`>3.0;

на что получаю 2 строчки:
+---+----------------------+----------------------+---------+----------+
| id | from_time | to_time | max_val | COUNT(*)|
+---+----------------------+----------------------+---------+----------+
| 1 | 2012-01-01 00:01:00 | 2012-01-01 01:15:29 | 2.54 | 11 |
| 4 | 2012-01-01 00:04:00 | 2012-01-01 01:13:57 | 20.37 | 8 |
+---+----------------------+----------------------+---------+----------+
очевидно, множество было разбито на 2 части: там где val > 3.0 верно и там где оно не верно, независимо от хронологического порядка. Эксперименты и написание процедуры прекратил на циклическом повторении поиска интервалов превышений. Уверен, существует более грамотный способ заставить mysql группировать данные с учетом порядка времени. Прошу совета, заранее благодарен за комменты.

Отредактированно Placeholder (22.03.2012 13:43:35)

vasya · 21.03.2012 21:42:02

Хорошего способа я не знаю. Можно идти по таблице по возрастанию времени и с помощью пользовательских переменных для превышений устанавливать целочисленное значение равное номеру превышения. Потом группировать по этому полю.

http://webew.ru/articles/3923.webew

http://sqlinfo.ru/forum/viewtopic.php?id=1742&p=1 (читать всю тему до конца)

vasya · 22.03.2012 12:09:24

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

Placeholder · 22.03.2012 13:05:55

Стало немного стыдно за предыдущий пост, но Вы всё же успели на него ответить
Начал с подзапроса:

SET @f:=0; /**/

select `val`,`time`, if(val>3.0,@f,@f:=@f+1) AS f from test_grp  ORDER BY `time`;

+-------+---------------------+------+
| val | time | f |
+-------+---------------------+------+
| 0.08 | 2012-01-01 00:01:00 | 1 |
| 1.12 | 2012-01-01 00:02:00 | 2 |
| 2.34 | 2012-01-01 00:03:00 | 3 |
| 4.29 | 2012-01-01 00:04:00 | 3 |
| 7.67 | 2012-01-01 00:05:00 | 3 |
| 3.41 | 2012-01-01 00:06:00 | 3 |
| 2.15 | 2012-01-01 00:07:00 | 4 |
| 1.23 | 2012-01-01 00:08:00 | 5 |
| 8.79 | 2012-01-01 00:09:00 | 5 |
| 2.54 | 2012-01-01 01:00:00 | 6 |
| 1.36 | 2012-01-01 01:02:00 | 7 |
| 0.15 | 2012-01-01 01:03:00 | 8 |
| 15.81 | 2012-01-01 01:12:03 | 8 |
| 20.37 | 2012-01-01 01:13:31 | 8 |
| 16.72 | 2012-01-01 01:13:48 | 8 |
| 10.21 | 2012-01-01 01:13:57 | 8 |
| 1.89 | 2012-01-01 01:14:23 | 9 |
| 0.97 | 2012-01-01 01:14:39 | 10 |
| 0.31 | 2012-01-01 01:15:29 | 11 |
+-------+---------------------+------+
Теперь есть что группировать.

Отредактированно Placeholder (22.03.2012 13:38:31)

vasya · 22.03.2012 13:30:44

mysql> SET @f:=0,@i:=0;

Query OK, 0 rows affected (0.02 sec)

mysql> select @i:=@i+1 as `№ превышения`, min(`time`) `начало`, max(`time`) `конец`, max(val) `максимум` 

from (select `val`,`time`, if(val>3.0,@f,(@f:=@f+1) and 0) AS f from 

(select val, `time` from test_grp order by `time`) t) t1 where t1.f>0 group by t1.f;

+--------------+---------------------+---------------------+----------+

| № превышения | начало              | конец               | максимум |

+--------------+---------------------+---------------------+----------+

|            1 | 2011-12-31 20:04:00 | 2011-12-31 20:06:00 |     7.67 |

|            2 | 2011-12-31 20:09:00 | 2011-12-31 20:09:00 |     8.79 |

|            3 | 2011-12-31 21:12:03 | 2011-12-31 21:13:57 |    20.37 |

+--------------+---------------------+---------------------+----------+

3 rows in set (0.00 sec)

Placeholder · 22.03.2012 13:31:01

Вот итоговый запрос:

SET @f:=0;

SELECT MIN(`time`) from_time,MAX(`time`) to_time,MAX(`val`) max_val

FROM (SELECT`val`,`time`, IF(`val`>3.0,@f,@f:=@f+1) f FROM `test_grp`  ORDER BY `time`) t

        WHERE `val`>3.0 GROUP BY f;

Результат:

+---------------------+---------------------+---------+

| from_time           | to_time             | max_val |

+---------------------+---------------------+---------+

| 2012-01-01 00:04:00 | 2012-01-01 00:06:00 |    7.67 |

| 2012-01-01 00:09:00 | 2012-01-01 00:09:00 |    8.79 |

| 2012-01-01 01:12:03 | 2012-01-01 01:13:57 |   20.37 |

+---------------------+---------------------+---------+

То, что было нужно.
Ещё раз спасибо за подсказку и примеры.

Отредактированно Placeholder (22.03.2012 14:05:06)

Placeholder · 22.03.2012 14:13:25

Однако, мой вариант гарантирует, что упорядочен по времени будет только результат подзапроса. В Вашем варианте используется дополнительный подзапрос, выстраивающий данные в хронологическом порядке. Действительно, чтобы не инсертить данные каждую секунду, для устройств предусмотрен буфер, по накоплению которого происходит сброс в базу, вполне вероятно, что последовательные `id` будут иметь не последовательные показание времени. Моё упущение.

Форум пользователей MySQL

#1 21.03.2012 20:19:58

группировка по значениям

#2 21.03.2012 21:42:02

Re: группировка по значениям

#3 22.03.2012 12:09:24

Re: группировка по значениям

#4 22.03.2012 13:05:55

Re: группировка по значениям

#5 22.03.2012 13:30:44

Re: группировка по значениям

#6 22.03.2012 13:31:01

Re: группировка по значениям

#7 22.03.2012 14:13:25

Re: группировка по значениям

Board footer