Complex time series query time difference per action type












0














I have this data structure of documents in MongoDB and it is intended for complex data analysis from any point of view in relation to time series of different actions (flat data log). I found it difficult to extract the time taken between specific type of changes per document using mongo queries and then applying the $graphLookup function (shown below). I'm a beginner in MongoDB and I need help with the query to fetch the required data.



the data structure of a single document (example):



{  
"_id":NumberInt(1),
"Creation": ISODate("2018-11-19T06:30:42Z"),
"Creator": NumberInt(1),
"Replies": NumberInt(10),
//... other aggregated properties
"CurrentProperties":{ // a copy of the last update signifying the current state
"StatusId": NumberInt(8),
"PriorityId": NumberInt(6),
"DepartmentId": NumberInt(5),
"TypeId": NumberInt(4),
"CategoryId": NumberInt(2),
"SubcategoryId": NumberInt(333),
"ChangeTime": ISODate("2018-11-19T10:17:20Z"),
"TimeDelta": NumberLong(3600000), //timespan from last change in MS
"ChangeType": NumberInt(4),
"UserId": NumberInt(1)
},
"ChangeHistory":[ // time series changes
{
"StatusId": NumberInt(8),
"PriorityId": NumberInt(6),
"DepartmentId": NumberInt(1),
"TypeId": NumberInt(4),
"CategoryId": NumberInt(2),
"SubcategoryId": NumberInt(333),
"ChangeTime": ISODate("2018-11-19T10:14:20Z"),
"TimeDelta": NumberLong(0), //timespan from last change in MS
"ChangeType": NumberInt(0), // the changed property identifier (0= creation)
"UserId": NumberInt(1)
},
{
"StatusId": NumberInt(8),
"PriorityId": NumberInt(6),
"DepartmentId": NumberInt(2),
"TypeId": NumberInt(4),
"CategoryId": NumberInt(2),
"SubcategoryId": NumberInt(333),
"ChangeTime": ISODate("2018-11-19T10:15:50Z"),
"TimeDelta": NumberLong(90000), //timespan from last change in MS
"ChangeType": NumberInt(4), // the changed property identifier (4= department)
"UserId": NumberInt(1)
},
{
"StatusId": NumberInt(2),
"PriorityId": NumberInt(6),
"DepartmentId": NumberInt(2),
"TypeId": NumberInt(4),
"CategoryId": NumberInt(2),
"SubcategoryId": NumberInt(333),
"ChangeTime": ISODate("2018-11-19T10:16:20Z"),
"TimeDelta": NumberLong(30000), //timespan from last change in MS
"ChangeType": NumberInt(2), // the changed property identifier (2= status)
"UserId": NumberInt(1)
},
{
"StatusId": NumberInt(2),
"PriorityId": NumberInt(6),
"DepartmentId": NumberInt(5),
"TypeId": NumberInt(4),
"CategoryId": NumberInt(2),
"SubcategoryId": NumberInt(333),
"ChangeTime": ISODate("2018-11-19T10:17:20Z"),
"TimeDelta": NumberLong(60000), //timespan from last change in MS
"ChangeType": NumberInt(4), // the changed property identifier (4= department)
"UserId": NumberInt(1)
}
]
}


The expected result for department changes in time:



[{
RecordID: 1,
Department: 1,
ChangeTime: ISODate("2018-11-19T10:15:50Z"),
TimeSpent: 90000
},
{
RecordID: 1,
Department: 2,
ChangeTime: ISODate("2018-11-19T10:17:20Z")
TimeSpent: 90000
},
{
RecordID: 1,
Department: 5,
ChangeTime: ISODate("2018-11-21T09:47:47Z") // Current Time
TimeSpent: 171027000 //difference between now and last change in departments
}]


and for status:



[{
RecordID: 1,
Status: 8,
ChangeTime: ISODate("2018-11-19T10:16:20Z"),
TimeDelta: 120000
},
{
RecordID: 1,
Status: 2,
ChangeTime: ISODate("2018-11-21T09:47:47Z"), // Current Time
TimeDelta: 171087000 //difference between now and last change in status
}]


What I tried so far



The best result I got so far was using the following aggregation to create a view and then apply a $GraphLookup function on the view:



db.test.aggregate([
{$project: {
_id:0,
RecordID: "$_id",
history: {
$filter: {
input: "$ChangeHistory",
as: "changeHistory",
cond: {$or:[
{$eq:["$$changeHistory.ChangeType",0]},
{$eq:["$$changeHistory.ChangeType",4]}
]}

}
}
}},
{$unwind: {
path: "$history",
includeArrayIndex:"order"
}}, {$project: {
_id:"$RecordID",
"RecordID": "$RecordID",
"departmentID": "$history.DepartmentId",
"actionOrder":"$order",
"nextAction":{$add:["$order",1]},
"time":"$history.ChangeTime"
}}
])


then applied the following:



db.TestView.aggregate([{
$graphLookup: {
from: 'TestView',
startWith: "$nextAction",
connectFromField: 'nextAction',
connectToField: 'actionOrder',
as: 'pair',
}
}, {
$unwind: {
path: "$pair"
}
}, {
$project: {
_id: 0,
RecordID: "$_id",
Department: "$departmentID",
ChangeTime: "$pair.time",
TimeSpent: {
$subtract: ["$pair.time", "$time"]
}
}
}
])


the problem with this is that it mixes the action pairing across different documents, doesn't include the spent time till the current time and it has so many propagations on top of using a view in the middle.



The data structure can be modified a little if needed.










