通过MongoDB中的多个列进行分组 [英] GroupBy multiple columns in MongoDB

问题描述

我有一个如下的shifts集合

{
    "_id" : ObjectId("5885a1108c2fc432d649647d"),
    "from" : ISODate("2017-01-24T06:21:00.000Z"), //can be weekday, sat, sun
    "to" : ISODate("2017-01-24T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32 //this wil vary based on **from** field
}

{
    "_id" : ObjectId("5885a1108c2fc432d649647e"),
    "from" : ISODate("2017-01-25T06:21:00.000Z"),
    "to" : ISODate("2017-01-25T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

{
    "_id" : ObjectId("5885a1108c2fc432d649647f"),
    "from" : ISODate("2017-01-26T06:21:00.000Z"),
    "to" : ISODate("2017-01-26T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

我希望能够产生这样的输出

I want to be able to produce an output like this

• 平日收入-$ 50(2 * $ 25)
• 星期六收入-$ 90(3 * $ 30)
• 周日收入-$ 100(2 * $ 50)总收入= $ 240

• 平日收入..............

平日，周六或周日可以从from字段获得.对于工作日，需要将星期一至星期五的所有日子进行分组. 可以通过从to减去from来得出小时数.

Weekday or Saturday or Sunday can be derived from from field. For a Weekday, all days from Monday to Friday need to be grouped. Number of hours can be derived by subtracting from from to.

因此，我认为需要完成以下工作，但是我无法在MongoDB中实现

So, I think the following needs to be done, but I am not able to implement in MongoDB

• 按jobId分组，按天从from提取分组(也将所有工作日分组)，并减去from和to得出提取的总小时数.
• 最后，获取上述所有金额的总和，以获得总收入
• 获取所有工作收入的最终金额.

• Group by jobId, group by day extracted from from (also group all weekdays) and get the sum of hours extracted by subtracting from and to.
• Finally, get the sum of all the above sums to get the total earnings
• Get the final sum of all job earnings.

我发现了一些类似的问题，但无法将其应用于我的情况
由多个字段组成的mongodb组值
Mongodb聚合框架|分组多个值?
如何在mongodb

I found a few similar questions but I am not able to apply it to my situation
mongodb group values by multiple fields
Mongodb Aggregation Framework | Group over multiple values?
How to group by multiple columns and multiple values in mongodb

使用案例:
当用户访问一个名为收入"的页面时，我需要向他显示每个工作上周的收入和总收入(然后他可以更改日期范围).因此，我打算将节目划分为每个工作，再按工作日，周六和周日的收入以及 THAT 工作的总收入和工作时间进行划分.最终的总收入是所有个人工作收入的总和.

USE CASE:
When a user visits a page called earnings, I need to show him the earnings for the last week for each job and total earnings (then he can change the date range). So, I intent the show the split up for each Job, further split by weekday, saturday and sunday earnings and total earnings and work hours for THAT job. And final total earnings which is a summation of all the individual job earnings.

P.S我正在使用MongoDB 3.4

P.S I am using MongoDB 3.4

推荐答案

遵循以下聚合查询:

db.shifts.aggregate([{ 
    //this get the day of week and converts them into sunday, saturday
    $project: {
        jobId:1,
        hourlyRate:1, 
        dayOfWeek: { $dayOfWeek: "$from" }, 
        workedHours: {$divide:[{ $subtract: ["$to", "$from"] }, 3600000]}, 
        saturday:{$floor: {$divide:[{ $dayOfWeek: "$from" }, 7]}},
        sunday:{$floor: {$divide:[{$abs:{$subtract:[{ $dayOfWeek: "$from" }, 7]}}, 6]}},
    }
}, {
    //based on the values of sunday and saturday gets the value of weekday
    $project: {
        jobId:1,
        workedHours:1,
        hourlyRate:1,
        saturday:1,
        sunday: 1,
        weekday:{$abs: {$add:["$sunday","$saturday", -1]}},
    } 
}, {
    //here calculates the earnings for each job
    $group:{
        _id:"$jobId",
        sundayEarnings:{$sum: {$multiply:["$sunday", "$hourlyRate", "$workedHours"]}},
        saturdayEarnings:{$sum: {$multiply:["$saturday", "$hourlyRate", "$workedHours"]}},
        weekdayEarnings:{$sum: {$multiply:["$weekday", "$hourlyRate", "$workedHours"]}},
        totalEarnings: {$sum:{$multiply:["$hourlyRate", "$workedHours"]}},
        totalWorkedHours: {$sum: "$workedHours"}
    }
}, {
    //and finally calculates the total jobs earnings
    $group:{
        _id:null,
        jobs:{$push:{
            jobId: "$_id",
            sundayEarnings: "$sundayEarnings",
            saturdayEarnings: "$saturdayEarnings",
            weekdayEarnings: "$weekdayEarnings",
            totalEarnings: "$totalEarnings",
            totalWorkedHours: "$totalWorkedHours"
        }},
        totalJobsEarning: {$sum: "$totalEarnings"}
    }
}])

1. 第一个$project聚合通过进行多次算术计算，基于dayOfWeek值将 0或1 值赋予saturday和sunday.
2. 第二个$project聚合基于saturday和sunday值计算weekday的值.
3. 第一个$group计算每项工作每天的收入.
4. 最后，第二个$group聚合计算所有职位的收入总和.

1. The first $project aggregation gives either 0 or 1 values to saturday and sunday based on the dayOfWeek value by making several arithmetic calculations.
2. Second $project aggregation calculates the weekday's value based on the saturday and sunday values.
3. The first $group calculates the earnings for each day in each job.
4. Finally the second $group aggregation calculates the sum of earnings of all the jobs.

测试

这是我的输入:

{
    "_id" : ObjectId("5885a1108c2fc432d649647d"),
    "from" : ISODate("2017-01-24T06:21:00Z"),
    "to" : ISODate("2017-01-24T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("5885a1108c2fc432d649647e"),
    "from" : ISODate("2017-01-25T06:21:00Z"),
    "to" : ISODate("2017-01-25T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("5885a1108c2fc432d649647f"),
    "from" : ISODate("2017-01-26T06:21:00Z"),
    "to" : ISODate("2017-01-26T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("58870cfd59dfb6b0c4eadd72"),
    "from" : ISODate("2017-01-28T06:21:00Z"),
    "to" : ISODate("2017-01-28T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("58870dc659dfb6b0c4eadd73"),
    "from" : ISODate("2017-01-29T06:21:00Z"),
    "to" : ISODate("2017-01-29T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

上面的聚合查询提供以下输出:

The above aggregation query gives the following output:

{
    "_id" : null,
    "jobs" : [
        {
            "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
            "sundayEarnings" : 64,
            "saturdayEarnings" : 64,
            "weekdayEarnings" : 192,
            "totalEarnings" : 320,
            "totalWorkedHours" : 10 
        }
    ],
    "totalJobsEarning" : 320
}

在jobs数组中只有一项工作，因为shifts集合的文档被引用到同一jobId.您可以使用不同的jobId进行尝试，这将为您提供不同的总收入工作.

In the jobs array there is only one job because the docs of shifts collection is referenced to the same jobId. You can try this with different jobIds and it will give you different jobs with total earnings.

这篇关于通过MongoDB中的多个列进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！