mongo聚合函数 aggregate 类似mysql的group
若在java中使用,可下载此文档 https://download.csdn.net/download/xu180/11102227
认证概况 按时间分类 "%Y%m%d%H"
db.T_LOGINLOG.aggregate(
[{ "$match" : { "$and" : [{ "loginTime" : { "$gte" : { "$date" : 1552492800000 } } }, { "loginTime" : { "$lte" : { "$date" : 1552579199000 } } }, { "loginResult" : { "$eq" : 1 } }] } },
{ "$group" : { "_id" : { "$dateToString" : { "date" : { "$add" : ["$loginTime", 28800000] }, "format" : "%Y%m%d%H" } }, "count" : { "$sum" : 1 } } },
{ "$project" : { "dformat" : "$_id", "count" : "$count", "_id" : 0 } }, { "$sort" : { "_id" : 1 } }
]
);
("$add" : ["$loginTime", 28800000] // mongo少八小时)
账号概况 按时间分类
db.T_AUDITLOG.aggregate(
[{ "$match" : { "$and" : [{ "domain" : { "$eq" : "1" } }, { "opType" : { "$eq" : 1 } }, { "opTime" : { "$gte" : { "$date" : 1551369600000 } } },
{ "opTime" : { "$lte" : { "$date" : 1554047999000 } } }] } },
{ "$group" : { "_id" : { "$dateToString" : { "date" : { "$add" : ["$opTime", 28800000] }, "format" : "%Y%m%d" } }, "count" : { "$sum" : 1 } } },
{ "$project" : { "dformat" : "$_id", "count" : "$count", "_id" : 0 } },
{ "$sort" : { "_id" : 1 } }]
);
认证方式 按字段分类
db.T_LOGINLOG.aggregate(
[{ "$match" : { "$and" : [{ "loginTime" : { "$gte" : { "$date" : 1552233600000 } } }, { "loginTime" : { "$lte" : { "$date" : 1552838399000 } } }] } },
{ "$group" : { "_id" : "$loginMode", "count" : { "$sum" : 1 } } },
{ "$project" : { "groupName" : "$_id", "num" : "$count", "_id" : 0 } },
{ "$sort" : { "_id" : 1 } }]
);
大屏展示 累积认证 case when
db.T_LOGINLOG.aggregate(
[{ "$match" : { "$and" : [{ "loginTime" : { "$gte" : { "$date" : 1546272000000 } } },
{ "loginTime" : { "$lte" : { "$date" : 1575215999000 } } }] } },
{ "$group" : { "success" : { "$sum" : { "$cond" : [{ "$eq" : ["$loginResult", 1] }, 1, 0] } }, "fail" : { "$sum" : { "$cond" : [{ "$eq" : ["$loginResult", 0] }, 1, 0] } }, "_id" : "$loginName", "total" : { "$sum" : 1 } } },
{ "$project" : { "fail" : "$fail", "total" : "$total", "successRate" : { "$divide" : ["$success", "$total"] }, "success" : "$success", "loginName" : "$_id", "_id" : 0 } },
{ "$sort" : { "successRate" : -1 } }, { "$skip" : 0 }, { "$limit" : 6 }]
);
mongo 说明
https://www.cnblogs.com/suoning/p/6759367.html
https://stackoverflow.com/questions/5168904/group-by-dates-in-mongodb