All Forums Hadoop
karthikcsekar 17 posts Joined 02/14
23 Aug 2015
Need to get common users for every combination of user IDs

Hi,
I have a table which has one user ID mapped to multiple other user IDs. I want to get the common user IDs mapped to every two user IDs
 
My Data looks in the following way

User ID	User ID 1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9
2	1
2	3
2	5
2	7
2	9
2	11
2	13
2	15
2	17
.	.
.	.
.	.
.	.
.	.
.	.
2000	2001

User ID and User ID1 are 2 different columns. For each user ID in column 1 there are multiple user ID mapping  in column 2
I need to get for every User ID combination(Column 1 combination eg:(1 and 2), (1 and 3) and so on) starting from (1,2) till (1999,2000) how many common User ID1's are there
 
Thanks in advance for any solution

CK
dnoeth 4628 posts Joined 11/04
23 Aug 2015
SELECT t1.UserId, t2.UserId, COUNT(*)
FROM tab AS t1 
JOIN tab AS t2
  ON t1.UserId < t2.UserId   -- different user id 
 AND t1.UserId1 = t2.UserId1 -- same user id 1
GROUP BY 1,2 

 

Dieter

karthikcsekar 17 posts Joined 02/14
23 Aug 2015

Thanks Dieter for the solution

CK

You must sign in to leave a comment.