CODEWITHSUNDEEP
sqldatejoinvertica
Steeev
Steeev

Reputation: 51

Join one row with last foregoing date in Vertica

I want to get the status in table B that was current on the date of an event in table A.

Table A
Person: Date:       Event
Bob     2020-01-05  Party

Table B
Person:  Date: Status
Bob      2020-01-08     Awake 
Bob      2020-01-03     Dinner
Bob      2020-01-01     Cycling

So the right result will be

Bob     2020-01-05  Party Dinner

I tried this but got error 'Correlated subquery expression without aggregates and with limit is not supported'.

SELECT 
    A.Person, 
    A.Date, 
    A.Event,
    (
        SELECT Status FROM B
        WHERE A.Person = B.Person AND B.Date <= A.Date
        ORDER BY B.Date desc
        LIMIT 1
    ) AS Status
FROM A

Can anyone tell me what I should do instead? I tried a few other ideas but keep running into other errors.

Upvotes: 1

Views: 380

Answers (2)

marcothesane
marcothesane

Reputation: 6749

Vertica has the "Event Series Join" !

left, right or full outer join two tables with the INTERPOLATE PREVIOUS value predicate, which joins the current row with the immediately preceding row of the other table, according to the join column:

WITH 
a(person, dt,Event) AS (
SELECT 'Bob',DATE '2020-01-05','Party'
)
,
b(person,dt,status) AS (
          SELECT 'Bob', DATE '2020-01-08','Awake'
UNION ALL SELECT 'Bob', DATE '2020-01-03','Dinner'
UNION ALL SELECT 'Bob', DATE '2020-01-01','Cycling'
)
SELECT
  a.person
, a.dt
, a.event
, b.status
FROM a
LEFT OUTER JOIN b
 ON a.person = b.person
AND a.dt INTERPOLATE PREVIOUS VALUE b.dt
;
-- out  person |     dt     | event | status 
-- out --------+------------+-------+--------
-- out  Bob    | 2020-01-05 | Party | Dinner

Upvotes: 1

GMB
GMB

Reputation: 222632

If you want the result for just one person, you can just sort and limit:

select
    a.person,
    a.date,
    a.event,
    b.status
from tablea a
inner join tableb b on b.person = a.person
where a.person = 'Bob'
order by b.date desc
limit 1

If you want to handle more than than one person at a time, an option is to use window functions:

select *
from (
    select
        a.person,
        a.date,
        a.event,
        b.status,
        row_number() over(partition by a.person order by b.date desc) rn
    from tablea a
    inner join tableb b on b.person = a.person
) t
where rn = 1

Upvotes: 1

Related Questions