db.audio_tb_gmvcs_police_situation.aggregate([
{$match:{
"handlers.org_path":{$regex:"^/44010000/"},
bjsj: {
$gte: ISODate("2018-01-16T00:00:00+08:00"),
$lte: ISODate("2018-12-21T00:00:00+08:00")}
}
}
]).skip(0).limit(2).sort({"bjsj" : -1,"handlers.org_path" : 1 }).explain("allPlansExecution")
###################################part1
--part1 个人
db.stat_tb_gmvcs_daily_usage.aggregate([
{
$match: {
uid:"1",
date: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")}
}
},
{
$group : {_id :null, spzsc : {$sum : "$video_duration_total"}, ypzsc : {$sum : "$audio_duration_total"}, tpzs : {$sum : "$pic_only_count"}}
},
{
$project :{"spzsc":1,"ypzsc":1,"tpzs":1}
}
]).explain()
--part1 部门
db.stat_tb_gmvcs_daily_count.aggregate([
{$match:{
date: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")}
,org_path:{$regex:"^/44010000/"}
}},
{$group:{
_id:null,
spzsc:{$sum:"$count_video_duration"},
ypzsc:{$sum:"$count_audio_duration"},
tpzs:{$sum:"$count_pic_file"}
}}
]).explain()
##################################在线时长
--个人(日)
db.stat_tb_gmvcs_daily_usage.aggregate([
{
$match: {
uid:"1",
date: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")}
}
}
]).explain()
--个人(小时)
db.audio_tb_gmvcs_base_file.aggregate([
{ $match : {
uid : "GMU4403030020180804020041ffbfffffe" ,
start_time: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")},
type : 0}
} ,
{ $group : { "_id" : "$uid" , "spzsc" : { "$sum" : "$duration"}}
},
{$project :{spzsc:1}}
])
.explain()
--部门(日)
db.stat_tb_gmvcs_daily_count.aggregate([
{$match:{
date:{$gte:ISODate("2018-09-01T00:00:00.000+08:00"), $lte:ISODate("2018-09-10T00:00:00.000+08:00")}
,org_path:{$regex:"^/44010000/"}
}},
{$project :{date:1,count_video_duration:1,day : { $dayOfYear : ["$date"]}}},
{$group:{
_id:"$day",
date : {$first: "$date"},
ypzsc:{$sum:"$count_video_duration"}
}}
], {explain:true})
--部门(月)
db.stat_tb_gmvcs_daily_count.aggregate([
{$match:{
date:{$gte:ISODate("2018-09-01T00:00:00.000+08:00"), $lte:ISODate("2018-09-10T00:00:00.000+08:00")}
,org_path:{$regex:"^/44010000/"}
}},
{$project :{date:1,count_video_duration:1,day : { $month : ["$date"]}}},
{$group:{
_id:"$day",
date : {$first: "$date"},
ypzsc:{$sum:"$count_video_duration"}
}}
], {explain:true})
################################首页及时导入率
--个人
db.stat_tb_gmvcs_daily_usage.aggregate([
{
$match: {
uid:"1",
date: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")}
},
{$group:{
_id:null,
after24h:{$sum:"$video_mtgs_24h_count"},
videoCount:{$sum:"$video_count"}
}}
}
]).explain()
--部门
db.stat_tb_gmvcs_daily_count.aggregate([
{$match:{
date:{
$gte:ISODate("2018-09-01T00:00:00.000+08:00"),
$lte:ISODate("2018-09-10T00:00:00.000+08:00")}
,org_path:{$regex:"^/44010000/"}
}},
{$group:{
_id:null,
after24h:{$sum:"$video_mtgs_24h_count"},
videoCount:{$sum:"$count_video_file"}
}}
]).explain()
################################首页展示个人排名
db.stat_tb_gmvcs_daily_usage.aggregate([
{
$match: {
uid:"1",
date: {
$gte: ISODate("2018-10-16T00:00:00+08:00"),
$lte: ISODate("2018-10-27T00:00:00+08:00")}
},
{$group:{
_id:"$uid",
uid : {$first: "$uid"},
userCode : {$first: "$user_code"},
userName : {$first: "$user_name"},
spzsc:{$sum:"$video_count"},
ypzsc:{$sum:"$audio_only_count"},
tpzs:{$sum:"$pic_only_count"}
}}
}
]).sort({spzsc:-1})
.explain()
################################首页展示关联情况,关联率,考核率
--个人
db.stat_tb_gmvcs_daily_usage.aggregate([
{ "$match" : { "uid" : "GMU0000000020181019185145ffffffffd" ,
date: {
$gte: ISODate("2018-10-19T00:00:00+08:00"),
$lte: ISODate("2018-10-19T23:59:59+08:00")}
{ "$group" : {
"_id" : null ,
"psCount" : { "$sum" : "$ps_count"} ,
"cmCount" : { "$sum" : "$cm_count"} ,
"psMatchCount" : { "$sum" : "$ps_match_count"} ,
"cmMatchCount" : { "$sum" : "$cm_match_count"} ,
"accidentCount" : { "$sum" : "$accident_count"} ,
"forceCount" : { "$sum" : "$force_count"} ,
"surveilCount" : { "$sum" : "$surveil_count"} ,
"violationCOunt" : { "$sum" : "$violation_count"} ,
"accidentMatchCount" : { "$sum" : "$accident_match_count"} ,
"forceMatchCount" : { "$sum" : "$force_match_count"} ,
"surveilMatchCount" : { "$sum" : "$surveil_match_count"} ,
"violationMatchCount" : { "$sum" : "$violation_match_count"} ,
"violationSpotChecksCount" : { "$sum" : "$violation_spot_checks_count"} ,
"violationPassCount" : { "$sum" : "$violation_pass_count"} ,
"surveilSpotChecksCount" : { "$sum" : "$surveil_spot_checks_count"} ,
"surveilPassCount" : { "$sum" : "$surveil_pass_count"} ,
"forceSpotChecksCount" : { "$sum" : "$force_spot_checks_count"} ,
"forcePassCount" : { "$sum" : "$force_pass_count"} ,
"accidentSpotChecksCount" : { "$sum" : "$accident_spot_checks_count"} ,
"accidentPassCount" : { "$sum" : "$accident_pass_count"} ,
"cmSpotChecksCount" : { "$sum" : "$cm_spot_checks_count"} ,
"cmPassCount" : { "$sum" : "$cm_pass_count"} ,
"psSpotChecksCount" : { "$sum" : "$ps_spot_checks_count"} ,
"psPassCount" : { "$sum" : "$ps_pass_count"}}
}
]).explain()
--部门
db.stat_tb_gmvcs_daily_count.aggregate([
{$match : {
date: {
$gte: ISODate("2018-10-19T00:00:00+08:00"),
$lte: ISODate("2018-10-19T23:59:59+08:00")},
$or : [ { org_path : { $regex : "^/44010401/"}}]}
}
,
{ "$group" : {
"_id" : null ,
"psCount" : { "$sum" : "$ps_count"} ,
"cmCount" : { "$sum" : "$cm_count"} ,
"psMatchCount" : { "$sum" : "$ps_match_count"} ,
"cmMatchCount" : { "$sum" : "$cm_match_count"} ,
"accidentCount" : { "$sum" : "$accident_count"} ,
"forceCount" : { "$sum" : "$force_count"} ,
"surveilCount" : { "$sum" : "$surveil_count"} ,
"violationCOunt" : { "$sum" : "$violation_count"} ,
"accidentMatchCount" : { "$sum" : "$accident_match_count"} ,
"forceMatchCount" : { "$sum" : "$force_match_count"} ,
"surveilMatchCount" : { "$sum" : "$surveil_match_count"} ,
"violationMatchCount" : { "$sum" : "$violation_match_count"} ,
"violationSpotChecksCount" : { "$sum" : "$violation_spot_checks_count"} ,
"violationPassCount" : { "$sum" : "$violation_pass_count"} ,
"surveilSpotChecksCount" : { "$sum" : "$surveil_spot_checks_count"} ,
"surveilPassCount" : { "$sum" : "$surveil_pass_count"} ,
"forceSpotChecksCount" : { "$sum" : "$force_spot_checks_count"} ,
"forcePassCount" : { "$sum" : "$force_pass_count"} ,
"accidentSpotChecksCount" : { "$sum" : "$accident_spot_checks_count"} ,
"accidentPassCount" : { "$sum" : "$accident_pass_count"} ,
"cmSpotChecksCount" : { "$sum" : "$cm_spot_checks_count"} ,
"cmPassCount" : { "$sum" : "$cm_pass_count"} ,
"psSpotChecksCount" : { "$sum" : "$ps_spot_checks_count"} ,
"psPassCount" : { "$sum" : "$ps_pass_count"}}
}
])
.explain()
MongoDB Aggregate的常用方式
最新推荐文章于 2024-08-08 10:59:45 发布