与总和的Mongo查询不起作用 [英] Mongo query Distinct with Sum is not working
问题描述
我在这里更新了我的问题. 这是输入数据,您可以使用此命令将其插入本地数据库:
Here I have updated my question. This is the input data, you can use this command to insert in your local db:
db.pms_teamleadtimesheets.insertMany( [
{ "Text" : "Analysis",
"Comments" : "4",
"TaskType" : "DELIVERY",
"Items" : "Others",
"StartDate" : "28-05-2018",
"EndDate" : "2018-05-28",
"Hours" : 240,
"phase" : "Analysis",
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"UserName" : "Admin",
"FacilityID" : "59a53f0c6077b2a029c52b7f",
"TaskID" : "5b0baafffb8df2401af90fea",
"TaskDescription" : "Analysis",
"IsBillable" : true
},
{ "Text" : "Analysis",
"Comments" : "8",
"TaskType" : "DELIVERY",
"Items" : "Others",
"StartDate" : "28-05-2018",
"EndDate" : "2018-05-28",
"Hours" : 240,
"phase" : "Analysis",
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"UserName" : "Admin",
"FacilityID" : "59a53f0c6077b2a029c52b7f",
"TaskID" : "5b0baafffb8df2401af90fea",
"TaskDescription" : "Analysis",
"IsBillable" : true
},
{"Text" : "Analysis",
"Comments" : "2",
"TaskType" : "DELIVERY",
"Items" : "CRI",
"StartDate" : "29-05-2018",
"EndDate" : "2018-05-29",
"Hours" : 120,
"phase" : "Analysis",
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"UserName" : "Admin",
"FacilityID" : "59a53f0c6077b2a029c52b7f",
"TaskID" : "5b0baafffb8df2401af90fea",
"TaskDescription" : "Analysis",
"IsBillable" : true
},
{ "Text" : "Analysis",
"Comments" : "2",
"TaskType" : "DELIVERY",
"Items" : "CRI",
"StartDate" : "29-05-2018",
"EndDate" : "2018-05-29",
"Hours" : 120,
"phase" : "Analysis",
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"UserName" : "Admin",
"FacilityID" : "59a53f0c6077b2a029c52b7f",
"TaskID" : "5b0baafffb8df2401af90fea",
"TaskDescription" : "Analysis",
"IsBillable" : true }
] );
从这个集合中,我想做不同的和求和.在这里,distinct可以很好地工作,但是总和不能工作.
From this collection, I want to do distinct and sum. Here distinct is working fine but the sum is not working.
这是我使用过的查询:
db.Collection.aggregate([
//where query
{ "$match": { UserName: "USER",FacilityID:"FID",ProjectID:"ID" } },
//distinct column
{ "$group": { _id: { ProjectID: "$ProjectID", Task: "$Text", Phase: "$phase", Comments: "$Comments", TaskType: "$TaskType", Items: "$Items", UserName: "$UserName", IsBillable: "$IsBillable", Date: "$StartDate", Hours:{$sum:"$Hours" } }} },
//provide column name for the output
{ "$project": { _id: 0, ProjectID: "$_id.ProjectID" ,Phase: "$_id.Phase",Task: "$_id.Task",Comments: "$_id.Comments",TaskType: "$_id.TaskType",Items: "$_id.Items",UserName: "$_id.UserName",IsBillable: "$_id.IsBillable",Date: "$_id.Date",Hours: { $divide: [ "$_id.Hours", 60 ] } } }
]);
我得到这样的结果.它不计算值的总和.
I am getting the result like this. It does not calculating the sum of the value.
此处未添加总小时数.它应该返回4,但在这里它返回2.
Total hours is not getting added here. It should return 4 but here it is returning 2.
任何人都可以解决我的问题吗?
can anyone resolve me to solve this?
推荐答案
$ group阶段具有以下原型形式:
The $group stage has the following prototype form:
{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }
您的field1为小时,累加器1为总和, 因此,您的汇总应如下所示:
Your field1 is Hours and accumulator1 is sum, Therefore your aggregation should be like this:
db.pms_teamleadtimesheets.aggregate(
// Pipeline
[
// Stage 1
{
$match: {
UserName:"Admin",
FacilityID:"59a53f0c6077b2a029c52b7f",
ProjectID:"5a042ba02af18ac8388bd3c0"
}
},
// Stage 2
{
$group: {
_id: { ProjectID: "$ProjectID",
Task: "$Text", Phase: "$phase",
Comments: "$Comments",
TaskType: "$TaskType",
Items: "$Items",
UserName: "$UserName",
IsBillable: "$IsBillable",
Date: "$StartDate"
},
Hours:{$sum:"$Hours" }
}
},
// Stage 3
{
$project: {
_id: 0,
ProjectID: "$_id.ProjectID",
Phase: "$_id.Phase",
Task: "$_id.Task",
Comments: "$_id.Comments",
TaskType: "$_id.TaskType",
Items: "$_id.Items",
UserName: "$_id.UserName",
IsBillable: "$_id.IsBillable",
Date: "$_id.Date",
Hours: {
$divide: [ "$Hours", 60 ]
}
}
},
]
);
具有给定测试数据的输出:
Output for with the given test data:
{
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"Phase" : "Analysis",
"Task" : "Analysis",
"Comments" : "2",
"TaskType" : "DELIVERY",
"Items" : "CRI",
"UserName" : "Admin",
"IsBillable" : true,
"Date" : "29-05-2018",
"Hours" : 4.0
}
{
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"Phase" : "Analysis",
"Task" : "Analysis",
"Comments" : "8",
"TaskType" : "DELIVERY",
"Items" : "Others",
"UserName" : "Admin",
"IsBillable" : true,
"Date" : "28-05-2018",
"Hours" : 4.0
}
{
"ProjectID" : "5a042ba02af18ac8388bd3c0",
"Phase" : "Analysis",
"Task" : "Analysis",
"Comments" : "4",
"TaskType" : "DELIVERY",
"Items" : "Others",
"UserName" : "Admin",
"IsBillable" : true,
"Date" : "28-05-2018",
"Hours" : 4.0
}
进一步阅读此处
这篇关于与总和的Mongo查询不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!