Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of qiaoresearcher@gmail.com
 designates 209.85.192.171 as permitted sender)
MIME-Version: 1.0
Date: Fri, 30 Aug 2013 18:34:24 -0500
Message-ID: 
 <CAO_EuLbbxLCvQSQHpsoMdAUGvBCj7WLH0Nvak3Y=im48ZB9wzw@mail.gmail.com>
Subject: how to write hive query to solve this problem?
From: qiaoresearcher <qiaoresearcher@gmail.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=001a11344ca8a9311004e532a9c1

--001a11344ca8a9311004e532a9c1
Content-Type: text/plain; charset=ISO-8859-1

I have three tables:

Table 1: record when and who visited gas station or not, this contains all
the users of interest, name all the users as a set A
date                         |      user name    |   visited gas station?
2013-09-01                         tom                             yes
2013-09-02                         tom                             yes
2013-09-01                         hanks                          yes
2013-09-03                         tomy                            yes
....                                           ...
       ...

Table 2: record when and who visited Bestbuy,   the user in set A appear
here, but not all users of A will appear in this table, also table 2 has
users does not belong to set A
date                         |      user name    |   visited Bestbuy?
2013-09-01                         tom                             yes
2013-09-02                         jacob                           yes
2013-09-01                         hanks                          yes
2013-09-03                         michael                       yes
....                                           ...
       ...

Table 3: record when and who arrives one of three destinations: CA, NY and
DC, the users in table 3 has similar situation as users in table 2
regarding set A.
date                         |      user name    |   visited Bestbuy or not

2013-09-01                         tom                             CA
2013-09-02                         tom                             NY
2013-09-01                         hanks                          DC
2013-09-03                         tomy                            CA
....                                           ...
       ...

Now we want to know,  within a 90 days period, what are the following
numbers:
(1) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, after that date, user went to
Bestbuy, finally arrive CA
(2) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, then did not visit Bestbuy, finally
arrive CA

(3) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, then went Bestbuy, finally arrive NY
(4) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, then did not visit Bestbuy,
finally arrive NY

(5) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, then went Bestbuy, finally arrive DC
(6) for any given day, for the users in table 1, how many of them has a
path like:   visited Gas station first, then did not visit Bestbuy,
finally arrive DC

It is possible in a given day, a user may visit Bestbuy many times for
table 2, this will be simply regarded as a status 'visited Bestbuy'. One
user will be counted once in a given day.
>From the day of user visiting gas station, within next 90 days, this user
has to arrive one of three final destinations in table 3, and arrives only
one station. no multiple arrived stations.

For example,
a user tom may visit gas station on 9/1, then went to Bestbuy on 9/5,
finally went to CA on 9/30, then this user tom can be counted as 1 on 9/1
for path (1).
a user hanks may visit gas station on 9/1, then went to Bestbuy on 9/5 and
on 9/8, finally went to CA on 9/30, then this user tom count as 1 on 9/1
for path (1).
a user ruby may visit gas station on 9/1, and tables 2 does not have
records to show ruby visit Bestbuy till 11/30, and ruby arrives DC in table
3 before 11/30, then ruby contribute 1 for path (6) for the day 9/1.

How to write Hive query to get those numbers for the six paths?
a sample output will be :
9/1: 100 for path (1), 90 for path (2), ...., etc
9/2: 60 for path (1), 80 for path (2), ...., etc
9/3: ................

Any suggestions or suggested reference/readings will be deeply appreciated.

Thanks!
Qiao

--001a11344ca8a9311004e532a9c1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><br class=3D"">I have three tables:<div><br><div>Tabl=
e 1: record when and who visited gas station or not, this contains all the =
users of interest, name all the users as a set A</div></div><div>date =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0user name =A0 =A0|=
 =A0 visited gas station?=A0</div>
<div>2013-09-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tom =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 yes</div><div><div>2013-09=
-02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tom =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 yes</div><div>2013-09-01 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 hanks =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0yes</div>
<div>2013-09-03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tomy =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0yes</div><div>.... =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 ... =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0...=
</div><div><br></div><div>Table 2: record when and who visited Bestbuy, =A0=
 the user in set A appear here, but not all users of A will appear in this =
