如果您有固定数量的传感器并且几乎从不更新行，则将数据存储在 IoTData 对于每个时间戳只有一行的情况，就像您所做的那样，可以获得最小的存储大小和良好的性能。
但是，您正接近允许的绝对最大列数，这表明您可能朝着错误的方向移动。请参见：
select或create语句中的数据库列
如果以后需要添加/删除传感器，则必须调整表结构以及依赖于它的所有内容，包括我将提供的函数。痛苦的过程。因此，通常情况下，使用一个更通用的模型，每个度量值有一行（800行而不是只有一行）会更合理。这使存储空间膨胀，但用途更广。
也就是说，它可以这样工作：

CREATE TEMP TABLE threshold (
   sensorname varchar(10), 
   minlimit numeric, 
   maxlimit numeric
);

INSERT INTO threshold VALUES
  ('Sensor1', 80 , 115)
, ('Sensor2', 60 , 70)
, ('Sensor3', 100, 120)
-- more
;

CREATE TABLE iotdata (
   iotdatetime timestamp PRIMARY KEY
 , sensor1 numeric(3)
 , sensor2 numeric(8,5)
 , sensor3 numeric(5,2)
);

INSERT INTO iotdata VALUES
  ('2020-01-01 11:05:00', 85, 65, 110)
, ('2020-01-01 11:10:00', 86, 11, 109)  -- low
, ('2020-01-01 11:15:00', 77, 15, 666)  -- low + hi
;

TABLE threshold;

sensorname | minlimit | maxlimit
:--------- | -------: | -------:
Sensor1    |       80 |      115
Sensor2    |       60 |       70
Sensor3    |      100 |      120

-- pivot table threshold to match pivoted data
CREATE TABLE dim_threshold AS
SELECT *
FROM  crosstab(
 $$(
   SELECT 'min' AS dimension, sensorname, minlimit
   FROM   threshold
   ORDER  BY sensorname
   )
   UNION ALL
   (
   SELECT 'max' AS dimension, sensorname, maxlimit
   FROM   threshold
   ORDER  BY sensorname
   )$$
   , $$(SELECT unnest('{Sensor1,Sensor2,Sensor3}'::text[]))$$
   ) AS (dimension text, sensor1 numeric, sensor2 numeric, sensor3 numeric);

TABLE dim_threshold;

dimension | sensor1 | sensor2 | sensor3
:-------- | ------: | ------: | ------:
min       |      80 |      60 |     100
max       |     115 |      70 |     120

TABLE iotdata;

iotdatetime         | sensor1 |  sensor2 | sensor3
:------------------ | ------: | -------: | ------:
2020-01-01 11:05:00 |      85 | 65.00000 |  110.00
2020-01-01 11:10:00 |      86 | 11.00000 |  109.00
2020-01-01 11:15:00 |      77 | 15.00000 |  666.00

-- aux function to calculate percentage
CREATE FUNCTION f_calc_pct(bigint, bigint)
  RETURNS float LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ($1 * 100)::float / $2';

-- main function
CREATE OR REPLACE FUNCTION udf_sensor_fail_per_day(pstartdate timestamp
                                                 , penddate   timestamp)
  RETURNS TABLE(the_day date, dimenstion text, sensor1 float, sensor2 float, sensor3 float) -- more?
  LANGUAGE sql ROWS 100 AS
$func$
WITH cte AS (
   SELECT iotdatetime::date AS the_day
        , count(*) AS ct
        , count(*) FILTER (WHERE i.sensor1 < min.sensor1) AS s1_min
        , count(*) FILTER (WHERE i.sensor2 < min.sensor2) AS s2_min
        , count(*) FILTER (WHERE i.sensor3 < min.sensor3) AS s3_min
      -- more ...
        , count(*) FILTER (WHERE i.sensor1 > max.sensor1) AS s1_max
        , count(*) FILTER (WHERE i.sensor2 > max.sensor2) AS s2_max
        , count(*) FILTER (WHERE i.sensor3 > max.sensor3) AS s3_max
      -- more ...
   FROM   iotdata i
   CROSS  JOIN (SELECT * FROM dim_threshold WHERE dimension = 'min') min
   CROSS  JOIN (SELECT * FROM dim_threshold WHERE dimension = 'max') max
   WHERE  iotdatetime >= pstartdate
   AND    iotdatetime <  penddate
   GROUP  BY 1
   )
SELECT the_day, 'min' AS dimension
     , f_calc_pct(s1_min, ct) -- AS s1
     , f_calc_pct(s2_min, ct) -- AS s2
     , f_calc_pct(s3_min, ct) -- AS s3
    -- more ...
FROM   cte
UNION ALL
SELECT the_day, 'max' AS dimension
     , f_calc_pct(s1_max, ct) -- AS s1
     , f_calc_pct(s2_max, ct) -- AS s2
     , f_calc_pct(s3_max, ct) -- AS s3
    -- more ...
FROM   cte;
$func$;

-- call
SELECT * FROM udf_sensor_fail_per_day('2020-01-01 00:00', '2020-01-11 00:00');

the_day    | dimenstion | sensor1          | sensor2          | sensor3         
:--------- | :--------- | :--------------- | :--------------- | :---------------
2020-01-01 | min        | 33.3333333333333 | 66.6666666666667 | 0               
2020-01-01 | max        | 0                | 0                | 33.3333333333333

db<>在这里摆弄