Rails按分组对象字段过滤 分组查询替代N+1匹配支付记录

需求规则 #

针对给定的主支付记录集合，为每条主记录匹配同表内符合以下规则的相似支付记录：

• 相似记录created_at早于对应主支付记录的创建时间
• 相似记录与主支付记录的创建时间间隔不超过6个月
• 结果排除主支付记录本身，超出对应6个月窗口的记录不纳入结果

原有代码问题 #

当前编写的查询逻辑存在本质错误，无法实现预期效果：

payments_to_group_by = payments # 传入的主支付记录集合
Payment
  .where('created_at >= ? AND created_at <= ?', 
    6.months.ago, payments_to_group_by.map(&:created_at)
  )
  .where.not(id: payments_to_group_by.map(&:id))
  .group(payments_to_group_by.map(&:id))

• 写死的6.months.ago是基于当前时间计算的全局值，不是基于每条主记录创建时间计算的动态窗口，会导致较早的主记录漏匹配数据
• group方法传入主记录ID列表没有实际意义，SQL分组只能基于表中实际存在的字段做聚合，没有建立相似记录和主记录的关联关系，根本无法按主记录维度聚合结果

正确实现方案 #

这个场景需要用表自关联实现单条SQL查询，把主记录集合作为驱动表，给每条主记录绑定独立的时间匹配窗口，一次查询拉取所有结果：

PostgreSQL 版本 #

main_payment_ids = payments_to_group_by.map(&:id)
# 关联主记录基础数据，避免重复传参
main_payments = Payment.where(id: main_payment_ids).select(:id, :created_at)

# 单条SQL完成所有匹配+聚合
similar_payments_map = main_payments
  .joins(
    "INNER JOIN payments AS similar_payments
     ON similar_payments.created_at < payments.created_at
     AND similar_payments.created_at >= payments.created_at - INTERVAL '6 months'
     AND similar_payments.id != payments.id"
  )
  .group("payments.id")
  .select("payments.id AS main_payment_id, ARRAY_AGG(similar_payments.*) AS similar_records")
  .each_with_object({}) do |row, result_map|
    result_map[row.main_payment_id] = row.similar_records
  end

返回的similar_payments_map是主支付记录ID => 对应相似支付记录数组的哈希结构，后续直接按主记录ID取值即可。

MySQL 兼容版本 #

MySQL不支持ARRAY_AGG函数，可以查询明细后在Ruby层做分组聚合，依然是单条SQL无N+1问题：

main_payment_ids = payments_to_group_by.map(&:id)
main_payments = Payment.where(id: main_payment_ids).select(:id, :created_at)

raw_matched_records = main_payments
  .joins(
    "INNER JOIN payments AS similar_payments
     ON similar_payments.created_at < payments.created_at
     AND similar_payments.created_at >= DATE_SUB(payments.created_at, INTERVAL 6 MONTH)
     AND similar_payments.id != payments.id"
  )
  .select("payments.id AS main_payment_id, similar_payments.*")

similar_payments_map = raw_matched_records
  .group_by(&:main_payment_id)
  .transform_values do |records|
    records.map { |r| Payment.new(r.attributes.except("main_payment_id")) }
  end

优化提示 #

如果payments_to_group_by是已经加载到内存的ActiveRecord对象集合，不需要额外查一次主记录，可以直接在Ruby层提取id和创建时间拼成CTE，进一步减少查询开销。如果支付表数据量较大，记得给created_at字段加索引，能大幅提升自关联的查询速度。

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