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

Question

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

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

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

现在，用户选择的每家公司都属于一个分类层次结构。通常，分类层次结构如下所示:

分部--> 主要组别--> 行业组别--> 分类--> 公司

这里有些例子:

采矿 --> 金属开采 --> 铁矿石 --> 褐矿开采 --> A 公司

采矿 --> 金属开采 --> 铁矿石 --> 褐矿开采 --> B 公司

采矿 --> 金属采矿 --> 铁矿石 --> 褐铁矿开采 --> C 公司

采矿 --> 金属采矿 --> 铁矿石 --> 褐铁矿开采 --> D 公司

制造 --> 烟草制品 --> 雪茄 --> Stogies --> E 公司

制造 --> 烟草制品 --> 雪茄 --> Stogies --> F 公司

制造业 --> 烟草制品 --> 雪茄 --> 小雪茄 --> G 公司

制造业 --> 烟草制品 --> 雪茄 --> 小雪茄 --> H 公司

......等等......

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

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

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

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

用户 ID

花费金额(例如 10 美元)

结果(例如，赢了，输了)

挑选(例如，A 公司)

matchup_id

pick_price_id

amount_won

已解决(真或假)

created_at

updated_at

目前，当每个选择被解决时，另一个名为 pick_records 的表被更新，它具有以下属性:

用户 ID

recordable_id

recordable_type(部门或主要组或行业组或分类或公司)

选择(总选择，不考虑选择类型)

赢了(赢得的总选秀权，不考虑选秀权类型)

丢失(丢失的总选择数，不考虑选择类型)

金钱(赢得的总金钱)

money_per_pick(金钱/选秀权)

performance_picks

performance_won

性能丢失

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