df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

线性插值

中等 19-timeseries 时间序列 interpolatefillwindowsentinel

对每个传感器的温度读数做线性插值,补上缺失值。

  1. temperature = -999 是坏读数,先当成 null。
  2. 每个传感器内按 ts 升序排列,对 null 做按行位置的线性插值:只看前后最近的两个非 null 值和它们之间隔了几行,不考虑时间间隔。例如三行依次为 10、null、20,中间补 15,与时间戳无关。
  3. 一个传感器开头和结尾的连续 null(前面或后面已经没有非 null 值)保持为 null,不外推。全为 null 的传感器保持全 null。

每条读数输出一行。

输入

  • readings(sensor_id, ts, temperature, humidity):同一传感器内 ts 不重复,输入不保证有序

输出

列为 sensor_id, ts, temperature(插值后的温度),行顺序不限。

输入表 (case base,共 2 个 case:base、edge)

readings iot/readings
#sensor_idstringtsdatetimetemperaturefloat64humidityfloat64
1 s01 2026-09-01 00:23:47 21.7 60.2
2 s01 2026-09-01 00:45:48 22.1 60.2
3 s01 2026-09-01 00:59:29 22.2 46.2
4 s01 2026-09-01 01:12:53 22.3 51.3
5 s01 2026-09-01 01:37:08 22.5 43.4
6 s01 2026-09-01 01:55:18 22.5 57.9
7 s01 2026-09-01 02:03:26 22.3 39.0
8 s01 2026-09-01 02:09:39 22.1 58.2
9 s01 2026-09-01 02:21:34 22.2 41.0
10 s01 2026-09-01 02:33:45 22.4 63.2
11 s01 2026-09-01 02:46:42 null 47.4
12 s01 2026-09-01 02:56:58 null 37.0
13 s01 2026-09-01 03:12:50 null 55.3
14 s01 2026-09-01 03:28:54 22.4 44.3
15 s01 2026-09-01 03:44:24 22.3 49.2
16 s01 2026-09-01 03:55:27 22.1 42.7
17 s01 2026-09-01 04:02:14 21.9 51.5
18 s01 2026-09-01 04:21:37 -999.0 46.3
19 s01 2026-09-01 04:28:05 22.4 63.7
20 s01 2026-09-01 04:40:12 22.4 57.7
显示前 20 行,共 600 行

期望输出 (case base,行顺序不限)

#sensor_idStringtsDatetime(time_unit='us', time_zone=None)temperatureFloat64
1 s03 2026-09-01 00:32:42 19.7
2 s03 2026-09-01 00:56:43 19.7
3 s03 2026-09-01 01:18:56 19.7
4 s03 2026-09-01 01:36:35 19.8
5 s03 2026-09-01 01:55:54 19.9
6 s03 2026-09-01 02:08:58 19.9
7 s03 2026-09-01 02:17:40 20.1
8 s03 2026-09-01 02:40:53 20.0
9 s03 2026-09-01 02:49:50 20.2
10 s03 2026-09-01 03:09:15 20.4
11 s03 2026-09-01 03:27:39 20.475
12 s03 2026-09-01 03:44:03 20.55
13 s03 2026-09-01 04:07:47 20.625
14 s03 2026-09-01 04:20:05 20.7
15 s03 2026-09-01 04:34:56 20.2
16 s03 2026-09-01 04:54:45 19.8
17 s03 2026-09-01 05:03:21 19.2
18 s03 2026-09-01 05:22:47 19.0
19 s03 2026-09-01 05:44:20 18.8
20 s03 2026-09-01 05:52:58 18.75
21 s03 2026-09-01 06:09:09 18.7
22 s03 2026-09-01 06:32:47 18.65
23 s03 2026-09-01 06:51:59 18.6
24 s03 2026-09-01 07:08:45 18.7
25 s03 2026-09-01 07:29:53 19.1
26 s03 2026-09-01 07:50:34 18.4
27 s03 2026-09-01 08:04:31 18.2
28 s03 2026-09-01 08:13:31 17.7
29 s03 2026-09-01 08:24:38 17.8
30 s03 2026-09-01 08:34:14 17.9
31 s03 2026-09-01 08:51:06 17.9
32 s03 2026-09-01 09:14:43 17.9
33 s03 2026-09-01 09:24:06 17.9
34 s03 2026-09-01 09:41:17 17.9
35 s03 2026-09-01 09:49:32 17.6
36 s03 2026-09-01 09:59:10 17.8
37 s03 2026-09-01 10:09:53 18.1
38 s03 2026-09-01 10:18:24 18.1
39 s03 2026-09-01 10:33:25 18.4
40 s03 2026-09-01 10:46:23 18.6
41 s03 2026-09-01 10:56:41 19.0
42 s03 2026-09-01 11:05:17 18.7
43 s03 2026-09-01 11:24:38 18.8
44 s03 2026-09-01 11:38:04 18.8
45 s03 2026-09-01 11:52:46 18.8
46 s03 2026-09-01 12:16:16 18.6
47 s03 2026-09-01 12:27:50 18.9
48 s03 2026-09-01 12:48:12 18.4
49 s03 2026-09-01 12:56:12 18.5
50 s03 2026-09-01 13:02:28 18.3
显示前 50 行,共 600 行