按日期间隔分组 [英] Group by date intervals

问题描述

我有一个包含以下文档的集合:

I have a collection with documents like this:

{ datetime: new Date(), count: 1234 }

我想按 24 小时、7 天 和 30 天 间隔获得 count 的总和.

I want to get sums of count by 24 hours, 7 days and 30 days intervals.

结果应该是这样的:

{ "sum": 100,  "interval": "day" }
{ "sum": 700,  "interval": "week" }
{ "sum": 3000, "interval": "month" }

更抽象地说，我需要按多个条件(在本例中为多个时间间隔)对结果进行分组

In more abstract terms, I need to group results by multiple conditions (in this case — multiple time intervals)

MySQL 等价物是:

The MySQL equivalent would be:

SELECT 
    IF (time>CURRENT_TIMESTAMP() - INTERVAL 24 HOUR, 1, 0) last_day,
    IF (time>CURRENT_TIMESTAMP() - INTERVAL 168 HOUR, 1, 0) last_week,
    IF (time>CURRENT_TIMESTAMP() - INTERVAL 720 HOUR, 1, 0) last_month,
    SUM(count) count
FROM table
GROUP BY    last_day,
            last_week,
            last_month

推荐答案

有两种不同的方法可以做到这一点.一种是发出单独的count()查询每个范围.这很容易，如果日期时间字段被索引，它会很快.

There are two different ways to do this. One is to issue a separate count() query for each of the ranges. This is pretty easy, and if the datetime field is indexed, it will be fast.

第二种方法是使用与 SQL 示例类似的方法将它们全部组合成一个查询.为此，您需要使用 aggregate() 方法，创建$project 为新的last_day"、last_week"和last_month"字段创建 0 或 1 值，然后使用 $group 运算符进行求和.

The second way is to combine them all into one query using a similar method as your SQL example. To do this, you need to use the aggregate() method, creating a pipeline of $project to create the 0 or 1 values for the new "last_day", "last_week", and "last_month" fields, and then use the $group operator to do the sums.

这篇关于按日期间隔分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！