Inexact, time-series, and range joins

This guide covers the joins in Deephaven that don't require an exact match on every key: aj, raj, and range_join. It shows how to use each one and when to choose it.

Which method should you use?

You typically use the as-of joins, aj and raj, to compare time-series data.

  • Use aj to find the closest match before or at an event.
  • Use raj to find the closest match after or at an event.
  • Use range_join when your tables are static and you want to group the right-table data that falls in a range defined by each left-table row, such as all events in each left-table time window.

The following flowchart helps you choose among the exact joins, the as-of joins, and the range joins.

As-of (time-series) joins

As-of joins, also called time-series joins, are common when no exact match between key values is guaranteed, such as when you join two tables on event timestamps.

The output table contains all of the rows and columns of the left table, plus additional columns that contain data from the right table. If a left-table row has no match in the right table, the appended columns hold null values in that row.

As-of join syntax

The syntax for performing an as-of join is as follows, where join_method is aj or raj:

An as-of join matches on zero or more exact match columns, whose values must be equal, followed by exactly one inexact match column. The inexact match column must have an ordered type, such as a numeric, date-time, or other sortable (Java Comparable) column. The join matches it to the closest value in one direction: aj looks at or below the left-table value, and raj looks at or above it. The list of match columns must end in that single inexact match column.

As-of joins take the following parameters:

  • table: The right table, which supplies the data the join adds to the left table.
  • on: The column(s) on which to join the two tables.

The third argument is optional:

  • joins: The column(s) in the right table to join to the left table. If you omit it, the join adds every right-table column except those with the same name as a left-table match column.

Match columns with different names

The match columns of two tables often don't have identical names. The following example joins the left and right tables on ColumnToMatchLeft and ColumnToMatchRight:

Rename joined columns

If you join a right-table column that has the same name as a left-table column, the join raises a name conflict error. This includes a match column such as Timestamp when you list it under its own name. In such a case, aj and raj let you rename joined columns. The following example renames OldColumnName from the right table to NewColumnName as it adds the column to the left table:

aj

The as-of join, aj, joins each left-table row to the right-table row whose inexact match value is closest to the left-table value without going over. aj relates the inexact match columns with > or >=:

  • > joins on inexact matches only.
  • >= joins on an exact or inexact match. This is the implied relation when no relation is specified (e.g., on=["ColumnToMatch"]).

The following example uses aj to join the left and right tables. The match columns X (in left) and Y (in right) contain identical values. The first result table, result_inexact_exact, uses >= to relate the two match columns, so every row of left gets the right row with the same value. The second result table, result_inexact_only, uses >. Its first row has null values in the appended columns because the first value of X isn't greater than any value of Y.

The next example uses market data. Quotes are the published prices and sizes at which people are willing to trade a security. Trades record the prices and sizes at which trades actually executed.

The example uses aj to join the quotes table to the trades table, first on the Ticker column as an exact match, then on Timestamp as the inexact match. For each trade, it finds the most recent quote at or before the trade's Timestamp. The joins argument renames the right table's Timestamp column to QuoteTime, because the left table already has a Timestamp column.

raj

The reverse as-of join, raj, works like aj in the opposite direction. By default, aj takes the right-table row with the same or the closest lower value. raj takes the right-table row with the same or the closest higher value. The syntax is the same as for aj, but raj relates the inexact match columns with < or <=:

  • < joins on inexact matches only.
  • <= joins on an exact or inexact match. This is the implied relation when no relation is specified (e.g., on=["ColumnToMatch"]).

The following example uses raj with <= (exact or inexact) and < (inexact only). In result_inexact_only, the last row has null values in the appended columns because no value of Y is greater than the last value of X.

The following example joins the trades and quotes tables from the aj example with raj. For each trade, it finds the first quote at or after the trade's Timestamp.

Range joins

range_join creates a new table containing all of the rows and columns of the left table, plus additional columns containing aggregated data from the right table. Each left-table row defines a range, and the right-table rows that fall in it are the row's responsive range. The range join applies the requested aggregation directly to each responsive range, so each cell in an appended column holds the aggregated result for its left-table row.

Range join syntax

The syntax for performing a range join is as follows:

The last entry in on is a range match expression of the form LeftStartColumn < RightRangeColumn < LeftEndColumn.

range_join takes the following parameters:

  • table: The right table, which supplies the data the join adds to the left table.
  • on: Zero or more exact match columns followed by one range match expression.
  • aggs (required): The aggregation(s) to perform over each left-table row's responsive range. range_join currently supports only the group aggregation.

The match expressions section of the reference page describes the full range match syntax, including the optional <- marker before the expression and -> marker after it. Each marker requires <= on its side of the range. When no right-table value equals the left-table row's start value, <- also includes the closest right-table row before the start. When no right-table value equals the end value, -> also includes the closest right-table row after the end.

Note

The right range column is the right-table column that the range match compares against. range_join has the following restrictions:

  • It supports only static tables.
  • It discards right-table rows whose right range column holds null or NaN.
  • You must sort the remaining right-table rows by the right range column within each set of rows that share the same exact-match key values.

Range join examples

The following example joins two tables with range_join. It uses only a range match, with no exact-match columns. The range match expression matches each left-table row to the right-table rows whose RightValue is greater than that row's LeftStartValue and less than its LeftEndValue. The group aggregation groups the right table's Y values for each left-table row into a vector in the Y column of result.

For a similar example that adds an exact-match column, with a row-by-row explanation of its output, see the range_join reference examples.

Queries often follow a range_join with an update or update_view that processes the grouped column. The following code block uses the built-in sum function to sum each group in the result table from the previous example.

The following example uses range_join with date-time columns as the range columns. This is a common use case, since it groups all of the events that happened in each time window. Both tables have a Y column, so the group aggregation names its output RightY to avoid replacing the left table's Y. As in the previous example, the built-in sum function then sums each group.