聚合多个日期范围的 $group [英] Aggregate $group for multiple date ranges

问题描述

在我看来，流中的每个文档都会有一个日期.

In my aggregate, each document in the stream will have a date on it.

我需要对日期范围内的一些值求和..

I need to sum some values over date ranges..

我的文档看起来像:

{ value: 3,  date: [SoME TIME STAMP] },
{ value: 4,  date: [SoME TIME STAMP] },
{ value: 1,  date: [SoME TIME STAMP] },
{ value: -6, date: [SoME TIME STAMP] }

我希望能够根据日期范围对这些文档进行分组.即:1-7 天前，8-15 天前.和 15-30 天前.

I want to be able to group these document base on the date range. i.e.: 1-7 days ago, 8-15 days ago. and 15-30 days ago.

我可能在日期上应用了 3 个不同的聚合查询和 3 个不同的 $match.

I probably apply 3 different aggregate queries with 3 different $match on dates.

但是是否可以执行所有 $group 并将值"相加?一场比赛?

But is it possible to do all the $group and sum the "value" field in one run?

推荐答案

您需要根据当前日期在范围之间的位置有条件地确定分组键.这基本上是通过 $cond 带有嵌套条件和 $lt<的逻辑变体/code>:

You need to conditionally determine the grouping key based on where the current date falls in between the range. This is basically achieved via $cond with nested condtions and the logical variant of $lt:

// work out dates somehow
var today = new Date(),
    oneDay = ( 1000 * 60 * 60 * 24 ),
    thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) ),
    fifteenDays = new Date( today.valueOf() - ( 15 * oneDay ) ),
    sevenDays = new Date( today.valueOf() - ( 7 * oneDay ) );

db.collection.aggregate([
    { "$match": {
        "date": { "$gte": thirtyDays }
    }},
    { "$group": {
        "_id": {
            "$cond": [
                { "$lt": [ "$date", fifteenDays ] },
                "16-30",
                { "$cond": [
                    { "$lt": [ "$date", sevenDays ] },
                    "08-15",
                    "01-07"
                ]}
            ]
        },
        "count": { "$sum": 1 },
        "totalValue": { "$sum": "$value" }
    }}
])

由于 $cond 是一个三元运算符，所以评估第一个条件以查看条件是否为真，如果为真，则返回第二个参数，否则返回第三个参数为假.因此，通过在 false 情况下嵌套另一个 $cond，您可以对日期所在的位置进行逻辑测试，或者小于 15 天日期"，这意味着它在最旧的范围内，或者小于 7天"表示中间范围，或者当然是在最新范围内.

As $cond is a ternary operator, the first condition is evaluated to see if the condition is true, and when true the second argument is returned otherwise the third is returned when false. So by nesting another $cond in the false case you get the logical test on where the date falls, either "less that the 15 day date" which means its in the oldest range, or "less than 7 days" which means the middle range, or of course it's in the newest range.

我只是在此处给小于 10 的数字加上 0 前缀，因此如果您愿意，它可以为您提供一些可以排序的东西，因为 $group<中键"的输出/code> 本身并不是有序的.

I'm just prefixing the numbers here less than 10 with a 0 so it gives you something to sort on if you want, since the output of "keys" in $group is not in itself ordered.

但这就是您在单个查询中执行此操作的方式.您只需根据日期所在的位置和每个键的累积来确定分组键应该是什么.

But that is how you do this in a single query. You just work out what the grouping key should be based on where the date falls and accumulate for each key.

这篇关于聚合多个日期范围的 $group的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！