Joining Time-Series Data

You can use the ASOF JOIN construct to join tables that contain time-series data. ASOF JOIN is available as standard syntax within the FROM clause of a SELECT statement. Although ASOF JOIN queries can be emulated through the use of complex SQL, other types of joins, and window functions, these queries are easier to write (and are optimized) if you use the ASOF JOIN syntax.

Introduction

You can use ASOF joins to analyze time-series data, such as financial trading data. Transaction-cost analysis, for example, requires “slippage” calculations, which measure the difference between the price quoted at the time of a decision to buy stocks and the price actually paid when the trade was executed and recorded. The ASOF JOIN can expedite this type of analysis. Given that the key capability of this join method is the analysis of one time series with respect to another, ASOF JOIN may be useful for analyzing any data set that is historical in nature, such as weather observations, readings from sensors, or audit trails. In many of these use cases, ASOF JOIN may be used to associate data when readings from different devices have timestamps that are not exactly the same.

The assumption is that the time-series data you need to analyze exists in two tables, and there is a timestamp for each row in each table. This timestamp represents the precise “as of” date and time for a recorded event. For each row in the first (or left) table, the join uses a “match condition” with a comparison operator that you specify to find a single row in the second (or right) table where the timestamp value is one of the following:

  • Less than or equal to the timestamp value in the left table.

  • Greater than or equal to the timestamp value in the left table.

  • Less than the timestamp value in the left table.

  • Greater than the timestamp value in the left table.

The qualifying row on the right side is the closest match, which could be equal in time, earlier in time, or later in time, depending on the specified comparison operator.

The cardinality of the result of the ASOF JOIN is always equal to the cardinality of the left table. If the left table contains 40 million rows, the ASOF JOIN returns 40 million rows. Therefore, the left table may be thought of as the “preserving” table, and the right table as the “referenced” table.

Conceptual Example of an ASOF JOIN Query

For example, in a financial application, you might have a table named quotes and a table named trades. One table records the history of bids to buy stock, and the other records the history of actual trades. A bid to buy stocks happens before the trade (or possibly at the “same” time, depending on the granularity of the recorded time). Both tables have timestamps, and both have other columns of interest that you may want to compare. A simple ASOF JOIN query will return the closest quote (in time) prior to each trade. In other words, the query asks: What was the price of a given stock at the time I made a trade?

Assume that the trades table contains three rows, and the quotes table contains seven rows. The background color of the cells shows which three rows from quotes will qualify for the ASOF JOIN when the rows are joined on matching stock symbols and their timestamp columns are compared.

TRADES Table (Left or “Preserving” Table)

Trades table data, consisting of three rows, which are joined with three rows in the quotes table.

QUOTES Table (Right or “Referenced” Table)

Quotes table data, consisting of seven rows, identifying the three specific rows that qualify for the join with the quotes table.

To see the syntax that produces the combination of these three highlighted rows (and many other examples), see Join with match and ON conditions.

Language: English