按条件和精度从 MySql 中获取百分比

2021-12-27 00:00:00 group-by mysql

我正要问 MySql 列表并记住了 SO.

I was about to ask the MySql list this and remembered about SO.

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

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.

我收集了数以百万计的数据，需要按一个字段分组前 50 名，以及前 50 名所占的百分比.

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.

这是我想出来的……1)我觉得我可以更有效率，也许加入2) 我怎样才能得到百分数精度的百分比，所以 * 100.00即:.07 变为 7.00，如果 I (percentage * 100)

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;

第二个问题，每天一次我需要将上述结果存档.关于如何最好地做到这一点的任何建议?我可以使用 cron 进行调度，或者在我的情况下使用 launchd，除非有人有更好的建议.

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.

您认为简单的SELECT(上述)INTO foo"就足够了吗?

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

推荐答案

第一期:

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;

相关文章