mongodb汇总查询的排序结果和显示日期名称

本文介绍了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 } } */ ]);