GridDB downsample into OHLC bars with nanosecond epoch int as row key

Question

For a table structure e.g. containing an int key (reflecting epoch in nanoseconds accuracy which is required) and price. How can this be downsampled into groups of open high low close rows (ohlc bars) with time interval e.g. minutes?

Answer 1

I would solve the problem by using Multi-queries which is significantly faster than running each query individually. There would be four queries for each period you wish to build OHLC bars for. The following pseudocode gives a rough solution, leaving out some details that can be found in GridDB's MultiQuery documentation.

for period in periods:

queries.add("select min(price) where epoch > period.start and epoch < period.end")
  queries.add("select max(price) where epoch > period.start and epoch < period.end")
  queries.add("select * where epoch > period.start order by epoch asc limit 1")
  queries.add("select * where epoch < period.end order by epoch desc limit 1")

fetch_all(queries)

while i < len(queries)
  for metric in ['low', 'high', 'open', 'close']:
     query = queries[i]
     rs = query.get_rs()
     while rs.has_next():
          bars[period[p].start][metric] = rs.next()
     i=i+1
  p=p+1

*Please note though that nanoseconds accuracy is not yet supported.