table, also table 2 has users does not belong to set A=A0</div>
<div>date =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0user=
 name =A0 =A0| =A0 visited Bestbuy? =A0=A0</div><div>2013-09-01 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tom =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 yes</div><div><div>2013-09-02 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 jacob =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 yes</div>
<div>2013-09-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 hanks =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0yes</div><div>2013-09-03 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 michael =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 yes</div><div>.... =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ... =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0...</div>
</div><div><br></div><div>Table 3: record when and who arrives one of three=
 destinations: CA, NY and DC, the users in table 3 has similar situation as=
 users in table 2 regarding set A. =A0</div><div>date =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0user name =A0 =A0| =A0 visited Bes=
tbuy or not =A0</div>
<div>2013-09-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tom =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 CA</div><div><div>2013-09-=
02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tom =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 NY</div><div>2013-09-01 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 hanks =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0DC</div>
<div>2013-09-03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tomy =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0CA</div><div>.... =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 ... =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0...=
</div></div><div>=A0=A0=A0</div><div>Now we want to know, =A0within a 90 da=
ys period, what are the following numbers:=A0<br>
</div><div>(1) for any given day, for the users in table 1,=A0how many of t=
hem has a path like: =A0 visited Gas station first, after that date, user w=
ent to Bestbuy, finally arrive CA</div><div><div>(2) for any given day, for=
 the users in table 1,=A0how many of them has a path like: =A0 visited Gas =
station first, then did not visit Bestbuy, finally arrive CA</div>
<div></div></div><div><div><br></div><div>(3) for any given day, for the us=
ers in table 1,=A0how many of them has a path like: =A0 visited Gas station=
 first, then went Bestbuy, finally=A0arrive=A0NY</div><div><div>(4) for any=
 given day, for the users in table 1,=A0how many of them has a path like: =
=A0 visited Gas station first, then did not visit Bestbuy, finally=A0arrive=
=A0NY</div>
<div><div><br></div><div>(5) for any given day, for the users in table 1,=
=A0how many of them has a path like: =A0 visited Gas station first, then we=
nt Bestbuy, finally=A0arrive=A0DC</div><div><div>(6) for any given day, for=
 the users in table 1,=A0how many of them has a path like: =A0 visited Gas =
station first, then did not visit Bestbuy, finally=A0arrive=A0DC</div>
<div><br></div><div>It is possible in a given day, a user may visit Bestbuy=
 many times for table 2, this will be simply regarded as a status &#39;visi=
ted Bestbuy&#39;. One user will be counted once in a given day.=A0</div><di=
v>
>From the day of user visiting gas station, within next 90 days, this user h=
as to arrive one of three final destinations in table 3, and arrives only o=
ne station. no multiple arrived stations. =A0=A0</div><div><br></div><div>F=
or example,=A0</div>
<div>a user tom may visit gas station on 9/1, then went to Bestbuy on 9/5, =
finally went to CA on 9/30, then this user tom can be counted as 1 on 9/1 f=
or path (1).</div><div><div>a user hanks may visit gas station on 9/1, then=
 went to Bestbuy on 9/5 and on 9/8, finally went to CA on 9/30, then this u=
ser tom count as 1 on 9/1 for path (1).</div>
<div>a user ruby may visit gas station on 9/1, and tables 2 does not have r=
ecords to show ruby visit Bestbuy till 11/30, and ruby arrives DC in table =
3 before 11/30, then ruby contribute 1 for path (6) for the day 9/1.=A0</di=
v>
<div><br></div><div>How to write Hive query to get those numbers for the si=
x paths?=A0</div><div>a sample output will be : =A0</div><div>9/1: 100 for =
path (1), 90 for path (2), ...., etc =A0</div><div><div>9/2: 60 for path (1=
), 80 for path (2), ...., etc =A0</div>
<div>9/3: ................</div><div><br></div></div><div>Any suggestions o=
r suggested reference/readings will be deeply appreciated.=A0</div><div><br=
></div><div>Thanks!</div><div>Qiao=A0</div><div><br></div></div></div></div=
>
</div></div></div></div><div style><div style><div><div><div><div></div></d=
iv></div><div></div></div></div></div></div>

--001a11344ca8a9311004e532a9c1--