Event Series Joins

An event series join is a Vertica SQL extension that enables the analysis of two series when their measurement intervals don’t align precisely, such as with mismatched timestamps. You can compare values from the two series directly, rather than having to normalize the series to the same measurement interval.

Event series joins are an extension of Outer Joins, but instead of padding the non-preserved side with NULL values when there is no match, the event series join pads the non-preserved side values that it interpolates from the previous value.

The difference in how you write a regular join versus an event series join is the INTERPOLATE predicate, which is used in the ON clause. For example, the following two statements show the differences, which are shown in greater detail in Writing Event Series Joins.

SELECT * FROM hTicks h FULL OUTER JOIN aTicks a 
ON (h.time = a.time);

SELECT * FROM hTicks h FULL OUTER JOIN aTicks a 
ON (h.time INTERPOLATE PREVIOUS VALUE a.time);

Similar to regular joins, an event series join has inner and outer join modes, which are described in the topics that follow.

For full syntax, including notes and restrictions, see INTERPOLATE in the SQL Reference Manual