时间戳

首页 > 解决方案 > 如何从Mongodb中的生日日期计算平均年龄？

mongodb - 如何从Mongodb中的生日日期计算平均年龄？

问题描述

我有一组客户，我需要计算每个性别的客户的平均年龄。

客户资料：

{"Id_Cust":"4145","firstName":"Albade","lastName":"Maazou","gender":"female","birthday":"21/03/1981","creationDate":"2010-03-13T02:10:23.099+0000","locationIP":"41.138.53.138","browserUsed":"Internet Explorer","place":"1263"},
{"Id_Cust":"5296","firstName":"Rafael","lastName":"Oliveira","gender":"male","birthday":"08/06/1987","creationDate":"2010-02-05T06:08:26.512+0000","locationIP":"192.160.128.234","browserUsed":"Internet Explorer","place":"574"},
{"Id_Cust":"6192","firstName":"Abdul Rahman","lastName":"Budjana","gender":"male","birthday":"26/01/1990","creationDate":"2010-02-08T20:32:23.706+0000","locationIP":"27.112.77.226","browserUsed":"Firefox","place":"640"},
{"Id_Cust":"6660","firstName":"Jerzy","lastName":"Ciesla","gender":"female","birthday":"28/12/1982","creationDate":"2010-03-23T11:02:30.998+0000","locationIP":"31.192.218.139","browserUsed":"Firefox","place":"1285"},
{"Id_Cust":"8491","firstName":"Chen","lastName":"Xu","gender":"female","birthday":"27/02/1985","creationDate":"2010-03-31T15:59:11.072+0000","locationIP":"1.1.7.155","browserUsed":"Chrome","place":"437"},
{"Id_Cust":"8664","firstName":"Andrej","lastName":"Benedejcic","gender":"female","birthday":"31/08/1988","creationDate":"2010-03-24T03:12:59.456+0000","locationIP":"90.157.195.42","browserUsed":"Firefox","place":"549"},
{"Id_Cust":"10027","firstName":"Ning","lastName":"Chen","gender":"female","birthday":"08/12/1982","creationDate":"2010-03-27T12:58:05.517+0000","locationIP":"1.2.9.86","browserUsed":"Firefox","place":"332"},
{"Id_Cust":"10664","firstName":"Emperor of Brazil","lastName":"Barreto","gender":"female","birthday":"02/02/1982","creationDate":"2010-03-04T09:43:10.090+0000","locationIP":"192.111.230.73","browserUsed":"Internet Explorer","place":"576"},
{"Id_Cust":"2199023256013","firstName":"Eli","lastName":"Peretz","gender":"female","birthday":"18/01/1989","creationDate":"2010-04-28T05:16:53.514+0000","locationIP":"193.194.1.47","browserUsed":"Internet Explorer","place":"1227"},

结果必须是这样的：

{ "_id" : "female" , "count" : 35}
{ "_id" : "male" , "count" : 35}

注意：35 只是一个例子。

标签： mongodbmongodb-queryaggregation-framework

解决方案

纯粹根据从生日到今天的岁数计算年龄，以下应该这样做：

db.tempColl.aggregate(
   [
     {
       $group:
         {
           _id: "$gender",
           count: { $avg: { $subtract: [ {$year: new Date()}, {$toInt:{$substr: ["$birthday", 6, -1]}} ] } }
         }
     }
   ]
);

这给出如下输出：

{ "_id" : "male", "count" : 30.5 }
{ "_id" : "female", "count" : 34.857142857142854 }

注意：如果需要绝对数字，可以使用$ceil运算符。这也假设您的生日值将采用给定格式 dd/mm/yyyy。tempColl是您的收藏。

推荐阅读