LINUX.ORG.RU
Форум — Development  

[mysql] Выборка диапазонов дат неизменных значений

 


0

1

Доброе утро!

Табличка с полями 

id:INT | datetime: TIMESTAMP | speed: DOUBLE

Это упрощенная табличка трека автомобиля. Иногда автомобиль останавливается, и тогда в базе оказывается несколько подряд записей, в которых speed=0. И мне нужно сделать выборку, как бы сгруппированную по таким диапазонам. В целом нужно узнать время и длительность стоянок. Есть ли решения? Ручная постобработка - в крайнем случае.

★★★★★

Чисто запросом без хранимок - ИМХО никак.

r_asian ★☆☆
()
Ответ на: комментарий от staseg

А чо типы в описании таблицы какие нечоткие? Кстати, на такой задаче хранимка у тебя будет тормозить чопипец. В мускуле хранимки, триггеры и транзакции - это в принципе пипец.

Сразу постобработку делай.

r_asian ★☆☆
()
Ответ на: комментарий от r_asian

>А чо типы в описании таблицы какие нечоткие?

А как чотко?

Сразу постобработку делай.

Тогда тоже вопрос. Вся выборка - несколько миллионов записей. Если отрезать ее условием speed=0, постобработка будет быстрой, но кривоватой. Например две соседние записи, разделенные десятиминутным интервалом, - это одна стоянка (по техническим причинам данные могли не приходить) или между ними было движение (отфильтрованное запросом). Пойти на условности, мол разница меньше часа - одна стоянка, нельзя. А постобработка сразу двух миллионов записей сама по себе будет тормозить. Есть ли возможность как-то «подготовить» выборку, отфильтровав множество лишних записей?

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

staseg ★★★★★
() автор топика

Ну, если у тебя немного данных, запрос можно написать - но он очень хреновый. Мне его писать минут 20 (а значит влом, и «забесплатно не работаем»). Так что лучше тебе юзать «постобработку».

no-dashi ★★★★★
()
Ответ на: комментарий от staseg

А как чотко?

Например - так:

