Как реализовать такой SQL-запрос по описанию?

Question

[eldar_web]

Это не задача школьная или какая-нибудь. Это реальная задача, которую я хочу понять.
Есть таблица с погодными параметрами:

CREATE TABLE `weather` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `where_id` int(11) NOT NULL default '0',     # где снимались показания (идентификатор станции)
      `temperature` smallint(4) UNSIGNED NOT NULL default '0',  # температура воздуха в Кельвинах
      `precipitation` ENUM('none', 'rain', 'rain_snow', 'snowfall','hail') NOT NULL default 'none',   # осадки 
      `wind` smallint(4) UNSIGNED NOT NULL default '0',   # сила ветра в метрах в секунду
      `date` datetime NOT NULL default '0000-00-00 00:00:00', # точное время когда снималось показание
       PRIMARY KEY(`id`)
  ) ENGINE=MyISAM;

1) Получить день, неделю и месяц, в которых разница температур достигает максимального значения (для каждой станции).
2) Получить день, неделю и месяц, в которых разница температур выше 10.
3) Расчитать среднегодовое число дней с гололедицей.
4) Оптимизировать структуру таблицы под получившиеся запросы.

Как все это решается?

Comments

[Евгений]

разница температур это между чем и чем?

Answer 1

[Дмитрий Спирин]

Судя по запросу у вас MySQL. Будь у меня Postgres, я бы воспользовался, например, возможностями CTE. В Мускуле такого нет, кажется, есть какие-то воркэраунды.

Но можно банально и примитивно. Например, ответ на первый вопрос:

SELECT * 
FROM (
	SELECT min(temperature) as min, max(temperature) as max, where_id, DATE_FORMAT(date,'%Y-%m-%d') as date from weather GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
) AS data
ORDER BY max - min DESC 
LIMIT 1;

В результате увидим дату, место и минимальную и максимальную температуру в этой точке за данный день.

P.S.
Можно и в один запрос:

SELECT min(temperature) as minn, max(temperature) as maxx, where_id, DATE_FORMAT(date,'%Y-%m-%d') as date from weather GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
ORDER BY max(temperature) - min(temperature) DESC 
LIMIT 1;

P.P.S.
Полный ответ на вопрос 1:

SELECT 
  where_id, 
  (
    SELECT max(temperature) - min(temperature)
    FROM weather
    WHERE where_id = w1.where_id
    GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
    ORDER BY max(temperature) - min(temperature) DESC
    LIMIT 1
  ) as diff,
  DATE_FORMAT(date,'%Y-%m-%d') as date
FROM weather as w1
WHERE id = (
  SELECT MAX(id)
  FROM weather
  WHERE where_id = w1.where_id
  GROUP BY where_id, DATE_FORMAT(date,'%Y-%m-%d')
  ORDER BY max(temperature) - min(temperature) DESC
  LIMIT 1
)

Comments

[eldar_web]

Но там одну станцию (where_id) выводит, а не для каждой станции max(temperature).

[Дмитрий Спирин]

eldar_web: посмотрите внимательно, я группирую по where_id и дате... Вся суть в правильном GROUP BY.

[eldar_web]

Пускай будет так: SELECT min(temperature) as minn, max(temperature) as maxx, where_id, DATE_FORMAT(date,'%Y-%m-%d') as date from weather , и так выводит одну станцию, а у меня в БД 3 станции (where_id).

[Дмитрий Спирин]

eldar_web: и это правильно, потому что используются агрегативные функции min и max, которые автоматом включают у запроса группировку...

[Дмитрий Спирин]

eldar_web: в этом случае группировка неявно используется по тем полям, что мы выбираем, т.е. по тем же самым датам и where_id

[eldar_web]

Ясно, но я должен же сделать по заданию - выводит max у каждой станции, то есть, таким образом:
where_id | max
1 | 9
2 | 7
3 | 11

[Дмитрий Спирин]

eldar_web: дописал в ответ P.P.S.

Answer 2

[Дмитрий Миронов]

Можно так еще (ответ на 1 вопрос). Только пришлось убрать UNSIGNED

Comments

[Дмитрий Спирин]

Ммм, спасибо за наводку на ресурс sqlfiddle.com, не знал. =)

[eldar_web]

А можете сказать что означает:
4) Оптимизировать структуру таблицы под получившиеся запросы. ?

[Дмитрий Миронов]

eldar_web: Каак минимум добавить индексы, что бы запросы бытрее выполнялись. Но для этого нужно много знать: какие данные, сколько, какие запросы идут к таблице. Дальше каждый запрос смотреть с помощью EXPLAIN и смотреть как можно оптимизировать табличку :-). Сразу говорю, что ответа на тостере тут не хватит.