mysql 中average_MySQL查詢：如何計算一行中的平均值？

1

我偶然發現了類似的情況。這很有用:http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/

From the page:

從頁面:

When we do:

當我們這樣做時:

SELECT *, (V.rank_0 + V.rank_1 + V.rank_2) / 3

AS row_avg FROM voting V

we only receive correct averages for the rows where all values are not NULL. But when I have e.g. 3, NULL, 4 I’d like to get 3.5 as a return. That’s the moment when function COALESCE() comes handy.

我們隻接收所有值都不為NULL的行的正確平均值。但當我有例如3,NULL,4我想得到3.5作為回報。這是函數COALESCE()派上用場的那一刻。

What does COALESCE () do? From MySQL manual we have:

COALESCE()做什麼?從MySQL手冊我們有:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

傳回清單中的第一個非NULL值,如果沒有非NULL值,則傳回NULL。

mysql> SELECT COALESCE(NULL,1);

-> 1

mysql> SELECT COALESCE(NULL,NULL,NULL);

-> NULL

And these information will help us to build another SELECT statement:

這些資訊將幫助我們構建另一個SELECT語句:

SELECT *,

#first part

(COALESCE(V.rank_0, 0)

+ COALESCE(V.rank_1, 0)

+ COALESCE(V.rank_2, 0))

/

#second part

(3 -

(COALESCE(V.rank_0 - V.rank_0, 1)

+ COALESCE(V.rank_1 - V.rank_1, 1)

+ COALESCE(V.rank_2 - V.rank_2, 1))

) AS row_avg FROM voting V