Блог

РЕДАКТИРОВАТЬ: я переработал вопрос и пример, поскольку предыдущий пример включал только один город.

Я ищу, чтобы найти одну строку для каждого города с наивысшим значением max_wind ИЛИ max_gust.

Вот базовый запрос (http://sqlfiddle.com /#!9/42f63e/2):

         SELECT *
          FROM high_wind 
         WHERE timestamp > NOW() - INTERVAL 72 HOUR;                                                                              
 ------ ----------- ---------- ---------- --------------------- 
| hid  | city      | max_wind | max_gust | timestamp           |
 ------ ----------- ---------- ---------- --------------------- 
| 4784 | Vancouver | 63       | 90       | 2021-01-13 08:26:00 |
| 4785 | Vancouver | 54       | 71       | 2021-01-13 08:40:00 |
| 4786 | Calgary   | 52       | 71       | 2021-01-13 15:35:00 |
| 4787 | Calgary   | 57       | 79       | 2021-01-13 17:53:00 |
| 4788 | Calgary   | 67       | 86       | 2021-01-13 18:14:00 |
| 4789 | Calgary   | 70       | 84       | 2021-01-13 18:40:00 |
| 4790 | Calgary   | 51       | 86       | 2021-01-13 19:00:00 |
| 4791 | Calgary   | 55       | 75       | 2021-01-13 20:00:00 |
| 4792 | Saskatoon | 66       | 81       | 2021-01-14 00:18:00 |
| 4793 | Saskatoon | 53       | 73       | 2021-01-14 01:26:00 |
| 4794 | Saskatoon | 59       | 77       | 2021-01-14 01:44:00 |
| 4795 | Saskatoon | 72       | 91       | 2021-01-14 02:00:00 |
| 4796 | Saskatoon | 77       | 103      | 2021-01-14 03:00:00 |
| 4797 | Saskatoon | 52       | 65       | 2021-01-14 05:00:00 |
| 4798 | Saskatoon | 57       | 68       | 2021-01-14 06:13:00 |
| 4799 | Saskatoon | 50       | 64       | 2021-01-14 09:00:00 |
| 4800 | Saskatoon | 51       | 61       | 2021-01-14 15:00:00 |
| 4801 | Resolute  | 51       |          | 2021-01-15 04:00:00 |
 ------ ----------- ---------- ---------- --------------------- 
18 rows in set (0.004 sec)
 

Из приведенного выше примера данных это будет конечный предполагаемый результат:

  ------ ----------- ---------- ---------- --------------------- 
| hid  | city      | max_wind | max_gust | timestamp           |
 ------ ----------- ---------- ---------- --------------------- 
| 4784 | Vancouver | 63       | 90       | 2021-01-13 08:26:00 |
| 4788 | Calgary   | 67       | 86       | 2021-01-13 18:14:00 |
| 4796 | Saskatoon | 77       | 103      | 2021-01-14 03:00:00 |
| 4801 | Resolute  | 51       |          | 2021-01-15 04:00:00 |
 ------ ----------- ---------- ---------- --------------------- 
 

Я думал, что нашел решение со следующим запросом ВНУТРЕННЕГО СОЕДИНЕНИЯ, однако результаты неоднозначны. Здесь он правильно выдал два результата, но пропустил два других.

         SELECT *
          FROM high_wind a 
          JOIN 
             ( SELECT city
                    , MAX(max_wind) max_wind
                   , MAX(max_gust) max_gust 
              FROM high_wind
             WHERE timestamp >= NOW() - INTERVAL 72 HOUR 
             GROUP 
                BY city
             ) b 
            ON a.city = b.city 
           AND a.max_wind = b.max_wind 
           AND a.max_gust = b.max_gust 
         WHERE timestamp >= NOW() - INTERVAL 72 HOUR;          
                    
 ------ ----------- ---------- ---------- --------------------- 
| hid  | city      | max_wind | max_gust | timestamp           |
 ------ ----------- ---------- ---------- --------------------- 
| 4784 | Vancouver | 63       | 90       | 2021-01-13 08:26:00 |
| 4801 | Resolute  | 51       |          | 2021-01-15 04:00:00 |
 ------ ----------- ---------- ---------- --------------------- 
2 rows in set (0.006 sec)
 

 SELECT hw.*
    FROM (
        SELECT MAX(wind) AS max_wind,
               MAX(gust) AS max_gust
            FROM high_wind
            WHERE ...
         ) AS mx
    JOIN high_wind AS hw  ON (   hw.wind = mx.max_wind
                              OR hw.gust = mx.max_gust )
    WHERE ...
    ORDER BY ...
 

Вы можете получить 1 строку, если максимальные значения относятся к одному и тому же городу и времени.

Возможно, у вас больше 2 строк, если для MAX (..) одинаковые значения для разных городов или времени.

Если у вас миллион строк в high_wind, мы можем обсудить индексы, чтобы повысить производительность. Для моего запроса потребуется 2 полных сканирования таблицы, если вы не добавите INDEX(wind) и INDEX(gust) . Но если вам нужно что-то в WHERE (например, ограничение по провинции или диапазон дат), этих индексов будет недостаточно.

Для простого WHERE timestamp > ... добавления

 INDEX(timestamp)
INDEX(wind, timestamp)
INDEX(gust, timestamp)
 

Еще

Не использовать VARCHAR для числовых (или дат) значений:

 `max_wind` varchar(20) DEFAULT NULL,
 

При этом «102» меньше, чем «99»!