Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: domain of sanjiv.is.on@gmail.com
 designates 209.85.212.178 as permitted sender)
MIME-Version: 1.0
Reply-To: sanjiv.is.on@gmail.com
In-Reply-To: <D44C0997-D299-4C1D-B6E0-05C55E2465E6@upstreamsoftware.com>
References: <D44C0997-D299-4C1D-B6E0-05C55E2465E6@upstreamsoftware.com>
Date: Sun, 5 Apr 2015 15:55:45 +0530
Message-ID: 
 <CAAef+M7HQzdodT71S_R0OGDHa-oNyREtp4t1A8yXPKSfP3nwhQ@mail.gmail.com>
Subject: Re: Is it possible to do a LEFT JOIN LATERAL in Hive?
From: "@Sanjiv Singh" <sanjiv.is.on@gmail.com>
To: user@hive.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

--  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 =3D a.subject_id) abc where bdate < adate group by
subject_id,adate;


=09
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 LH=
S
> 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
> );
>
>        =E2=80=94Subjects 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 =3D lhs.subject_id ) rhs1 ON true;
>
>
>        insert into events (subject_id, date_time, event_val) values (1,99=
9,
> 1);
>        insert into events (subject_id, date_time, event_val) values (1,10=
00,
> 2);
>        insert into events (subject_id, date_time, event_val) values (1,10=
01,
> 3);
>        insert into events (subject_id, date_time, event_val) values (1,19=
99,
> 4);
>        insert into events (subject_id, date_time, event_val) values (1,20=
00,
> 5);
>        insert into events (subject_id, date_time, event_val) values (1,20=
01,
> 6);
>
>        insert into events (subject_id, date_time, event_val) values (2,99=
9,
> 10);
>        insert into events (subject_id, date_time, event_val) values (2,10=
00,
> 20);
>        insert into events (subject_id, date_time, event_val) values (2,10=
01,
> 30);
>        insert into events (subject_id, date_time, event_val) values (2,19=
99,
> 40);
>        insert into events (subject_id, date_time, event_val) values (2,20=
00,
> 50);
>        insert into events (subject_id, date_time, event_val) values (2,20=
01,
> 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 =3D lhs.subject_id ) rhs1 ON true;
>
>        =E2=80=94results:
>        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
>
>
>


--=20
Regards
Sanjiv Singh
Mob :  +091 9990-447-339