Hi Jeremy, Adding to my response ....
1. Hive doesn't support named insertion , so need to use other ways of insertion data in hive table .. 2. As you know , hive doesn't support LEFT JOIN LATERAL. Query , I given , is producing same result . hope that it can help you formulate things and achieve the same in hive. On Apr 5, 2015 3:55 PM, "@Sanjiv Singh" <sanjiv.is...@gmail.com> wrote: > -- create table lhs > > create table lhs ( > subject_id int, > date_time BIGINT > ); > > -- insert some records in table lhs , named insertion will not work > in case of hive > > insert into table lhs select 1,1000 from tmpTableWithOneRecords limit 1; > insert into table lhs select 1,1100 from tmpTableWithOneRecords limit 1; > insert into table lhs select 1,2000 from tmpTableWithOneRecords limit 1; > insert into table lhs select 2,1002 from tmpTableWithOneRecords limit 1; > insert into table lhs select 2,1998 from tmpTableWithOneRecords limit 1; > > create table events ( > subject_id int, > date_time BIGINT, > event_val int > ); > > insert into table events select 1,999, 1 from > tmpTableWithOneRecords limit 1; > insert into table events select 1,1000, 2 from > tmpTableWithOneRecords limit 1; > insert into table events select 1,1001, 3 from > tmpTableWithOneRecords limit 1; > insert into table events select 1,1999, 4 from > tmpTableWithOneRecords limit 1; > insert into table events select 1,2000, 5 from > tmpTableWithOneRecords limit 1; > insert into table events select 1,2001, 6 from > tmpTableWithOneRecords limit 1; > > insert into table events select 2,999, 10 from > tmpTableWithOneRecords limit 1; > insert into table events select 2,1000, 20 from > tmpTableWithOneRecords limit 1; > insert into table events select 2,1001, 30 from > tmpTableWithOneRecords limit 1; > insert into table events select 2,1999, 40 from > tmpTableWithOneRecords limit 1; > insert into table events select 2,2000, 50 from > tmpTableWithOneRecords limit 1; > insert into table events select 2,2001, 60 from > tmpTableWithOneRecords limit 1; > > > select subject_id,adate,SUM(event_val),COUNT(event_val) from (SELECT > a.subject_id as subject_id ,a.date_time as adate , b.date_time as > bdate , b.event_val as event_val FROM events b LEFT OUTER JOIN lhs a > ON b.subject_id = a.subject_id) abc where bdate < adate group by > subject_id,adate; > > > > 1 1000 1 1 > 1 1100 6 3 > 1 2000 10 4 > 2 1002 60 3 > 2 1998 60 3 > > > On 4/5/15, Jeremy Davis <jda...@datasong.com> wrote: > > 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 > > > > > > > > > -- > Regards > Sanjiv Singh > Mob : +091 9990-447-339 >
