如何在MySQL中组合聚合函数？

I'm just learning MySQL - is there a way to combine (or nest) aggregate functions?

我只是学习MySQL - 有没有办法组合(或嵌套)聚合函数?

Given a query:

给出一个查询:

SELECT user, count(answer) FROM surveyValues WHERE study='a1' GROUP BY user;

This will give me the number of questions answered by each user. What I really want is the average number of questions answered per user...something like:

这将为我提供每个用户回答的问题数量。我真正想要的是每个用户回答的平均问题数量......例如:

SELECT avg(count(answer)) FROM surveyValues WHERE study='a1';

What's the correct way to compute this statistic?

计算此统计数据的正确方法是什么?

If this is possible, is there a way to then break this statistic down for each question? (users can answer the same question multiple times). Something like:

如果这是可能的,那么有没有办法可以为每个问题打破这个统计数据? (用户可以多次回答同一个问题)。就像是:

SELECT avg(count(answer)) FROM surveyValues WHERE study='a1' GROUP BY question;

2 个解决方案

#1

You have to use subqueries:

你必须使用子查询:

  SELECT x.user, 
         AVG(x.cnt)
    FROM (SELECT user, COUNT(answer) AS cnt
            FROM surveyValues 
           WHERE study='a1' 
        GROUP BY user) x
GROUP BY x.user

You can't wrap an aggregate with another aggregate. You could wrap an analytic in an aggregate, if MySQL supported analytic/ranking/windowing functions...

您无法使用其他聚合包装聚合。如果MySQL支持分析/排名/窗口功能,您可以将分析包装在聚合中...

#2

-3

yes - those all look reasonable.

是的 - 那些看起来都很合理。

Have you tried them and received unexpected results?

你试过它们并收到意想不到的结果吗?

usually, i would expect that you also include the driving column in the select list:

通常,我希望您还在选择列表中包含驱动列:

SELECT question, avg(count(answer)) 
FROM surveyValues 
WHERE study='a1' 
GROUP BY question;

#1