I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this...
websites
-------
website_id (pk)
url
description
visitors
-------
visitor_id (pk)
ip_address
website_visits
--------
visit_id (pk)
website_id (fk)
visitor_id (fk)
visit_date
visit_duration
I know that I can use two GROUP BY 's to produce a single list ordered by website using the query below...
SELECT w.website_id, v.visitor_id, count(wv.visit_id)
FROM website_visits ws, websites w, visitors v
WHERE wv.website_id = w.website_id and wv.visitor_id = v.visitor_id
GROUP BY w.website_id, v.visitor_id
ORDER BY w.website_id ASC, count(wv.visit_id) DESC
But I would like to improve this by a) only showing the top ten for each site, and b) grouping each top 10 result into an array, so that the results look something like this:
website_id top_ten
432 [{visitor_id:354,visits:958},{visitor_id:987,visits:971}...etc]
325 [{visitor_id:456,visits:955},{visitor_id:1056,visits:465}...etc]
(I have used JSON here but it could be in any format)
I have experimented with using the SELECT ARRAY(...) function in a subquery but I cannot seem to get it working and I am not sure if thats the correct approach?
Can someone advise? Thanks
Using PostgreSQL 9.0 by the way