Рассчитать моментум как средневзвешенное значение по давности

У меня есть таблица подписок со связанным каналом feed_id и отметкой времени создания. Канал имеет N подписок.

Достаточно просто показать самые популярные фиды, используя запрос group для подсчета количества записей с каждым фидом_id. Но я хочу рассчитать импульс, чтобы показать самые трендовые ленты.

Упрощенный алгоритм будет таким:

momentum of feed_id =
    10 * (count of subscriptions with created_at in past day)
  + 5 *  (count of subscriptions with created_at from 2-7 days ago)
  + 1 *  (count of subscriptions with created_at from 7-28 days ago)

Как можно сделать что-то подобное в одном (My) SQL-запросе вместо того, чтобы делать это с 3 запросами и программно суммировать результаты?


mysql weighted-average trend
person mahemoff    schedule 15.03.2015    source источник
comment
Отредактируйте свой вопрос и покажите структуру данных. . . выборочные данные и желаемые результаты действительно помогают понять проблему.   -  person Gordon Linoff    schedule 15.03.2015

Ответы (2)


arrow_upward
2
arrow_downward

Для этого можно использовать условную агрегацию. MySQL обрабатывает логические значения как целые числа, где true равно «1», поэтому вы можете просто суммировать выражение для времени.

Я предполагаю, что это выглядит примерно так:

select feedid,
       (10 * sum(createdat >= date_sub(now(), interval 1 day)) +
         5 * sum(createdat >= date_sub(now(), interval 7 day) and
                 createdat < date_sub(now(), interval 1 day)) +
         1 * sum(createdat >= date_sub(now(), interval 28 day) and
                 createdat < date_sub(now(), interval 7 day))
       ) as momentum
from subscriptions
group by feedid
person Gordon Linoff    schedule 15.03.2015

arrow_upward
1
arrow_downward 

SELECT 10*COUNT(IF(created_at >= CURDATE(), 1, 0)) +
       5*COUNT(IF(created_at BETWEEN DATE_ADD(CURDATE(), - INTERVAL 7 days) AND DATE_ADD(CURDATE(), - INTERVAL 1 day), 1, 0) +
       1*COUNT(IF(created_at BETWEEN DATE_ADD(CURDATE(), - INTERVAL 28 days) AND DATE_ADD(CURDATE(), - INTERVAL 8 day), 1, 0)
FROM ...

Я не уверен на 100%, что поймал крайние условия (вчера или 8 дней назад), чтобы получить точно правильный подсчет. Вы захотите проверить это.

Если вас интересуют 24-часовые периоды, просто замените NOW() на CURDATE(), и все перейдет к DATETIME.

person Peter Bowers    schedule 15.03.2015