Задать вопрос
@nurzhannogerbek
sql

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

Есть база данных 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";
  • Вопрос задан
  • 299 просмотров
2 комментария
Подписаться 1 Простой 2 комментария
Пригласить эксперта
Ответы на вопрос 2
Kwisatz
@Kwisatz
Больше web-приложений, хороших и разных
Все зависит от ваших задач.

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

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;
Ответ написан
Комментировать
Комментировать
tsklab
@tsklab
Здесь отвечаю на вопросы.
Свяжите запрос (как подзапрос) с той же таблицей по полям CITY и SPEED.
Ответ написан
2 комментария
2 комментария
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Тестировщик SQL
Bell Integrator • Санкт-Петербург
До 200 000 ₽
Программист SQL
САМО-Софт • Москва
До 220 000 ₽
Разработчик БД SQL
FS Travel • Москва
от 170 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Настройка сервера
18 апр. 2024, в 21:56
2000 руб./за проект
Помощь с водпресс
18 апр. 2024, в 21:00
150 руб./за проект
Спарсить ссылки на все товары конкретного продавца в озон
18 апр. 2024, в 20:13
2000 руб./за проект
Ещё заказы