Querying: Aggregating and Projecting Time Series

In an RQL query, use the group by expression to aggregate time series (or ranges of time series entries) in groups by a chosen resolution or by the entry tag. Use the select keyword to choose and project entries by a chosen criterion.

• In this example, we group entries of users' HeartRate time series and project the lowest and highest values of each group.
  Each HeartRate entry holds a single value.

  from Employees as e
  where e.Birthday > '1960-01-01'
  select timeseries(
      from HeartRate between 
          '2020-05-17T00:00:00.0000000Z' 
          and '2020-05-23T00:00:00.0000000Z'
              where Tag == 'watches/fitbit'
      group by '1 days'
      select min(), max()
  )

  • group by '1 days'
    We group each user's HeartRate time series entries in consecutive 1-day groups.
  • select min(), max()
    We select the lowest and highest values of each group and project them to the client.

• In this example, we group entries of companies' StockPrice time series in consecutive 7-day groups and project the highest and lowest values of each group.
  Each StockPrice entry holds five values, the query returns the Max and Min values of each:
  Values[0] - Open - stock price when the trade opens
  Values[1] - Close - stock price when the trade ends
  Values[2] - High - highest stock price during trade time
  Values[3] - Low - lowest stock price during trade time
  Values[4] - Volume - overall trade volume
  

  declare timeseries SP(c) 
  {
      from c.StockPrices 
      where Values[4] > 500000
          group by '7 day'
          select max(), min()
  }
  from Companies as c
  where c.Address.Country = 'USA'
  select c.Name, SP(c)

  • where Values[4] > 500000
    Query stock price behavior when the trade volume is high.
  • group by '7 day'
    Group each company's StockPrice entries in consecutive 7-day groups.
  • select max(), min()
    Select the highest (Max) and lowest (Min) values of each group and project them to the client.
    Since each entry holds 5 values, the query will project 5 Max values for each group (the highest Values[0], highest Values[1], etc.) and 5 Min values for each group (the lowest Values[0], lowest Values[1], etc.).
  • select c.Name, SP(c)
    Project the company's name along with the time series query results to make the results easier to read and understand.

• This example is similar to the one above it, except that time series entries are not aggregated, so the highest and lowest values are collected not from each group but from the entire result-set.
  

  declare timeseries SP(c) 
  {
      from c.StockPrices 
      where Values[4] > 500000
          select max(), min()
  }
  from Companies as c
  where c.Address.Country = 'USA'
  select c.Name, SP(c)

  • select max(), min()
    Since there is no aggregation, the entire result-set is queried and the results include only the all-time highest and lowest Open, Close, High, Low and Volume values.

• In these two example we group time series data by the entry tags. In the first query we simply use group by tag and also filter the results based on the tag value. In the second, we access the tag using load and then filter:
  

  from Employees as e
  select timeseries(
      from HeartRates
      where Tag == 'watches/fitbit' or Tag == 'Heartrate_Monitor'
      group by tag
      select min(), max()
  )

  from Employees as e
  select timeseries(
      from HeartRates
      load Tag as monitor
      where monitor == 'watches/fitbit' or monitor == 'Heartrate_Monitor'
      group by tag
      select min(), max()
  )

• Finally, in this example we group by time series in a LINQ query:

  var query = session.Query<User>()
      .Select(u => RavenQuery.TimeSeries(u, "HeartRates")
          .GroupBy(g => g
                  .Hours(1)
                  .ByTag()
                 )
          .Select(g => new
          {
              Max = g.Max(),
              Min = g.Min()
          }));

To aggregate time series entries, use GroupBy() in a LINQ query or group by in a raw RQL query.
To select time series values for projection, use Select() in a LINQ query or select in a raw RQL query.

• Here we express the query we've discussed above using LINQ and both RQL syntaxes.
  
  • LINQ
  • Raw-RQL-Select-Syntax
  • Raw-RQL-Declare-Syntax

  IRavenQueryable<TimeSeriesAggregationResult> query = session.Query<Company>()
      .Where(c => c.Address.Country == "USA")
      .Select(q => RavenQuery.TimeSeries(q, "StockPrice")
          .Where(ts => ts.Values[4] > 500000)
          .GroupBy(g => g.Days(7))
          .Select(g => new
          {
              Min = g.Min(),
              Max = g.Max()
          })
          .ToList());
  
  var result = query.ToList();

  IRawDocumentQuery<TimeSeriesAggregationResult> aggregatedRawQuery =
      session.Advanced.RawQuery<TimeSeriesAggregationResult>(@"
          from Companies as c
              where c.Address.Country = 'USA'
              select timeseries ( 
                  from StockPrice 
                  where Values[4] > 500000
                      group by '7 day'
                      select max(), min()
              )
          ");
  
  var aggregatedRawQueryResult = aggregatedRawQuery.ToList();

  IRawDocumentQuery<TimeSeriesAggregationResult> aggregatedRawQuery =
      session.Advanced.RawQuery<TimeSeriesAggregationResult>(@"
          declare timeseries SP(c) {
              from c.StockPrice
              where Values[4] > 500000
              group by '7 day'
              select max(), min()
          }
          from Companies as c
          where c.Address.Country = 'USA'
          select c.Name, SP(c)"
          );
  
  var aggregatedRawQueryResult = aggregatedRawQuery.ToList();