使用DISTINCT ON处理timestamp列时，如何获取每日最新value值？

你的问题出在DISTINCT ON的排序逻辑上——当你只按business_time::date desc排序时，PostgreSQL在每个日期分组里只会随机挑一行返回，因为同一日期内的行没有明确的排序规则。

要获取每天最新的value，你需要在排序时，先按日期分组，再在每个组内按business_time（或insert_time）降序排列，这样DISTINCT ON就会选中每个日期里时间最晚的那一行。

修正后的查询如下：

select distinct on (business_time::date) 
       business_time::date as business_date, 
       value
from home.assets
where name = 'USD_RLS'
order by business_time::date desc, business_time desc;

补充说明： #

• DISTINCT ON (column) 会保留每个分组中ORDER BY子句里排在最前面的记录
• 新增的business_time desc确保同一日期内，取business_time最晚的那条数据
• 如果存在同一天多条记录business_time完全相同的情况，可以改用insert_time desc排序，因为insert_time默认是当前时间，能保证最新插入的那条被选中：

select distinct on (business_time::date) 
       business_time::date as business_date, 
       value
from home.assets
where name = 'USD_RLS'
order by business_time::date desc, insert_time desc;

另一种方案：使用窗口函数 #

如果你觉得DISTINCT ON的语法不够直观，也可以用ROW_NUMBER()窗口函数实现：

select business_date, value
from (
    select 
        business_time::date as business_date,
        value,
        row_number() over (partition by business_time::date order by business_time desc) as rn
    from home.assets
    where name = 'USD_RLS'
) t
where rn = 1
order by business_date desc;

这个子查询里，partition by business_time::date按日期分组，order by business_time desc给每个组内的记录按时间降序编号，取编号为1的就是每天最新的那条数据。

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