Rails Arel队列分析

Question

我正尝试在Rails中进行队列分析查询，但遇到了问题，无法正确地按上次操作日期进行分组。

我希望以以下数据行作为结束：http://www.quickcohort.com/

count first_action last_action

所有在去年注册的用户。first_action和last_action被截断为最近的月份。

获得按first_action分组的计数很简单，但是当我尝试将它扩展到包括我遇到的last_action时

ActiveRecord::StatementInvalid: PGError: ERROR:  aggregates not allowed in GROUP BY clause

这是我到目前为止所知道的

User
  .select("COUNT(*) AS count,
    date_trunc('month', users.created_at) AS first_action,
    MAX(date_trunc('month', visits.created_at)) AS last_action # <= Problem
  ")
  .joins(:visits)
  .group("first_action, last_action") # TODO: Subquery ?
  .order("first_action ASC, last_action ASC")
  .where("users.created_at >= date_trunc('month', CAST(? AS timestamp))", 12.months.ago)

访问表跟踪用户对站点的所有访问。使用最新的访问作为最后一个操作似乎很容易，但我在将其转换为SQL时遇到了麻烦。

如果有更好的方法，我也对其他解决方案持开放态度，但似乎单个SQL查询的性能最好。

Answer 1

我认为您需要在子查询中执行此操作。类似于：

select first_action, last_action, count(1)
from (
    select
        date_trunc('month', visits.created_at) as first_action,
        max(date_trunc('month', visits.created_at)) as last_action
    from visits
    join users on users.id = visits.user_id
    where users.created_at >= ?
    group by user_id
)
group by first_action, last_action;

我不确定在ARel中做这件事最优雅的方式是什么，但我认为应该是这样的。(直接使用SQL可能更容易一些。)

def date_trunc_month(field)
  Arel::Nodes::NamedFunction.new(
    'date_trunc', [Arel.sql("'month'"), field])
end

def max(*expressions)
  Arel::Nodes::Max.new(expressions)
end

users = User.arel_table
visits = Visit.arel_table

user_visits = visits.
    join(users).on(visits[:user_id].eq(users[:id])).
    where(users[:created_at].gteq(12.months)).
    group(users[:id]).
    project(
        users[:id],
        date_trunc_month(visits[:created_at]).as('first_visit'),
        max(date_trunc_month(visits[:created_at])).as('last_visit')
    ).
    as('user_visits')

cohort_data = users.
    join(user_visits).on(users[:id].eq(user_visits[:id])).
    group(user_visits[:first_visit], user_visits[:last_visit]).
    project(
        user_visits[:first_visit],
        user_visits[:last_visit],
        Arel::Nodes::Count.new([1]).as('count')
    )