hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Davis <jda...@datasong.com>
Subject Is it possible to do a LEFT JOIN LATERAL in Hive?
Date Sat, 04 Apr 2015 23:08:57 GMT
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



Mime
View raw message