如何编写ActiveRecord查询，分组同一用户1小时内创建的Story？

嘿，这个需求我之前也碰到过，刚好可以给你几个实用的解决方案！核心思路就是按用户ID + 按小时截断的创建时间分组，然后从每个分组里取出你想要的那条Story（比如最新的或者最早的）。下面分几种场景给你具体的实现代码：

1. PostgreSQL专属：用DISTINCT ON快速实现 #

如果你用的是PostgreSQL，DISTINCT ON是最简洁高效的方式，它能直接保留每个分组的第一条记录：

# 每个用户每小时只显示最新的一条Story
Story.select('DISTINCT ON (user_id, date_trunc(\'hour\', created_at)) *')
     .order(:user_id, 'date_trunc(\'hour\', created_at)', created_at: :desc)

原理说明： #

• date_trunc('hour', created_at)会把created_at精确到小时（比如2024-05-20 14:35:22变成2024-05-20 14:00:00）
• DISTINCT ON会按照括号里的字段分组，只保留每个分组的第一条记录
• 最后的created_at: :desc确保每个分组里取的是最新的那条，要是想取最早的改成:asc就行

2. 跨数据库通用方案：分组取最新时间再关联 #

如果你的项目需要兼容多种数据库（比如MySQL、SQLite），可以先分组获取每个用户每小时的最新创建时间，再关联回Story表拿到完整记录：

针对不同数据库调整时间截断函数： #

• PostgreSQL: date_trunc('hour', created_at)
• MySQL: date_format(created_at, '%Y-%m-%d %H:00:00')
• SQLite: strftime('%Y-%m-%d %H:00:00', created_at)

通用代码示例（以PostgreSQL为例）： #

# 第一步：获取每个用户每小时的最新created_at
latest_stories_subquery = Story.group(:user_id, "date_trunc('hour', created_at)")
                               .select(:user_id, "MAX(created_at) AS latest_created_at")

# 第二步：关联回Story表，拿到完整的Story记录
Story.joins("INNER JOIN (#{latest_stories_subquery.to_sql}) latest 
             ON stories.user_id = latest.user_id 
             AND stories.created_at = latest.latest_created_at")

3. 窗口函数方案（支持PostgreSQL/MySQL 8+） #

如果你的数据库支持窗口函数，这种方法灵活性更高，能应对更复杂的排序需求：

# 给每个分组的Story加上排序序号，取序号为1的那条
Story.from(
  Story.select("*, ROW_NUMBER() OVER (
                  PARTITION BY user_id, date_trunc('hour', created_at) 
                  ORDER BY created_at DESC
                ) AS rn"),
  :stories
).where(rn: 1)

原理说明： #

• PARTITION BY按用户ID和小时分组
• ORDER BY created_at DESC让每个分组里最新的Story序号为1
• 最后筛选rn = 1就能得到每个用户每小时的最新Story

4. Rails 7+ 跨数据库适配：用Arel自动兼容不同数据库 #

如果你用的是Rails 7及以上版本，可以用Arel写一套适配所有数据库的代码，不用手动切换函数：

# 定义跨数据库的小时截断逻辑
hour_trunc = Arel::Nodes::NamedFunction.new(
  case ActiveRecord::Base.connection.adapter_name
  when 'PostgreSQL' then 'date_trunc'
  when 'MySQL' then 'date_format'
  when 'SQLite' then 'strftime'
  end,
  [
    case ActiveRecord::Base.connection.adapter_name
    when 'PostgreSQL' then Arel.sql("'hour'")
    when 'MySQL' then Arel.sql("'%Y-%m-%d %H:00:00'")
    when 'SQLite' then Arel.sql("'%Y-%m-%d %H:00:00'")
    end,
    Story.arel_table[:created_at]
  ]
)

# 获取每个用户每小时的最新created_at
latest_stories_subquery = Story.group(Story.arel_table[:user_id], hour_trunc)
                               .select(
                                 Story.arel_table[:user_id],
                                 Arel::Nodes::NamedFunction.new('MAX', [Story.arel_table[:created_at]]).as('latest_created_at')
                               )

# 关联查询拿到完整记录
Story.joins("INNER JOIN (#{latest_stories_subquery.to_sql}) latest 
             ON stories.user_id = latest.user_id 
             AND stories.created_at = latest.latest_created_at")

这样不管你切换到哪种数据库，代码都能自动适配，非常方便！

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