mongodb汇总查询的排序结果和显示日期名称 [英] mongodb sort result of aggregate query and display day name
本文介绍了mongodb汇总查询的排序结果和显示日期名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下汇总查询
db.orders.aggregate(
{ "$project" :
{ _id : 0, "dateDay" :
{"$concat" : [
{"$substr" : [{"$dayOfMonth" : "$timestamp"}, 0, 2]}, "-",
{"$substr" : [{"$month" : "$timestamp"}, 0, 2]}, "-",
{"$substr" : [{"$year" : "$timestamp"}, 0, 4]}
] }
}
},
{ "$group" :
{ "_id" : "$dateDay", "Count" : { "$sum" : 1 } }
},
{ $sort : { "Count : 1 } }
);
返回类似以下内容的
{
"_id" : "3-9-2014",
"Count" : 81
},
{
"_id" : "14-12-2014",
"Count" : 10
},
{
"_id" : "2-9-2014",
"Count" : 98
},
{
"_id" : "1-9-2014",
"Count" : 118
},
{
"_id" : "20-8-2014",
"Count" : 79
},
{
"_id" : "8-6-2015",
"Count" : 128
}
],
"ok" : 1
}
如何按年,月,日的顺序对结果进行排序?
How do I sort the result by order of Day-Month-Year?
{$ sort:{"Count:1}}-给我按Count进行排序,但我喜欢MySQL查询:
{ $sort : { "Count : 1 } } - gives me the sort by Count, but I like something like the MySQL query:
mysql> SELECT DATE(stamp), DAYNAME(stamp), COUNT(*) FROM user_orders WHERE DATE(stamp) >= DATE('2015-05-29') AND DATE(stamp) <= DATE('2015-06-19') GROUP BY DATE(stamp);
+-------------+----------------+----------+
| DATE(stamp) | DAYNAME(stamp) | COUNT(*) |
+-------------+----------------+----------+
| 2015-05-29 | Friday | 63 |
| 2015-05-30 | Saturday | 9 |
| 2015-05-31 | Sunday | 11 |
| 2015-06-01 | Monday | 94 |
| 2015-06-02 | Tuesday | 92 |
| 2015-06-03 | Wednesday | 109 |
| 2015-06-04 | Thursday | 89 |
| 2015-06-05 | Friday | 68 |
| 2015-06-06 | Saturday | 9 |
| 2015-06-07 | Sunday | 11 |
| 2015-06-08 | Monday | 125 |
| 2015-06-09 | Tuesday | 101 |
| 2015-06-10 | Wednesday | 97 |
| 2015-06-11 | Thursday | 110 |
| 2015-06-12 | Friday | 85 |
| 2015-06-13 | Saturday | 13 |
| 2015-06-14 | Sunday | 6 |
| 2015-06-15 | Monday | 113 |
| 2015-06-16 | Tuesday | 109 |
| 2015-06-17 | Wednesday | 125 |
| 2015-06-18 | Thursday | 77 |
| 2015-06-19 | Friday | 81 |
+-------------+----------------+----------+
22 rows in set (0.05 sec)
任何建议,不胜感激
推荐答案
您可以将 $group
管道步骤,该步骤将日期聚合运算符作为组键表达式.然后,以下 $sort
运算符管道将按照Day-Month-Year的顺序对这些键进行排序,如本例所示:
Instead of having the first pipeline stage as the $project
operator step, you could have the $group
pipeline step that has the date aggregation operators as the group key expression. The following $sort
operator pipeline would then sort on those keys in the order Day-Month-Year, like in this example:
db.orders.aggregate([
{
"$group": {
"_id": {
"day": { "$dayOfMonth" : "$timestamp" },
"month": { "$month" : "$timestamp" },
"year": { "$year" : "$timestamp" }
},
"Count": { "$sum" : 1 }
}
},
{
"$sort": {
"_id.year": 1,
"_id.month": 1,
"_id.day": 1
}
},
{
"$project": {
"_id": 0,
"dateDay": {
"$concat": [
{"$substr" : [ "$_id.day", 0, 2]}, "-",
{"$substr" : [ "$_id.month", 0, 2]}, "-",
{"$substr" : [ "$_id.year", 0, 4]}
]
},
"Count": 1
}
}/*,
{ $sort : { "Count : 1 } }
*/
]);
这篇关于mongodb汇总查询的排序结果和显示日期名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文