share|improve this question



























    0














    I have this data structure of documents in MongoDB and it is intended for complex data analysis from any point of view in relation to time series of different actions (flat data log). I found it difficult to extract the time taken between specific type of changes per document using mongo queries and then applying the $graphLookup function (shown below). I'm a beginner in MongoDB and I need help with the query to fetch the required data.



    the data structure of a single document (example):



    {  
    "_id":NumberInt(1),
    "Creation": ISODate("2018-11-19T06:30:42Z"),
    "Creator": NumberInt(1),
    "Replies": NumberInt(10),
    //... other aggregated properties
    "CurrentProperties":{ // a copy of the last update signifying the current state
    "StatusId": NumberInt(8),
    "PriorityId": NumberInt(6),
    "DepartmentId": NumberInt(5),
    "TypeId": NumberInt(4),
    "CategoryId": NumberInt(2),
    "SubcategoryId": NumberInt(333),
    "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
    "TimeDelta": NumberLong(3600000), //timespan from last change in MS
    "ChangeType": NumberInt(4),
    "UserId": NumberInt(1)
    },
    "ChangeHistory":[ // time series changes
    {
    "StatusId": NumberInt(8),
    "PriorityId": NumberInt(6),
    "DepartmentId": NumberInt(1),
    "TypeId": NumberInt(4),
    "CategoryId": NumberInt(2),
    "SubcategoryId": NumberInt(333),
    "ChangeTime": ISODate("2018-11-19T10:14:20Z"),
    "TimeDelta": NumberLong(0), //timespan from last change in MS
    "ChangeType": NumberInt(0), // the changed property identifier (0= creation)
    "UserId": NumberInt(1)
    },
    {
    "StatusId": NumberInt(8),
    "PriorityId": NumberInt(6),
    "DepartmentId": NumberInt(2),
    "TypeId": NumberInt(4),
    "CategoryId": NumberInt(2),
    "SubcategoryId": NumberInt(333),
    "ChangeTime": ISODate("2018-11-19T10:15:50Z"),
    "TimeDelta": NumberLong(90000), //timespan from last change in MS
    "ChangeType": NumberInt(4), // the changed property identifier (4= department)
    "UserId": NumberInt(1)
    },
    {
    "StatusId": NumberInt(2),
    "PriorityId": NumberInt(6),
    "DepartmentId": NumberInt(2),
    "TypeId": NumberInt(4),
    "CategoryId": NumberInt(2),
    "SubcategoryId": NumberInt(333),
    "ChangeTime": ISODate("2018-11-19T10:16:20Z"),
    "TimeDelta": NumberLong(30000), //timespan from last change in MS
    "ChangeType": NumberInt(2), // the changed property identifier (2= status)
    "UserId": NumberInt(1)
    },
    {
    "StatusId": NumberInt(2),
    "PriorityId": NumberInt(6),
    "DepartmentId": NumberInt(5),
    "TypeId": NumberInt(4),
    "CategoryId": NumberInt(2),
    "SubcategoryId": NumberInt(333),
    "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
    "TimeDelta": NumberLong(60000), //timespan from last change in MS
    "ChangeType": NumberInt(4), // the changed property identifier (4= department)
    "UserId": NumberInt(1)
    }
    ]
    }


    The expected result for department changes in time:



    [{
    RecordID: 1,
    Department: 1,
    ChangeTime: ISODate("2018-11-19T10:15:50Z"),
    TimeSpent: 90000
    },
    {
    RecordID: 1,
    Department: 2,
    ChangeTime: ISODate("2018-11-19T10:17:20Z")
    TimeSpent: 90000
    },
    {
    RecordID: 1,
    Department: 5,
    ChangeTime: ISODate("2018-11-21T09:47:47Z") // Current Time
    TimeSpent: 171027000 //difference between now and last change in departments
    }]


    and for status:



    [{
    RecordID: 1,
    Status: 8,
    ChangeTime: ISODate("2018-11-19T10:16:20Z"),
    TimeDelta: 120000
    },
    {
    RecordID: 1,
    Status: 2,
    ChangeTime: ISODate("2018-11-21T09:47:47Z"), // Current Time
    TimeDelta: 171087000 //difference between now and last change in status
    }]


    What I tried so far



    The best result I got so far was using the following aggregation to create a view and then apply a $GraphLookup function on the view:



    db.test.aggregate([
    {$project: {
    _id:0,
    RecordID: "$_id",
    history: {
    $filter: {
    input: "$ChangeHistory",
    as: "changeHistory",
    cond: {$or:[
    {$eq:["$$changeHistory.ChangeType",0]},
    {$eq:["$$changeHistory.ChangeType",4]}
    ]}

    }
    }
    }},
    {$unwind: {
    path: "$history",
    includeArrayIndex:"order"
    }}, {$project: {
    _id:"$RecordID",
    "RecordID": "$RecordID",
    "departmentID": "$history.DepartmentId",
    "actionOrder":"$order",
    "nextAction":{$add:["$order",1]},
    "time":"$history.ChangeTime"
    }}
    ])


    then applied the following:



    db.TestView.aggregate([{
    $graphLookup: {
    from: 'TestView',
    startWith: "$nextAction",
    connectFromField: 'nextAction',
    connectToField: 'actionOrder',
    as: 'pair',
    }
    }, {
    $unwind: {
    path: "$pair"
    }
    }, {
    $project: {
    _id: 0,
    RecordID: "$_id",
    Department: "$departmentID",
    ChangeTime: "$pair.time",
    TimeSpent: {
    $subtract: ["$pair.time", "$time"]
    }
    }
    }
    ])


    the problem with this is that it mixes the action pairing across different documents, doesn't include the spent time till the current time and it has so many propagations on top of using a view in the middle.



    The data structure can be modified a little if needed.










    share|improve this question

























      0












      0








      0







      I have this data structure of documents in MongoDB and it is intended for complex data analysis from any point of view in relation to time series of different actions (flat data log). I found it difficult to extract the time taken between specific type of changes per document using mongo queries and then applying the $graphLookup function (shown below). I'm a beginner in MongoDB and I need help with the query to fetch the required data.



      the data structure of a single document (example):



      {  
      "_id":NumberInt(1),
      "Creation": ISODate("2018-11-19T06:30:42Z"),
      "Creator": NumberInt(1),
      "Replies": NumberInt(10),
      //... other aggregated properties
      "CurrentProperties":{ // a copy of the last update signifying the current state
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(5),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
      "TimeDelta": NumberLong(3600000), //timespan from last change in MS
      "ChangeType": NumberInt(4),
      "UserId": NumberInt(1)
      },
      "ChangeHistory":[ // time series changes
      {
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(1),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:14:20Z"),
      "TimeDelta": NumberLong(0), //timespan from last change in MS
      "ChangeType": NumberInt(0), // the changed property identifier (0= creation)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(2),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:15:50Z"),
      "TimeDelta": NumberLong(90000), //timespan from last change in MS
      "ChangeType": NumberInt(4), // the changed property identifier (4= department)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(2),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(2),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:16:20Z"),
      "TimeDelta": NumberLong(30000), //timespan from last change in MS
      "ChangeType": NumberInt(2), // the changed property identifier (2= status)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(2),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(5),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
      "TimeDelta": NumberLong(60000), //timespan from last change in MS
      "ChangeType": NumberInt(4), // the changed property identifier (4= department)
      "UserId": NumberInt(1)
      }
      ]
      }


      The expected result for department changes in time:



      [{
      RecordID: 1,
      Department: 1,
      ChangeTime: ISODate("2018-11-19T10:15:50Z"),
      TimeSpent: 90000
      },
      {
      RecordID: 1,
      Department: 2,
      ChangeTime: ISODate("2018-11-19T10:17:20Z")
      TimeSpent: 90000
      },
      {
      RecordID: 1,
      Department: 5,
      ChangeTime: ISODate("2018-11-21T09:47:47Z") // Current Time
      TimeSpent: 171027000 //difference between now and last change in departments
      }]


      and for status:



      [{
      RecordID: 1,
      Status: 8,
      ChangeTime: ISODate("2018-11-19T10:16:20Z"),
      TimeDelta: 120000
      },
      {
      RecordID: 1,
      Status: 2,
      ChangeTime: ISODate("2018-11-21T09:47:47Z"), // Current Time
      TimeDelta: 171087000 //difference between now and last change in status
      }]


      What I tried so far



      The best result I got so far was using the following aggregation to create a view and then apply a $GraphLookup function on the view:



      db.test.aggregate([
      {$project: {
      _id:0,
      RecordID: "$_id",
      history: {
      $filter: {
      input: "$ChangeHistory",
      as: "changeHistory",
      cond: {$or:[
      {$eq:["$$changeHistory.ChangeType",0]},
      {$eq:["$$changeHistory.ChangeType",4]}
      ]}

      }
      }
      }},
      {$unwind: {
      path: "$history",
      includeArrayIndex:"order"
      }}, {$project: {
      _id:"$RecordID",
      "RecordID": "$RecordID",
      "departmentID": "$history.DepartmentId",
      "actionOrder":"$order",
      "nextAction":{$add:["$order",1]},
      "time":"$history.ChangeTime"
      }}
      ])


      then applied the following:



      db.TestView.aggregate([{
      $graphLookup: {
      from: 'TestView',
      startWith: "$nextAction",
      connectFromField: 'nextAction',
      connectToField: 'actionOrder',
      as: 'pair',
      }
      }, {
      $unwind: {
      path: "$pair"
      }
      }, {
      $project: {
      _id: 0,
      RecordID: "$_id",
      Department: "$departmentID",
      ChangeTime: "$pair.time",
      TimeSpent: {
      $subtract: ["$pair.time", "$time"]
      }
      }
      }
      ])


      the problem with this is that it mixes the action pairing across different documents, doesn't include the spent time till the current time and it has so many propagations on top of using a view in the middle.



      The data structure can be modified a little if needed.










      share|improve this question













      I have this data structure of documents in MongoDB and it is intended for complex data analysis from any point of view in relation to time series of different actions (flat data log). I found it difficult to extract the time taken between specific type of changes per document using mongo queries and then applying the $graphLookup function (shown below). I'm a beginner in MongoDB and I need help with the query to fetch the required data.



      the data structure of a single document (example):



      {  
      "_id":NumberInt(1),
      "Creation": ISODate("2018-11-19T06:30:42Z"),
      "Creator": NumberInt(1),
      "Replies": NumberInt(10),
      //... other aggregated properties
      "CurrentProperties":{ // a copy of the last update signifying the current state
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(5),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
      "TimeDelta": NumberLong(3600000), //timespan from last change in MS
      "ChangeType": NumberInt(4),
      "UserId": NumberInt(1)
      },
      "ChangeHistory":[ // time series changes
      {
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(1),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:14:20Z"),
      "TimeDelta": NumberLong(0), //timespan from last change in MS
      "ChangeType": NumberInt(0), // the changed property identifier (0= creation)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(8),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(2),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:15:50Z"),
      "TimeDelta": NumberLong(90000), //timespan from last change in MS
      "ChangeType": NumberInt(4), // the changed property identifier (4= department)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(2),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(2),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:16:20Z"),
      "TimeDelta": NumberLong(30000), //timespan from last change in MS
      "ChangeType": NumberInt(2), // the changed property identifier (2= status)
      "UserId": NumberInt(1)
      },
      {
      "StatusId": NumberInt(2),
      "PriorityId": NumberInt(6),
      "DepartmentId": NumberInt(5),
      "TypeId": NumberInt(4),
      "CategoryId": NumberInt(2),
      "SubcategoryId": NumberInt(333),
      "ChangeTime": ISODate("2018-11-19T10:17:20Z"),
      "TimeDelta": NumberLong(60000), //timespan from last change in MS
      "ChangeType": NumberInt(4), // the changed property identifier (4= department)
      "UserId": NumberInt(1)
      }
      ]
      }


      The expected result for department changes in time:



      [{
      RecordID: 1,
      Department: 1,
      ChangeTime: ISODate("2018-11-19T10:15:50Z"),
      TimeSpent: 90000
      },
      {
      RecordID: 1,
      Department: 2,
      ChangeTime: ISODate("2018-11-19T10:17:20Z")
      TimeSpent: 90000
      },
      {
      RecordID: 1,
      Department: 5,
      ChangeTime: ISODate("2018-11-21T09:47:47Z") // Current Time
      TimeSpent: 171027000 //difference between now and last change in departments
      }]


      and for status:



      [{
      RecordID: 1,
      Status: 8,
      ChangeTime: ISODate("2018-11-19T10:16:20Z"),
      TimeDelta: 120000
      },
      {
      RecordID: 1,
      Status: 2,
      ChangeTime: ISODate("2018-11-21T09:47:47Z"), // Current Time
      TimeDelta: 171087000 //difference between now and last change in status
      }]


      What I tried so far



      The best result I got so far was using the following aggregation to create a view and then apply a $GraphLookup function on the view:



      db.test.aggregate([
      {$project: {
      _id:0,
      RecordID: "$_id",
      history: {
      $filter: {
      input: "$ChangeHistory",
      as: "changeHistory",
      cond: {$or:[
      {$eq:["$$changeHistory.ChangeType",0]},
      {$eq:["$$changeHistory.ChangeType",4]}
      ]}

      }
      }
      }},
      {$unwind: {
      path: "$history",
      includeArrayIndex:"order"
      }}, {$project: {
      _id:"$RecordID",
      "RecordID": "$RecordID",
      "departmentID": "$history.DepartmentId",
      "actionOrder":"$order",
      "nextAction":{$add:["$order",1]},
      "time":"$history.ChangeTime"
      }}
      ])


      then applied the following:



      db.TestView.aggregate([{
      $graphLookup: {
      from: 'TestView',
      startWith: "$nextAction",
      connectFromField: 'nextAction',
      connectToField: 'actionOrder',
      as: 'pair',
      }
      }, {
      $unwind: {
      path: "$pair"
      }
      }, {
      $project: {
      _id: 0,
      RecordID: "$_id",
      Department: "$departmentID",
      ChangeTime: "$pair.time",
      TimeSpent: {
      $subtract: ["$pair.time", "$time"]
      }
      }
      }
      ])


      the problem with this is that it mixes the action pairing across different documents, doesn't include the spent time till the current time and it has so many propagations on top of using a view in the middle.



      The data structure can be modified a little if needed.







      mongodb data-science business-intelligence






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 21 at 10:21









      CME64

      1,478923




      1,478923
























          1 Answer
          1






          active

          oldest

          votes


















          0














          I actually took 2 days trying to figure out a solution for this before posting the question, and I solved it a few hours later.



          Just wanted to share my solution and if anyone could optimize it for performance or anything please feel free to post your answers too



          Solution



          it makes use of the $zip function in order to form pairs of actions after the filter is applied by passing the original array of events and another copy of the same array excluding the first element, so that the first element gets matched with the second and the second with the third and so on. I also added a default of the current time to calculate the last element's delta from the current time.



          db.test.aggregate([{
          $project: {
          RecordID: "$_id",
          history: {
          $filter: {
          input: "$ChangeHistory",
          as: "changeHistory",
          cond: {
          $or: [{
          $eq: ["$$changeHistory.ChangeType", 0]
          },
          {
          $eq: ["$$changeHistory.ChangeType", 2]
          }
          ]
          }

          }
          }
          }
          },
          {
          $addFields: {
          pairs: {
          $zip: { // here is the trick
          inputs: ["$history", {
          $slice: ["$history", 1, {
          $size: "$history"
          }]
          }],
          useLongestLength: true,
          defaults: [0, {
          ChangeTime: new Date()
          }]
          }
          }
          }
          },
          {
          $unwind: {
          path: "$pairs"
          }
          },
          {
          $project: {
          id: "$_id",
          old: {
          $arrayElemAt: ["$pairs", 0]
          },
          new: {
          $arrayElemAt: ["$pairs", 1]
          }
          }
          },
          {
          $project: {
          RecordID: "$id",
          Status: "$old.StatusId",
          TimeDeltaMS: {
          $subtract: ["$new.ChangeTime", "$old.ChangeTime"]
          },
          ChangeTime: "$new.ChangeTime"
          }
          },
          ])





          share|improve this answer





















            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409898%2fcomplex-time-series-query-time-difference-per-action-type%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            I actually took 2 days trying to figure out a solution for this before posting the question, and I solved it a few hours later.



            Just wanted to share my solution and if anyone could optimize it for performance or anything please feel free to post your answers too



            Solution



            it makes use of the $zip function in order to form pairs of actions after the filter is applied by passing the original array of events and another copy of the same array excluding the first element, so that the first element gets matched with the second and the second with the third and so on. I also added a default of the current time to calculate the last element's delta from the current time.



            db.test.aggregate([{
            $project: {
            RecordID: "$_id",
            history: {
            $filter: {
            input: "$ChangeHistory",
            as: "changeHistory",
            cond: {
            $or: [{
            $eq: ["$$changeHistory.ChangeType", 0]
            },
            {
            $eq: ["$$changeHistory.ChangeType", 2]
            }
            ]
            }

            }
            }
            }
            },
            {
            $addFields: {
            pairs: {
            $zip: { // here is the trick
            inputs: ["$history", {
            $slice: ["$history", 1, {
            $size: "$history"
            }]
            }],
            useLongestLength: true,
            defaults: [0, {
            ChangeTime: new Date()
            }]
            }
            }
            }
            },
            {
            $unwind: {
            path: "$pairs"
            }
            },
            {
            $project: {
            id: "$_id",
            old: {
            $arrayElemAt: ["$pairs", 0]
            },
            new: {
            $arrayElemAt: ["$pairs", 1]
            }
            }
            },
            {
            $project: {
            RecordID: "$id",
            Status: "$old.StatusId",
            TimeDeltaMS: {
            $subtract: ["$new.ChangeTime", "$old.ChangeTime"]
            },
            ChangeTime: "$new.ChangeTime"
            }
            },
            ])





            share|improve this answer


























              0














              I actually took 2 days trying to figure out a solution for this before posting the question, and I solved it a few hours later.



              Just wanted to share my solution and if anyone could optimize it for performance or anything please feel free to post your answers too



              Solution



              it makes use of the $zip function in order to form pairs of actions after the filter is applied by passing the original array of events and another copy of the same array excluding the first element, so that the first element gets matched with the second and the second with the third and so on. I also added a default of the current time to calculate the last element's delta from the current time.



              db.test.aggregate([{
              $project: {
              RecordID: "$_id",
              history: {
              $filter: {
              input: "$ChangeHistory",
              as: "changeHistory",
              cond: {
              $or: [{
              $eq: ["$$changeHistory.ChangeType", 0]
              },
              {
              $eq: ["$$changeHistory.ChangeType", 2]
              }
              ]
              }

              }
              }
              }
              },
              {
              $addFields: {
              pairs: {
              $zip: { // here is the trick
              inputs: ["$history", {
              $slice: ["$history", 1, {
              $size: "$history"
              }]
              }],
              useLongestLength: true,
              defaults: [0, {
              ChangeTime: new Date()
              }]
              }
              }
              }
              },
              {
              $unwind: {
              path: "$pairs"
              }
              },
              {
              $project: {
              id: "$_id",
              old: {
              $arrayElemAt: ["$pairs", 0]
              },
              new: {
              $arrayElemAt: ["$pairs", 1]
              }
              }
              },
              {
              $project: {
              RecordID: "$id",
              Status: "$old.StatusId",
              TimeDeltaMS: {
              $subtract: ["$new.ChangeTime", "$old.ChangeTime"]
              },
              ChangeTime: "$new.ChangeTime"
              }
              },
              ])





              share|improve this answer
























                0












                0








                0






                I actually took 2 days trying to figure out a solution for this before posting the question, and I solved it a few hours later.



                Just wanted to share my solution and if anyone could optimize it for performance or anything please feel free to post your answers too



                Solution



                it makes use of the $zip function in order to form pairs of actions after the filter is applied by passing the original array of events and another copy of the same array excluding the first element, so that the first element gets matched with the second and the second with the third and so on. I also added a default of the current time to calculate the last element's delta from the current time.



                db.test.aggregate([{
                $project: {
                RecordID: "$_id",
                history: {
                $filter: {
                input: "$ChangeHistory",
                as: "changeHistory",
                cond: {
                $or: [{
                $eq: ["$$changeHistory.ChangeType", 0]
                },
                {
                $eq: ["$$changeHistory.ChangeType", 2]
                }
                ]
                }

                }
                }
                }
                },
                {
                $addFields: {
                pairs: {
                $zip: { // here is the trick
                inputs: ["$history", {
                $slice: ["$history", 1, {
                $size: "$history"
                }]
                }],
                useLongestLength: true,
                defaults: [0, {
                ChangeTime: new Date()
                }]
                }
                }
                }
                },
                {
                $unwind: {
                path: "$pairs"
                }
                },
                {
                $project: {
                id: "$_id",
                old: {
                $arrayElemAt: ["$pairs", 0]
                },
                new: {
                $arrayElemAt: ["$pairs", 1]
                }
                }
                },
                {
                $project: {
                RecordID: "$id",
                Status: "$old.StatusId",
                TimeDeltaMS: {
                $subtract: ["$new.ChangeTime", "$old.ChangeTime"]
                },
                ChangeTime: "$new.ChangeTime"
                }
                },
                ])





                share|improve this answer












                I actually took 2 days trying to figure out a solution for this before posting the question, and I solved it a few hours later.



                Just wanted to share my solution and if anyone could optimize it for performance or anything please feel free to post your answers too



                Solution



                it makes use of the $zip function in order to form pairs of actions after the filter is applied by passing the original array of events and another copy of the same array excluding the first element, so that the first element gets matched with the second and the second with the third and so on. I also added a default of the current time to calculate the last element's delta from the current time.



                db.test.aggregate([{
                $project: {
                RecordID: "$_id",
                history: {
                $filter: {
                input: "$ChangeHistory",
                as: "changeHistory",
                cond: {
                $or: [{
                $eq: ["$$changeHistory.ChangeType", 0]
                },
                {
                $eq: ["$$changeHistory.ChangeType", 2]
                }
                ]
                }

                }
                }
                }
                },
                {
                $addFields: {
                pairs: {
                $zip: { // here is the trick
                inputs: ["$history", {
                $slice: ["$history", 1, {
                $size: "$history"
                }]
                }],
                useLongestLength: true,
                defaults: [0, {
                ChangeTime: new Date()
                }]
                }
                }
                }
                },
                {
                $unwind: {
                path: "$pairs"
                }
                },
                {
                $project: {
                id: "$_id",
                old: {
                $arrayElemAt: ["$pairs", 0]
                },
                new: {
                $arrayElemAt: ["$pairs", 1]
                }
                }
                },
                {
                $project: {
                RecordID: "$id",
                Status: "$old.StatusId",
                TimeDeltaMS: {
                $subtract: ["$new.ChangeTime", "$old.ChangeTime"]
                },
                ChangeTime: "$new.ChangeTime"
                }
                },
                ])






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 22 at 6:53









                CME64

                1,478923




                1,478923






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409898%2fcomplex-time-series-query-time-difference-per-action-type%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    404 Error Contact Form 7 ajax form submitting

                    How to know if a Active Directory user can login interactively

                    TypeError: fit_transform() missing 1 required positional argument: 'X'