mongodb - 计算开始和结束日期每天的总小时数

Question

我有很多这样的文件：

{_id: ObjectId("5adc864eaaf408a2b6e325f7"), employee: ObjectId("5adc864eaa3c92b3c4c252c1"), end: { day: "2018-04-22 12:06:46.623" }, start: { day: "2018-04-22 11:06:46.623" }, date: "2018-04-22 11:06:46.623"}
{_id: ObjectId("5adc864eaaf408a2b6e325c8"),employee: ObjectId("5adc864eaa3c92b3c4c252c1"), end: { day: "2018-04-22 10:06:46.623" }, start: { day: "2018-04-22 8:06:46.623" }, date: "2018-04-22 11:06:46.623"}
{_id: ObjectId("5adc864eaaf408a2b6e325f6"),employee: ObjectId("5adc864eaa3c92b3c4c252c1"), end: { day: "2018-05-22 12:06:46.623" }, start: { day: "2018-04-22 11:06:46.623" }, date: "2018-05-22 11:06:46.623"}
{_id: ObjectId("5adc864eaaf408a2b6e325c4"),employee: ObjectId("5adc864eaa3c92b3c4c252c1"), end: { day: "2018-05-22 10:06:46.623" }, start: { day: "2018-05-22 8:06:46.623" }, date: "2018-05-22 11:06:46.623"}

这代表了每个员工白天的活动。

我需要计算每天工作的小时数，计算每个活动的开始日期“start.day”和结束日期“end.day”之间每个活动的小时数，并将一天的所有活动相加。

我尝试使用一些聚合，如 redact、sum、substrac，但我不知道我需要做什么逻辑来实现这一点。

标签： mongodbmongodb-queryaggregation-framework

Answer 1

因此，真正要涵盖的第一件事是您当前的“日期”都是“字符串”，这确实无济于事。将所有内容都转换为 BSON 日期会更好，因为无论如何这基本上是聚合操作所需要的。

第二点是在一个区间内获得“每一天”的总数并不容易。事实上，你真的需要在 MongoDB 上抛出一些表达式才能做这样的事情：

db.collection.aggregate([
  { "$addFields": {
    "start": { "$toDate": "$start.day" },
    "end": { "$toDate": "$end.day" },
    "date": { "$toDate": "$date" },
    "dayworking": {
      "$map": {
        "input": {
          "$range": [
            0,
            { "$ceil": {
              "$divide": [
                { "$subtract": [
                  { "$toDate": "$end.day" },
                  { "$toDate": "$start.day" }
                ]},
                1000 * 60 * 60 * 24
              ]
            }}
          ]
        },
        "in": {
          "$toDate": {
            "$add": [
              { "$multiply": ["$$this", 1000 * 60 * 60 * 24 ] },
              { "$subtract": [
                { "$toLong": { "$toDate": "$start.day" } },
                { "$mod": [ { "$toLong": { "$toDate": "$start.day" } }, 1000 * 60 * 60 * 24 ] }
              ]}
            ]
          }
        }
      }
    }
  }},
  { "$unwind": "$dayworking" },
  { "$group": {
    "_id": {
      "employee": "$employee",
      "day": "$dayworking"
    },
    "hours": {
      "$sum": {
        "$floor": {
          "$divide": [
            { "$switch": {
              "branches": [
                { 
                  "case": {
                    "$and": [
                      { "$lt": [ "$dayworking", "$start" ] },
                      { "$gt": [
                        { "$add": [ "$dayworking", 1000 * 60 * 60 * 24 ] },
                        "$end"
                      ]}
                    ]
                  },
                  "then": { "$subtract": [ "$end", "$start" ] }
                },
                {
                  "case": {
                    "$lt": [
                      "$end",
                      { "$add": [ "$dayworking", 1000 * 60 * 60 * 24 ] }
                    ]
                  },
                  "then": {
                    "$subtract": [ "$end", "$dayworking" ]
                  }
                },
                {
                  "case": { "$lt": [ "$dayworking", "$start" ] },
                  "then": {
                    "$subtract": [
                      { "$add": [ "$dayworking", 1000 * 60 * 60 * 24 ] },
                      "$start"
                    ]
                  }
                }
              ],
              "default": 1000 * 60 * 60 * 24
            }},
            1000 * 60 * 60
          ]
        }
      }
    }
  }},
  { "$sort": { "_id": 1 } }
])

基本上每天在开始和结束间隔内返回（为简洁起见截断）：

{
        "_id" : {
                "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
                "day" : ISODate("2018-04-22T00:00:00Z")
        },
        "hours" : 15
}
{
        "_id" : {
                "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
                "day" : ISODate("2018-04-23T00:00:00Z")
        },
        "hours" : 24
}

.... each day in between ...

{
        "_id" : {
                "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
                "day" : ISODate("2018-05-21T00:00:00Z")
        },
        "hours" : 24
}
{
        "_id" : {
                "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
                "day" : ISODate("2018-05-22T00:00:00Z")
        },
        "hours" : 14
}

这是“全天”分配的 24 小时和其他时间的部分小时。从您的示例开始，第一天的数据生成为：

