时间戳

首页 > 解决方案 > 如何在mongodb中减去相同索引处的两个数组的元素

java - 如何在mongodb中减去相同索引处的两个数组的元素

问题描述

我正在尝试使用 spring 数据在我的 mongodb 中找到报告事件的平均完成时间。

我想找到的是每个 serviceRequestType 事件的平均完成时间。完成时间必须是completionDate和creationDate的减法。

我有一个收集事件，一个文档如下所示：

{
    "_id": "5c451b7ab5bd0fd4a6d9b4ba",
    "serviceRequestType": "Type A",
    "status": "Completed",
    "creationDate": "2014-01-18T00:00:00",
    "completionDate": "2014-01-21T00:00:00",
    "streetAddress": "5541 W CATALPA AVE"
}

我试过这个：

Aggregation agg = newAggregation(
  group("serviceRequestType").addToSet("creationDate").as("start")
                             .addToSet("completionDate").as("end"),
  unwind("end", "endArray"),
  unwind("start", "startArray")

AggregationResults<Query4Result> result = mongoTemplate.aggregate(agg, "incidents", Query4Result.class);

return result.getMappedResults();

但它产生

{_id=TypeA, start=2014-04-07T00:00:00, end=2014-05-12T00:00:00, 
endArray=0, startArray=0}
{_id=TypeA, start=2014-04-06T00:00:00, end=2014-05-12T00:00:00, 
endArray=0, startArray=1}
{_id=TypeA, start=2014-04-05T00:00:00, end=2014-05-12T00:00:00, 
endArray=0, startArray=2}
{_id=TypeA, start=2014-04-07T00:00:00, end=2014-07-09T00:00:00,             
endArray=1, startArray=0}
{_id=TypeA, start=2014-04-06T00:00:00, end=2014-07-09T00:00:00,     
endArray=1, startArray=1}
{_id=TypeA, start=2014-04-05T00:00:00, end=2014-07-09T00:00:00, 
endArray=1, startArray=2}


{_id=TypeB, start=2014-04-07T00:00:00, end=2014-05-23T00:00:00, 
endArray=0, startArray=0}
{_id=TypeB, start=2014-04-06T00:00:00, end=2014-05-23T00:00:00, 
endArray=0, startArray=1}
{_id=TypeB, start=2014-04-05T00:00:00, end=2014-05-23T00:00:00, 
endArray=0, startArray=2}
{_id=TypeB, start=2014-04-07T00:00:00, end=2014-05-20T00:00:00, 
endArray=1, startArray=0}
{_id=TypeB, start=2014-04-06T00:00:00, end=2014-05-20T00:00:00, 
endArray=1, startArray=1}
{_id=TypeB, start=2014-04-05T00:00:00, end=2014-05-20T00:00:00, 
endArray=1, startArray=2}

关于如何减去每个 serviceRequestType 每个文档的完成和创建日期的任何其他想法

标签： javamongodbspring-data

解决方案

MongoQuery 应该如下所示：

db.getCollection('incidents').aggregate([
    { "$group": {
        "_id": "$serviceRequestType" ,
        "totalMinutes": {
            "$sum": {
                "$divide": [
                    { "$subtract": [ {$dateFromString : {"dateString": "$completionDate"}}, {$dateFromString : {"dateString": "$creationDate"}}] },
                    1000 * 60
                ]
            }
        },
        "count": {
            $sum: 1
        }
    }},
    {$project : {"serviceRequestType" : 1, "averageTime": {$divide : ["$totalMinutes", "$count"]}}}

])

如果您的日期已经在 ISODate 中，您可以跳过 datefromstring 方法。

推荐阅读