Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тёплая осень в Москве / 17 сообщений из 17, страница 1 из 1
07.11.2013, 23:11:29
    #38457070
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
Добрый день!
За окном, как никогда тёплая погода по наблюдениям московских метеорологов. Между станциями метеонаблюдения возник спор, надо им постараться помочь.

Есть таблица "Погода"

ID Номер станции Температура Дата1 1 12 '2013-11-07 00:00:00'2 1 1 '2013-11-06 00:00:00'3 1 4 '2013-11-05 05:25:23'4 1 1 '2013-11-06 05:11:47'5 1 2 '2013-11-05 00:00:00'6 1 2 '2013-11-08 05:13:43'7 2 1 '2013-11-08 10:16:35'8 1 2 '2013-11-08 05:13:43'9 2 1 '2013-11-08 10:16:35'10 2 32 '2013-11-08 00:00:00'11 2 26 '2013-11-05 10:23:00'12 2 30 '2013-11-08 00:46:00'13 1 45 '2013-11-05 04:00:26'14 2 45 '2013-11-05 20:28:00'15 1 0 '2014-01-01 06:30:44'16 1 0 '2014-01-01 04:26:39'17 1 0 '2014-01-01 10:49:34'18 1 10 '2013-11-08 13:18:54'19 1 10 '2013-11-08 05:18:54'

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

Пока на примере 2х станций, спор метеорологов не удалось разрешить, у какой из станций и в какой день были самые большие колебания температур.

Удалось найти наибольшие колебания температур в день по каждой станции и сгруппировать их.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT stanc, 
        DATE, MAX( temper ) AS t_max, 
        MIN( temper ) AS t_min, 
        MAX( temper ) - MIN( temper ) AS t_div
FROM  `weather` 
GROUP BY stanc, DAY( DATE ) 
ORDER BY stanc, t_div DESC



Результат запроса
Номер станции Дата MAX темп MIN темп Разница темп12013-11-05 05:25:234524312013-11-08 05:13:43102812014-01-01 06:30:4400012013-11-07 00:00:001212012013-11-06 00:00:0011022013-11-08 10:16:353213122013-11-05 10:23:00452619

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

Можно, конечно, это все сделать через промежуточные таблицы, но хотелось бы красивого решения.
Не исключаю, что, возможно, с GROUP BY изначально пошел по неверному пути, тогда как именно организовать это запрос, что бы можно было получить 2 заветные строчки. Буду благодарен за любые идеи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 03:51:31
    #38457261
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2,

Метеорологам полезно прочитать вот
этот ФАК : 7543220

Похоже что промежуточная таблица таки нужна
или придется два раза ее получать для само-соденинения.

Есть способ "паровозиком" за один раз, но он
может быть оооочень громоздким....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 05:13:10
    #38457286
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
javajdbcПохоже что промежуточная таблица таки нужна
или придется два раза ее получать для само-соденинения.А откуда "2 раза"? У нас уже есть готовая сгруппированная таблица. Перенумеровать по группам, потом посчитать кол-во строк с номером 1, всё.
Хотя... я что-то не пойму, что именно надо ТСу:
neiron2 Необходимо получить дeнь в который рaзница температур дoстигaет мaксимaльного знaчения для кaждой стaнции.neiron2Но результат должен быть равен количеству станций , в данном случае 2, со строчками, которые выделены красным, т.е. именно в эти дни у первой и второй станции были максимальные колебания температур в течение дня.?
Первый вариант вообще какой-то странный... а если у разных станций максимальная разница будет наблюдаться в разные дни, какой брать? Или всё же нужно посчитать количество этих дней? Типа, если у первой и второй сегодня, а у третьей позавчера, то результат=2? В общем, задачу надо уточнять.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 09:15:22
    #38457369
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
tanglirПервый вариант вообще какой-то странный... а если у разных станций максимальная разница будет наблюдаться в разные дни, какой брать? Или всё же нужно посчитать количество этих дней? Типа, если у первой и второй сегодня, а у третьей позавчера, то результат=2? В общем, задачу надо уточнять.
Да, может быть такое, что максимальная разница температур у каждой из станций наблюдалась в один и тот же день, это и нужно указать, что

1ст01.01.20142ст01.01.20143ст01.01.2014
а, например по замерам у 4-ой станции, наибольшая разница была 31.12.2013

4ст 31.12.2013
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 10:49:04
    #38457471
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2,

т.е. вам нужен всё-таки список вида "станция, день с максимальной разницей на этой станции"? Тогда tanglirУ нас уже есть готовая сгруппированная таблица. Перенумеровать по группам (станциям) по убыванию температуры, потом посчитать кол-во строк вывести строки с номером 1, всё.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 10:51:37
    #38457473
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
Вроде, удалось обойтись без промежуточных таблиц, но есть вложенный подзапрос.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select stanc as 'Станция',
DATE_FORMAT(date,' %d.%m.%Y') as 'Дата',
t_div as 'Максимальная разница температуры' 
from(
     SELECT stanc, date, max(temp)-min(temp) as t_div
     FROM `weather`
     group by stanc, day(date)
     order by stanc, t_div DESC
)as t
group by stanc


Ну и результат:

Станция Дата Максимальная разница T105.11.201343208.11.201331

Все спасибо, лишний раз убеждаюсь, что утро вечера мудренее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 10:54:58
    #38457478
qwerty112
qwerty112
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2Вроде, удалось обойтись без промежуточных таблиц, но есть вложенный подзапрос.
это случайный результат !
его повтор - не гарантируется !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 10:58:16
    #38457483
