Database 为多个列 Select 最新的非空值

不幸的是，Postgres没有(到第16页为止)实现IGNORE NULLS的窗口函数.这将允许对每个值列进行简单的first_value()调用.参见:

• How to use first_value() window function to get last non null value?个
• Select a row of first non-null values in a sparse table个

解决方案

fiddle个

There are various shorter and possibly (much) faster options.
You should at least have a (partial) index on (ts). Possibly on (sensor_id, ts). Or more. See below. All depending on undisclosed details.

我觉得timestamp栏的"时间"这个名字有误导性.用"ts"代替.

first_value() + DISTINCT ON

一个更短的临时替补.

SELECT DISTINCT ON (sensor_id)
       sensor_id
     , first_value(wind_speed    ) OVER (w ORDER BY wind_speed     IS NULL, ts DESC) AS wind_speed
     , first_value(wind_direction) OVER (w ORDER BY wind_direction IS NULL, ts DESC) AS wind_direction
--   , ... more?
FROM   sensor_data
WHERE  ts > LOCALTIMESTAMP - interval '7 days'
WINDOW w AS (PARTITION BY sensor_id);

大约DISTINCT ON:

• Select first row in each GROUP BY group?个

count() window function in subquery + filtered aggregate in main

SELECT sensor_id
     , min(wind_speed)     FILTER (WHERE ws_ct = 1) AS wind_speed
     , min(wind_direction) FILTER (WHERE wd_ct = 1) AS wind_direction
--   , ... more?
FROM  (
   SELECT *
        , count(wind_speed)     OVER w AS ws_ct
        , count(wind_direction) OVER w AS wd_ct
   --   ,  ... more?
   FROM   sensor_data
   WHERE  ts > LOCALTIMESTAMP - interval '7 days'
   WINDOW w AS (PARTITION BY sensor_id ORDER BY ts DESC)
   ) sub
GROUP  BY sensor_id;

请参见:

• Aggregate columns with additional (distinct) filters个
• Carry over long sequence of missing values with Postgres个

基于"传感器"表的更简单

如果您也有一个表"SENSOR"，每相关的sensor_id有一行(您可能应该这样做)，那么它就会变得更简单:

SELECT sensor_id
    , (SELECT wind_speed     FROM sensor_data WHERE sensor_id = s.sensor_id AND ts > t.ts_min AND wind_speed     IS NOT NULL ORDER BY ts DESC LIMIT 1) AS wind_speed
    , (SELECT wind_direction FROM sensor_data WHERE sensor_id = s.sensor_id AND ts > t.ts_min AND wind_direction IS NOT NULL ORDER BY ts DESC LIMIT 1) AS wind_direction
--  , ... more?
FROM   sensor s
    , (SELECT LOCALTIMESTAMP - interval '7 days') t(ts_min)
;

最后一个查询(与冗长的原始查询一样)可以使用定制的索引.理想情况下，部分索引-每个传感器有many行、few个值列、many个空值和many个过时的行.

CREATE INDEX sensor_data_wind_speed_idx     ON sensor_data (sensor_id, ts DESC, wind_speed)
WHERE  wind_speed IS NOT NULL
AND    ts > '2023-12-12 00:00';  -- constant!

CREATE INDEX sensor_data_wind_direction_idx ON sensor_data (sensor_id, ts DESC, wind_direction)
WHERE  wind_direction IS NOT NULL
AND    ts > '2023-12-12 00:00';  -- constant!

在创建时使用一个过go 一周的常量.随着时间的推移，索引的大小会增加，但仍然适用.重新创建索引，并不时地在稍后关闭，以保持大小不变.(不过，不确定时间戳绑定是否为您的hypertables付费.简单的索引可能就足够了.我有一个简单的Postgres.)

然后运行相同的查询，但时间戳为constant:

SELECT ...
FROM   sensor s
    , (SELECT timestamp '2023-12-12 03:47:16') t(ts_min)  -- MUST be a constant to use partial index!
;

Sorted subquery + first() aggregate function

If index-support is not an option or not efficient, the most convenient query would be with the aggregate function first() - probably fastest, too, if you use the C version from the additional module first_last_agg. 请参见:

• Get values from first and last row per group个

每个数据库需要一次:

CREATE EXTENSION first_last_agg;

SELECT sensor_id
     , first(wind_speed    ) FILTER (WHERE wind_speed IS NOT NULL)     AS wind_speed
     , first(wind_direction) FILTER (WHERE wind_direction IS NOT NULL) AS wind_direction
--   , ... more?
FROM   (
   SELECT * FROM sensor_data
   WHERE  ts > LOCALTIMESTAMP - interval '7 days'
   ORDER  BY ts DESC
   ) s
GROUP  BY 1;