在mongoDB中汇总OHLC时如何避免时间间隔

本文介绍了在mongoDB中汇总OHLC时如何避免时间间隔的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图从存储在mongoDB中的1分钟间隔数据库中提取ohlc 5分钟间隔.以下是我当前的查询.

I am trying to extract ohlc 5 min interval from a 1 min interval DB stored in mongoDB. Below is my current query.

myModel.aggregate([ {"$project": { "data": { "$let": { "vars": { "mints":{"$arrayElemAt":[{"$arrayElemAt":["$data",0]},0]}, "maxts":{"$arrayElemAt":[{"$arrayElemAt":["$data",-1]},0]} }, "in": { "$map": { "input":{"$range":["$$mints",{"$add":["$$maxts",300]},300]}, "as":"rge", "in": { "$let": { "vars": { "five": { "$filter": { "input":"$data", "as":"fres", "cond": { "$and": [ {"$gte":[{"$arrayElemAt":["$$fres",0]},"$$rge"]}, {"$lt":[{"$arrayElemAt":["$$fres",0]},{"$add":["$$rge",300]}]} ] } } } }, "in": [ {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},0]}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",0]},1]}, {"$max":{"$map":{"input":"$$five","as":"res","in":{"$arrayElemAt":["$$res",2]}}}}, {"$min":{"$map":{"input":"$$five","as":"res","in":{"$arrayElemAt":["$$res",3]}}}}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},-2]}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},-1]} ] } } } } } } } } ]);

似乎提取了5分钟，但没有考虑1分钟间隔数据中的间隔.相反，对于那些时刻，我得到的是空数组.我们如何避免空数组?

It seem to extract the 5 min but not taking care of gaps in 1 min interval data. Instead for those time instants, I am getting null array. How do we avoid null arrays?

样本1 DB数据: gist.github/parthi2929/36e6898cff7be45ccdd008ec750e70e9

Sample 1 DB data: gist.github/parthi2929/36e6898cff7be45ccdd008ec750e70e9

5分钟提取的输出快照是此处

5 min extracted output snapshot is here

正如您在快照中看到的那样，我得到了很多空数组.如何避免它们?

As you can see in snapshot, I get lot of null arrays. How do I avoid them?

我尝试在$ and运算符中插入{ "$ne":[{"$arrayElemAt":["$$fres",0]},null] }，但这没有帮助.

I tried inserting { "$ne":[{"$arrayElemAt":["$$fres",0]},null] } in $and operator, but it did not help.

更新2018年2月14日:根据Veeram的建议，以下是修改后的代码，并包含建议的更改.但是，我仍然得到一个空数组(显然在那个时间间隔中已经有很多空数组消失了，但是被单个空数组代替了)，这个空数组也应该被修复.

Update 14th Feb 2018: As per Veeram's suggestion, below is the modified code incorporated with the suggested changes. However, I still get one empty array (that is apparently many empty arrays in that time gap is now gone, but replaced by single empty array) which should also be fixed.

db.getCollection('ohlc-koinex-1').aggregate( [ {"$project": { "data": { "$let": { "vars": { "mints":{"$arrayElemAt":[{"$arrayElemAt":["$data",0]},0]}, "maxts":{"$arrayElemAt":[{"$arrayElemAt":["$data",-1]},0]} }, "in": { "$setDifference": [ { "$map": { "input":{"$range":["$$mints",{"$add":["$$maxts",300]},300]}, "as":"rge", "in": { "$let": { "vars": { "five": { "$filter": { "input":"$data", "as":"fres", "cond": { "$and": [ {"$gte":[{"$arrayElemAt":["$$fres",0]},"$$rge"]}, {"$lt":[{"$arrayElemAt":["$$fres",0]},{"$add":["$$rge",300]}]} ] } } } }, "in": { "$cond":[ {"$eq":["$$five",[]]}, "$$five", [ {"$arrayElemAt": [{"$arrayElemAt":["$$five",-1]},0]}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",0]},1]}, {"$max":{"$map":{"input":"$$five","as":"res","in":{"$arrayElemAt":["$$res",2]}}}}, {"$min":{"$map":{"input":"$$five","as":"res","in":{"$arrayElemAt":["$$res",3]}}}}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},-2]}, {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},-1]} ] ] } } } } },[] ] } } } } } ] )

此处是结果的快照

推荐答案

您可以添加$cond运算符以解决空格问题，然后添加$filter来过滤空数组值.

You can add $cond operator to account for gaps followed by $filter to filter the empty array values.

您需要进行两项更改.

第一个更改是存储间隙的[]值而不是具有空值的数组.

The first change to store [] values for gaps instead of array with null values.

将内部$ let表达式更新为以下内容:

Update inner $let expression to below:

{ "$let":{ "vars":{"five":...}, "in":{ "$cond":[ {"$eq":["$$five",[]]}, "$$five", [{"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},0]}, .... {"$arrayElemAt":[{"$arrayElemAt":["$$five",-1]},-1]}] ] } } }

第二个更改，用于过滤输出中的空数组值.

The second change to filter the empty array values from the output.

{ "$project":{ "data":{ "$let":{ "vars":{"mints":...}, "in":{"$filter":{"input":{"$map":...},as:"flr", "cond":{"$ne":["$$flr",[]]}}} } } } }