Vertica - Join on MAX(Date)

Question

I m trying to replicate this sql syntax in Vertica, but it returns "ERROR: Subqueries in the ON clause are not supported". The aim is to join two tables, table1 and table2, on column and date, if a.date = b.date or the closest but lesser b.date. Any hint?

SELECT      *
FROM        table1 a
LEFT JOIN   table2 b 
ON a.column = b.column
AND         b.Date = (SELECT   MAX (b2.Date) 
                      FROM     table2 b2 
                      WHERE    a.column = b2.column
                      AND      b2.Date <= a.Date)

Answer 1

Vertica has something handier for that: the event series join. With that, you can OUTER JOIN two tables so that they match the same or the immediately preceding value of the join column of the other table. The predicate is INTERPOLATE PREVIOUS VALUE instead of an equi-predicate.

Here's an example with the oil pressure curve and the engine rpm curve with the timestamp matching only once.

DROP TABLE IF EXISTS oilpressure;                                                                                                               
CREATE TABLE oilpressure (
 op_vid,op_ts,op_psi
) AS (
          SELECT 42,TIMESTAMP '2020-04-01 07:00:00', 25.356
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:12', 35.124
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:23', 47.056
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:34', 45.225
)
;

DROP TABLE IF EXISTS revspeed;
CREATE TABLE revspeed (
 rs_vid,rs_ts,rpm
) AS (
          SELECT 42,TIMESTAMP '2020-04-01 07:00:00', 2201
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:10', 3508
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:20', 6504
UNION ALL SELECT 42,TIMESTAMP '2020-04-01 07:00:30', 6608
)
;
-- without
\pset null '(null)'
SELECT *
FROM oilpressure
LEFT OUTER JOIN revspeed
ON op_vid=rs_vid AND op_ts=rs_ts ;
-- out Null display is "(null)".
-- out  op_vid |        op_ts        | op_psi | rs_vid |        rs_ts        |  rpm   
-- out --------+---------------------+--------+--------+---------------------+--------
-- out      42 | 2020-04-01 07:00:00 | 25.356 |     42 | 2020-04-01 07:00:00 |   2201
-- out      42 | 2020-04-01 07:00:12 | 35.124 | (null) | (null)              | (null)
-- out      42 | 2020-04-01 07:00:23 | 47.056 | (null) | (null)              | (null)
-- out      42 | 2020-04-01 07:00:34 | 45.225 | (null) | (null)              | (null)

-- with
SELECT *
FROM oilpressure
LEFT OUTER JOIN revspeed
ON op_vid=rs_vid AND rs_ts INTERPOLATE PREVIOUS VALUE op_ts;
-- out  op_vid |        op_ts        | op_psi | rs_vid |        rs_ts        | rpm  
-- out --------+---------------------+--------+--------+---------------------+------
-- out      42 | 2020-04-01 07:00:00 | 25.356 |     42 | 2020-04-01 07:00:00 | 2201
-- out      42 | 2020-04-01 07:00:12 | 35.124 |     42 | 2020-04-01 07:00:10 | 3508
-- out      42 | 2020-04-01 07:00:23 | 47.056 |     42 | 2020-04-01 07:00:20 | 6504
-- out      42 | 2020-04-01 07:00:34 | 45.225 |     42 | 2020-04-01 07:00:30 | 6608