如何分别在mongodb中按天，周，月分别在相应的周，月，年分别汇总数据 [英] How to aggregate data by each day, week, month wise in corresponding week, month, year respectively in mongodb

问题描述

在我的项目中，我有一个垃圾收集器，如下所示

In my project I have a wastes collection and it is as follows

wastes:[
        {weight: 100, date: ISODate("2016-01-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-01-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-02-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-02-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-03-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-03-01T10:20:41.417Z")}
        ........................................................
        {weight: 100, date: ISODate("2016-12-01T10:20:41.417Z")}
        {weight: 100, date: ISODate("2016-12-01T10:20:41.417Z")}
       ]

我想像当年一样在当年的每个月明智地汇总浪费

I want aggregate the waste on each month wise in current year like

results: [
          <month1>:{weight: 200},
          <month2>:{weight: 200},
          ......................
          <month12>:{weight: 200} 
]

还有当月的明智之举和当周的明智之举，如下所示:

And also week wise in current month and day wise in current week as follows

results:[
        <week1>:{weight: 100},
        .....................
        <week4>:{weight: 100}
        ]

results: [
          <day1>:{weight: 100},
          ....................
          <day7>:{weight: 100}
         ]

注意:按周和按天的聚合值只是我需要这种输出的虚拟值.

Note: week wise and day wise aggregated values are just a dummy values I need that kind of output.

推荐答案

对于与查询相关的所有日期时间，您需要从日期字段中将其值投影到项目中-

For all date time connected with query you need to $project it values from date field - manual here

在这种情况下，汇总框架会提供帮助-请参阅下面的基本查询，该查询具有每日和每周的权重汇总，因此您可以将此查询转换为其他时间段，或者每个周期使用一个查询:

Aggregation framework come with help in this case - please see basic query below, which have daily and weekly weight aggregate, so you can transform this query for other time periods or use one query per period:

db.timing.aggregate([{
        $project : {
            year : {
                $year : "$date"
            },
            month : {
                $month : "$date"
            },
            week : {
                $week : "$date"
            },
            day : {
                $dayOfWeek : "$date"
            },
            _id : 1,
            weight : 1
        }
    }, {
        $group : {
            _id : {
                year : "$year",
                month : "$month",
                week : "$week",
                day : "$day"
            },
            totalWeightDaily : {
                $sum : "$weight"
            }
        }
    },
    {
        $group : {

            _id : {
                year : "$_id.year",
                month : "$_id.month",
                week : "$_id.week"
            },
            totalWeightWeekly : {
                $sum : "$totalWeightDaily"
            },
            totalWeightDay : {
                $push : {
                    totalWeightDay : "$totalWeightDaily",
                    dayOfWeek : "$_id.day"
                }
            }
        }
    }, {
        $match : {
            "_id.month" : 3
        }
    }
])

第3个月我的虚拟数据的示例结果如下:

Example results for month 3 for my dummy data are below:

{
    "_id" : {
        "year" : 2016,
        "month" : 3,
        "week" : 10
    },
    "totalWeightWeekly" : 600,
    "totalWeightDay" : [ 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 7
        }, 
        {
            "totalWeightDay" : 400,
            "dayOfWeek" : 6
        }
    ]
}
{
    "_id" : {
        "year" : 2016,
        "month" : 3,
        "week" : 9
    },
    "totalWeightWeekly" : 1000,
    "totalWeightDay" : [ 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 4
        }, 
        {
            "totalWeightDay" : 600,
            "dayOfWeek" : 3
        }, 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 7
        }
    ]
}
{
    "_id" : {
        "year" : 2016,
        "month" : 3,
        "week" : 12
    },
    "totalWeightWeekly" : 400,
    "totalWeightDay" : [ 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 7
        }, 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 2
        }
    ]
}
{
    "_id" : {
        "year" : 2016,
        "month" : 3,
        "week" : 13
    },
    "totalWeightWeekly" : 200,
    "totalWeightDay" : [ 
        {
            "totalWeightDay" : 200,
            "dayOfWeek" : 3
        }
    ]
}

并根据需要形成形状，您可以使用$ project阶段

and to form shape as needed you could use $project phase

   {$project:{
                _id:0,
                "year" : "$_id.year", //this could be ommited but use $match to avoid sum of other years
                "month" : "$_id.month",  //this could be ommited but use $match to avoid sum of other months
                "week" :"$_id.week",                
                totalWeightWeekly:1

                }}

输出

{
    "totalWeightWeekly" : 600,
    "year" : 2016,
    "month" : 3,
    "week" : 10
}

欢迎任何评论！

这篇关于如何分别在mongodb中按天，周，月分别在相应的周，月，年分别汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！