Выборка с промежутками min и max

[Sla]

таблица

Код:

id  time   name

1  100001 track1
2  100002 track1
3  100003 track1
4  100004 track1
5  100005 track1

6  100006 track2
7  100007 track2
8  100008 track2
9  100009 track2
10 100010 track2

11 100011 track3
12 100012 track3
13 100013 track3
14 100014 track3
15 100015 track3

16 100016 track1
17 100017 track1
18 100018 track1
19 100019 track1
20 100020 track1

21 100021 track4
22 100022 track4
23 100023 track4
24 100024 track4
25 100025 track4

Рабочий код

Код:

select min(`time_query`) as mintime, max(`time_query`) as maxtime ,`name_track`
from stat
where `time_query` > $period
GROUP BY `name_track` ORDER BY max(`time_query`) ASC

Но...

Код:

select min(`time_query`) as mintime, max(`time_query`) as maxtime ,`name_track`
from stat
where `time_query` > 100003
GROUP BY `name_track` ORDER BY max(`time_query`) ASC

100006   100010 track2
100011   100015 track3
100004   100020 track1
100021   100025 track4
 
Как написать правильный запрос чтобы min и мах шли без прерывания.

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

[Dimka]

Sla, а задача-то как звучит? сгруппировать по name? А каковы характеристики групп?

[RXL]

Слав, не совсем понятно, чего ты хочешь. Пересортировать иначе?

[baldr]

Sla, используй подзапрос?

[Kivals]

Насколько я понимаю задача стоит так: определить min и max значения time в пределах группы, где группа - это набор подряд (по time) идущих записей с одинаковым name
Т.е. должны получить:

100001	100005	track1
100006	100010	track2
100011	100015	track3
100016	100020	track1
100021	100025	track4

[Sla]

вот Kivals, правильно все понял

[Sla]

Sla, используй подзапрос?

Как?

[RXL]

СУБД MySQL?

[Sla]

да

[Алексей++]

а объединить оба значения в одну строку и по этой строке отсортировать ?


'00000004'+'00000016'

или как там строки объединяются - не помню

[Sla]

Алексей1153++, и что это даст? Развивай идею

[RXL]

Задаем порядок.

mysql> select id, time, name from stat order by id;
+----+--------+--------+
| id | time   | name   |
+----+--------+--------+
|  1 | 100001 | track1 |
|  2 | 100002 | track1 |
|  3 | 100003 | track1 |
|  4 | 100004 | track1 |
|  5 | 100005 | track1 |
|  6 | 100006 | track2 |
|  7 | 100007 | track2 |
|  8 | 100008 | track2 |
|  9 | 100009 | track2 |
| 10 | 100010 | track2 |
| 11 | 100011 | track3 |
| 12 | 100012 | track3 |
| 13 | 100013 | track3 |
| 14 | 100014 | track3 |
| 15 | 100015 | track3 |
+----+--------+--------+
15 rows in set (0.00 sec)

Применяем хитрости для последовательной обработки строк.

mysql> set @grp = '';
Query OK, 0 rows affected (0.00 sec)

mysql> set @time = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, if(name = @grp, @time, @time := time) t1, time, if(name = @grp, @grp, @grp := name) n1, name from (select id, time, name from stat order by id) t;
+----+--------+--------+--------+--------+
| id | t1     | time   | n1     | name   |
+----+--------+--------+--------+--------+
|  1 | 100001 | 100001 | track1 | track1 |
|  2 | 100001 | 100002 | track1 | track1 |
|  3 | 100001 | 100003 | track1 | track1 |
|  4 | 100001 | 100004 | track1 | track1 |
|  5 | 100001 | 100005 | track1 | track1 |
|  6 | 100006 | 100006 | track2 | track2 |
|  7 | 100006 | 100007 | track2 | track2 |
|  8 | 100006 | 100008 | track2 | track2 |
|  9 | 100006 | 100009 | track2 | track2 |
| 10 | 100006 | 100010 | track2 | track2 |
| 11 | 100011 | 100011 | track3 | track3 |
| 12 | 100011 | 100012 | track3 | track3 |
| 13 | 100011 | 100013 | track3 | track3 |
| 14 | 100011 | 100014 | track3 | track3 |
| 15 | 100011 | 100015 | track3 | track3 |
+----+--------+--------+--------+--------+
15 rows in set (0.00 sec)

Получаем искомое.

mysql> set @grp = '';
Query OK, 0 rows affected (0.00 sec)

mysql> set @time = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select min_time, max(time) max_time, name from (select if(name = @grp, @time, @time := time) min_time, time, if(name = @grp, @grp, @grp := name) name from (select id, time, name from stat order by id) t) tt group by min_time, name;
+----------+----------+--------+
| min_time | max_time | name   |
+----------+----------+--------+
|   100001 |   100005 | track1 |
|   100006 |   100010 | track2 |
|   100011 |   100015 | track3 |
+----------+----------+--------+
3 rows in set (0.01 sec)

Внимание! Full scan в кубе!

mysql> explain select min_time, max(time) max_time, name from (select if(name = @grp, @time, @time := time) min_time, time, if(name = @grp, @grp, @grp := name) name from (select id, time, name from stat order by id) t) tt group by min_time, name;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using temporary; Using filesort |
|  2 | DERIVED     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |   15 |                                 |
|  3 | DERIVED     | stat       | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
3 rows in set (0.00 sec)

[Dimka]

По-моему тут две независимых задачи, решаемые через два вложенных запроса: одни - группировка, второй - поиск непрерывных интервалов/промежутков.

[Алексей++]

Sla, не, не подходит

