涉及多态关联的复杂时间序列统计聚合

Question

好的。请耐心等待，因为我需要提供大量上下文细节才能为我的问题寻求合理的答案。

我有一个网站，可以让你每天选股。它的工作原理是，系统会提示您在当天对峙的公司之间做出选择。例如，GE 与 IBM。您可以做出两种类型的选择：表现（哪只股票表现更好？）和总成交量（合并后的股票交易量会高于还是低于 X？）。每天你会得到 100 虚拟美元来进行选择。

最终，我们的目标是跟踪哪个用户在以下时间段内在各个类别（如下所述）中每次选择赚的钱最多：5 天、15 天、30 天、90 天、180 天、1 年、全部 -时间。计算每次选秀赚多少钱非常简单。这是赚（或亏）的总钱/选秀次数。

现在，用户选择的每个公司都属于分类层次结构。一般来说，分类层次结构如下所示：

Division -->主要组 -->产业集团-->分类-->公司

以下是一些示例：

• 采矿业 -->金属开采 -->铁矿石 -->褐矿开采 --> A公司
• 矿业-->金属开采 -->铁矿石 -->褐矿开采 --> B公司
• 矿业-->金属开采 -->铁矿石 -->褐铁矿开采 --> C
• 矿业公司 -->金属开采 -->铁矿石 -->褐铁矿开采 --> D 公司
• 制造 -->烟草制品 -->雪茄-->斯托吉斯 --> E公司
• 制造-->烟草制品 -->雪茄-->斯托吉斯 --> F公司
• 制造 -->烟草制品 -->雪茄-->小雪茄 --> G公司
• 制造——>烟草制品 -->雪茄-->小雪茄 -->公司 H
• ……等等……

每个类别都有一个模型（当然还有相应的表），并且它们是关联的（想想foreign_key），就像您在上面看到的那样。

Matchup 有一个模型，每条记录代表当天有哪些公司在对峙。每条记录都跟踪每家公司的起始和最终股票价格以及总交易量。

每个比赛都有一个或多个可以全天变化的 :pick_prices。通常，每场比赛都有一个表现选择价格和一个总数量选择价格。价格决定了选择的成本以及正确选择的收入。 （现在，这只是背景信息。您无需担心那些特定的价格计算。）

在交易日结束时，用户的选择得到解决。选择在 Pick 模型中表示，具有以下属性：

• user_id
• amount_spent（例如，$10）
• result（例如，WON、LOST）
• pick（例如，公司 A）
• matchup_id
• pick_price_id
• amount_won
• returned（true 或 false）
• created_at
• Updated_at

目前，当每个pick 解决后，另一个名为 pick_records 的表被更新，该表具有以下属性：

• user_id
• recordable_id
• recordable_type （部门或主要组或行业组或分类或公司）
• 选秀权（做出的总选秀权，无论选秀类型如何）
• 获胜（赢得的总选秀权，无论选秀类型如何）
• 丢失（丢失的总选秀权，无论选秀类型）
• 金钱（赢得的总金钱）
• money_per_pick（金钱/选秀权）
• performance_picks
• Performance_won
• Performance_lost
• Performance_money
• Performance_money_per_pick
• Volume_picks
• Volume_Won
• Volume_lost
• Volume_Money
• Volume_Money_Per_pick
• Created_At
• Updated_At

当您可以看出，这是一个多态模型。该表汇总了所有时间的挑选记录统计数据。

那么现在的挑战是：

在现有的设计下，我需要做什么才能捕获用户在以下时间段内的选择记录：5天、15天、30天、90天、180天、1年，全时？它需要简单、高效、快速！

我目前在 MySQL DB 上运行 Rails 2.3.11。

Answer 1

我认为不需要表 pick_records。
您可以对任意天数执行这样的查询：

SELECT 
   user_id
   ,sum(amount_spent) 
   ,sum(IF(result = 'WON',1,0)) as WON_count
   ,sum(IF(result = 'LOST',1,0)) as LOST_count
   ,pick 
   /*matchup_id*/
   ,sum(pc.price) as price
   ,sum(IF(result = 'WON'),amount_won,0)) as amount_won
   ,sum(IF(result = 'LOST'),amount_won,0)) as amount_lost
   ,sum(IF(result = 'WON'),amount_won,-amount_won)) as nett_amount
FROM picks
INNER JOIN pick_price pc ON (pc.id = user.pick_price_id)
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW()
  AND resolved = 'true'
GROUP BY user_id, pick

Answer 2

不确定我的问题是否正确，但是...

@records=Pick_record.all(:conditions => ["user_id = ?", user_id],
                         :group => "date(created_at)", 
                         :having => ["created_at > ?", 5.days.ago])

Answer 3

如果我理解正确的话，现在每个用户只有一个 pick_record，它包含他的总选择的概述，并在解决选择时更新。

由于 pick_record 的内容可以计算，因此它仅用于缓存并确保您可以非常快速地提供数据/报告。

为了解决您的问题，我建议如下：

在整个生命周期中，我不会为您感兴趣的每个时间段提供一个 pick_record，而是拥有一个 pick_record。因此，您将拥有一个包含过去 4 天结果的 pick_record，一个以及过去 14 天的结果，29 ...那些您每天计算一次的结果，最好是在晚上（或者当您的网站使用率较低时）。当需要显示选定时间段的报告时，您只需添加当天的结果即可完成！

因此，回顾一下：

1. 为每个感兴趣的时间段引入一个 pick_record（添加一个指示时间段的字段：5、15、30，...）
2. 每天预先计算一次结果（后台作业、eq resque 或elaided_job）
3. 在检索时间段时 -结果，你只需要添加当天的结果

你觉得怎么样？