Тёплая осень в Москве / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тёплая осень в Москве

17 сообщений из 17, страница 1 из 1

Тёплая осень в Москве

#38457070

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

07.11.2013, 23:11:29

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457261

javajdbc

Участник

Откуда: Montreal

Сообщения: 18 207

Рейтинг: 0 / 0

neiron2,

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

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

Есть способ "паровозиком" за один раз, но он
может быть оооочень громоздким....

...

Рейтинг:

0 / 0

08.11.2013, 03:51:31

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457286

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

javajdbcПохоже что промежуточная таблица таки нужна
или придется два раза ее получать для само-соденинения.А откуда "2 раза"? У нас уже есть готовая сгруппированная таблица. Перенумеровать по группам, потом посчитать кол-во строк с номером 1, всё.
Хотя... я что-то не пойму, что именно надо ТСу:
neiron2 Необходимо получить дeнь в который рaзница температур дoстигaет мaксимaльного знaчения для кaждой стaнции.neiron2Но результат должен быть равен количеству станций , в данном случае 2, со строчками, которые выделены красным, т.е. именно в эти дни у первой и второй станции были максимальные колебания температур в течение дня.?
Первый вариант вообще какой-то странный... а если у разных станций максимальная разница будет наблюдаться в разные дни, какой брать? Или всё же нужно посчитать количество этих дней? Типа, если у первой и второй сегодня, а у третьей позавчера, то результат=2? В общем, задачу надо уточнять.

...

Рейтинг:

0 / 0

08.11.2013, 05:13:10

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457369

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, 09:15:22

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457471

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

neiron2,

т.е. вам нужен всё-таки список вида "станция, день с максимальной разницей на этой станции"? Тогда tanglirУ нас уже есть готовая сгруппированная таблица. Перенумеровать по группам (станциям) по убыванию температуры, потом посчитать кол-во строк вывести строки с номером 1, всё.

...

Рейтинг:

0 / 0

08.11.2013, 10:49:04

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457473

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:51:37

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457478

qwerty112

Гость

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

...

Рейтинг:

0 / 0

08.11.2013, 10:54:58

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457483

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:58:16

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457485

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, 10:59:23

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457488

neiron2

Гость

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

...

Рейтинг:

0 / 0

08.11.2013, 11:02:58

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457527

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

Код: 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:18:44

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457546

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:24:47

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457566

neiron2

Гость

tanglir
qwerty112

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

Всем +1

...

Рейтинг:

0 / 0

08.11.2013, 11:40:34

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457658

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

neiron2у меня результат тоже не случайно получаетсяПонимаете, он у вас "не случайно получается" потому, что оптимизатор так написан. Или вообще, потому что звёзды сегодня так сложились. А в следующей версии воткнут туда какую-нибудь очередную фичу, он начнёт по-другому "унутре" выполнения запроса строки сортировать, (или просто звёзды завтра по=другому встанут) и весь ваш "не случайный" запрос накроется медным тазом.
С сортированными вьюшками в мсскл 2000 похожее дело было - если в определении вьюшки был ордербай, то и простой "селект * фром ордеред_вью" давал отсортированный результат даже без явного указания сортировки. А в 2003 ВНЕЗАПНО от этого отказались, причём, насколько я понял, по-тихому. Ох и много же кирпичей было тогда отложено по этому поводу, а всё потому что понадеялись на такубю вот "не случайную" фичу. Но она-то хоть задокументирована была, а вот то, что вы написали, вообще ни из одного мануала не следует!

...

Рейтинг:

0 / 0

08.11.2013, 12:24:01

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457659

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

tanglirчто понадеялись*в смысле, запросокорябатели понадеялись

...

Рейтинг:

0 / 0

08.11.2013, 12:24:49

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457774

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

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:14:59

| Ответить | Цитировать | Написать

Тёплая осень в Москве

#38457869

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

MasterZiv, ему надо не все группы, а по одной записи из каждой группы.

...

Рейтинг:

0 / 0

08.11.2013, 13:58:51

| Ответить | Цитировать | Написать

17 сообщений из 17, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тёплая осень в Москве

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&msg=38457286&tid=1835765]:	0ms
get settings:	8ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	46ms
get topic data:	11ms
get forum data:	2ms
get page messages:	57ms
get tp. blocked users:	2ms
others:	222ms

total:	368ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы