sql - SQL:每个值​​的最大出现次数

Question

我有一个非常简单的表（LOG），其中包含属性MAC_ADDR，IP_SRC，IP_DST，URL，PROTOCOL。我希望前n行包含IP_SRC，URL，＃OfOccurrences，方法是在PROTOCOL ='DNS'时通过减少表中每个IP_SRC的#OfOccurrences来排序。

更清楚地说，我希望能够列出表中每个IP_SRC的前n个访问量最大的页面。

我可以获得每个IP_SRC的访问量最大的URL，如下所示：

select ip_src,url,cnt
from (
    select ip_src,url,count(*) as cnt,protocol
    from log as b group by ip_src,url order by ip_src,cnt desc
) as c
where cnt>=(select MAX(cpt)
            from (select count(*) as cpt from log as b
            where c.ip_src==b.ip_src group by ip_src,url)
           )
      and protocol='DNS';

但是，此解决方案显然未优化。

这是更实用的代码（用于每个IP_SRC访问量最大的URL）：

select ip_src,url,cnt
from (select ip_src,url,count(*) as cnt
      from log where protocol='DNS'
      group by ip_src,url
      order by ip_src,cnt asc)
group by ip_src;

第二种选择更快！但是，我想要每个IP_SRC的n个访问量最大的页面，我不知道该怎么做。

谢谢你的帮助。

Answer 1

使用common table expression：

WITH Temp1 AS (
  SELECT ip_src, url, count(*) AS cnt
  FROM Log
  WHERE protocol = 'DNS'
  GROUP BY ip_src, url
)
SELECT ip_src, url, cnt
FROM Temp1 AS T1
WHERE url IN (
  SELECT url
  FROM Temp1 AS T2
  WHERE T2.ip_src = T1.ip_src
    AND T2.cnt >= T1.cnt
  ORDER BY cnt DESC
  LIMIT 3  -- or whatever you want it to be
)
ORDER BY ip_src ASC, cnt DESC;