Комплексная статистическая агрегация временных рядов с участием полиморфных ассоциаций

Ok. Потерпите меня, поскольку мне нужно предоставить множество контекстуальных подробностей, прежде чем я смогу вынести разумный ответ на мой вопрос.

У меня есть сайт, который позволяет вам ежедневно собирать акции. Способ, которым он работает, заключается в том, что вам предлагается сделать выбор между компаниями, которые сталкиваются в течение дня. Например, GE против IBM. Вы можете сделать два типа выбора: Производительность (какой запас будет работать лучше?) И Общий объем (будут ли объединенные акции торговаться на объемах выше или ниже X?). Вы получаете 100 виртуальных долларов каждый день, чтобы сделать выбор.

В конечном счете, наша цель состоит в том, чтобы отслеживать, какой пользователь делает наибольшее количество денег за выбор в разных категориях (объясняется ниже) в следующие периоды времени: 5 дней, 15 дней, 30 дней, 90 дней, 180 дней, 1 год, все время. Очень просто подсчитать, сколько денег сделано за выбор. Это общая сумма денег (или потеряна)/количество выборов.

Теперь каждая компания, которую пользователь делает выбор, попадает под категориальную иерархию. Как правило, категориальная иерархия выглядит следующим образом:

Отдел → Основная группа → Промышленная группа → Классификация → Компания

Вот несколько примеров:

• Добыча полезных ископаемых → Металлургия → Железные руды → Руда руды → Компания A
• Добыча полезных ископаемых → Металлургия → Железные руды → Руда руды → Компания B
• Добыча полезных ископаемых → Металлургия → Железные руды → Лимонитовая промышленность → Компания C
• Добыча полезных ископаемых → Металлургия → Железные руды → Лимонит Майнинг → Компания D
• Производство → Табачные изделия → Сигары → Стили → Компания E
• Производство → Табачные изделия → Сигары → Стили → Компания F
• Производство → Табачные изделия → Сигары → Сигариллы → Компания G
• Производство → Табачные изделия → Сигары → Сигариллы → Компания H
• ... и т.д.

Есть модель для каждой категории (и соответствующая таблица, конечно), и они связаны (думаю, foreign_key) так же, как вы видите выше.

Существует модель для Matchup, с каждой записью, представляющей, какие компании сталкиваются в течение дня. Каждая запись отслеживает начальные и конечные цены акций для каждой компании, а также общий объем торговли.

Каждый Matchup имеет один или несколько: pick_prices, которые могут меняться в течение дня. Как правило, каждый матч имеет цену исполнения и общую сумму выбора. Цена определяет, какой выбор будет стоить вам и сколько вы заработаете за правильный выбор. (Теперь это всего лишь справочная информация. Вам не нужно беспокоиться об этих конкретных расчетах цен.)

В конце торгового дня пользовательские выборы разрешаются. Выборы представлены в модели Pick со следующими атрибутами:

• user_id
• amount_spent (например, $10)
• (например, WON, LOST)
• выберите (например, компанию A)
• matchup_id
• pick_price_id
• amount_won
• разрешено (true или false)
• created_at
• updated_at

В настоящее время, когда каждый выбор разрешен, другая таблица обновляется с именем 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 год, время? Он должен быть простым, эффективным и быстрым!

В настоящее время я запускаю Rails 2.3.11 в MySQL DB.

Ответы

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

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