google big query on sorting unique combination of multiple columns

Question

We already have a table of all referral of a website: ( one entry per referral )

String : referral
String : target
integer: date

Now, we want to use big query to sort all referrals based on counts, like:

referral       : target,         count:

google.com/... : welcome.html,   28353 
bing.com/...   : welcome.html,   5334 
gmail.com/...  : about.html,     343
...

What should the big query sql be?

score 4 · Accepted Answer · answered Jan 07 '13 at 21:36

4

why not just group by both columns?

SELECT referral, target, COUNT(*) as cnt
FROM [mydataset.referrallog] 
GROUP BY referral, target
ORDER BY cnt DESC

answered Jan 07 '13 at 21:36

Jordan Tigani

26,089
4
60
63

score 1 · Answer 2 · edited Jan 08 '13 at 01:00

1

if I got you right, so:

SELECT COUNT(path) AS path_count, path
FROM (
  SELECT concat(referrer, target) AS path
  FROM [mydataset.referrallog])
GROUP BY path ORDER BY path_count DESC;

edited Jan 08 '13 at 01:00

Michael Manoochehri

7,931
6
33
47

answered Dec 30 '12 at 21:15

user1516770

697
1
9
20

I have to remove "distinct", so it outputs count correctly. "SELECT count (path), path FROM (select concat(referrer, target) as path FROM [mydataset.referrallog]) group by path" – Tom Fishman Dec 31 '12 at 21:09
But this sql hasn't sort the result based on count yet. – Tom Fishman Dec 31 '12 at 21:11
So the final answer is "SELECT count (path) as path_count, path FROM (select concat(referrer, target) as path FROM [mydataset.referrallog]) group by path order by path_count desc". – Tom Fishman Dec 31 '12 at 21:12
user1516770: If you can edit your original answer, I will approve it! – Tom Fishman Dec 31 '12 at 21:12

google big query on sorting unique combination of multiple columns

2 Answers2