基于PostgreSQL的用户月度目标与实际数据高效查询方案求助

模型结构 #

# user.rb
class User < ApplicationRecord
  has_many :enquiries
  has_many :sales_projections
end

# enquiry.rb
class Enquiry < ApplicationRecord
  belongs_to :user
  # 字段: actual_target_count, actual_visit_count
  # 存在business、formal等不同类型
end

# sales_projection.rb
class SalesProjection < ApplicationRecord
  belongs_to :user
  belongs_to :enquiry
  # 字段: target_month, desired_target_count, desired_visit_count
end

问题背景 #

现有500+用户提交了2023年7月的目标，enquiries表有6万条记录，需要获取指定月份所有用户的目标值与实际值对比数据，期望输出格式：

user   |   desired target  | actual target | desired visit | actual visit | target_month

当前使用的ActiveRecord伪代码因多次关联查询和Ruby层面遍历，执行耗时超1分钟：

# 低效伪代码
SalesProjection.where(target_month: "July 2023")
  .order(target_month: :asc)
  .group_by { |m| m.user.id }
  .map do |key, value|
    {
      user: User.find(key).full_name,
      desired_target: ..., # 多次join计算
      actual_target: ...,  # 多次join计算
      desired_visit: ...,
      actual_visit: ...,
      target_month: "July 2023"
    }
  end

解决方案 #

1. PostgreSQL CTE 查询语句 #

通过CTE预计算用户的实际值和目标值，避免多次关联查询：

WITH user_actuals AS (
  SELECT
    u.id AS user_id,
    u.full_name AS user,
    SUM(e.actual_target_count) AS actual_target,
    SUM(e.actual_visit_count) AS actual_visit
  FROM users u
  JOIN enquiries e ON u.id = e.user_id
  GROUP BY u.id, u.full_name
),
user_targets AS (
  SELECT
    sp.user_id,
    SUM(sp.desired_target_count) AS desired_target,
    SUM(sp.desired_visit_count) AS desired_visit,
    sp.target_month
  FROM sales_projections sp
  WHERE sp.target_month = 'July 2023'
  GROUP BY sp.user_id, sp.target_month
)
SELECT
  ua.user,
  COALESCE(ut.desired_target, 0) AS desired_target,
  COALESCE(ua.actual_target, 0) AS actual_target,
  COALESCE(ut.desired_visit, 0) AS desired_visit,
  COALESCE(ua.actual_visit, 0) AS actual_visit,
  ut.target_month
FROM user_targets ut
LEFT JOIN user_actuals ua ON ut.user_id = ua.user_id
ORDER BY ut.target_month ASC;

说明：

• user_actuals CTE：预计算每个用户所有enquiry的实际目标和访问总数
• user_targets CTE：预计算指定月份每个用户的期望目标和访问总数
• 用LEFT JOIN关联两个CTE，COALESCE处理空值，确保无实际数据的用户显示0而非NULL

2. ActiveRecord 动态查询语句 #

将CTE逻辑转化为ActiveRecord代码，一次查询完成所有计算：

target_month = "July 2023"

# 预计算用户实际数据的子查询
user_actuals = Enquiry.select(
  "users.id AS user_id",
  "users.full_name AS user",
  "SUM(enquiries.actual_target_count) AS actual_target",
  "SUM(enquiries.actual_visit_count) AS actual_visit"
).joins(:user).group("users.id, users.full_name")

# 预计算用户目标数据的查询
user_targets = SalesProjection.select(
  "user_id",
  "SUM(desired_target_count) AS desired_target",
  "SUM(desired_visit_count) AS desired_visit",
  "target_month"
).where(target_month: target_month).group("user_id, target_month")

# 关联子查询并获取最终结果
result = user_targets.joins(
  "LEFT JOIN (#{user_actuals.to_sql}) ua ON sales_projections.user_id = ua.user_id"
).select(
  "ua.user",
  "COALESCE(sales_projections.desired_target, 0) AS desired_target",
  "COALESCE(ua.actual_target, 0) AS actual_target",
  "COALESCE(sales_projections.desired_visit, 0) AS desired_visit",
  "COALESCE(ua.actual_visit, 0) AS actual_visit",
  "sales_projections.target_month"
).order("sales_projections.target_month ASC")

# 转换为目标格式的哈希数组
formatted_result = result.map do |row|
  {
    user: row.user,
    desired_target: row.desired_target,
    actual_target: row.actual_target,
    desired_visit: row.desired_visit,
    actual_visit: row.actual_visit,
    target_month: row.target_month
  }
end

说明：

• 用子查询替代CTE，贴合ActiveRecord写法
• 避免Ruby层面的group_by和N+1查询，所有计算在数据库层面完成
• COALESCE保证数值字段统一显示为0，避免NULL值

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