RAILS - 如何编写查询语句获取创建时间间隔1分钟以内的记录

核心实现思路 #

通常有两种常用方案：窗口函数（适配现代数据库，性能更优）、自连接（兼容低版本数据库）。如果是查询和某条指定记录间隔1分钟以内的记录，直接用范围查询即可。

方案1：窗口函数（推荐，适配PostgreSQL、MySQL 8+、SQLite 3.25+） #

通过LEAD/LAG窗口函数获取相邻记录的创建时间，筛选时间差小于60秒的条目即可，示例模型以Post为例：

# 第一步：给每条记录关联下一条记录的创建时间
posts_with_neighbor = Post.select(
  "posts.*",
  "LEAD(created_at) OVER (ORDER BY created_at) AS next_created_at"
)

# 第二步：筛选时间差小于60秒的记录
# 以下为PostgreSQL语法，MySQL可替换为 TIMESTAMPDIFF(SECOND, created_at, next_created_at) < 60
result = Post.from(posts_with_neighbor, :posts)
             .where("EXTRACT(EPOCH FROM (next_created_at - created_at)) < 60")

方案2：自连接（兼容低版本数据库） #

通过表自连接匹配所有创建时间差小于60秒的记录，注意去重避免重复返回：

# PostgreSQL语法示例
Post.joins("INNER JOIN posts p2 ON p2.id != posts.id")
    .where("ABS(EXTRACT(EPOCH FROM (posts.created_at - p2.created_at))) < 60")
    .distinct

特殊场景：查询和指定记录间隔1分钟以内的记录 #

如果有明确的基准记录，直接用时间范围查询即可，写法最简单性能最优：

# 假设基准记录为 target_post
target_time = target_post.created_at
related_posts = Post.where(created_at: (target_time - 60)..(target_time + 60))
                    .where.not(id: target_post.id)

注意事项 #

• 数据量较大时建议给created_at字段加索引，避免全表扫描拖慢查询性能
• 时间差计算语法需要和你使用的数据库类型匹配，以上示例默认适配PostgreSQL，可根据数据库类型调整对应函数

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