将动量计算为新近度的加权平均值

Question

我有一个订阅表，其中包含关联的feed_id和创建时间戳。一个源有 N 个订阅。

使用group查询来计算每个feed_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 个查询并以编程方式对结果求和？

Answer 1

您可以为此使用条件聚合。 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

Answer 2

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。