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

问题描述

我有一个具有日期时间字段并且已提交 count 的收藏集:

I have a collection with datetime field and count filed:

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

我希望在24小时，7天和30天的间隔内获得计数的总和. 结果必须像:

I want to get sums of count by 24 hours, 7 days and 30 days intervals. The result must be like:

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

更多摘要:我需要按多个条件(在本例中为多个时间间隔)进行分组结果

More abstract: I need group results by multiple conditions (in this case — multiple time intervals)

MySQL示例:

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() 查询.这很容易，如果将datetime字段编入索引，它将很快.

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 创建0或1值输入新的"last_day"，"last_week"和"last_month"字段，然后使用

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.

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