如何在MySQL中组合聚合函数? [英] How to combine aggregate functions in MySQL?

问题描述

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

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

给出查询:

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;

推荐答案

您必须使用子查询:

  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

您不能将一个聚合与另一个聚合一起包装.如果MySQL支持分析/排序/窗口功能，则可以将分析汇总起来.

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中组合聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！