mysql不同组的和及百分比,通过条件和精度从组中获取MySql中的百分比

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;