Сложная статистическая агрегация временных рядов с использованием полиморфных ассоциаций

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

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

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

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

Подразделение --> Основная группа --> Отраслевая группа --> Классификация --> Компания

Вот некоторые примеры:

  • Добыча полезных ископаемых --> Добыча металлов --> Железные руды --> Добыча бурой руды --> Компания А
  • Добыча полезных ископаемых --> Добыча металлов --> Железные руды --> Добыча бурой руды --> Компания B
  • Добыча полезных ископаемых --> Добыча металлов --> Железные руды --> Добыча лимонита --> Компания C
  • Добыча полезных ископаемых --> Добыча металлов --> Железные руды --> Добыча лимонита --> Компания D
  • Производство --> Табачные изделия --> Сигары --> Стоги --> Компания E
  • Производство --> Табачные изделия --> Сигары --> Стоги --> Компания F
  • Производство --> Табачные изделия --> Сигары --> Сигариллы --> Компания G
  • Производство --> Табачные изделия --> Сигары --> Сигариллы --> Компания H
  • …и так далее…

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

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

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

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

  • Идентификатор пользователя
  • сумма_потрачено (например, 10 долларов США)
  • результат (например, ВЫИГРАЛ, ПРОИГРАЛ)
  • выбрать (например, компанию А)
  • matchup_id
  • pick_price_id
  • Сумма выигрыша
  • разрешено (правда или ложь)
  • создано в
  • updated_at

В настоящее время, когда каждый выбор разрешен, обновляется другая таблица, называемая pick_records, которая имеет следующие атрибуты:

  • Идентификатор пользователя
  • записываемый_id
  • recordable_type (подразделение или основная группа, или отраслевая группа, или классификация, или компания)
  • пики (всего сделанные пики, независимо от типа пикинга)
  • выиграл (общее количество выигранных пиков, независимо от типа пика)
  • проиграно (общее количество потерянных пиков, независимо от типа пика)
  • деньги (общая сумма выигранных денег)
  • money_per_pick (деньги/выбор)
  • performance_picks
  • performance_won
  • performance_lost
  • performance_money
  • performance_money_per_pick
  • Volume_picks
  • том_выиграл
  • объем_потерянный
  • объем_деньги
  • volume_money_per_pick
  • создано в
  • updated_at

Как вы понимаете, это полиморфная модель. В таблице собраны статистические данные о пикировках за все время.

Итак, теперь вот задача:

Учитывая существующий дизайн, что мне нужно сделать, чтобы я мог фиксировать записи о выборах пользователей за следующие периоды времени: 5 дней, 15 дней, 30 дней, 90 дней, 180 дней, 1 год, за все время? Он должен быть простым, эффективным и быстрым!

В настоящее время я использую Rails 2.3.11 в базе данных MySQL.


mysql ruby-on-rails sql aggregation polymorphic-associations
person keruilin    schedule 04.06.2011    source источник
comment
Примечание: каждая таблица выше имеет временные метки — created_at, updated_at.   -  person keruilin    schedule 04.06.2011
comment
надеюсь, что некоторые из ответов работают на вас; дайте мне знать, что работает, а что нет, чтобы я мог дать вам ответ, который соответствует тому, что вам нужно.   -  person Johan    schedule 10.06.2011
comment
+1 за то, что вы вложили половину своей репутации в награду.   -  person Matthew    schedule 10.06.2011
comment
ну, если кто-то собирается потратить время на то, чтобы прочитать все это и дать ответ, лучше сделать это стоящим своих усилий!   -  person keruilin    schedule 12.06.2011
comment
У вас есть база данных ограниченного размера? Если это так, вы хотели бы реализовать метод для суммирования и очистки данных Pick, а также реализовать метод для расчета пользовательского рейтинга/рейтинга/и т. д. на основе средних значений пользователей. Так ли это?   -  person RLH    schedule 13.06.2011

Ответы (3)


arrow_upward
3
arrow_downward

Я не вижу необходимости в таблице 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
person Johan    schedule 04.06.2011
comment
я сам склонялся к прямому SQL, поэтому мне нравится ваша идея - person keruilin; 12.06.2011

arrow_upward
0
arrow_downward

Не уверен, что правильно понял вопрос, но...

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

arrow_upward
0
arrow_downward

Если я правильно понимаю, теперь для каждого пользователя есть только один pick_record, и он содержит обзор его общего количества пиков и обновляется при разрешении пика.

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

Для решения вашей проблемы предлагаю следующее:

Вместо того, чтобы иметь одну запись pick_record за весь срок службы, у меня будет pick_record для каждого интересующего вас периода времени. Таким образом, у вас будет pick_record с результатом за последние 4 дня, один с результатом за последние 14 дней. , 29 ... Те, которые вы рассчитываете один раз в день, предпочтительно ночью (или когда ваш сайт мало используется). Когда нужно показать отчет за выбранный период времени, вам нужно только добавить результат текущего дня и готово!

Итак, резюмируя:

  1. ввести pick_record за интересующий период (добавить поле с указанием периода: 5, 15, 30, ...)
  2. предварительно рассчитывать результаты один раз в день (фоновое задание, например, resque или delayed_job)
  3. при получении результатов периода вам нужно только добавить результаты текущего дня

Что вы думаете?

person nathanvda    schedule 09.06.2011