{
        "_id" : ObjectId("5adc864eaaf408a2b6e325f7"),
        "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
        "end" : ISODate("2018-04-22T12:06:46.623Z"),
        "start" : ISODate("2018-04-22T11:06:46.623Z"),
        "date" : ISODate("2018-04-22T11:06:46.623Z"),
        "dayending" : ISODate("2018-04-22T00:00:00Z"),
        "hours" : 1
}
{
        "_id" : ObjectId("5adc864eaaf408a2b6e325c8"),
        "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
        "end" : ISODate("2018-04-22T10:06:46.623Z"),
        "start" : ISODate("2018-04-22T08:06:46.623Z"),
        "date" : ISODate("2018-04-22T11:06:46.623Z"),
        "dayending" : ISODate("2018-04-22T00:00:00Z"),
        "hours" : 2
}
{
        "_id" : ObjectId("5adc864eaaf408a2b6e325f6"),
        "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
        "end" : ISODate("2018-05-22T12:06:46.623Z"),
        "start" : ISODate("2018-04-22T11:06:46.623Z"),
        "date" : ISODate("2018-05-22T11:06:46.623Z"),
        "dayending" : ISODate("2018-04-22T00:00:00Z"),
        "hours" : 12
}

作为唯一的两个条目和一个剩余 12 小时的条目，这构成了 15 小时和最后一天：

{
        "_id" : ObjectId("5adc864eaaf408a2b6e325f6"),
        "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
        "end" : ISODate("2018-05-22T12:06:46.623Z"),
        "start" : ISODate("2018-04-22T11:06:46.623Z"),
        "date" : ISODate("2018-05-22T11:06:46.623Z"),
        "dayending" : ISODate("2018-05-22T00:00:00Z"),
        "hours" : 12
}
{
        "_id" : ObjectId("5adc864eaaf408a2b6e325c4"),
        "employee" : ObjectId("5adc864eaa3c92b3c4c252c1"),
        "end" : ISODate("2018-05-22T10:06:46.623Z"),
        "start" : ISODate("2018-05-22T08:06:46.623Z"),
        "date" : ISODate("2018-05-22T11:06:46.623Z"),
        "dayending" : ISODate("2018-05-22T00:00:00Z"),
        "hours" : 2
}

有 2 小时的入场时间和另外 12 小时的剩余时间，总共 14 小时。

解释

日期转换和数学

解释一下，除了明显的“日期转换”之外，还有两件主要的事情需要做。顺便说一句，可以$toDate从 MongoDB 4.0 或通过$dateFromStringMongoDB 3.6 来完成。请注意，在后一种情况下，您还需要为“日期数学”应用不同的方法

在您拥有早期版本并且需要先转换数据或直接转换数据的情况下，在 MongoDb 中按 15 分钟时间间隔分组结果中有在早期 MongoDB 版本中处理“日期数学”的详细示例。$dateFromString

投影范围内的日期

完成这项工作的下一个主要部分是您基本上需要构造一个文档在源文档中适用的日期数组。这就是$range表达式所做的，通过获取一个起始值（0在这种情况下）和一个结束值，在这里我们将其应用于“天数”和日期值之间的“天数” 。startend

该差异$subtract以毫秒为单位从 a 返回，因此$divide在一天中的恒定毫秒内使用 a 以获得整个整数。使用$ceil此处进行舍入，但这很容易用于$mod该$subtract运算符在早期版本中不可用的位置。

那时，$range实际上刚刚生成了一个整数值数组，因此将$map应用于该数组，以便将它们转换为应该表示数据申请的“日期”的实际 BSON Date 对象。同样，它只是一些“日期数学” ，将数组索引值（当然是 +1）添加到原始四舍五入的开始日期。

计算小时数

现在有了来自早期阶段的日期数组以及将文档值重新格式化为可用 BSON 日期的其他一些格式，您需要将这个“数组”内容与每个值进行实际比较start，end以确定当天应用了多少小时。

第一个基本情况以及为什么我们实际上为此创建了一个数组是使用$unwind，它有效地复制间隔内发生的每一天的结果文档。这是一个小而重要的步骤，必须在您之前完成$group并实际计算事情。底线是$group实际将这些值用作输出的“主键”的一部分，并与其他日期信息进行比较。

当然，这里真正的工作都是在$switch语句中完成的，这也可以是早期版本中的“嵌套”使用。$cond在这里，您基本上要分析三种可能的情况，当然还有“全天”的默认回退。

案件基本上是：

• 如果当前的“分组日”小于start并且“下一天”将大于end日期，只需减去差异即可。

• 如果不是上述情况，则当end日期小于分组的“下一天”时，从当前日期中减去该“分组日”end以获得该天开始到该end时间的小时数。

• 如果不是上述情况，那么当“分组日”小于start（没有早先的其他end条件）时，工作时间将从“下一天”减去从当天到结束的start差额。start

• 如果这些不正确，则默认显示“全天”，在此示例中显示为 24 小时。

如果您有其他工作时间要申请，那么只需调整它，即“一天开始”+8 小时，早上 8 点开始。同样的事情基本上适用于“一天结束”，通过添加类似 +17 的内容来完成下午 5 点。但是实现的逻辑基本原理还是和上图一样。

注意：这里的主要约束是$range，我相信它来自 MongoDB 3.0 或可能是 3.2。无论如何，目前您可能真的不应该运行 3.4 之前的任何 MongoDB 版本。

如果您确实有较早的版本，那么还有更多关于Group 的详细信息，并在开始和结束范围内计数，而我的另一个较早的答案显示了使用多个查询的类似过程，甚至mapReduce()除了一个非常相似的$range示例。