например тут

100006   100010 track2
100011   100015 track3
100004   100020 track1
100021   100025 track4

вообще невозможно порешить задачу без разбивки интервала 100004   100020 на два

[RXL]

Леш, пост №11

[Dusk]

RXL,  а что, в мускуле можно предыдущими запросами создавать переменные? Если так, то это красивое решение.

[Sla]

+----+--------+--------+
| id | time   | name   |
+----+--------+--------+
|  1 | 100001 | track1 |
|  2 | 100002 | track1 |
|  3 | 100003 | track1 |
|  4 | 100004 | track1 |
|  5 | 100005 | track1 |
|  6 | 100006 | track2 |
|  7 | 100007 | track2 |
|  8 | 100008 | track2 |
|  9 | 100009 | track2 |
| 10 | 100010 | track2 |
| 11 | 100011 | track1 |
| 12 | 100012 | track1 |
| 13 | 100013 | track1 |
| 14 | 100014 | track1 |
| 15 | 100015 | track1 |
+----+--------+--------+

Что будет при таком раскладе?

[RXL]

Это переменные на вроде переменных среды, только в MySQL. Есть глобальные, есть сессионные. Типа не имеют (текстовые). Прямо в запросе можно изменить через ":=". Причем выражение "@var := expr" равно expr. Естественно, что при каждом вычислении expr меняется, а с ним и значение переменной.

Слав, а нельзя ли сразу дампом? Перебивать неудобно.

+----------+----------+--------+
| min_time | max_time | name   |
+----------+----------+--------+
|   100001 |   100005 | track1 |
|   100006 |   100010 | track2 |
|   100011 |   100015 | track1 |
+----------+----------+--------+

[Dusk]

Вот мой вариант на сон грядущий:

Код:

select t2.min_time, t2.max_time, t2.name
from (select min(t1.time) as min_time, max(t1.time) as max_time, t1.name, t1.group_key
     from (select round((s.id+4)/5,0) as group_key, s.time, s.name from stat s) as t1
     group by t1.name, t1.group_key
     order by t1.group_key) as t2

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

[Sla]

Kivals,

+----+--------+--------+--------+--------+
| id | t1     | time   | n1     | name   |
+----+--------+--------+--------+--------+
|  1 | 100001 | 100001 | track1 | track1 |
|  2 | 100001 | 100002 | track1 | track1 |
|  3 | 100001 | 100003 | track1 | track1 |
|  4 | 100001 | 100004 | track1 | track1 |
|  5 | 100001 | 100005 | track1 | track1 |
|  6 | 100006 | 100006 | track2 | track2 |
|  7 | 100006 | 100007 | track2 | track2 |
|  8 | 100006 | 100008 | track2 | track2 |
|  9 | 100006 | 100009 | track2 | track2 |
| 10 | 100006 | 100010 | track2 | track2 |
| 11 | 100011 | 100011 | track3 | track3 |
| 12 | 100011 | 100012 | track3 | track3 |
| 13 | 100011 | 100013 | track3 | track3 |
| 14 | 100011 | 100014 | track3 | track3 |
| 15 | 100011 | 100015 | track3 | track3 |
+----+--------+--------+--------+--------+

тебе это что-нибудь напоминает?

[RXL]

Dusk, по данным из поста 16 с твоим запросом получилось так:

+----------+----------+--------+
| min_time | max_time | name   |
+----------+----------+--------+
|   100001 |   100003 | track1 |
|   100006 |   100008 | track2 |
|   100004 |   100005 | track1 |
|   100009 |   100010 | track2 |
|   100011 |   100013 | track1 |
|   100014 |   100015 | track1 |
+----------+----------+--------+

Мой результат в после 17.

[HandKot]

мои пять копеек

Код:

select
	Min(t.time)
	, Max(t.time)
	, t.name
from (
	select
		t1.id
		, t1.time
		, t1.name
		, COUNT(t2.id) cnt
	from
		@t t1
	left join @t t2 on t2.name = t1.name and t2.id < t1.id
	group by
		t1.id
		, t1.time
		, t1.name) t
group by
	t.name
	, t.id - cnt
order by
	Min(t.time)

[RXL]

HandKot, работает! План такой же.

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using temporary; Using filesort |
|  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using temporary; Using filesort |
|  2 | DERIVED     | t2         | ALL  | PRIMARY       | NULL | NULL    | NULL |   15 |                                 |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Вопрос к Славе: что означает поле time?

[Kivals]

Kivals,
....
тебе это что-нибудь напоминает?

А то Что я и предлагал, только здесь ты это будешь заполнять при каждом запросе, а в моем предложении (через триггер или функцию) - при добавлении новой записи... ИМХО это будет быстрее.
Я и подозревал что при запросе план выполнения будет Full scan, вот только у меня практического опыта работы с планами нет...

[Sla]

time - unixstamp

[RXL]

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

[Sla]

по сути твои сеты - это аналог оракловского rownum

[Sla]

Kivals, в твоей схеме появляется избыточность и лишний запрос или триггер при вставке новой записи

[Dusk]

RXL, блин, проверю где я накосячил....

Проверил в firebird - результат соответствует таблице, прописанной Kivals в 4 посте....
Чуть попозже проверю в мускуле - уверен в результате, так как использовались стандартные средства.

[Kivals]

Kivals, в твоей схеме появляется избыточность и лишний запрос или триггер при вставке новой записи

Да, но ИМХО вставка будет реже, чем выборка, а значит времени в итоге должно тратиться меньше. Кроме того - можно чистить таблицу: имеет смысл хранить только итоговую запись по треку