需要对下面的集合根据LastUpdate按天分组累加TranslateFields值。
|
1
2
3
4
5
6
|
rs_test:SECONDARY> db.new_result.find();
{
"_id"
: ObjectId(
"57fb0756e31f84a56ed41889"
),
"LastUpdate"
: ISODate(
"2016-09-02T01:35:02.471Z"
),
"TranslateFields"
: 9 }
{
"_id"
: ObjectId(
"57fb0756e31f84a56ed4188a"
),
"LastUpdate"
: ISODate(
"2016-09-05T11:13:28.344Z"
),
"TranslateFields"
: 10 }
{
"_id"
: ObjectId(
"57fb0756e31f84a56ed4188b"
),
"LastUpdate"
: ISODate(
"2016-09-05T09:26:41.016Z"
),
"TranslateFields"
: 33 }
{
"_id"
: ObjectId(
"57fb0756e31f84a56ed4188c"
),
"LastUpdate"
: ISODate(
"2016-09-02T13:34:50.114Z"
),
"TranslateFields"
: 12 }
{
"_id"
: ObjectId(
"57fb0756e31f84a56ed4188d"
),
"LastUpdate"
: ISODate(
"2016-08-26T03:49:52.369Z"
),
"TranslateFields"
: 17 }
|
如果是在SQL Server里，分组统计应该像这样写：
|
1
2
3
4
|
SELECT
CONVERT
(
varchar
,LastUpdate,112),
SUM
(TranslateFields)
FROM
dbo.new_result
GROUP
BY
CONVERT
(
varchar
,LastUpdate,112)
ORDER
BY
1;
|
那么在MongoDB里，有3种聚合方法：group、aggregate和mapReduce
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
//2.6版本aggregate方法
db.new_result.aggregate(
{
$
group
: {
_id : {
year
: { $
year
:
"$LastUpdate"
},
month
: { $
month
:
"$LastUpdate"
},
day
: { $dayOfMonth:
"$LastUpdate"
} },
totalTime: { $
sum
:
"$TranslateFields"
}
}
},
{
$sort : {
"_id.year"
: 1,
"_id.month"
: 1,
"_id.day"
: 1
}
}
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
//3.0版本aggregate方法
db.new_result.aggregate(
{
$
group
: {
yearMonthDay: { $dateToString: { format:
"%Y-%m-%d"
,
date
:
"$LastUpdate"
} },
totalTime: { $
sum
:
"$TranslateFields"
}
}
},
{
$sort : {
"yearMonthDay"
: 1
}
}
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
//
group
方法
db.new_result.
group
({
keyf :
function
(doc){
var
date
= new
Date
(doc.LastUpdate);
var dateKey =
""
+
date
.getFullYear()+
"-"
+(
date
.getMonth()+1)+
"-"
+
date
.getDate();
return
{
'day'
:dateKey};
},
initial : {
"time"
:0},
reduce :
function
(doc, prev){
prev.
time
+= doc.TranslateFields;
},
finalize :
function
Finalize(
out
) {
return
out
;
}
}
});
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
//先存为日期
//1
db.tmp_result.find({
"value.Status"
:3},{
"value.TranslateFields"
:1,
"value.LastUpdate"
:1}).forEach(
function
(item){
db.new_result.save({
"LastUpdate"
:item.value.LastUpdate.getFullYear()+
"-"
+
(item.value.LastUpdate.getMonth()+1)+
"-"
+
item.value.LastUpdate.getDate(),
"TranslateFields"
:item.value.TranslateFields});
}
)
//2
db.new_result.aggregate(
{
$
group
: {
_id:
"$LastUpdate"
,
totalTime: { $
sum
:
"$TranslateFields"
}
}
}
,{
"$sort"
:{
"_id"
:1}}
)
|
对于aggregate方法，最好在$group之前$match，减少数据量，如果过滤的键上有索引，查询也会走索引。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
db.TranslateTicket.aggregate(
{
"$match"
:
{
"LastUpdate"
: {
"$gte"
:ISODate(
"2016-06-19T00:00:00.000Z"
),
"$lt"
:ISODate(
"2016-09-19T00:00:00.000Z"
)},
"Status"
: 3
}
},
{
"$group"
:
{
_id : {
month
: { $
month
:
"$LastUpdate"
},
day
: { $dayOfMonth:
"$LastUpdate"
},
year
: { $
year
:
"$LastUpdate"
} },
totalTime: { $
sum
:
"$CharactersCount"
}
}
},
{
"$sort"
:
{
"_id.year"
:1,
"_id.month"
:1,
"_id.day"
:1
}
}
)
|
这种情况，最好在创建如下索引：
|
1
|
db.TranslateTicket.createIndex({“LastUpdate”:1,”Status”:1},{background:1})
|
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1861003 ，如需转载请自行联系原作者
