MongoDB:聚合查询以求和持续时间字段 [英] MongoDB: Aggregate query to sum duration field

问题描述

我有一组记录，我需要总结要显示的持续时间

I have set of records from where I need to sum up duration to be displayed

记录

[{   
  id:"1",
  duration:"07:30:00"
},
{   
  id:"1",
  duration:"07:30:00"
}
{   
  id:"2",
  duration:"07:30:00"
}]

输出

[{
  _id: 1,
  totalDuration: "15:00"
},{
  _id: 2,
  totalDuration: "07:30"
}]

推荐答案

从 MongoDB version >= 4.4 开始，您可以使用 $function 运算符来定义自定义函数以实现 MongoDB 查询语言不支持的行为.以 HH:MM:SS 格式添加两个时间数据的逻辑取自 此处.

From MongoDB version >= 4.4 you can use the $function operator to define custom functions to implement behavior not supported by the MongoDB Query Language. Logic to add two time-data in HH:MM:SS format is taken from here.

试试这个查询:

db.testCollection.aggregate([
    {
        $group: {
            _id: "$id",
            times: { $push: "$duration" }
        }
    },
    {
        $project: {
            _id: 1,
            totalDuration: {
                $function: {
                    body: function(times) {
                        let hours = 0;
                        let minutes = 0;
                        let seconds = 0;

                        for (let i = 0; i < times.length; i++) {
                            let values = times[i].split(":");

                            hours += parseInt(values[0]);
                            minutes += parseInt(values[1]);
                            seconds += parseInt(values[2]);
                        }

                        let realHrs = hours + Math.floor(minutes / 60);
                        let realMins = (minutes % 60) + Math.floor(seconds / 60);
                        let realSecs = seconds % 60;

                        return realHrs + ":" + realMins + ":" + realSecs
                    },
                    args: ["$times"],
                    lang: "js"
                }
            }
        }
    },
    {
        $sort: { _id: 1 }
    }
]);

输出

/* 1 */
{
    "_id" : "1",
    "totalDuration" : "15:6:12"
},

/* 2 */
{
    "_id" : "2",
    "totalDuration" : "7:30:0"
}

testCollection 集合中的数据:

{
    "_id" : ObjectId("..."),
    "id" : "1",
    "duration" : "07:30:30"
},
{
    "_id" : ObjectId("..."),
    "id" : "1",
    "duration" : "07:35:42"
},
{
    "_id" : ObjectId("..."),
    "id" : "2",
    "duration" : "07:30:00"
}

这篇关于MongoDB:聚合查询以求和持续时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！