我有一个如下的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"} } }])
测试
这是我的输入:
{ "_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中的多个列进行分组
发布评论