如何获取每日最高value对应行的id、timestamp及value字段？

方法一：窗口函数（推荐） #

用ROW_NUMBER()窗口函数按日期分组，每组内按value降序排序，取排序第一的行：

SELECT id, timestamp, value
FROM (
    SELECT 
        id, 
        timestamp, 
        value,
        ROW_NUMBER() OVER (PARTITION BY timestamp::date ORDER BY value DESC) AS rn
    FROM yourtable
) t
WHERE rn = 1
ORDER BY timestamp::date;

如果同一天存在多行value同为最大值的情况，想保留所有这些行，可以把ROW_NUMBER()换成RANK()。

方法二：子查询关联 #

先通过子查询算出每日最大value，再关联主表匹配日期和value：

SELECT t.id, t.timestamp, t.value
FROM yourtable t
JOIN (
    SELECT timestamp::date AS day, MAX(value) AS max_value
    FROM yourtable
    GROUP BY day
) m ON t.timestamp::date = m.day AND t.value = m.max_value
ORDER BY m.day;

内容的提问来源于stack exchange，提问作者montes