为多个日期范围聚合 $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 天日期"，这意味着它在最旧的范围内，或者小于 7days" 表示中间范围，或者当然是最新的范围.

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<中的keys"输出/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屋！