在 mongodb 中按日期分组 [英] group by dates in mongodb
问题描述
我正在从事一个项目，我正在跟踪某个主题的点击次数.
我正在使用 mongodb，我必须按日期对点击次数进行分组(我想对 15 天的数据进行分组).
我在 mongodb 中有以下格式的数据存储
<代码>{"_id" : ObjectId("4d663451d1e7242c4b68e000"),"date" : "2010 年 12 月 27 日星期一 18:51:22 GMT+0000 (UTC)","主题": "abc",时间":18:51:22"}{"_id" : ObjectId("4d6634514cb5cb2c4b69e000"),"date" : "2010 年 12 月 27 日星期一 18:51:23 GMT+0000 (UTC)","topic": "bce",时间":18:51:23"}我想对主题的点击次数进行分组:abc 按天(15 天)...我知道如何分组，但如何按存储在我的数据库中的日期分组
我正在寻找以下格式的结果
<预><代码>[{"date" : "日志中的日期",点击":9},{"date" : "日志中的日期",点击":19},]我已经编写了代码，但只有当日期在字符串中时它才能工作(代码在这里 http://pastebin.com/2wm1n1ix)...请指导我如何分组
使用 Mongo 聚合框架的新答案
在提出并回答了这个问题之后，10gen 发布了带有聚合框架的 Mongodb 2.2 版，这是现在进行此类查询的更好方法.这个查询有点挑战性，因为你想按日期分组，并且存储的值是时间戳，所以你必须做一些事情来将时间戳转换为匹配的日期.出于示例的目的，我将只编写一个获取正确计数的查询.
db.col.aggregate({ $group: { _id: { $dayOfYear: "$date"},点击:{ $sum: 1 } } })这将返回如下内容:
[{_id":144，点击":165},{_id":275，点击":12}]您需要使用 $match 将查询限制在您感兴趣的日期范围内，并使用 $project 将 _id 重命名为 <代码>日期.如何将一年中的某一天转换回日期作为练习留给读者.:-)
10gen 有一个方便的 SQL 到 Mongo 聚合转换图表值得书签.还有一篇关于日期聚合运算符的特定文章.
变得更发烧友，您可以使用:
db.col.aggregate([{ $组:{_ID: {$添加:[{ $dayOfYear: "$date"},{ $乘法:[400, {$year: "$date"}]}]},点击:{ $sum: 1 },第一个:{$min:$date"}}},{ $sort: {_id: -1} },{ $limit: 15 },{ $project: { date: "$first", click: 1, _id: 0} }])这将为您提供最近的 15 天，并在 date 字段中返回每天内的一些日期时间.例如:
[{点击":431，日期":ISODate(2013-05-11T02:33:45.526Z")},{点击":702，日期":ISODate(2013-05-08T02:11:00.503Z")},...{点击":814，日期":ISODate(2013-04-25T00:41:45.046Z")}]I am working on a project in which I am tracking number of clicks on a topic.
I am using mongodb and I have to group number of click by date( i want to group data for 15 days).
I am having data store in following format in mongodb
{
"_id" : ObjectId("4d663451d1e7242c4b68e000"),
"date" : "Mon Dec 27 2010 18:51:22 GMT+0000 (UTC)",
"topic" : "abc",
"time" : "18:51:22"
}
{
"_id" : ObjectId("4d6634514cb5cb2c4b69e000"),
"date" : "Mon Dec 27 2010 18:51:23 GMT+0000 (UTC)",
"topic" : "bce",
"time" : "18:51:23"
}
i want to group number of clicks on topic:abc by days(for 15 days)..i know how to group that but how can I group by date which are stored in my database
I am looking for result in following format
[
{
"date" : "date in log",
"click" : 9
},
{
"date" : "date in log",
"click" : 19
},
]
I have written code but it will work only if date are in string (code is here http://pastebin.com/2wm1n1ix) ...please guide me how do I group it
New answer using Mongo aggregation framework
After this question was asked and answered, 10gen released Mongodb version 2.2 with an aggregation framework, which is now the better way to do this sort of query. This query is a little challenging because you want to group by date and the values stored are timestamps, so you have to do something to convert the timestamps to dates that match. For the purposes of example I will just write a query that gets the right counts.
db.col.aggregate(
{ $group: { _id: { $dayOfYear: "$date"},
click: { $sum: 1 } } }
)
This will return something like:
[
{
"_id" : 144,
"click" : 165
},
{
"_id" : 275,
"click" : 12
}
]
You need to use $match to limit the query to the date range you are interested in and $project to rename _id to date. How you convert the day of year back to a date is left as an exercise for the reader. :-)
10gen has a handy SQL to Mongo Aggregation conversion chart worth bookmarking. There is also a specific article on date aggregation operators.
Getting a little fancier, you can use:
db.col.aggregate([
{ $group: {
_id: {
$add: [
{ $dayOfYear: "$date"},
{ $multiply:
[400, {$year: "$date"}]
}
]},
click: { $sum: 1 },
first: {$min: "$date"}
}
},
{ $sort: {_id: -1} },
{ $limit: 15 },
{ $project: { date: "$first", click: 1, _id: 0} }
])
which will get you the latest 15 days and return some datetime within each day in the date field. For example:
[
{
"click" : 431,
"date" : ISODate("2013-05-11T02:33:45.526Z")
},
{
"click" : 702,
"date" : ISODate("2013-05-08T02:11:00.503Z")
},
...
{
"click" : 814,
"date" : ISODate("2013-04-25T00:41:45.046Z")
}
]
这篇关于在 mongodb 中按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
