Форум программистов «Весельчак У»
  *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

  • Рекомендуем проверить настройки временной зоны в вашем профиле (страница "Внешний вид форума", пункт "Часовой пояс:").
  • У нас больше нет рассылок. Если вам приходят письма от наших бывших рассылок mail.ru и subscribe.ru, то знайте, что это не мы рассылаем.
   Начало  
Наши сайты
Помощь Поиск Календарь Почта Войти Регистрация  
 
Страниц: 1 2 [Все]   Вниз
  Печать  
Автор Тема: Выборка с промежутками min и max  (Прочитано 30963 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« : 30-03-2011 13:42 » 

таблица
Код:
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
Деятель
Команда клуба

ru
Offline Offline
Пол: Мужской

« Ответ #1 : 30-03-2011 15:03 » 

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

Программировать - значит понимать (К. Нюгард)
Невывернутое лучше, чем вправленное (М. Аврелий)
Многие готовы скорее умереть, чем подумать (Б. Рассел)
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #2 : 30-03-2011 15:09 » 

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

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
baldr
Команда клуба

cy
Offline Offline
Пол: Мужской
Дорогие россияне


WWW
« Ответ #3 : 30-03-2011 15:11 » 

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

Приличный компьютер всегда будет стоить дороже 1000 долларов, потому что 500 долларов - это не вполне прилично
Kivals
Команда клуба

ca
Offline Offline
Пол: Мужской

WWW
« Ответ #4 : 30-03-2011 16:01 » 

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

100001   100005 track1
100006   100010 track2
100011   100015 track3
100016   100020 track1
100021   100025 track4
Записан
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #5 : 30-03-2011 17:27 » 

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

Мы все учились понемногу... Чему-нибудь и как-нибудь.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #6 : 30-03-2011 17:27 » 

Sla, используй подзапрос?
Как?
Записан

Мы все учились понемногу... Чему-нибудь и как-нибудь.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #7 : 30-03-2011 17:32 » 

СУБД MySQL?
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #8 : 30-03-2011 17:33 » 

да
Записан

Мы все учились понемногу... Чему-нибудь и как-нибудь.
Алексей++
глобальный и пушистый
Глобальный модератор

ru
Offline Offline
Сообщений: 13


« Ответ #9 : 30-03-2011 17:45 » 

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


'00000004'+'00000016'

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

Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #10 : 30-03-2011 17:58 » 

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

Мы все учились понемногу... Чему-нибудь и как-нибудь.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #11 : 30-03-2011 18:05 » 

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

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)
« Последнее редактирование: 30-03-2011 18:10 от RXL » Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Dimka
Деятель
Команда клуба

ru
Offline Offline
Пол: Мужской

« Ответ #12 : 30-03-2011 18:06 » 

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

Программировать - значит понимать (К. Нюгард)
Невывернутое лучше, чем вправленное (М. Аврелий)
Многие готовы скорее умереть, чем подумать (Б. Рассел)
Алексей++
глобальный и пушистый
Глобальный модератор

ru
Offline Offline
Сообщений: 13


« Ответ #13 : 30-03-2011 18:09 » 

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

например тут
Цитата
100006   100010 track2
100011   100015 track3
100004   100020 track1
100021   100025 track4
вообще невозможно порешить задачу без разбивки интервала 100004   100020 на два


Записан

RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #14 : 30-03-2011 18:10 » 

Леш, пост №11
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Dusk
Команда клуба

ru
Offline Offline
Пол: Мужской
Редкий, но веселый вид


« Ответ #15 : 30-03-2011 20:45 » 

RXL,  а что, в мускуле можно предыдущими запросами создавать переменные? Если так, то это красивое решение.
« Последнее редактирование: 30-03-2011 21:05 от Dusk » Записан

Человек, сделавший хотя бы шаг к цели, сразу становится мишенью для всех отставших
Опыт - это то, что появляется сразу после того, как он был так необходим...
Бывают минуты, когда у тебя есть секунды, чтобы исправить деланное часами и не получить последствия на годы...
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #16 : 30-03-2011 20:50 » 

