Is it possible to do a LEFT JOIN LATERAL in Hive?

[Jeremy Davis]

Hello!
I would like to do a LEFT JOIN LATERAL .. Which is using values on the LHS as 
parameters on the RHS. Is this sort of thing possible in Hive? 


-JD


---- Some example SQL:


create table lhs (
    subject_id integer,
    date_time  BIGINT
);

       —Subjects and responses at Arbitrary response times:
       insert into lhs (subject_id, date_time) values (1,1000);
       insert into lhs (subject_id, date_time) values (1,1100);
       insert into lhs (subject_id, date_time) values (1,2000);
       insert into lhs (subject_id, date_time) values (2,1002);
       insert into lhs (subject_id, date_time) values (2,1998);

create table events (
    subject_id  integer,
    date_time   BIGINT,
    event_val   integer
);

SELECT * from lhs LEFT JOIN LATERAL ( select SUM(event_val) as val_sum, 
count(event_val) as ecnt from events WHERE date_time < lhs.date_time and 
subject_id = lhs.subject_id ) rhs1 ON true;


       insert into events (subject_id, date_time, event_val) values (1,999, 1);
       insert into events (subject_id, date_time, event_val) values (1,1000, 2);
       insert into events (subject_id, date_time, event_val) values (1,1001, 3);
       insert into events (subject_id, date_time, event_val) values (1,1999, 4);
       insert into events (subject_id, date_time, event_val) values (1,2000, 5);
       insert into events (subject_id, date_time, event_val) values (1,2001, 6);

       insert into events (subject_id, date_time, event_val) values (2,999, 10);
       insert into events (subject_id, date_time, event_val) values (2,1000, 
20);
       insert into events (subject_id, date_time, event_val) values (2,1001, 
30);
       insert into events (subject_id, date_time, event_val) values (2,1999, 
40);
       insert into events (subject_id, date_time, event_val) values (2,2000, 
50);
       insert into events (subject_id, date_time, event_val) values (2,2001, 
60);

       SELECT * from lhs LEFT JOIN LATERAL ( select SUM(event_val) as val_sum, 
count(event_val) as ecnt from events WHERE date_time < lhs.date_time and 
subject_id = lhs.subject_id ) rhs1 ON true;

       —results:
       subject_id;date_time;val_sum;ecnt
       1;1000;1;1
       1;1100;6;3
       1;2000;10;4
       2;1002;60;3
       2;1998;60;3