如何用ActiveRecord按条件批量获取含软删除判断的订阅量统计序列

我来帮你把循环查询改成高效的单次批量查询！之前每月单独查一次确实会浪费数据库资源，尤其是数据量大的时候，单次批量查询能大幅提升性能。

核心逻辑回顾 #

你要统计的是每个月底时仍处于活跃状态的订阅数：即订阅在该月底前创建，并且要么从未被软删除，要么软删除时间晚于该月底。

解决方案（分数据库类型） #

1. PostgreSQL 版本（推荐，因为有generate_series方便生成月份列表）

# 生成需要统计的13个月份（0到12个月前的月底）
months = (0..12).map { |i| i.months.ago.end_of_month }
month_starts = months.map(&:beginning_of_month)
month_min = month_starts.min
month_max = months.max

# 构造单次查询：先统计有订阅的月份，再左连接所有需要的月份确保无数据的月份返回0
stats = ActiveRecord::Base.connection.select_all(<<~SQL)
  SELECT 
    m.month::date as month_start,
    COALESCE(s.active_count, 0) as subscription_count
  FROM 
    generate_series('#{month_min.to_s(:db)}', '#{month_max.to_s(:db)}', '1 month'::interval) as m(month)
  LEFT JOIN (
    SELECT
      date_trunc('month', created_at) as month,
      COUNT(*) as active_count
    FROM subscriptions
    WHERE 
      created_at <= '#{month_max.to_s(:db)}'
      AND (soft_destroyed_at IS NULL OR soft_destroyed_at > '#{month_max.to_s(:db)}')
    GROUP BY date_trunc('month', created_at)
  ) s ON s.month = m.month
  ORDER BY m.month
SQL

# 转换成和你原来格式一致的Hash（键为月份起始日期，值为订阅数）
result = stats.each_with_object({}) do |row, hash|
  hash[Date.parse(row["month_start"])] = row["subscription_count"].to_i
end

2. MySQL 版本（用递归CTE生成月份列表）

MySQL没有generate_series，我们用递归CTE来生成需要的月份范围：

months = (0..12).map { |i| i.months.ago.end_of_month }
month_starts = months.map(&:beginning_of_month)
month_min = month_starts.min.to_s(:db)
month_max = months.max.to_s(:db)

stats = ActiveRecord::Base.connection.select_all(<<~SQL)
  WITH RECURSIVE month_list AS (
    SELECT STR_TO_DATE('#{month_min}', '%Y-%m-%d') as month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM month_list
    WHERE month_start < STR_TO_DATE('#{month_max}', '%Y-%m-%d')
  )
  SELECT
    m.month_start,
    COALESCE(COUNT(s.id), 0) as subscription_count
  FROM month_list m
  LEFT JOIN subscriptions s
    ON DATE_FORMAT(s.created_at, '%Y-%m-01') = m.month_start
    AND s.created_at <= LAST_DAY(m.month_start)
    AND (s.soft_destroyed_at IS NULL OR s.soft_destroyed_at > LAST_DAY(m.month_start))
  GROUP BY m.month_start
  ORDER BY m.month_start
SQL

result = stats.each_with_object({}) do |row, hash|
  hash[Date.parse(row["month_start"])] = row["subscription_count"].to_i
end

关键优化点 #

• 减少数据库往返：从13次查询变成1次，大幅降低网络开销和数据库连接压力
• 确保全月份覆盖：用LEFT JOIN和COALESCE保证即使某个月没有订阅，也会返回0，不会丢失月份数据
• 索引建议：给subscriptions表的created_at和soft_destroyed_at字段加联合索引，能让查询更快：

  # 生成索引迁移
  class AddIndexToSubscriptionsForStats < ActiveRecord::Migration[7.0]
    def change
      add_index :subscriptions, [:created_at, :soft_destroyed_at]
    end
  end
  

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