如何从MongoDB的每日、每周和每月收集中生成报告

Question

这是我的收藏的结构

{"_id":{
"$oid":"61a5f45e7556f5670e50bd25"
},
"agent_id":"05046630001",
"c_id":null,
"agentName":"Testing",
"agent_intercom_id":"4554",
"campaign":[
"Campaig227"
],
"first_login_time":"28-12-2021  10":"55":42 AM,
"last_logout_time":"21-01-2022  2":"20":10 PM,
"parent_id":4663,
"total_call":2,
"outbound_call":1,
"iinbound_call":1,
"average_call_handling_time":56,
"logged_in_duration":2,
"total_in_call_time":30,
"total_break_duration":10,
"total_ring_time":2,
"available_time":40,
"ideal_time":0,
"occupancy":0,
"inbound_calls_missed":0,
"created_at":{
"$date":"2021-11-29T18:30:00.000Z"
}
}

我想生成这样的每月结果：

出>>

Agent	Campaign	总呼叫数	传入		空闲时间
Agent 1	0:23:57
Agent 2

建议如下：

db.collection.aggregate([
{
$group: {
_id: {
agent_id: "$agent_id",
campaign: "$campaign",
date: {
$dateTrunc: {
date: "$created_at",
unit: "week",
timezone: "Europe/Zurich",
startOfWeek: "monday"
}
}
},
"Total call": { $sum: "$total_in_call_time" },
Outgoing: { $sum: "$outbound_call" },
Incoming: { $sum: "$iinbound_call" },
"Average Call": { $avg: "$total_in_call_time" },
"Total Time": { $sum: "$total_call" },
"Idle Time": { $sum: "$ideal_time" }
}
},
{
$set: {
"Average Call": { $dateToString: { date: { $toDate: { $multiply: ["$Average Call", 1000] } }, format: "%H:%M:%S" } },
"Total Time": { $dateToString: { date: { $toDate: { $multiply: ["$Total Time", 1000] } }, format: "%H:%M:%S" } },
"Idle Time": { $dateToString: { date: { $toDate: { $multiply: ["$Idle Time", 1000] } }, format: "%H:%M:%S" } }
}
},
{ $replaceWith: { $mergeObjects: ["$_id", "$$ROOT"] } },
{ $unset: "_id" }
])

注意，$dateToString: {format: "%H:%M:%S"}的工作时间长达24小时。

Mongo游乐场