通过条件和精度从组中获取MySql中的百分比 [英] Getting a percentage from MySql with a group by condition, and precision

问题描述

运行MySql 5.0.85，我需要尽可能高效地处理几个查询。如果我能得到一些评论，我将不胜感激。

我以数百万的数据收集数据，并且需要按照一个字段分组的前50名

下面是我想到的...
1）我有一种感觉，我可以更有效率，也许与一个连接
2）我怎样才能百分之一的精度百分比，所以* 100.00
即：.07变为7.00，如果我（百分比* 100）得到SQL错误

  SELECT user_agent_parsed，user_agent_original，COUNT（user_agent_parsed）AS thecount，
 COUNT（*）/（SELECT COUNT（*）FROM agents） AS百分比
 FROM代理
 GROUP BY user_agent_parsed 
 ORDER BY thecount DESC LIMIT 50; 
  

第二个问题，每天一次，我需要归档上述结果。有关如何最好地做到这一点的任何建议？我可以安排cron，或者在我的情况下，启动，除非有人有更好的建议。

你会认为简单的'SELECT（the above）INTO foo'

解决方案

第一期：

 从代理选择计数（*）到@AgentCount; 
 
 SELECT user_agent_parsed 
，user_agent_original 
，COUNT（user_agent_parsed）AS thecount 
，COUNT（*）/（@AgentCount）AS百分比
 FROM agents 
 GROUP BY user_agent_parsed 
 ORDER BY thecount DESC LIMIT 50; 
  

I was about to ask the MySql list this and remembered about 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.

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.

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)

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.

Would you think that a simple 'SELECT (the above) INTO foo' would suffice?

解决方案

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;

这篇关于通过条件和精度从组中获取MySql中的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！