hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "@Sanjiv Singh" <sanjiv.is...@gmail.com>
Subject Re: Is it possible to do a LEFT JOIN LATERAL in Hive?
Date Sun, 05 Apr 2015 10:25:45 GMT
--  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 <jdavis@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

Mime
View raw message