MongoDB 聚合填补缺失的天数 [英] MongoDB aggreagte fill missing days
问题描述
我有一个包含以下文档的产品系列:
I have a product collection with the following documents:
{ "_id" : 1, "item" : "abc", created: ISODate("2014-10-01T08:12:00Z") }
{ "_id" : 2, "item" : "jkl", created: ISODate("2014-10-02T09:13:00Z") }
{ "_id" : 3, "item" : "hjk", created: ISODate("2014-10-02T09:18:00Z") }
{ "_id" : 4, "item" : "sdf", created: ISODate("2014-10-07T09:14:00Z") }
{ "_id" : 5, "item" : "xyz", created: ISODate("2014-10-15T09:15:00Z") }
{ "_id" : 6, "item" : "iop", created: ISODate("2014-10-16T09:15:00Z") }
我想画一个按天描述产品数量的图表，所以我使用mongodb聚合框架按天计算产品组:
I want to draw a chart describing product count by day, so I use mongodb aggregation framework to count product group by day:
var proj1 = {
"$project": {
"created": 1,
"_id": 0,
"h": {"$hour": "$created"},
"m": {"$minute": "$created"},
"s": {"$second": "$created"},
"ml": {"$millisecond": "$created"}
}
};
var proj2 = {
"$project": {
"created": {
"$subtract": [
"$created", {
"$add": [
"$ml",
{"$multiply": ["$s", 1000]},
{"$multiply": ["$m", 60, 1000]},
{"$multiply": ["$h", 60, 60, 1000]}
]
}]
}
}
};
db.product.aggregate([
proj1,
proj2,
{$group: {
_id: "$created",
count: {$sum: 1}
}},
{$sort: {_id: 1}}
])
mongo shell 中的结果是:
The result in mongo shell is:
{
"result" : [
{
"_id" : ISODate("2014-10-01T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-10-02T00:00:00.000Z"),
"count" : 2
},
{
"_id" : ISODate("2014-10-07T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-10-15T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-10-16T00:00:00.000Z"),
"count" : 1
}
],
"ok" : 1
}
当然，有些日子没有产品，使用上述结果集的图表如下所示:
Of course, there is no product some days and the chart using the result set above looks like this:
但是想要的图表应该是这样的:
But the desired chart should look like this:
所以问题是:如何使用 count = 0 将缺失的天数(例如过去 30 天)添加到结果集中?这意味着，所需的结果集应如下所示:
So the question is: How can I add missing days (of the last 30 days, for example) to the result set with count = 0? That means, the desired result set should looks like this:
{
"result" : [
{
"_id" : ISODate("2014-09-16T00:00:00.000Z"),
"count" : 0
},
{
"_id" : ISODate("2014-09-17T00:00:00.000Z"),
"count" : 0
},
...
{
"_id" : ISODate("2014-10-01T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-10-02T00:00:00.000Z"),
"count" : 2
},
{
"_id" : ISODate("2014-10-03T00:00:00.000Z"),
"count" : 0
},
...
{
"_id" : ISODate("2014-10-07T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-09-08T00:00:00.000Z"),
"count" : 0
},
...
{
"_id" : ISODate("2014-10-15T00:00:00.000Z"),
"count" : 1
},
{
"_id" : ISODate("2014-10-16T00:00:00.000Z"),
"count" : 1
},
// also, add some extra days
{
"_id" : ISODate("2014-10-17T00:00:00.000Z"),
"count" : 0
},
{
"_id" : ISODate("2014-10-10T00:00:00.000Z"),
"count" : 0
}
],
"ok" : 1
}
推荐答案
使用aggregate完全解决这个问题很痛苦.
但是可以到达.
(需要 MongoDB V2.6+)
Using aggregate to handle this question completely is a pain.
But it can be reached.
(MongoDB V2.6+ required)
var proj1 = {
"$project" : {
"created" : 1,
"_id" : 0,
"h" : {
"$hour" : "$created"
},
"m" : {
"$minute" : "$created"
},
"s" : {
"$second" : "$created"
},
"ml" : {
"$millisecond" : "$created"
}
}
};
var proj2 = {
"$project" : {
"created" : {
"$subtract" : [ "$created", {
"$add" : [ "$ml", {
"$multiply" : [ "$s", 1000 ]
}, {
"$multiply" : [ "$m", 60, 1000 ]
}, {
"$multiply" : [ "$h", 60, 60, 1000 ]
} ]
} ]
}
}
};
var group1 = {
$group : {
_id : "$created",
count : {
$sum : 1
}
}
};
var group2 = {
$group : {
_id : 0,
origin : {
$push : "$$ROOT"
},
maxDate : {
$max : "$_id"
}
}
};
var step = 24 * 60 * 60 * 1000; // milliseconds of one day
var project3 = {
$project : {
origin : 1,
extents : {
$map : {
"input" : [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
"as" : "e",
"in" : {
_id : {
$subtract : [ "$maxDate", {
$multiply : [ step, "$$e"]
}]
},
count : {
$add : [0]
}
}
}
}
}
};
var project4 = {
$project : {
_id : 0,
values : {
$setUnion : [ "$origin", "$extents"]
}
}
};
var unwind1 = {
$unwind : "$values"
};
var group3 = {
$group : {
_id : "$values._id",
count : {
$max : "$values.count"
}
}
};
db.product.aggregate([ proj1, proj2, group1, group2, project3, project4,
unwind1, group3, {
$sort : {
_id : 1
}
} ]);
<小时>
我想在申请结束时填写以下内容供您参考:
I would like to fill the missing part at application end something like this for your reference:
function sortResult(x, y) {
var t1 = x._id.getTime();
var t2 = y._id.getTime();
if (t1 < t2) {
return -1;
} else if (t1 == t2) {
return 0;
} else {
return 1;
}
}
var result = db.product.aggregate();
var endDateMilliseconds = result[result.length - 1]._id.getTime();
var step = 24 * 60 * 60 * 1000; // milliseconds of one day
var map = {};
for (var i in result) {
map[ result[i]._id.getTime() ] = result[i];
}
for (var ms = endDateMilliseconds, x = 1; x < 30; x++) {
ms -= step;
if ( ! ( ms in map ) ) {
map[ms] = {_id : new Date(ms), count : 0};
}
}
var finalResult = [];
for (var x in map) {
finalResult.push(map[x]);
}
finalResult.sort(sortResult);
printjson(finalResult);
这篇关于MongoDB 聚合填补缺失的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
