Postgresql获取每天对应时间的最大值

Question

我有以下表格:

Date      | Time     | Value  | ReceivedTime
2022-04-01| 00:59:59 | 5      | 00:30:15
2022-04-01| 13:59:59 | 15     | 13:30:00
2022-04-02| 21:59:59 | 5      | 21:30:15
2022-04-02| 22:59:59 | 25     | 22:25:15
2022-04-02| 23:59:59 | 25     | 23:00:15
2022-04-03| 14:59:59 | 50     | 00:30:15
2022-04-03| 15:59:59 | 555    | 00:30:15
2022-04-03| 16:59:59 | 56     | 00:30:15

我想获得最大值以及日期，ReceivedTime。

预期结果:

Date       | Value  | ReceivedTime
2022-04-01 | 15     | 13:30:00
2022-04-02 | 25     | 23:00:15
2022-04-03 | 555    | 00:30:15

Answer 1

这个答案假设，在给定的一天有两个或更多的记录为相同的最大值而被绑定的情况下，您希望保留具有最近的ReceivedTime的单个记录。我们可以在这里使用DISTINCT ON:

SELECT DISTINCT ON (Date) Date, Value, ReceivedTime
FROM yourTable
ORDER BY Date, Value DESC, ReceivedTime DESC;