Mongodb汇总多个日期范围的$ group [英] Mongodb aggregate $group for multiple 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]
}

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

db.Collection.aggregate([
{$match: {some matching}},
{$group: {What should i do here??}}
])

我当然可以在日期上进行3种不同的匹配以及3种不同的$ match.

是否可以一次运行所有$ group并求和"value"字段?

解决方案

您需要根据当前日期在范围之间的位置来有条件地确定分组键.这基本上是通过 $cond 与嵌套条件和 $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是三元运算符，因此对第一个条件进行求值以查看条件是否为true，如果为true，则返回第二个参数，否则为false时返回第三个参数.因此，通过在错误的情况下嵌套另一个$cond，您可以对日期落在哪里进行逻辑测试，要么是小于15天的日期"，这意味着该日期位于最旧的范围内，要么是小于7天的时间"，这意味着该日期中间的值范围，或者当然是最新范围.

我只是在此处用0在小于10的数字前面加上前缀，因此如果需要，它可以为您提供一些排序依据，因为$group中的键"的输出本身并不是有序的.

但这就是您在单个查询中执行此操作的方式.您只需根据日期落在哪里来确定分组密钥应该是什么，并为每个密钥进行累积.

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

I need to sum some values over date ranges..

Ie.

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

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

db.Collection.aggregate([
{$match: {some matching}},
{$group: {What should i do here??}}
])

I can of course do 3 different aggregate with 3 different $match on dates.

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

解决方案

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" }
    }}
])

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.

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.

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