如何在Rails中完成交易双统计并转为upsert_all适配格式？

问题背景 #

现有两张数据表：

# CategoryTransaction
category_id, transaction_id, buyer_id, seller_id

其中buyer_id和seller_id均关联Person表（同一Person可同时作为买家/卖家），需要统计[category_id, buyer_id]和[category_id, seller_id]的组合频次，存入CategoryPerson表：

# CategoryPerson
person_id, category_id, bought_count, sold_count

已完成的统计步骤：

# 1. 按品类分组收集交易记录
category_transactions = CategoryTransaction.all.select(:category_id, :buyer_id, :seller_id).group_by(&:category_id)
# 格式示例: { category_id: [CategoryTransaction, …], … }

# 2. 计算两类统计结果
tallies = category_transactions.collect{ |k,v| [k, v.collect(&:buyer_id).tally, v.collect(&:seller_id).tally] }
# 格式示例: [category_id, { buyer_id: count, buyer2_id: count, … }, { seller_id: count, … }]

需要将tallies转换为upsert_all可直接接收的格式：

[{ category_id: 1, person_id: 101, bought_count: 3, sold_count: 0 }, {…}, …]

转换实现 #

通过遍历tallies，合并每个品类下的买家/卖家统计，生成目标格式数组：

upsert_data = tallies.flat_map do |category_id, buyer_tally, seller_tally|
  # 收集当前品类下所有涉及的Person ID并去重
  all_person_ids = (buyer_tally.keys + seller_tally.keys).uniq

  all_person_ids.map do |person_id|
    {
      category_id: category_id,
      person_id: person_id,
      bought_count: buyer_tally[person_id] || 0,
      sold_count: seller_tally[person_id] || 0
    }
  end
end

代码说明 #

• flat_map遍历每个品类的统计数据，最终展开为一维数组
• 合并并去重买家/卖家ID，确保每个[category_id, person_id]组合仅出现一次
• 对每个Person ID，从统计结果中取对应次数，无记录则填0
• 生成的upsert_data可直接传入upsert_all：

CategoryPerson.upsert_all(upsert_data, unique_by: [:category_id, :person_id])

性能优化建议（大数据量场景） #

如果交易数据量较大，Ruby层处理会占用较多内存，推荐直接用SQL完成统计+插入，效率更高：

INSERT INTO category_people (person_id, category_id, bought_count, sold_count)
SELECT
  person_id,
  category_id,
  SUM(CASE WHEN role = 'buyer' THEN count ELSE 0 END) AS bought_count,
  SUM(CASE WHEN role = 'seller' THEN count ELSE 0 END) AS sold_count
FROM (
  SELECT buyer_id AS person_id, category_id, COUNT(*) AS count, 'buyer' AS role
  FROM category_transactions
  GROUP BY buyer_id, category_id
  UNION ALL
  SELECT seller_id AS person_id, category_id, COUNT(*) AS count, 'seller' AS role
  FROM category_transactions
  GROUP BY seller_id, category_id
) AS combined
GROUP BY person_id, category_id
ON CONFLICT (category_id, person_id) DO UPDATE SET
  bought_count = EXCLUDED.bought_count,
  sold_count = EXCLUDED.sold_count;

在Rails中可通过ActiveRecord::Base.connection.execute(sql)执行该SQL。

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