Rails7+PostgreSQL：如何获取每日最后一条Total记录？

现有代码问题 #

你的当前实现存在以下不足：

• 若同一天存在多条created_at完全一致的记录，会返回多条结果，不符合“每日最后一条”的需求
• 未集成时间范围筛选，无法直接适配近7天、1个月等统计场景
• 两次查询的方式效率偏低，且依赖created_at的唯一性（业务场景中无法完全保证）

最优实现（PostgreSQL窗口函数） #

利用PostgreSQL原生的ROW_NUMBER()窗口函数，可在单查询内完成按天分组、筛选当日最后一条记录的操作，同时支持时间范围过滤，全程避免Ruby循环，效率最优：

# 按需定义时间范围示例：
# 近7天：start_date = 7.days.ago.beginning_of_day
# 近1个月：start_date = 1.month.ago.beginning_of_day
start_date = 7.days.ago.beginning_of_day
end_date = Time.current.end_of_day

daily_last_totals = Total
  .where(account: current_account)
  .where(created_at: start_date..end_date)
  .select(
    'totals.*',
    "ROW_NUMBER() OVER (PARTITION BY date_trunc('day', created_at) ORDER BY created_at DESC) AS row_num"
  )
  .having('row_num = 1')
  .group('totals.id, date_trunc(\'day\', created_at), row_num')
  .order('date_trunc(\'day\', created_at) ASC')

代码说明 #

• 窗口函数逻辑：通过PARTITION BY date_trunc('day', created_at)按天分组，每组内按created_at倒序排序，给每条记录分配行号row_num
• 筛选规则：having('row_num = 1')保留每组的第一条记录，即当日最后一条Total
• 时间范围适配：通过where(created_at: start_date..end_date)快速切换近7天、1个月等统计周期
• UUID兼容：分组时包含totals.id（UUID主键），PostgreSQL可正确处理分组逻辑

简洁版写法（Rails 7+） #

如果追求代码可读性，可采用子查询方式：

start_date = 7.days.ago.beginning_of_day
end_date = Time.current.end_of_day

ranked_totals = Total
  .where(account: current_account)
  .where(created_at: start_date..end_date)
  .select(
    'totals.*',
    "ROW_NUMBER() OVER (PARTITION BY date_trunc('day', created_at) ORDER BY created_at DESC) AS row_num"
  )

daily_last_totals = Total.from("(#{ranked_totals.to_sql}) ranked").where(row_num: 1).order('created_at ASC')

方案优势 #

• 单查询完成所有逻辑，避免多次数据库交互
• 窗口函数为PostgreSQL原生优化功能，性能优于多查询或Ruby循环处理
• 数据库层面完成筛选，减少返回至应用层的数据量

内容的提问来源于stack exchange，提问作者Anton S.