I’m trying to optimise some SQL queries in a script of mine that uses a SQLite database. Currently I loop through creating multiple queries as below to get aggregate data for a list of IP addresses. Is there a way to run this as one query instead? Desired output is the “top 20 domains” for each IP.
SELECT ipaddr, domain, Count(domain) Count FROM tablea WHERE (ipaddr = 'IP') GROUP BY domain ORDER BY COUNT(domain) DESC LIMIT 20;
Current results, with 1 query run per IP. I’d like to combine these into 1 query if possible.
IP1|DOMAIN1|COUNT IP1|DOMAIN2|COUNT IP2|DOMAIN3|COUNT IP2|DOMAIN4|COUNT
Advertisement
Answer
You must group by ipaddr
and domain
and use ROW_NUMBER()
(or RANK()
) window function to rank the the domains:
SELECT ipaddr, domain, Count FROM ( SELECT ipaddr, domain, COUNT(*) Count, ROW_NUMBER() OVER (PARTITION BY ipaddr ORDER BY COUNT(*) DESC) rn FROM tablea WHERE ipaddr IN ('IP1', 'IP2', 'IP3', ...) GROUP BY ipaddr, domain ) WHERE rn <= 20 ORDER BY ipaddr, Count DESC