我如何获得一个组的mysql总行数的百分比? [英] How can I get the percentage of total rows with mysql for a group?
问题描述
下面我有一个查询,将从用户代理表和IP地址链接表中获取网站的最常见用户代理:
below I have a query that will get the most common user agents for a site from a table of user agents and a linked table of ip addresses:
SELECT count(*) as num, string FROM `useragent_ip`
left join useragents on useragent_id = useragents.id
group by useragent_id
having num > 2
order by num desc, string
有时它会向我显示类似的东西
Sometimes it will show me something like
25 Firefox
22 IE
11 Chrome
3 Safari
1 Spider 1
1 Spider 2
1 Spider 3
我的问题是,是否有一种方法可以使既然左侧的数字代表一个整体的百分比,并且会随着时间而增长,那么我能否使用sql语句的一部分来显示每个组在整体中的百分比?这样一来,不用使用having num > 2
,我可以做些事情,说获得总行数的百分比,而不仅仅是行数?
My question is if there is a way that since the numbers on the left represent percentages of a whole, and will grow with time, can I have part of the sql statement to show each group's percentage of the whole? So that instead of using having num > 2
then I could do something that would say get the percentage of the total rows instead of just the number of rows?
推荐答案
是的,您可以:
select num, string, 100 * num / total as percent
from (
select count(*) as num, string
from useragent_ip
left join useragents on useragent_id = useragents.id
group by useragent_id) x
cross join (
select count(*) as total
from useragent_ip
left join useragents on useragent_id = useragents.id) y
order by num desc, string;
我删除了having num > 2
,因为它似乎没有意义.
I removed the having num > 2
, because it didn't seem to make sense.
这篇关于我如何获得一个组的mysql总行数的百分比?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!