public async Task Add(IntervalKey intervalKey) { using (var context = ContextFactory.CreateDbContext(null)) { if (await context.IntervalKey.FindAsync(intervalKey.Key) == null) { await context.IntervalKey.AddAsync(new IntervalKeyEntity { IntervalKey = intervalKey.Key, IntervalGroupId = (int)intervalKey.IntervalGroup, Label = intervalKey.Label }); await context.SaveChangesAsync(); var from = new DateTime(2008, 1, 1, 0, 0, 0, DateTimeKind.Utc); var to = new DateTime(DateTime.Now.Year + 2, 1, 1, 0, 0, 0, DateTimeKind.Utc); var cursor = from; while (cursor < to) { var intervals = IntervalFactory.GenerateIntervals(intervalKey, new Epoch(cursor), new Epoch(cursor.AddYears(1))); await AddInterval(intervals); cursor = cursor.AddYears(1); } } } }
public async Task <ICollection <MarketAggregate> > GetTradeAggregates(ExchangeEnum exchange, SymbolCodeEnum symbolCode, IntervalKey intervalKey, Epoch from, int dataPoints) { var intervals = IntervalFactory.GenerateIntervals(intervalKey, from, dataPoints); var min = intervals.First(); var max = intervals.Last(); var aggs = await GetTradeAggregates(exchange, symbolCode, intervalKey, min.From, max.From, null, null); return(aggs.Items); }
public async Task <PagedCollection <MarketAggregate> > GetTradeAggregates(ExchangeEnum exchange, SymbolCodeEnum symbolCode, IntervalKey intervalKey, Epoch from, Epoch to, int?pageSize, int?pageNumber) { var intervals = IntervalFactory.GenerateIntervals(intervalKey, from, to); var min = intervals.First(); var max = intervals.Last(); using (var context = ContextFactory.CreateDbContext(null)) { var query = context.ExchangeTradeAggregate .Where(a => a.ExchangeId == (int)exchange && a.SymbolId == (int)symbolCode && a.IntervalKey == intervalKey.Key && a.Timestamp >= min.From.TimestampMilliseconds && a.Timestamp <= max.From.TimestampMilliseconds); var totalCount = 0; if (pageSize.HasValue) { totalCount = await query.CountAsync(); query = query.Skip(pageNumber.GetValueOrDefault(0) * pageSize.Value).Take(pageSize.Value); } var aggs = await query.ToListAsync(); return(new PagedCollection <MarketAggregate>() { PageNumber = pageNumber.GetValueOrDefault(0), PageSize = pageSize.GetValueOrDefault(0), ItemCount = pageSize.HasValue ? totalCount : aggs.Count, Items = aggs.Select(a => new MarketAggregate { Exchange = exchange, Symbol = symbolCode, IntervalKey = a.IntervalKey, Epoch = Epoch.FromMilliseconds(a.Timestamp), Open = a.Open, OpenEpoch = Epoch.FromMilliseconds(a.OpenTimestamp), High = a.High, Low = a.Low, Close = a.Close, CloseEpoch = Epoch.FromMilliseconds(a.CloseTimestamp), BuyVolume = a.BuyVolume, SellVolume = a.SellVolume, TotalVolume = a.TotalVolume, BuyCount = a.BuyCount, SellCount = a.SellCount, TotalCount = a.TotalCount }).ToList() }); } }
public async Task SaveTradeStatAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTradeStat> tradeStats) { var min = tradeStats.Min(t => t.Epoch); var max = tradeStats.Max(t => t.Epoch); var expanded = new List <TradeStatCartesian>(); foreach (var group in IntervalFactory.ListGroups()) { foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup)) { foreach (var tradeStat in tradeStats) { var period = IntervalFactory.GenerateIntervals(ik, tradeStat.Epoch, tradeStat.Epoch).FirstOrDefault(); expanded.Add(new TradeStatCartesian { IntervalKey = period.IntervalKey, IntervalEpoch = period.From, Exchange = tradeStat.Exchange, SymbolCode = symbolCode, StatKey = tradeStat.StatKey, Epoch = tradeStat.Epoch, Value = tradeStat.Value }); } } } using (var context = ContextFactory.CreateDbContext(null)) { using (var cmd = context.Database.GetDbConnection().CreateCommand()) { var sql = @"insert into `exchange_trade_aggregate` (`exchange_id`, `symbol_id`, `interval_key`, `timestamp`, `open`, `open_timestamp`, `high`, `low`, `close`, `close_timestamp`, `total_count`) values "; sql += string.Join(",\r\n", expanded.Select(t => $"(" + $"{(int)t.Exchange}," + $"{(int)t.SymbolCode}," + $"'{t.IntervalKey.Key}'," + $"{t.IntervalEpoch.TimestampMilliseconds}," + $"{t.Value}," + $"{t.Epoch.TimestampMilliseconds}," + $"{t.Value}," + $"{t.Value}," + $"{t.Value}," + $"{t.Epoch.TimestampMilliseconds}," + $"1)")); sql += @" on duplicate key update `open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end, `open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end, `high` = case when values(`high`) > `high` then values(`high`) else `high` end, `low` = case when values(`low`) < `low` then values(`low`) else `low` end, `close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end, `close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end, `total_count` = `total_count` + values(`total_count`)" ; cmd.CommandText = sql; await cmd.Connection.OpenAsync(); await cmd.ExecuteNonQueryAsync(); cmd.Connection.Close(); } } }
public async Task SaveTradeAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTrade> trades) { var min = trades.Min(t => t.Epoch); var max = trades.Max(t => t.Epoch); var expanded = new List <TradeCartesian>(); foreach (var group in IntervalFactory.ListGroups()) { foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup)) { foreach (var trade in trades) { var period = IntervalFactory.GenerateIntervals(ik, trade.Epoch, trade.Epoch).FirstOrDefault(); expanded.Add(new TradeCartesian { IntervalKey = period.IntervalKey, IntervalEpoch = period.From, Exchange = trade.Exchange, SymbolCode = trade.SymbolCode, Epoch = trade.Epoch, TradeId = trade.TradeId, Price = trade.Price, Volume = trade.Volume, Side = trade.Side }); } } } var context = (HistorianDbContext)transaction.GetContext(); using (var cmd = context.Database.GetDbConnection().CreateCommand()) { cmd.Transaction = context.Database.CurrentTransaction.GetDbTransaction(); var sql = @"insert into `exchange_trade_aggregate` (`exchange_id`, `symbol_id`, `interval_key`, `timestamp`, `open`, `open_timestamp`, `high`, `low`, `close`, `close_timestamp`, `buy_volume`, `sell_volume`, `total_volume`, `buy_count`, `sell_count`, `total_count`) values "; sql += string.Join(",\r\n", expanded.Select(t => $"(" + $"{(int)t.Exchange}," + $"{(int)t.SymbolCode}," + $"'{t.IntervalKey.Key}'," + $"{t.IntervalEpoch.TimestampMilliseconds}," + $"{t.Price}," + $"{t.Epoch.TimestampMilliseconds}," + $"{t.Price}," + $"{t.Price}," + $"{t.Price}," + $"{t.Epoch.TimestampMilliseconds}," + $"{(t.Side == OrderSideEnum.Buy ? t.Volume.ToString() : "NULL")}," + $"{(t.Side == OrderSideEnum.Sell ? t.Volume.ToString() : "NULL")}," + $"{t.Volume}," + $"{(t.Side == OrderSideEnum.Buy ? "1" : "NULL")}," + $"{(t.Side == OrderSideEnum.Sell ? "1" : "NULL")}," + $"1)")); sql += @" on duplicate key update `open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end, `open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end, `high` = case when values(`high`) > `high` then values(`high`) else `high` end, `low` = case when values(`low`) < `low` then values(`low`) else `low` end, `close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end, `close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end, `buy_volume` = case when values(`buy_volume`) is not null then ifnull(`buy_volume`, 0) + values(`buy_volume`) else `buy_volume` end, `sell_volume` = case when values(`sell_volume`) is not null then ifnull(`sell_volume`, 0) + values(`sell_volume`) else `sell_volume` end, `total_volume` = `total_volume` + values(`total_volume`), `buy_count` = case when values(`buy_count`) is not null then ifnull(`buy_count`, 0) + values(`buy_count`) else `buy_count` end, `sell_count` = case when values(`sell_count`) is not null then ifnull(`sell_count`, 0) + values(`sell_count`) else `sell_count` end, `total_count` = `total_count` + values(`total_count`)"; cmd.CommandText = sql; await cmd.ExecuteNonQueryAsync(); } }