如何在C#中从cosmos db获取确切的最新5分钟数据 [英] How to get exact latest 5 minutes data from cosmos db in C#
问题描述
我有一个连续运行的webjob，并从web socket api读取数据.
I have a webjob which run continuously and read data from web socket api.
下面是每1秒钟自动运行并将滴答数据添加到cosmos db中的代码.
Below is the code which automatically run after every 1 second and add tick data into cosmos db.
private static void OnTick(Tick TickData)
{
var latestTickData = new MyObject()
{
InstrumentID = TickData.InstrumentToken,
Close = TickData.LastPrice,
High = TickData.LastPrice,
Low = TickData.LastPrice,
Open = TickData.LastPrice,
TimeStamp = TickData.Timestamp.HasValue ? TickData.Timestamp.Value : DateTime.Now
};
// add data into cosmos
Task.Run(() =>
{
Program.documentClient.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri("tickerDatabase", "tickerContainer"), latestTickData);
}).Wait();
}
现在，我想读取最近5分钟的数据，并采用开高低位收盘价.
Now I want to read most recent 5 minutes data and take open high low close values.
对于最近的5分钟数据，我目前每5分钟运行一次计时器作业，该作业从波斯菊读取数据并计算开盘高低开盘，但是这里是时间.
For latest 5 minutes data currently I'm running a timer job every 5 minutes which read data from cosmos and calculate open high low close but issue is time here.
如果计时器作业延迟1分钟运行，那么该数据的寡妇也会更改并获得错误的值.
If timer job runs 1 minute late also the widow of that data will change and getting wrong values.
我的问题是，如何从宇宙中获取准确的5分钟最新数据?
My question is, how to get exact 5 minutes latest data from cosmos?
当前计时器作业代码-
myobject.cs
myobject.cs
public class MyObject
{
public uint InstrumentID { get; set; }
public decimal Close { get; set; }
public decimal High { get; set; }
public decimal Low { get; set; }
public decimal Open { get; set; }
public DateTime TimeStamp { get; set; }
public uint Volume { get; set; }
public DateTime GetStartOfPeriodByMins(int numMinutes)
{
int oldMinutes = TimeStamp.Minute;
int newMinutes = (oldMinutes / numMinutes) * numMinutes;
DateTime startOfPeriod = new DateTime(TimeStamp.Year, TimeStamp.Month, TimeStamp.Day, TimeStamp.Hour, newMinutes, 0);
return startOfPeriod;
}
}
myfunction.cs
myfunction.cs
public static void ExecuteProcess([TimerTrigger("0 */5 * * * *")] TimerInfo myTimer)
{
var option = new FeedOptions { EnableCrossPartitionQuery = true };
var queryable = Program.documentClient.CreateDocumentQuery<MyObject>
(UriFactory.CreateDocumentCollectionUri("tickerDatabase", "tickerContainer"), option).ToList();
var resultSet = queryable.GroupBy(i => i.GetStartOfPeriodByMins(5))
.Select(gr =>
new
{
StartOfPeriod = gr.Key,
Low = gr.Min(item => item.Low),
High = gr.Max(item => item.High),
Open = gr.OrderBy(item => item.TimeStamp).First().Open,
Close = gr.OrderBy(item => item.TimeStamp).Last().Close
});
var my5min = resultSet.LastOrDefault();
Console.WriteLine("time " + my5min.StartOfPeriod + " open " + my5min.Open + " high " + my5min.High + " low " + my5min.Low + " close " + my5min.Close);
让我用1分钟的数据来解释问题(尽管我原本需要5分钟).
Let me explain issue with 1 minutes data (though I originally want 5 minute).
下面是示例记录-
List<MyObject> test = new List<MyObject>();
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 01) });
test.Add(new MyObject() { Open = 2933, High = 2933, Low = 2933, Close = 2933, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 01) });
test.Add(new MyObject() { Open = 2936, High = 2936, Low = 2936, Close = 2936, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 03) });
test.Add(new MyObject() { Open = 2944, High = 2944, Low = 2944, Close = 2944, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 05) });
test.Add(new MyObject() { Open = 2944, High = 2944, Low = 2944, Close = 2944, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 08) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 10) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 15) });
test.Add(new MyObject() { Open = 2932, High = 2932, Low = 2932, Close = 2932, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 25) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 26) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 28) });
test.Add(new MyObject() { Open = 2932, High = 2932, Low = 2932, Close = 2932, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 30) });
test.Add(new MyObject() { Open = 2941, High = 2941, Low = 2941, Close = 2941, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 32) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 35) });
test.Add(new MyObject() { Open = 2941, High = 2941, Low = 2941, Close = 2941, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 40) });
test.Add(new MyObject() { Open = 2937, High = 2937, Low = 2937, Close = 2937, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 42) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 45) });
test.Add(new MyObject() { Open = 2937, High = 2937, Low = 2937, Close = 2937, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 48) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 50) });
test.Add(new MyObject() { Open = 2939, High = 2939, Low = 2939, Close = 2939, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 52) });
test.Add(new MyObject() { Open = 2937, High = 2937, Low = 2937, Close = 2937, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 54) });
test.Add(new MyObject() { Open = 2935, High = 2935, Low = 2935, Close = 2935, TimeStamp = new DateTime(2020, 10, 15, 10, 01, 56) });
test.Add(new MyObject() { Open = 2935, High = 2935, Low = 2935, Close = 2935, TimeStamp = new DateTime(2020, 10, 15, 10, 02, 12) });
将1分钟作为参数传递给GetStartOfPeriodByMins(1)-
Passed 1 minutes as a parameter to GetStartOfPeriodByMins(1) -
这里记录的是时间10:1:56和10:2:12.
Here records are for time 10:1:56 and 10:2:12.
现在您可以观察到最后，我们将在resultSet中获得2个记录集
Now you can observe last we will get 2 record set into resultSet
如果在特定时间未运行计时器触发功能，则只会取最后一条记录的平均值10:2:12，这是不正确的
If timer trigger function is not run at specific time it will only take average of last record for 10:2:12 and it is not right
那么问题是如何匹配运行时间?
So question is how to match running time?
同样的事情也将在5分钟之内发生.
Same will happen with 5 minutes as well.
上述样本数据输出1分钟
Output of above sample data for 1 minutes
因此，我们需要确保在该时间范围内数据是完整的.
So we need to ensure data should be complete from that timeframe.
推荐答案
这里的一种可能的解决方案是利用文档的 _ts 属性.您可以直接使用5分钟跨度的开始日期和结束日期进行SQL查询.除此之外，您还可以将开始日期作为最后使用的结束日期保存在数据库中(费用可以忽略不计).查询应如下所示:
One possible solution here can be to leverage _ts property of documents. You can directly have a SQL query with start date and end date with 5 minutes span. In addition to this, you can save start date as last used end date in DB(negligible cost). The query shall look like this:
SELECT * FROM c where c._ts <= 1601890740 AND c._ts >= 1601890585
还请注意，您将不得不进行POSIX到DateTime的来回转换.
Also note that, you will have to do some to and fro conversions of POSIX to DateTime.
这篇关于如何在C#中从cosmos db获取确切的最新5分钟数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
