MySQL 查询平均 3 列并排除 0？

Question

这显然是错误的，但是对 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

谢谢。

Answer 1

如果我保留内联 View 查询(不清楚为什么需要它)。我可能会做这样的事情:

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