来自多列和空值的每行 SQL 平均值

Question

我有一个记录传感器数据的应用程序，我希望能够从多个传感器生成平均值，可以是一个、两个、三个或很多...

编辑:这些是温度传感器，因此 0 是传感器可能作为值存储在数据库中的值。

我最初的出发点是这个 SQL 查询:

SELECT grid.t5||'.000000' as ts,
avg(t.sensorvalue)  sensorvalue1
, avg(w.sensorvalue)AS sensorvalue2
FROM 
(SELECT generate_series(min(date_trunc('hour', ts))                         
,max(ts), interval '5 min') AS t5   FROM device_history_20865735 where      
 ts between '2015/05/13 09:00' and '2015/05/14 09:00'   ) grid 

 LEFT JOIN device_history_20865735 t ON t.ts >= grid.t5 AND t.ts <  grid.t5 +  interval '5 min' 
 LEFT JOIN device_history_493417852 w ON w.ts >= grid.t5 AND w.ts <  grid.t5 +  interval '5 min' 
--WHERE t.sensorvalue notnull
GROUP  BY grid.t5 ORDER  BY grid.t5

我得到 5 分钟的平均值，因为它更适合我的应用。

预期的结果对于 sensorvalue1 或 2 具有 NULL 值:

ts;sensorvalue1;sensorvalue2
"2015-05-13 09:00:00.000000";19.9300003051758;
"2015-05-13 09:05:00.000000";20;
"2015-05-13 09:10:00.000000";;
"2015-05-13 09:15:00.000000";20.0599994659424;
"2015-05-13 09:20:00.000000";;
"2015-05-13 09:25:00.000000";20.1200008392334;

我的目标是计算所有可用传感器每 5 分钟间隔的平均值，因为 NULL 是一个问题，我想到使用 CASE 语句，所以如果有 NULL 来获取另一个传感器的值......

SELECT grid.t5||'.000000' as ts,
CASE 
        WHEN avg(t.sensorvalue) ISNULL  THEN avg(w.sensorvalue)
        ELSE avg(t.sensorvalue)
END AS sensorvalue
,
CASE 
        WHEN avg(w.sensorvalue) ISNULL  THEN avg(t.sensorvalue)
        ELSE avg(w.sensorvalue)
END AS sensorvalue2
FROM 
(SELECT generate_series(min(date_trunc('hour', ts)),max(ts), interval '5 min') AS t5
FROM device_history_20865735 where      
 ts between '2015/05/13 09:00' and '2015/05/14 09:00'   ) grid 

 LEFT JOIN device_history_20865735 t ON t.ts >= grid.t5 AND t.ts <  grid.t5 +  interval '5 min' 
 LEFT JOIN device_history_493417852 w ON w.ts >= grid.t5 AND w.ts <  grid.t5 +  interval '5 min' 
GROUP  BY grid.t5 ORDER  BY grid.t5

但是为了计算平均值，我必须在此基础上再做一次选择，并根据列数(又名传感器)进行划分，如果它们只有两个，那没关系，但如果有 3 或 4 个传感器，这可能会变得非常困惑，因为每行可能有多个传感器具有 NULL 值...

SQL 是从使用 postgres 9.4 的应用程序(使用 Python)中按语法派生的，所以有没有一种简单的方法可以实现所需的内容，因为我觉得我走的是一条相当复杂的路线...？

编辑 #2:根据您的输入，我生成了这段 SQL 代码，它看起来相当复杂，但如果它可靠且可维护，您可以接受您的想法和审查:

SELECT ts, sensortotal, sensorcount,
CASE
    WHEN sensorcount = 0 THEN -1000
    ELSE sensortotal/sensorcount
END AS sensorAvg

FROM (
    WITH grid as (
          SELECT t5
          FROM (SELECT generate_series(min(date_trunc('hour', ts)), max(ts), interval '5 min') as t5
                FROM device_history_20865735
               ) d
          WHERE t5 between '2015-05-13 09:00' and '2015-05-14 09:00'   
         )
    SELECT d1.t5 || '.000000' as ts
           , Coalesce(avg(d1.sensorvalue), 0) + Coalesce(avg(d2.sensorvalue),0) as sensorTotal
           , (CASE
                    WHEN avg(d1.sensorvalue) ISNULL THEN 0
                    ELSE 1
           END + CASE
            WHEN avg(d2.sensorvalue) ISNULL THEN 0
            ELSE 1
           END) as sensorCount

    FROM (SELECT grid.t5, avg(t.sensorvalue) as sensorvalue
          FROM grid LEFT JOIN
               device_history_20865735 t
               ON t.ts >= grid.t5 AND t.ts <grid.t5 +  interval '5 min' 
          GROUP BY grid.t5
         ) d1 LEFT JOIN
         (SELECT grid.t5, avg(t.sensorvalue) as sensorvalue
          FROM grid LEFT JOIN
               device_history_493417852 t
               ON t.ts >= grid.t5 AND t.ts <grid.t5 +  interval '5 min' 
         GROUP BY grid.t5
         ) d2 on d1.t5 = d2.t5
    GROUP BY d1.t5
    ORDER BY d1.t5
) tmp;

谢谢!

Answer 1

听起来你想要这样的东西:

(coalesce(value1,0) + coalesce(value2,0) + coalesce(value3,0)) /
(value1 IS NOT NULL::int + value2 IS NOT NULL::int + value3 IS NOT NULL::int)
AS average

基本上，只需对每一行进行计算即可。唯一“棘手”的部分是如何“计算”非空值——我使用了强制转换，但还有其他选项，例如:

CASE WHEN value1 IS NULL THEN 0 ELSE 1 END