mysql - MySQL查询平均3列，排除0？

这显然是错误的，但是对3列的SUM求平均值并排除0的正确方法是什么？

SELECT (
    AVG(NULLIF(`dices`.`Die1`,0)) +
    AVG(NULLIF(`dices`.`Die2`,0)) +
    AVG(NULLIF(`dices`.`Die3`,0))
) /3  as avgAllDice
FROM (
    SELECT `Die1`,`Die2`,`Die3` FROM `GameLog`
    WHERE PlayerId = "12345"
) dices

谢谢。

最佳答案

如果我要保留内联视图查询（不清楚为什么需要它）。我可能会做这样的事情：

SELECT AVG( NULLIF( CASE d.i
                    WHEN 1 THEN dices.`Die1`
                    WHEN 2 THEN dices.`Die2`
                    WHEN 3 THEN dices.`Die3`
                    END
            ,0)
       ) AS `avgAllDice`
  FROM ( SELECT gl.`Die1`
              , gl.`Die2`
              , gl.`Die3`
           FROM `GameLog` gl
          WHERE gl.playerId = '12345'
       ) dices
 CROSS
  JOIN ( SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 ) d

诀窍是交叉联接操作，给我从骰子返回的每一行三行，以及一个表达式，该表达式分别在三行的每一行中分别选择Die1，Die2和Die3的值。

为了排除0值，我们将0替换为NULL（因为AVG不包含NULL值。）

现在，将所有非零DieN值堆叠到单个列中，我们可以仅使用AVG函数。



另一种方法是获取Die1，Die2，Die3 ....中每一个的分子和分母，然后将分子求和，将分母求和，然后将总分子除以总分母。

这将给出等效的结果。

SELECT ( IFNULL(t.n_die1,0) + IFNULL(t.n_die2,0) + IFNULL(t.n_die3,0) )
     / (        t.d_die1    +        t.d_die2    +        t.d_die3    )
       AS avgAllDice
  FROM ( SELECT SUM(  NULLIF(gl.die1,0))   AS n_die1
              , COUNT(NULLIF(gl.die1,0))   AS d_die1
              , SUM(  NULLIF(gl.die2,0))   AS n_die2
              , COUNT(NULLIF(gl.die2,0))   AS d_die2
              , SUM(  NULLIF(gl.die3,0))   AS n_die3
              , COUNT(NULLIF(gl.die3,0))   AS d_die3
           FROM `GameLog` gl
          WHERE gl.playerid = '12345'
       ) t

（我没有计算出在边角情况下返回的结果... GameLog中没有匹配的行，对于任何一个查询，Die1，Die2和Die3的所有值均为零，依此类推。 。结果可能会略有不同，返回零而不是NULL，除以零边沿大小写等）。

跟进

我对两个查询都进行了快速测试。

CREATE DATABASE d20170228 ;
USE d20170228 ;
CREATE TABLE GameLog
( playerid VARCHAR(5) DEFAULT '12345'
, die1 TINYINT
, die2 TINYINT
, die3 TINYINT
);
INSERT INTO GameLog (die1,die2,die3)
VALUES (3,0,0),(2,1,0),(4,3,3),(3,3,3),(0,0,0),(4,4,4),(5,4,0),(0,0,2)
;
SELECT (3+2+1+4+3+3+3+3+3+4+4+4+5+4+2)/15  AS manual_avg

manual_avg即将发布3.2。

两个查询也都返回3.2

关于mysql - MySQL查询平均3列，排除0？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42521808/