按日期间隔分组

Question

我收藏了这样的文件：

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

我想得到计数的24 hours，7 days和30 days间隔的和。

结果应该是：

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

在更抽象的术语中，我需要将结果按多个条件分组(在本例中是多个时间间隔)。

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

Answer 1

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

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

Answer 2

date aggregation operators可以用于MongoDB的聚合框架。因此，例如，使用$dayOfYear运算符从用于分组的日期获取该值：

db.collection.aggregate([
    { "$group": {
        "_id": { "$dayOfYear": "$datetime" },
        "total": { "$sum": "$count" }
    }}
])

或者你可以用日期数学方法代替。通过应用划时代日期，您可以将date对象转换为可以应用数学的数字：

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "$subtract": [
                { "$subtract": [ "$datetime", new Date("1970-01-01") ] },
                { "$mod": [
                    { "$subtract": [ "$datetime", new Date("1970-01-01") ] },
                    1000 * 60 * 60 * 24
                ]}
            ]
        },
        "total": { "$sum": "$count" }
    }}
])

如果您所追求的是来自当前时间点的间隔，那么您需要的基本上是日期数学方法，并通过$cond运算符在一些条件项中工作：

db.collection.aggregate([
    { "$match": {
        "datetime": { 
            "$gte": new Date(new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 365 ))
        }
    }},
    { "$group": {
        "_id": null,
        "24hours": { 
            "$sum": {
                "$cond": [
                    { "$gt": [
                        { "$subtract": [ "$datetime", new Date("1970-01-01") ] },
                        new Date().valueOf() - ( 1000 * 60 * 60 * 24 )
                    ]},
                    "$count",
                    0
                ]
            }
        },
        "30days": { 
            "$sum": {
                "$cond": [
                    { "$gt": [
                        { "$subtract": [ "$datetime", new Date("1970-01-01") ] },
                        new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 30 )
                    ]},
                    "$count",
                    0
                ]
            }
        },
        "OneYear": { 
            "$sum": {
                "$cond": [
                    { "$gt": [
                        { "$subtract": [ "$datetime", new Date("1970-01-01") ] },
                        new Date().valueOf() - ( 1000 * 60 * 60 * 24 * 365 )
                    ]},
                    "$count",
                    0
                ]
            }
        }
    }}
])

这基本上与SQL示例相同，其中查询有条件地计算日期值是否在所需范围内，并决定是否将该值添加到和中。

这里有一个附加的$match阶段，它将查询限制为只对那些可能在您所要求的最大一年范围内的项目进行操作。这使得它比提供的SQL稍微好一点，因为索引可以用来过滤掉这些值，并且您不需要通过集合中的非匹配数据“强制执行”。

在使用聚合管道时，使用$match限制输入总是一个好主意。

Answer 3

从Mongo 5开始，对于与$facet阶段相关联的$dateDiff操作符来说，这是一个很好的用例：

// { date: ISODate("2021-12-04"), count: 3  } <= today
// { date: ISODate("2021-11-29"), count: 5  } <= last week
// { date: ISODate("2021-11-24"), count: 1  } <= last month
// { date: ISODate("2021-11-12"), count: 12 } <= last month
// { date: ISODate("2021-10-04"), count: 8  } <= too old
db.collection.aggregate([

  { $set: {
    diff: { $dateDiff: { startDate: "$$NOW", endDate: "$date", unit: "day" } }
  }},

  { $facet: {
    lastMonth: [
      { $match: { diff: { $gt: -30 } } },
      { $group: { _id: null, total: { $sum: "$count" } } }
    ],
    lastWeek: [
      { $match: { diff: { $gt: -7 } } },
      { $group: { _id: null, total: { $sum: "$count" } } }
    ],
    lastDay: [
      { $match: { diff: { $gt: -1 } } },
      { $group: { _id: null, total: { $sum: "$count" } } }
    ]
  }},

  { $set: {
    lastMonth: { $first: "$lastMonth.total" },
    lastWeek: { $first: "$lastWeek.total" },
    lastDay: { $first: "$lastDay.total" }
  }}
])
// { lastMonth: 21, lastWeek: 8, lastDay: 3 }

这是：

• 首先计算(使用$dateDiff)今天("$$NOW")与文档的date之间的差天数

- if the date is 3 days ago, `diff` will be set to `-3`

- the intermediate result being:

{ date: ISODate("2021-12-04")，计数: 3，diff: 0}{ date: ISODate("2021-11-29")，计数: 5，diff：-5 }{date(“2021-11-24”)，计数: 1，diff：-10 }{日期: ISODate("2021-11-12")，计数: 12，diff：-22 }{日期: ISODate("2021-10-04")，计数: 8，diff：-61 }

• 然后执行一个$facet阶段，允许我们在同一个输入文档集上的单个阶段内运行多个聚合管道。每个子管道在输出文档中都有自己的字段，其结果存储为一个文档数组。

- this way, we can create a `lastMonth` field that'll contain the sum of counts (`$sum: "$count"`) for documents whose day diff with today is more than 30 days (`{ $match: { diff: { $gt: -30 } } }`)

- while we do the same for `lastWeek` and `lastDay`.

- the intermediate result being:

{ lastMonth：{ _id: null，总计: 21 }，lastWeek：{ _id: null，总计:8 }，lastDay：{ _id: null，总计:3}

• 最后，使用一个$facet阶段清理$facet输出，以获得格式良好的字段： { lastMonth: 21，lastWeek: 8，lastDay: 3}