+----+--------+--------+
| 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
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #17 : 30-03-2011 21:02 » 

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

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

+----------+----------+--------+
| min_time | max_time | name   |
+----------+----------+--------+
|   100001 |   100005 | track1 |
|   100006 |   100010 | track2 |
|   100011 |   100015 | track1 |
+----------+----------+--------+
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Dusk
Команда клуба

ru
Offline Offline
Пол: Мужской
Редкий, но веселый вид


« Ответ #18 : 30-03-2011 21:05 » 

Вот мой вариант на сон грядущий:
Код:
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
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #19 : 30-03-2011 21:07 » 

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
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #20 : 30-03-2011 21:12 » 

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
Молодой специалист

ru
Offline Offline

« Ответ #21 : 31-03-2011 05:10 » 

мои пять копеек
Код:
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)
Записан

I Have Nine Lives You Have One Only
THINK!
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #22 : 31-03-2011 06:04 » 

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?
« Последнее редактирование: 31-03-2011 06:05 от RXL » Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Kivals
Команда клуба

ca
Offline Offline
Пол: Мужской

WWW
« Ответ #23 : 31-03-2011 07:00 » 

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

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #24 : 31-03-2011 07:11 » 

time - unixstamp
Записан

Мы все учились понемногу... Чему-нибудь и как-нибудь.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #25 : 31-03-2011 07:13 » 

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

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #26 : 31-03-2011 07:19 » 

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

Мы все учились понемногу... Чему-нибудь и как-нибудь.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #27 : 31-03-2011 07:21 » 

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

Мы все учились понемногу... Чему-нибудь и как-нибудь.
Dusk
Команда клуба

ru
Offline Offline
Пол: Мужской
Редкий, но веселый вид


« Ответ #28 : 31-03-2011 08:15 » 

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

Проверил в firebird - результат соответствует таблице, прописанной Kivals в 4 посте....
Чуть попозже проверю в мускуле - уверен в результате, так как использовались стандартные средства.
« Последнее редактирование: 31-03-2011 08:36 от Dusk » Записан

Человек, сделавший хотя бы шаг к цели, сразу становится мишенью для всех отставших
Опыт - это то, что появляется сразу после того, как он был так необходим...
Бывают минуты, когда у тебя есть секунды, чтобы исправить деланное часами и не получить последствия на годы...
Kivals
Команда клуба

ca
Offline Offline
Пол: Мужской

WWW
« Ответ #29 : 01-04-2011 09:05 » 

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

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #30 : 01-04-2011 17:59 » 

Итоги можно кешировать и обновлять по крону или сделать EVENT в MySQL (версия >= 5.1).
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Kivals
Команда клуба

ca
Offline Offline
Пол: Мужской

WWW
« Ответ #31 : 02-04-2011 07:42 » 

Запись в таблицу идет по обновлению лога - не чаще чем одна запись пару раз в минуту
Выборка - вообще не понятно как. Пока наверное пару раз в сутки Улыбаюсь Позже - наверное до 200 раз в сутки
Так что тут можно выбирать вариант исходя из того, что удобнее разработчику (Sla)
Записан
McZim
Модератор

ru
Offline Offline
Пол: Мужской
Я странный


WWW
« Ответ #32 : 07-04-2011 10:48 » 

Если бы был Oracle, то вот так:

Код: (SQL)
SELECT name, min_t, max_t FROM
(
    SELECT name,
    MIN(TIME) keep(dense_rank FIRST ORDER BY TIME) AS min_t,
    MAX(TIME) keep(dense_rank LAST ORDER BY TIME) AS max_t
    FROM t
    WHERE TIME > '100003'
    GROUP BY name
)
Записан

The CBO without stats is like a morning without coffee. (c) T.Kyte.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #33 : 07-04-2011 12:38 » 

Для небольшого сайта Оракл слишком прожорлив...
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Страниц: 1 2 [Все]   Вверх
  Печать  
 

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines