MongoDB:计算每次状态值更改之间的停留时间 [英] MongoDB: Calculate dwell time between every status value change

问题描述

我想找出每次存在状态变化之间的停留时间.

I want to find out the dwell time between every presenceStatus change.

示例集合 -

   /* 1 */

{
    "_id" : ObjectId("5e4889a7c7959f6a13039902"),
    "presenceStatus" : 0,
    "createdAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "__v" : 0
}
/* 2 */
{
    "_id" : ObjectId("5e4889a7c7959f6a1303990c"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "__v" : 0
}
/* 3 */

{
    "_id" : ObjectId("5e4889a9c7959f6a1303995c"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:37.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("5e4889b0c7959f6a130399ff"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "__v" : 0
}

/* 5 */
{
    "_id" : ObjectId("5e4889b3c7959f6a13039a58"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:47.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "__v" : 0
}

/* 6 */
{
    "_id" : ObjectId("5e4889b5c7959f6a13039aad"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:49.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "__v" : 0
}

/* 7 */
{
    "_id" : ObjectId("5e4889b9c7959f6a13039b28"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:53.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "__v" : 0
}

/* 8 */
{
    "_id" : ObjectId("5e4889bcc7959f6a13039b78"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:56.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "__v" : 0
}

/* 9 */
{
    "_id" : ObjectId("5e4889bfc7959f6a13039c00"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:59.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "__v" : 0
}
/* 10 */
{
    "_id" : ObjectId("5e4889c2c7959f6a13039c4a"),
    "presenceStatus" : 0,
    "sensingTime" : ISODate("2020-02-16T00:16:02.000Z"),
    "createdAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "updatedAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "__v" : 0
}

如您所见，第二个文档中的 PresenceStatus 从值 0 更改为 1.

As you can see presenceStatus changed in 2nd document from value 0 to 1.

我想记录下这个状态保持为1的停留时间.(基本上是status = 1到status = 0的时间差)

I want to record this dwell Time period when the status remained 1. (basically time difference between status = 1 to status = 0 )

当它再次找到presenceStatus 1并记录该数据块的presenceStatus保持1的时间段时，相同的过程再次开始.

The same process starts again when it finds presenceStatus 1 again and records the time-period when presenceStatus remained 1 for that chunk of data.

结果集合看起来像 -

Result collection would look like -

{
    "_id" : xxx
    "occupiedTime" : ISODate("2020-02-16T00:15:35.121Z"), //  *updatedAt* value of document 2, cause that's when it changed to 1. 

    "vacantTime" : ISODate("2020-02-16T00:16:02.100Z"), // *updatedAt* of document 10, because that's when status changed from 0 to 1. 
    "dwellTime" : (vacant time - occupied time in HH:MM:SS)
    "created" : "2019-05-29 07:08:13",
    "__v" : 0
}

感谢您的帮助.

推荐答案

@Valijon, @Plancke感谢所有帮助过的人.我们选择了一种不同的方法，并将我们的一个简单的 for 循环现在能够完成这项工作.再次感谢.如果有人感兴趣，这是最终解决方案:

@Valijon, @Plancke Thanks all who helped. we chose to take a different approach and turned our a simple for loop was able to do the job for now. Thanks again. Here is final solution if any one was interested:

let prevSensingResults = {}; 

db.sensingresults.find({updatedAt : {$gt :"",$lte : "")}, presenceStatus : {$exists: 1}}).sort({updatedAt:1})
.forEach(function(doc) { 
    if (typeof prevSensingResults[doc.deviceId.toString()] !== undefined) {
         if (prevSensingResults[doc.deviceId.toString()].presenceStatus !== doc.presenceStatus && doc.presenceStatus === 0) {
             db.presenceagg.update({accountId: doc.accountId, buildingId: doc.buildingId, gatewayId: doc.gatewayId, deviceId: doc.deviceId, occupiedTime: prevSensingResults[doc.deviceId.toString()].updatedAt, vacantTime: doc.updatedAt}
         , {accountId: doc.accountId, buildingId: doc.buildingId, gatewayId: doc.gatewayId, deviceId: doc.deviceId, occupiedTime: prevSensingResults[doc.deviceId.toString()].updatedAt, vacantTime: doc.updatedAt, dwellPeriodInSeconds: (doc.updatedAt.getTime() - prevSensingResults[doc.deviceId.toString()].updatedAt.getTime()) / 1000}
                 , {upsert:true});
            prevSensingResults[doc.deviceId.toString()] = doc;
         } else if (prevSensingResults[doc.deviceId.toString()].presenceStatus !== doc.presenceStatus && doc.presenceStatus === 1) 
            prevSensingResults[doc.deviceId.toString()] = doc;
      }
    } else {
        prevSensingResults[doc.deviceId.toString()] = doc;
   }
})

这篇关于MongoDB:计算每次状态值更改之间的停留时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！