Mongo/Mongoose-按日期汇总 [英] Mongo / Mongoose - Aggregating by Date
问题描述
我有一个mongo/mongoose模式，在查询时会重新运行诸如
I have a mongo/mongoose schema which when queried retruns documents such as
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -33.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-04-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -61.3, "name" : "Amazon", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "Tesco", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -26.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -63.3, "name" : "Sky", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }
我想编写一个查询，该查询提供每个供应商("name" : "Amazon")的年度，每月和每周支出，例如，对于供应商RINGGO:
I would like to write a query what provides the yearly, monthly and weekly spend for each of the vendors ("name" : "Amazon"), so for example for vendor RINGGO:
- 2017年有3个支出，分别为33.3 + 26.3 + 3.3，因此年度总支出为59.9
- 2017-03月份有两笔支出，总支出为26.3 + 3.3，因此每月总支出为26.6
- 每笔支出都在不同的星期内，因此每周的总支出为(例如)wk12 26.3，wk13 3.3，wk 15 33.3
我可以写一个查询，例如
I can write a query such as
db.statements.aggregate(
[
{ $group : { _id : "$name", amount: { $push: "$amount" } } }
]
)
这将按供应商名称汇总所有支出(amount)，但是我不确定如何按如上所述按年，月，周细分支出.
which will aggregate all spends (amount) by vendor name, but I'm not sure how to break this down by year, month, week as described above.
根据评论进行编辑 我不完全确定结果的形状是否正确，但理想情况下，结果将类似于以下内容:
Edit in response to comment I'm not exactly sure that shape the result may have but ideally it would be something like the below:
我需要年，月，周等，以便查询可以由网址(例如domain.com/vendorname/2017，domain.com/vendorname/2017/3，domain.com/vendorname/2017/3/12)驱动
I need the year, month, week etc so that the queries can be driven by url (e.g. domain.com/vendorname/2017, domain.com/vendorname/2017/3, domain.com/vendorname/2017/3/12)
我也希望每年，每月/每周的个人支出和总支出同时打印在页面上.
I would also like both the individual spends and the total spends for each year/month/week as I would like to print these to the page.
{ "_id" :
{ "year" : 2017,
"month" : 3,
"week" : 12 },
"name": "RINGGO", //vendor name
"YearlySpends":[ 33.3, 26.3, 3.3]
"totalYearlylyAmount" : [ 59.9]
"MonthlySpends":[ 26.3, 3.3]
"totalMonthlyAmount" : [ 26.6]
"WeeklylySpends":[ 3.3]
"totalWeeklylyAmount" : [3.3]
}
推荐答案
一个好的方法是将聚合管道分成几个步骤，以计算每个组(即每年，每月和每周的聚合)的聚合.
A good approach would be to break the aggregate pipeline into several steps with the aim of calculating the aggregates with each group i.e. yearly, monthly and weekly aggregates.
我已经在尝试生成上述管道方面作了微弱的尝试，但是不确定这是否是您所追求的，但是可以为您提供一些解决方案的线索，更好而又最佳的解决方案.也许其他人可以给出更好的答案.
I've made a feeble attempt at generating the said pipeline but not sure if that's what you are after but could give you some leads to a solution, better yet an optimal one. Perhaps someone else could give a better answer.
考虑以下 unested 管道:
db.statements.aggregate([
{
"$group": {
"_id": {
"name": "$name",
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"week": { "$week": "$date" }
},
"total": { "$sum": "$amount" }
}
},
{
"$group": {
"_id": {
"name": "$_id.name",
"year": "$_id.year"
},
"YearlySpends": { "$push": "$total" },
"totalYearlyAmount": { "$sum": "$total" },
"data": { "$push": "$$ROOT" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"month": "$data._id.month"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$push": "$data.total" },
"totalMonthlyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"week": "$data._id.week"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$push": "$data.total" },
"totalWeeklyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": "$data._id",
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$first": "$WeeklySpends" },
"totalWeeklyAmount": { "$first": "$totalWeeklyAmount" }
}
}
])
示例输出
/* 1 */
{
"_id" : {
"name" : "Tesco",
"year" : 2017,
"month" : 3,
"week" : 11
},
"YearlySpends" : [
-3.3
],
"totalYearlyAmount" : -3.3,
"MonthlySpends" : [
-3.3
],
"totalMonthlyAmount" : -3.3,
"WeeklySpends" : [
-3.3
],
"totalWeeklyAmount" : -3.3
}
/* 2 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 4,
"week" : 17
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-33.3
],
"totalMonthlyAmount" : -33.3,
"WeeklySpends" : [
-33.3
],
"totalWeeklyAmount" : -33.3
}
/* 3 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 12
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-3.3,
-26.3
],
"totalMonthlyAmount" : -29.6,
"WeeklySpends" : [
-3.3
],
"totalWeeklyAmount" : -3.3
}
/* 4 */
{
"_id" : {
"name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 11
},
"YearlySpends" : [
-3.3,
-26.3,
-33.3
],
"totalYearlyAmount" : -62.9,
"MonthlySpends" : [
-3.3,
-26.3
],
"totalMonthlyAmount" : -29.6,
"WeeklySpends" : [
-26.3
],
"totalWeeklyAmount" : -26.3
}
/* 5 */
{
"_id" : {
"name" : "Sky",
"year" : 2017,
"month" : 3,
"week" : 9
},
"YearlySpends" : [
-63.3
],
"totalYearlyAmount" : -63.3,
"MonthlySpends" : [
-63.3
],
"totalMonthlyAmount" : -63.3,
"WeeklySpends" : [
-63.3
],
"totalWeeklyAmount" : -63.3
}
/* 6 */
{
"_id" : {
"name" : "Amazon",
"year" : 2017,
"month" : 3,
"week" : 12
},
"YearlySpends" : [
-61.3
],
"totalYearlyAmount" : -61.3,
"MonthlySpends" : [
-61.3
],
"totalMonthlyAmount" : -61.3,
"WeeklySpends" : [
-61.3
],
"totalWeeklyAmount" : -61.3
}
更新
如果您希望在聚合操作中包括过滤器，那么我建议您使用 $match 步骤，由于您要汇总过滤后的结果，因此前面的步骤会稍作更改，这与最初汇总所有文档然后对结果应用过滤器大不相同.
UPDATE
If you wish to include filters within the aggregate operation then I would suggest you use the $match query as the first pipeline stage. However, if there is an initial $match step then the preceding steps would be altered slightly as you will be aggregating filtered results, very different from aggregating all the documents as a whole initially and then applying the filter on the results.
如果要采用 filter-first-then-aggregate 路线，请考虑运行使用 $redact 管道步骤在日期字段的月份部分进一步过滤文档，然后其余的将是
If you are to take the filter-first-then-aggregate route, consider running an aggregate operation that uses $match as the first step which filters the documents by vendor, then a preceding $redact pipeline step to further filter the documents on the month part of the date field and then the rest would be the $group stages:
Statements.aggregate([
{ "$match": { "name": req.params.vendor } },
{
"$redact": {
"$cond": [
{ "$eq": [{ "$month": "$date" }, parseInt(req.params.month) ]},
"$$KEEP",
"$$PRUNE"
]
}
},
.....
/*
add the remaining pipeline steps after
*/
], function(err, data){
if (err) throw err;
console.log(data);
})
如果要采用 group-first-then-filter 路线，则过滤器将位于给出分组结果的最后一个管道之后，但应用于该部分下的文档的不同字段流的大小将与原始模式不同.
If you are to take the group-first-then-filter route, then the filter would be after the last pipeline that gives the grouped result but applied on different fields as the documents down that part of the stream would be different from the original schema.
此路由无效，因为您将对集合中的所有文档开始聚合操作，然后进行过滤:
This route is not performant since you are beginning the aggregate operation with all the documents in the collection and then filtering afterwards:
Statements.aggregate([
.....
/*
place the initial pipeline steps from
the original query above here
*/
.....
{
"$match": {
"_id.name": req.params.vendor,
"_id.month": parseInt(req.params.month)
}
}
], function(err, data){
if (err) throw err;
console.log(data);
})
对于多个日期过滤器参数， $redact 运算符将是
For multiple date filter parameters, the $redact operator would be
{
"$redact": {
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, parseInt(req.params.year) ]},
{ "$eq": [{ "$month": "$date" }, parseInt(req.params.month) ]},
{ "$eq": [{ "$week": "$date" }, parseInt(req.params.week) ]}
]
},
"$$KEEP",
"$$PRUNE"
]
}
}
这篇关于Mongo/Mongoose-按日期汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
