-- 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 <[email protected]> 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
