多列的平均值+每列的个别条件

问题描述

我有以下查询:

SELECT `station_id`, AVG(`jan`) AS avg_jan, AVG(`feb`) AS avg_feb, ... , 
AVG(`dec`) AS avg_dec 
FROM `climate_data`

WHERE `element_name` = "Temp_mean_mly" 
AND `jan` <> -999999 
AND `feb` <> -999999
AND ... 
AND `dec` <> -999999 
GROUP BY station_id 

我需要它为每个列分别返回与-999999不同的平均值！当前查询将消除所有列值为-999999(不正确)的行.

I need it to return average of values different than -999999 for each of the columns individually! The current query eliminates all rows with any of columns having value -999999 which is not correct.

推荐答案

您可以像这样使用NULLIF:

SELECT 
    station_id, 
    AVG(NULLIF(jan, -99999)) AS avg_jan, 
    AVG(NULLIF(feb, -99999)) AS avg_feb,
    ...
FROM climate_data
WHERE element_name = 'Temp_mean_mly'
GROUP BY station_id 

之所以可行，是因为聚合函数会跳过NULL值.

This works because aggregate functions skip NULL values.

这篇关于多列的平均值+每列的个别条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！