CREATE TABLE IF NOT EXISTS `tes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `datetime` int(11) unsigned NOT NULL,
  `speed` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Есть ли возможность как-то «подготовить» выборку, отфильтровав множество лишних записей?

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

Или даже можно создать временную таблицу, где сформировать интервалы с 2 концами, а обработку проводить в 2 этапа. Признак границы интервала - смена значения скорости от одной записи к другой. А потом уже эту промежуточную таблицу месить.

r_asian ★☆☆
()
Ответ на: комментарий от r_asian

>Например - так:

Ну я упрощенно написал, что там NOT NULL не важно. Индекс только один - по индексу, который автоинкремент.

`datetime` int(11)

Пока мой опыт говорит мне не хранить и не передавать даты в time_t, из-за того что в разных GMT они интерпретируются по-разному. Хотя хранить в time_t, а передавать в виде строк, наверное вариант. Почему не TIMESTAMP? Он медленный или почему?

Не стесняйся вводить новые поля, или даже таблицы, упрощающие обработку...

Спасибо. Сейчас попытаюсь переварить и посоображать.

staseg ★★★★★
() автор топика
Ответ на: комментарий от staseg

> Пока мой опыт говорит мне не хранить и не передавать даты в time_t, из-за того что в разных GMT они интерпретируются по-разному.

Я просто как бы намекал, что таблицы в «show create table» понятнее будут. Опять же их можно воспроизвести у себя без особого гемора, а если бы там и тестовые данные были ... ))))

r_asian ★☆☆
()
Ответ на: комментарий от r_asian

Я просто как бы намекал, что таблицы в «show create table» понятнее будут.

Намек не понял, исправляюсь :)

-- MySQL dump 10.13  Distrib 5.1.56, for pc-linux-gnu (i686)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.1.56-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `track`
--

DROP TABLE IF EXISTS `track`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `speed` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `track`
--

LOCK TABLES `track` WRITE;
/*!40000 ALTER TABLE `track` DISABLE KEYS */;
INSERT INTO `track` VALUES (1,'2011-11-22 09:02:20',60),(2,'2011-11-22 09:03:25',60),(3,'2011-11-22 09:04:25',62),(4,'2011-11-22 09:05:25',0),(5,'2011-11-22 09:25:25',0),(6,'2011-11-22 09:26:25',0),(7,'2011-11-22 09:28:25',0),(8,'2011-11-22 09:29:25',15),(9,'2011-11-22 09:29:55',40),(10,'2011-11-22 09:30:10',60),(11,'2011-11-22 09:30:30',70),(12,'2011-11-22 09:30:50',80),(13,'2011-11-22 09:40:50',0),(14,'2011-11-22 09:41:00',50),(15,'2011-11-22 09:41:20',50),(16,'2011-11-22 09:51:20',0),(17,'2011-11-22 09:51:30',0),(18,'2011-11-22 09:51:40',0),(19,'2011-11-22 09:56:40',13),(20,'2011-11-22 09:59:40',0),(21,'2011-11-22 10:05:00',42);
/*!40000 ALTER TABLE `track` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-11-22 12:09:36

Вот как выглядит полная выборка:

+----+---------------------+-------+
| id | datetime            | speed |
+----+---------------------+-------+
|  1 | 2011-11-22 12:02:20 |    60 |
|  2 | 2011-11-22 12:03:25 |    60 |
|  3 | 2011-11-22 12:04:25 |    62 |
|  4 | 2011-11-22 12:05:25 |     0 |
|  5 | 2011-11-22 12:25:25 |     0 |
|  6 | 2011-11-22 12:26:25 |     0 |
|  7 | 2011-11-22 12:28:25 |     0 |
|  8 | 2011-11-22 12:29:25 |    15 |
|  9 | 2011-11-22 12:29:55 |    40 |
| 10 | 2011-11-22 12:30:10 |    60 |
| 11 | 2011-11-22 12:30:30 |    70 |
| 12 | 2011-11-22 12:30:50 |    80 |
| 13 | 2011-11-22 12:40:50 |     0 |
| 14 | 2011-11-22 12:41:00 |    50 |
| 15 | 2011-11-22 12:41:20 |    50 |
| 16 | 2011-11-22 12:51:20 |     0 |
| 17 | 2011-11-22 12:51:30 |     0 |
| 18 | 2011-11-22 12:51:40 |     0 |
| 19 | 2011-11-22 12:56:40 |    13 |
| 20 | 2011-11-22 12:59:40 |     0 |
| 21 | 2011-11-22 13:05:00 |    42 |
+----+---------------------+-------+

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

staseg ★★★★★
() автор топика
Ответ на: комментарий от staseg

Намек не понял, исправляюсь :)

Вот цэ - добре.

Я бы сделал примерно так(при условии, что эти данные нужны не в режиме реального времени):

1) Делаем табличку 

CREATE TABLE IF NOT EXISTS `track_tmp` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Начало интервала',
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Конец интервала',
  `speed` double NOT NULL COMMENT 'Скорость на интервале',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Интервалы движения' AUTO_INCREMENT=15 ;

2) Пишем микроскрипт и вешаем на крон на время, наиболее свободное от записи трека (примерное решение на убогоньком)

    mysql_query("TRANCATE TABLE `track_tmp`");
    
    $query = "SELECT `datetime`,`speed` FROM `track` ORDER BY `datetime` ASC";
    $stha = mysql_query($query);
    $last_speed = 0; $last_datetime = '0000-00-00 00:00:00';
    while($row = mysql_fetch_assoc($stha))
        if($row['speed']!=$last_speed){
            mysql_query("INSERT INTO `track_tmp`(`start`,`end`,`speed`) VALUES('$last_datetime','".$row['datetime']."','$last_speed');");
            $last_speed     = $row['speed'];$last_datetime  = $row['datetime'];
        }

Если временной промежуток, за который надо посчитать известен, то не надо очищать таблицу, и чуть модифицировать запрос на выборку.

3) Выбираем из `track_tmp`

SELECT * FROM `track_tmp` WHERE `speed` =0 ORDER BY `end` ASC

Получаем

+----+---------------------+---------------------+-------+
| id | start               | end                 | speed |
+----+---------------------+---------------------+-------+
|  1 | 0000-00-00 00:00:00 | 2011-11-22 13:02:20 |     0 |
|  4 | 2011-11-22 13:05:25 | 2011-11-22 13:29:25 |     0 |
| 10 | 2011-11-22 13:40:50 | 2011-11-22 13:41:00 |     0 |
| 12 | 2011-11-22 13:51:20 | 2011-11-22 13:56:40 |     0 |
| 14 | 2011-11-22 13:59:40 | 2011-11-22 14:05:00 |     0 |
+----+---------------------+---------------------+-------+

Оптимизировать и пофиксить глюки по вкусу.

r_asian ★☆☆
()

я бы на твоем месте добавил был еще одно поле в таблицу, которое бы инкрементировалось бы при изменении значения speed.
То есть перед каждой вставке новой строки, проверяется значение speed для последней вставленной записи (SELECT speed, datetime FROM track order by datetime desc limit 1 - это выполнится мгновенно при наличии индекса по datetime).

Тогда твой запрос будет простой и состоять лишь в группировке по добавочному полю.
SELECT over1, max(datetime) - min(datetime) FROM track where speed = 0 group by over1 ;

где over1 - это оверхедное поле.

JFreeM ★★★☆
()
Ответ на: комментарий от JFreeM

ну да, и апдейт скритп для базы будет довольно простой.

JFreeM ★★★☆
()
Ответ на: комментарий от JFreeM

Да, это более годное решение, за исключением одного момента: неизвестно кто, и какими руками писал для этой БД обработчики. Может случиться так, что изменение количества и порядка полей окажется фатальным.

Сидит где-нибудь.

INSERT INTO `track` VALUES(NULL,'2011-11-22 14:55:03','0')

И приехали.

r_asian ★☆☆
()
Ответ на: комментарий от r_asian

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

SET
  @prevspeed=-1
 ,@grp=0;
SELECT
  datetime
 ,TIME_FORMAT(MAX(datetime)-MIN(datetime),'%H:%i:%s') AS tm
FROM (
  SELECT
    id
   ,datetime
   ,speed
   ,IF(speed=0 AND speed=@prevspeed,@grp,@grp:=@grp+1) AS grp
   ,@prevspeed:=speed AS ps
  FROM track
) AS main
WHERE
  speed=0
GROUP BY
  grp;
+---------------------+----------+
| datetime            | tm       |
+---------------------+----------+
| 2011-11-22 12:05:25 | 00:23:00 |
| 2011-11-22 12:40:50 | 00:00:00 |
| 2011-11-22 12:51:20 | 00:00:20 |
| 2011-11-22 12:59:40 | 00:00:00 |
+---------------------+----------+

Теперь буду смотреть, как оно поведет себя на больших массивах данных.

staseg ★★★★★
() автор топика

Если в таблице хранится множество треков юнита, и каждый трек занимает десяток-два строк то я бы выбирал трек (по номеру) и сортировал в клиентском (по отношению к БД) коде.
Так даже лучше, минимум логики просачивается в DB layer.

urxvt ★★★★★
()
Ответ на: комментарий от JFreeM

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

staseg ★★★★★
() автор топика
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Development