INTERPOLATE

Used to join two event series together using some ordered attribute, event series joins let you compare values from two series directly, rather than having to normalize the series to the same measurement interval.

Syntax

expression1 INTERPOLATE PREVIOUS VALUE expression2

Parameters

expression1
expression2

Acolumn reference from one the tables specified in the FROM Clause.

The referenced columns are typically a DATE/TIME data type, often TIMESTAMP, inasmuch as you are joining data that represents an event series; however, the referenced columns can be of any type.

PREVIOUS VALUE

Pads the non-preserved side with the previous values from relation when there is no match.

Input rows are sorted in ascending logical order of the join column.

Note: An ORDER BY clause, if used, does not determine the input order but only determines query output order.

Description

Restrictions

Example

The examples that follow use this simple schema.

CREATE TABLE t(x TIME);
CREATE TABLE t1(y TIME);
INSERT INTO t VALUES('12:40:23');
INSERT INTO t VALUES('14:40:25');
INSERT INTO t VALUES('14:45:00');
INSERT INTO t VALUES('14:49:55');
INSERT INTO t1 VALUES('12:40:23');
INSERT INTO t1 VALUES('14:00:00');
COMMIT;

Normal Full Outer Join

=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x = t1.y;

Notice the null rows from the non-preserved table:

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 |
 14:45:00 |
 14:49:55 |
          | 14:00:00
(5 rows)

Full Outer Join with Interpolation

=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x INTERPOLATE 
PREVIOUS VALUE t1.y;

In this case, the rows with no entry point are padded with values from the previous row.

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 12:40:23 | 14:00:00
 14:40:25 | 14:00:00
 14:45:00 | 14:00:00
 14:49:55 | 14:00:00
(5 rows)

Normal Left Outer Join

=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x = t1.y;

Again, there are nulls in the non-preserved table

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 |
 14:45:00 |
 14:49:55 |
(4 rows)

Left Outer Join with Interpolation

=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x INTERPOLATE
   PREVIOUS VALUE t1.y;

Nulls padded with interpolated values.

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 | 14:00:00
 14:45:00 | 14:00:00
 14:49:55 | 14:00:00
(4 rows)

Inner Joins

For inner joins, there is no difference between a regular inner join and an event series inner join. Since null values are eliminated from the result set, there is nothing to interpolate.

A regular inner join returns only the single matching row at 12:40:23:

=> SELECT * FROM t INNER JOIN t1 ON t.x = t1.y;
    x     |    y
----------+----------
 12:40:23 | 12:40:23
(1 row)

An event series inner join finds the same single-matching row at 12:40:23:

=> SELECT * FROM t INNER JOIN t1 ON t.x INTERPOLATE 
PREVIOUS VALUE t1.y;
    x     |    y
----------+----------
 12:40:23 | 12:40:23
(1 row)

Semantics

When you write an event series join in place of normal join, values are evaluated as follows (using the schema in the above examples):

Note: t LEFT OUTER JOIN t1 is equivalent to t1 RIGHT OUTER JOIN t.

In the case of a full outer join, all values from both tables are preserved.