在mongoDB中汇总OHLC时如何避免时间间隔 [英] How to avoid time gaps while aggregating OHLC in mongoDB
问题描述
我试图从存储在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数据: https://gist.github.com/parthi2929/36e6898cff7be45ccdd008ec750e70e9
Sample 1 DB data: https://gist.github.com/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",[]]}}}
}
}
}
}
这篇关于在mongoDB中汇总OHLC时如何避免时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
