时间戳

首页 > 解决方案 > 如何在 Rails 中选择聚合子查询的平均值

ruby-on-rails - 如何在 Rails 中选择聚合子查询的平均值

问题描述

我有以下返回预期结果的数据和 SQL 查询。我现在正在尝试使用Groupdate（和ActiveMedian）在 Rails 中实现相同的目标。

用户身份 积分 created_at
1 5 2020-10-01
1 15 2020-11-01
1 20 2020-11-02
2 33 2020-11-01 
SELECT
  AVG(points) AS points,
  DATE_TRUNC('MONTH', created_at) AS period
FROM
  (
    SELECT
      SUM(points) AS points,
      DATE_TRUNC('MONTH', created_at) AS created_at
    FROM
      user_points
    WHERE
      created_at IS NOT NULL
    GROUP BY
      user_id,
      DATE_TRUNC('MONTH', created_at)
  ) points_per_user_per_period
GROUP BY
  DATE_TRUNC('MONTH', created_at)

我预计

积分 时期
5 2020-10-01
34 2020-11-01

结果

UserPoints.from(
  UserPoints.group(:user_id).group_by_month(:created_at).sum(:points)
).group_by_month(:created_at).average(:points)

但由于.sum(:points)立即执行它不起作用。我可以用另一种方式制定查询还是sum不立即执行？还有其他想法吗？

总结结果应该是什么；用户在一段时间内获得的平均积分数。

标签： ruby-on-railspostgresqlactiverecord

解决方案

如果我们删除 Rails 的一个层，并使用支持 Active Record 的关系代数 (Arel)，这是可能的。

在这种方法中，我们将date_trunc自己教 Arel 函数*，然后为内部求和构建一个嵌套聚合查询，该查询不会立即执行，而是合并到外部平均聚合中：

class UserPoints
  def self.averages
    period = Arel::Nodes::NamedFunction.new('date_trunc', [
      Arel::Nodes::Quoted.new('month'),
      arel_table[:created_at]
    ])
    points = arel_table[:points].sum
    
    # The per-user aggregate sum subquery, as an abstract relational structure
    subquery = select(points.as("points"), period.as("period")).group(:user_id, :period)

    # Execute
    from(subquery, quoted_table_name).group(:period).average(:points)
  end
end

这种方法是通用的。适应范围关系的组合；例如，如果你想写，那么在最后一行UserPoints.where(created_at: Time.current.all_year).averages插入一个适当的，变成：unscope

from(subquery, quoted_table_name).unscope(:where).group(:period).average(:points)

同样，要与 Groupdate 库结合使用，至少对于外部查询*，请尝试：

from(subquery, quoted_table_name).group_by_month(:period).average(:points)

甚至可能有机会scope通过省略最终的聚合表达式将其重构为声明，从而获得使用其他表达式的灵活性。

现在需要注意的是：Arel 是一个 Rails 内部 API，这意味着如果你想要文档，你需要阅读它的源代码，即使在次要版本中也可能会有重大更改。这实际上很少见，如果您的代码佩戴了适当的安全装备，则可以使用 Arel（很多人都这样做），这当然是一个合适的测试用例。

* 我没有将 Groupdate gem 用于内部聚合查询，因为它缺少命名结果列的方法。

推荐阅读