hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Cam Bazz <camb...@gmail.com>
Subject why this query gives wrong results
Date Wed, 23 Feb 2011 11:16:28 GMT
Hello,

I have three tables, one that counts hits, the other unique visits,
and the other clicks on that page:

The query below will fail to produce correct results: (number of
uniques is wrong, always set to 8, same number for all)

select h.sel_sid, h.hits, u.uniques, if(c.clicks is not null,
c.clicks, 0) from selection_daily_hits h left outer join
selection_daily_uniques u left outer join selection_daily_clicks c on
(h.sel_sid = u.sel_sid and h.sel_sid = c.sel_sid and h.date_day =
'20110211' and u.date_day = '20110211' and c.date_day = '20110211');

where the query below will work and provide correct results

select h.sel_sid, h.hits, u.uniques, if(c.clicks is not null,
c.clicks, 0) from selection_daily_hits h left outer join
selection_daily_uniques u left outer join selection_daily_clicks c on
(h.sel_sid = u.sel_sid and c.sel_sid = h.sel_sid and h.date_day =
'20110211' and u.date_day = '20110211' and c.date_day = '20110211');

the only difference is, on the non working query I have h.sel_sid =
c.sel_sid and in the working query I have c.sel_sid = h.sel_sid

notice that while the first and second table will always have the same
number keys, the third table might not have some keys, hence those
lines are converted to 0.

Best Regards,
-C.B.

Mime
View raw message