按条件和精度从 MySql 中获取百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1576370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting a percentage from MySql with a group by condition, and precision
提问by user170579
I was about to ask the MySql list this and remembered about SO.
我正要问 MySql 列表并记住了 SO。
Running MySql 5.0.85, I need to be as efficient as possible about a few queries. If I could get a little review, I would appreciate it.
运行 MySql 5.0.85，我需要尽可能高效地处理一些查询。如果我能得到一点评论，我将不胜感激。
I collect data in the millions, and need the top 50 grouped by one field, with a percentage of how much those top 50 occupy.
我收集数以百万计的数据，需要按一个领域分组前 50 名，以及前 50 名所占的百分比。
Here is what I have come up with... 1) I have a feeling I can be more efficient, perhaps with a join 2) How can I get the percentage to be of precision in the hundredths, so * 100.00 ie: .07 becomes 7.00, getting SQL errors if I (percentage * 100)
这是我想出的...... 1）我觉得我可以更有效率，也许加入 2）我怎样才能让百分比精确到百分之一，所以 * 100.00 即：.07变为 7.00，如果 I（百分比 * 100），则会出现 SQL 错误
SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount,
COUNT( * ) / ( SELECT COUNT( * ) FROM agents ) AS percentage
FROM agents
GROUP BY user_agent_parsed
ORDER BY thecount DESC LIMIT 50;
Second issue, once a day I need to archive the result of the above. Any suggestions on how to best to do that? I can schedule with cron, or in my case, launchd, unless someone has a better suggestion.
第二个问题，我每天需要将上述结果存档一次。关于如何最好地做到这一点的任何建议？除非有人有更好的建议，否则我可以使用 cron 进行安排，或者在我的情况下使用 launchd。
Would you think that a simple 'SELECT (the above) INTO foo' would suffice?
你认为一个简单的 'SELECT (the above) INTO foo' 就足够了吗？
回答by lexu
First Issue:
首要问题：
select count(*) from agents into @AgentCount;
SELECT user_agent_parsed
, user_agent_original
, COUNT( user_agent_parsed ) AS thecount
, COUNT( * ) / ( @AgentCount) AS percentage
FROM agents
GROUP BY user_agent_parsed
ORDER BY thecount DESC LIMIT 50;
回答by junmats
I quite don't understand your question fully so I'll just answer first your question on how to get the percentage. And I'll use your present query.
我完全不明白你的问题，所以我会先回答你关于如何获得百分比的问题。我会使用你现在的查询。
SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount,
((COUNT( * ) / ( SELECT COUNT( * ) FROM agents)) * 100 ) AS percentage
FROM agents
GROUP BY user_agent_parsed
ORDER BY thecount DESC LIMIT 50;
In order for me to help you further, I think I need you to elaborate it further ;-)
为了让我进一步帮助您，我想我需要您进一步详细说明;-)
