时间戳

首页 > 解决方案 > 如何通过最大值知道其他列的值（PostgreSQL）？

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

问题描述

我在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";

标签： sqlpostgresqlpostgresql-10

解决方案

一种方法是使用子查询。另一个用途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";

推荐阅读