qwerty112
qwerty112
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
qwerty112neiron2Вроде, удалось обойтись без промежуточных таблиц, но есть вложенный подзапрос.
это случайный результат !
его повтор - не гарантируется !
http://www.mysql.ru/docs/man/Group_by_functions.html В MySQL расширены возможности использования оператора GROUP BY. Теперь в выражениях SELECT можно использовать столбцы или вычисления, которые не присутствуют в части GROUP BY. Это справедливо для любой возможной величины для этой группы. Данная возможность позволяет повысить производительность за счет исключения сортировки и группирования ненужных величин. Например, в следующем запросе нет необходимости в группировке customer.name:
....
В ANSI SQL к предложению GROUP BY необходимо добавлять customer.name. В MySQL, если работа происходит не в режиме ANSI, это имя избыточно.

Не используйте данное свойство, если столбцы, пропущенные в части GROUP BY, не являются уникальными в данной группе! Возможны непредсказуемые результаты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 10:59:23
    #38457485
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
tanglirneiron2,
т.е. вам нужен всё-таки список вида "станция, день с максимальной разницей на этой станции"? Тогда tanglirУ нас уже есть готовая сгруппированная таблица. Перенумеровать по группам (станциям) по убыванию температуры, потом посчитать кол-во строк вывести строки с номером 1, всё.

Не успел, Ваш ответ прочитать, пока отравлял сообщение на форум.
Если мы сделаем по убыванию температуры не получим ли такой результат

Станция Дата Температура1 10.10.2013 301 11.10.2013 292 10.10.2013 281 12.10.2013 272 12.10.2013 26
И какой функцией потом можно будет вывести строки с номером?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 11:02:58
    #38457488
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
qwerty112neiron2Вроде, удалось обойтись без промежуточных таблиц, но есть вложенный подзапрос.
это случайный результат !
его повтор - не гарантируется !
Я тут хотел уже обрадоваться, а Вы меня снова огорчаете. Сейчас попробую добавить в таблицу еще пару контр примеров, чтобы убедиться, что результат не случайный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 11:18:44
    #38457527
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select stanc, date, t_div
from (
 select stanc, date, t_div
  ,if(stanc=@s, @n:=@n+1, @n:=1+least(0, @s:=stanc)) as rn
 from(
      SELECT stanc, date, max(temp)-min(temp) as t_div
      FROM `weather`
      group by stanc, date
 ) as t, (set @n:=1,@s:='')
 order by t.stanc,t.t_div
) t0 where rn=1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 11:24:47
    #38457546
qwerty112
qwerty112
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2,

лучше всего будет сделать через времянку,

если НЕ через времянку, то так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
select a.*
from

(SELECT stanc, 
        DATE, MAX( temper ) AS t_max, 
        MIN( temper ) AS t_min, 
        MAX( temper ) - MIN( temper ) AS t_div
FROM  `weather` 
GROUP BY stanc, DAY( DATE ) ) a

left join

(SELECT stanc, 
        DATE, MAX( temper ) AS t_max, 
        MIN( temper ) AS t_min, 
        MAX( temper ) - MIN( temper ) AS t_div
FROM  `weather` 
GROUP BY stanc, DAY( DATE ) ) b

on a.stanc=b.stanc and a.t_div>b.t_div

where b.stanc is null



---
нуу, и ещё есть "грязный хак", с которым группирующий подзапрос будет выполняться один раз,
но в итоге может получиться и хуже ...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select stanc, left(xz, 5) as t_div, ...
from

(select stanc, max(concat(right(concat('00000', t_div),5), DATE)) as xz
from

(SELECT stanc, 
        DATE, MAX( temper ) AS t_max, 
        MIN( temper ) AS t_min, 
        MAX( temper ) - MIN( temper ) AS t_div
FROM  `weather` 
GROUP BY stanc, DAY( DATE ) ) a 

GROUP BY stanc ) b
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 11:40:34
    #38457566
neiron2
neiron2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
tanglir
qwerty112

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

Всем +1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 12:24:01
    #38457658
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2у меня результат тоже не случайно получаетсяПонимаете, он у вас "не случайно получается" потому, что оптимизатор так написан. Или вообще, потому что звёзды сегодня так сложились. А в следующей версии воткнут туда какую-нибудь очередную фичу, он начнёт по-другому "унутре" выполнения запроса строки сортировать, (или просто звёзды завтра по=другому встанут) и весь ваш "не случайный" запрос накроется медным тазом.
С сортированными вьюшками в мсскл 2000 похожее дело было - если в определении вьюшки был ордербай, то и простой "селект * фром ордеред_вью" давал отсортированный результат даже без явного указания сортировки. А в 2003 ВНЕЗАПНО от этого отказались, причём, насколько я понял, по-тихому. Ох и много же кирпичей было тогда отложено по этому поводу, а всё потому что понадеялись на такубю вот "не случайную" фичу. Но она-то хоть задокументирована была, а вот то, что вы написали, вообще ни из одного мануала не следует!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 12:24:49
    #38457659
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
tanglirчто понадеялись*в смысле, запросокорябатели понадеялись
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 13:14:59
    #38457774
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
neiron2,

Запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT stanc, 
        DAY( DATE ) as DAY, 
        MAX( temper ) AS t_max, 
        MIN( temper ) AS t_min, 
        MAX( temper ) - MIN( temper ) AS t_div
FROM  `weather` 
GROUP BY stanc, DAY( DATE ) 
ORDER BY stanc, MAX( temper ) - MIN( temper ) DESC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2013, 13:58:51
    #38457869
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тёплая осень в Москве
MasterZiv, ему надо не все группы, а по одной записи из каждой группы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тёплая осень в Москве / 17 сообщений из 17, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 321ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1835765]:
 0ms
get settings:
 7ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 34ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 32ms
get tp. blocked users:
 1ms
others: 213ms
total:  321ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]