sql - 如何通过最大值知道其他列的值(PostgreSQL)？

Question

我在 PostgreSQL 10 数据库中有表。

该表显示了英国各城市地铁站的列车每小时速度数据。

| 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        |

MAX_SPEED_STATION 列有问题。换句话说，我需要在结果中查看特定时间火车速度的最大值在城市的哪个车站。

我当前的 SQL 语句:

SELECT
    A."Stamper" AS DATE_TIME,
    A."CITY" AS city,
    AVG(A.speed) AS AVG_SPEED,
    MAX(A.speed) AS MAX_SPEED,
    MIN(A.speed) AS MIN_SPEED
FROM
    table_name A
GROUP BY
    A."Stamper",
    A."CITY";

Answer 1

一种方法是使用子查询。另一个使用 array_agg():

SELECT A."Stamper" AS DATE_TIME, A."CITY" AS city,
       AVG(A.speed) AS AVG_SPEED,
       MAX(A.speed) AS MAX_SPEED,
       MIN(A.speed) AS MIN_SPEED,
       ( ARRAY_AGG(A.station ORDER BY A.speed DESC) )[1] as station_at_max_speed
FROM table_name A
GROUP BY A."Stamper", A."CITY";