Write a custom join query in hive

I have two tables

table J:
P_ID  event   T_ID              URL
8187    6   14690481058450526   maplestage.com
8187    6   14690481058450527   maplestage.com

Table A:
P_ID  event   T_ID              URL
8187    7   14690481058450526   NULL
8187    7   14690481058450526   NULL
8187    7   14690481058450527   NULL

I have the following query to count the event that are 6 and events that are 7:

SELECT sum(if(j.event=6,1,0)) Type1, j.P_ID, j.URL, sum(if(a.event=7,1,0)) Type2  FROM Tabel_J j LEFT outer join Table_A a on a.T_ID = j.T_ID GROUP BY j.P_ID, j.URL;

The result i am getting is this:

Type1   P_ID          URL         Type2
3       8187      maplestage.com    3

The result i want to get is:

Type1   P_ID          URL         Type2
2       8187      maplestage.com    3

Please can someone help me with this.

Thanks

Answers


From your data, the significance of the T_ID column isn't immediately obvious to me. Having said that, based on what I think you are after (perhaps I'm wrong), this query might get you there:

SELECT
  SUM(IF(event=6,1,0)) Type1,
  P_ID,
  collect_set(URL)[0] URL,
  SUM(IF(event=7,1,0)) Type2
FROM
  (SELECT * FROM tabel_j UNION ALL SELECT * FROM table_a) everything
GROUP BY P_ID;

Need Your Help

Can I have form within a form? I am working on AngularJS

javascript html5 angularjs

What can happen when a submit is done in nested forms?

Get attributes of a div dynamically generated with AJAX

javascript jquery ajax

I would like to get attributes of a div dynamically generated with AJAX. Here's what I've tried:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.