Как определить значение поле по максимальному значению другого поле (PostgreSQL)?

Question

[nurzhannogerbek]

Есть база данных PostgreSQL.
В ней есть таблица. В таблице показаны ежечасные данные скорости поездов в каждой станции метро городов Англии.

| STAMPER             | STATION    | CITY      | SPEED    |
|---------------------------------------------------------|
| 2018-10-01 00:00:00 | Arsenal    | London    | 1078.125 |      
| 2018-10-01 01:00:00 | Arsenal    | London    | 1524.375 | 
| 2018-10-01 02:00:00 | Arsenal    | London    | 1127.752 |
| 2018-10-01 00:00:00 | Beckton    | London    | 2866.375 |
| 2018-10-01 01:00:00 | Beckton    | London    | 877.222  |
| 2018-10-01 02:00:00 | Beckton    | London    | 1618.533 |
| 2018-10-01 00:00:00 | Chesham    | Liverpool | 1567.588 |
| 2018-10-01 01:00:00 | Chesham    | Liverpool | 792.333  |
| 2018-10-01 02:00:00 | Chesham    | Liverpool | 1138.857 |
| 2018-10-01 00:00:00 | Farringdon | Liverpool | 1543.625 |
| 2018-10-01 01:00:00 | Farringdon | Liverpool | 538.666  |
| 2018-10-01 02:00:00 | Farringdon | Liverpool | 1587.583 |

Я пытаюсь получить агрегированные данные подобного рода:

| STAMPER             | CITY      | AVG_SPEED  | MAX_SPEED | MAX_SPEED_STATION |
|----------------------------------------------|-----------|-------------------|
| 2018-10-01 00:00:00 | London    | XXX        | 2866.375  | Beckton           |
| 2018-10-01 01:00:00 | London    | XXX        | 1524.375  | Arsenal           |
| 2018-10-01 02:00:00 | London    | XXX        | 1618.533  | Beckton           |
| 2018-10-01 00:00:00 | Liverpool | XXX        | 1567.588  | Chesham           |
| 2018-10-01 01:00:00 | Liverpool | XXX        | 792.333   | Chesham           |
| 2018-10-01 02:00:00 | Liverpool | XXX        | 1587.583  | Farringdon        |

Другими словами, здесь я пытаюсь выявить среднее, максимальное значение скорости поездов в городах за определенный час. Мне это удалось, но не пойму как узнать станцию, где было зафиксировано максимальное значение скорости. К примеру, в "2018-10-01 00:00:00" максимальная скорость поезда в городе London зафиксирована в станции Beckton. Помогите пожалуйста разобраться.

Мой текущий SQL запрос:

SELECT
	A."STAMPER",
	A."CITY" as city,
	AVG(A.SPEED) AS AVG_SPEED,
	MAX(A.SPEED) AS MAX_SPEED
FROM
	table_name A
GROUP BY
	A."STAMPER",
	A."CITY";

Comments

[freeExec]

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

[nurzhannogerbek]

freeExec, здравствуйте! Могли бы вы пожалуйста привести пример в виде SQL кода?

Answer 1

[Вячеслав Успенский]

Все зависит от ваших задач.

Вариант номер раз

SELECT a.stamper,
       a.city,
       AVG(a.speed) AS AVG_SPEED,
       MAX(a.speed) AS MAX_SPEED,
       (select b.station 
        from data as b 
        where b.city=a.city and
              b.speed=max(a.speed) 
        limit 1)  as max_speed_station
FROM  data a
GROUP BY  a.stamper,
          a.city;

Вариант два
(обратите внимание, при таком типе джоина будет более одной строки после объединения если строки с подходящими скоростями в пределах города)

select a.*,
       b.station as max_speed_station
from (SELECT stamper,
             city,
             AVG(speed) AS AVG_SPEED,
             MAX(speed) AS MAX_SPEED
      FROM data
      GROUP BY stamper,
               city) as a
left join data as b
  on a.city=b.city and
     a.MAX_SPEED=b.speed;

Answer 2

[Константин Цветков]

Свяжите запрос (как подзапрос) с той же таблицей по полям CITY и SPEED.

Comments

[nurzhannogerbek]

Здравствуйте, Константин! Могли бы вы пожалуйста привести пример в виде SQL кода?

[Константин Цветков]

NogerbekNurzhan, Вариант 2 от Вячеслав Успенский.