查找在接下来的30天内是否有人在mongo过生日 [英] Find whether someone got a birthday in the next 30 days with mongo
问题描述
假设我们有一个用户集合，每个用户的生日都是BSON类型日期格式.
Let's say that we got a collection of users, each with a birthday in BSON type date format.
我们如何运行查询以找出未来30天内所有生日的用户?
How can we run a query to find out all the users who got a birthday in the next 30 days ?
推荐答案
聚合框架绝对是正确的方法-服务器上需要JS的任何内容都是性能问题，而聚合全部在本地代码的服务器.
Aggregation framework is definitely the right approach - anything that requires JS on the server is a performance problem, while aggregations all run in the server in native code.
虽然可以将生日转换为即将到来的生日的日期，然后进行范围查询，但我更愿意自己做些不同的事情.
While it's possible to transform the birthday into dates of upcoming birthdays and then do a range query, I prefer to do it a slightly different way myself.
唯一的先决条件是计算一年中的今天". 有多种方法可以使用多种语言，因此可以在应用程序中完成层在调用聚合之前，将此数字传递给它.我本来要称呼我的todayDayOfYear，但我意识到您可以让聚合框架根据今天来计算它，因此唯一的变量将是今天的日期.
The only "prerequisite is to compute today's day of the year". There are ways to do this in various languages, so this could be done in the application layer before calling the aggregation, passing this number to it. I was going to call mine todayDayOfYear but I realized you can let aggregation framework figure it out based on today, so the only variable will be today's date.
var today=new Date();
我假设文档中包含姓名和生日，并针对变化进行适当调整
I'm assuming document that includes name and birthday, adjust appropriately for variations
var p1 = { "$project" : {
"_id" : 0,
"name" : 1,
"birthday" : 1,
"todayDayOfYear" : { "$dayOfYear" : today },
"dayOfYear" : { "$dayOfYear" : "$birthday"}
} };
现在，预测从今天到下一个生日的天数:
Now, project how many days from today till their next birthday:
var p2 = { "$project" : {
"name" : 1,
"birthday" : 1,
"daysTillBirthday" : { "$subtract" : [
{ "$add" : [
"$dayOfYear",
{ "$cond" : [{"$lt":["$dayOfYear","$todayDayOfYear"]},365,0 ] }
] },
"$todayDayOfYear"
] }
} };
排除所有在所需范围内的内容:
Exclude all but the ones within desired range:
var m = { "$match" : { "daysTillBirthday" : { "$lt" : 31 } } };
现在使用以下命令运行聚合:
Now run the aggregation with:
db.collection.aggregate( p1, p2, m );
为生日在30天内的所有幸运人员获取姓名，生日和生日的天数列表.
to get back a list of names, birthdays and days till birthday for all lucky folks whose birthday is within 30 days.
编辑
@ Sean999遇到了一个有趣的极端情况-在2月28日之后的a年出生的人的计算将减少一倍.以下是可以正确调整的聚合:
@Sean999 caught an interesting edge case - people who were born in a leap year after February 28th will have their calculation off by one. The following is aggregation that correctly adjusts for that:
var p1 = { "$project" : {
"_id" : 0,
"name" : 1,
"birthday" : 1,
"todayDayOfYear" : { "$dayOfYear" : ISODate("2014-03-09T12:30:51.515Z") },
"leap" : { "$or" : [
{ "$eq" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 400 ] } ] },
{ "$and" : [
{ "$eq" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 4 ] } ] },
{ "$ne" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 100 ] } ] } ] } ] },
"dayOfYear" : { "$dayOfYear" : "$birthday" } } };
var p1p = { "$project" : {
"name" : 1,
"birthday" : 1,
"todayDayOfYear" : 1,
"dayOfYear" : { "$subtract" : [
"$dayOfYear",
{ "$cond" : [ { "$and" : [ "$leap", { "$gt" : [ "$dayOfYear", 59 ] } ] }, 1, 0 ] } ] }
}
}
p2和m与上面相同.
测试输入:
db.birthdays.find({},{name:1,birthday:1,_id:0})
{ "name" : "Ally", "birthday" : ISODate("1975-06-12T00:00:00Z") }
{ "name" : "Ben", "birthday" : ISODate("1968-04-03T00:00:00Z") }
{ "name" : "Mark", "birthday" : ISODate("1949-12-23T00:00:00Z") }
{ "name" : "Paul", "birthday" : ISODate("2014-03-04T15:59:05.374Z") }
{ "name" : "Paul", "birthday" : ISODate("2011-02-07T00:00:00Z") }
{ "name" : "Sean", "birthday" : ISODate("2004-01-31T00:00:00Z") }
{ "name" : "Tim", "birthday" : ISODate("2008-02-28T00:00:00Z") }
{ "name" : "Sandy", "birthday" : ISODate("2005-01-31T00:00:00Z") }
{ "name" : "Toni", "birthday" : ISODate("2009-02-28T00:00:00Z") }
{ "name" : "Sam", "birthday" : ISODate("2005-03-31T00:00:00Z") }
{ "name" : "Max", "birthday" : ISODate("2004-03-31T00:00:00Z") }
{ "name" : "Jen", "birthday" : ISODate("1971-04-03T00:00:00Z") }
{ "name" : "Ellen", "birthday" : ISODate("1996-02-28T00:00:00Z") }
{ "name" : "Fanny", "birthday" : ISODate("1996-02-29T00:00:00Z") }
{ "name" : "Gene", "birthday" : ISODate("1996-03-01T00:00:00Z") }
{ "name" : "Edgar", "birthday" : ISODate("1997-02-28T00:00:00Z") }
{ "name" : "George", "birthday" : ISODate("1997-03-01T00:00:00Z") }
输出:
db.birthdays.aggregate( p1, p1p, p2, {$sort:{daysTillBirthday:1}});
{ "name" : "Sam", "birthday" : ISODate("2005-03-31T00:00:00Z"), "daysTillBirthday" : 22 }
{ "name" : "Max", "birthday" : ISODate("2004-03-31T00:00:00Z"), "daysTillBirthday" : 22 }
{ "name" : "Ben", "birthday" : ISODate("1968-04-03T00:00:00Z"), "daysTillBirthday" : 25 }
{ "name" : "Jen", "birthday" : ISODate("1971-04-03T00:00:00Z"), "daysTillBirthday" : 25 }
{ "name" : "Ally", "birthday" : ISODate("1975-06-12T00:00:00Z"), "daysTillBirthday" : 95 }
{ "name" : "Mark", "birthday" : ISODate("1949-12-23T00:00:00Z"), "daysTillBirthday" : 289 }
{ "name" : "Sean", "birthday" : ISODate("2004-01-31T00:00:00Z"), "daysTillBirthday" : 328 }
{ "name" : "Sandy", "birthday" : ISODate("2005-01-31T00:00:00Z"), "daysTillBirthday" : 328 }
{ "name" : "Paul", "birthday" : ISODate("2011-02-07T00:00:00Z"), "daysTillBirthday" : 335 }
{ "name" : "Tim", "birthday" : ISODate("2008-02-28T00:00:00Z"), "daysTillBirthday" : 356 }
{ "name" : "Toni", "birthday" : ISODate("2009-02-28T00:00:00Z"), "daysTillBirthday" : 356 }
{ "name" : "Ellen", "birthday" : ISODate("1996-02-28T00:00:00Z"), "daysTillBirthday" : 356 }
{ "name" : "Fanny", "birthday" : ISODate("1996-02-29T00:00:00Z"), "daysTillBirthday" : 356 }
{ "name" : "Edgar", "birthday" : ISODate("1997-02-28T00:00:00Z"), "daysTillBirthday" : 356 }
{ "name" : "Gene", "birthday" : ISODate("1996-03-01T00:00:00Z"), "daysTillBirthday" : 357 }
{ "name" : "George", "birthday" : ISODate("1997-03-01T00:00:00Z"), "daysTillBirthday" : 357 }
{ "name" : "Paul", "birthday" : ISODate("2014-03-04T15:59:05.374Z"), "daysTillBirthday" : 360 }
您可以看到，生日相同的人现在到生日的天数相同，无论他们是否出生于a年.现在可以对设计的截止点执行匹配步骤.
You can see that people with same birthday now have same number of days till birthday whether they were born on a leap year or not. Match step can now be performed for the cut-off designed.
编辑
从3.5.11版本开始，聚合管道中存在多个日期操作表达式，这些表达式使编写起来非常简单.特别是， $ dateFromParts表达式允许从各个部分构造日期，从而允许这种聚合:
As of version 3.5.11 there are several date manipulation expressions in aggregation pipeline that make this significantly simpler to write. In particular, the $dateFromParts expression allows constructing a date from various parts, allowing this aggregation:
var today = new Date();
var a1 = {$addFields:{
today:{$dateFromParts:{year:{$year:today},month:{$month:today},day:{$dayOfMonth:today}}},
birthdayThisYear:{$dateFromParts:{year:{$year:today}, month:{$month:"$birthday"}, day:{$dayOfMonth:"$birthday"}}},
birthdayNextYear:{$dateFromParts:{year:{$add:[1,{$year:today}]}, month:{$month:"$birthday"}, day:{$dayOfMonth:"$birthday"}}}
}};
var a2 = {$addFields:{
nextBirthday:{$cond:[ {$gte:[ "$birthdayThisYear", "$today"]}, "$birthdayThisYear", "$birthdayNextYear"]}
}};
var p1 = {$project:{
name:1,
birthday:1,
daysTillNextBirthday:{$divide:[
{$subtract:["$nextBirthday", "$today"]},
24*60*60*1000 /* milliseconds in a day */
]},
_id:0
}};
var s1 = {$sort:{daysTillNextBirthday:1}};
db.birthdays.aggregate([ a1, a2, p1, s1 ]);
您可以将"today"设置为任何日期(是否为ap年)，然后可以看到现在的计算总是正确且简单得多.
You can set "today" to any date (leap year or not) and see that the calculation is now always correct and much simpler.
这篇关于查找在接下来的30天内是否有人在mongo过生日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
