sql - 在 SQL 中插入缺失值

Question

我有一个数据集，用于跟踪手机电池电量和连接的快照。然而，当手机失去连接时，它会报告与上次良好连接相同的数字，例如:

   id           timestamp connection bat_level
1   1 2019-09-12 20:00:00       TRUE         4
2   1 2019-09-12 20:05:00       TRUE         4
3   1 2019-09-12 20:10:00       TRUE         4
4   1 2019-09-12 20:15:00       TRUE         4
5   1 2019-09-12 20:20:00       TRUE         3
6   1 2019-09-12 20:25:00      FALSE         3
7   1 2019-09-12 20:30:00      FALSE         3
8   1 2019-09-12 20:35:00      FALSE         3
9   1 2019-09-12 20:40:00      FALSE         3
10  1 2019-09-12 20:45:00      FALSE         3
11  1 2019-09-12 20:50:00      FALSE         3
12  1 2019-09-12 20:55:00      FALSE         3
13  1 2019-09-12 21:00:00       TRUE         1
14  1 2019-09-12 21:05:00       TRUE         1
15  1 2019-09-12 21:10:00      FALSE         1
16  1 2019-09-12 21:15:00      FALSE         1
17  1 2019-09-12 21:20:00      FALSE         1
18  1 2019-09-12 21:25:00      FALSE         1
19  1 2019-09-12 21:30:00      FALSE         1
20  1 2019-09-12 21:35:00      FALSE         1
21  1 2019-09-12 21:40:00      FALSE         1
22  1 2019-09-12 21:45:00      FALSE         1
23  1 2019-09-12 21:50:00      FALSE         1
24  1 2019-09-12 21:55:00      FALSE         1
25  1 2019-09-12 22:00:00      FALSE         1

我的目标是创建第五列，该列基本上插入 connection = FALSE 时发生的情况。我想从 last TRUE connection 的值开始以每分钟 0.05 的速度插入电池消耗率(不能低于 0)，因此输出将是:

   id           timestamp connection bat_level theoretical_bat_level
1   1 2019-09-12 20:00:00       TRUE         4                    NA
2   1 2019-09-12 20:05:00       TRUE         4                    NA
3   1 2019-09-12 20:10:00       TRUE         4                    NA
4   1 2019-09-12 20:15:00       TRUE         4                    NA
5   1 2019-09-12 20:20:00       TRUE         3                    NA
6   1 2019-09-12 20:25:00      FALSE         3                  2.75
7   1 2019-09-12 20:30:00      FALSE         3                  2.50
8   1 2019-09-12 20:35:00      FALSE         3                  2.25
9   1 2019-09-12 20:40:00      FALSE         3                  2.00
10  1 2019-09-12 20:45:00      FALSE         3                  1.75
11  1 2019-09-12 20:50:00      FALSE         3                  1.50
12  1 2019-09-12 20:55:00      FALSE         3                  1.25
13  1 2019-09-12 21:00:00       TRUE         1                    NA
14  1 2019-09-12 21:05:00       TRUE         1                    NA
15  1 2019-09-12 21:10:00      FALSE         1                  0.75
16  1 2019-09-12 21:15:00      FALSE         1                  0.50
17  1 2019-09-12 21:20:00      FALSE         1                  0.25
18  1 2019-09-12 21:25:00      FALSE         1                  0.00
19  1 2019-09-12 21:30:00      FALSE         1                  0.00
20  1 2019-09-12 21:35:00      FALSE         1                  0.00
21  1 2019-09-12 21:40:00      FALSE         1                  0.00
22  1 2019-09-12 21:45:00      FALSE         1                  0.00
23  1 2019-09-12 21:50:00      FALSE         1                  0.00
24  1 2019-09-12 21:55:00      FALSE         1                  0.00
25  1 2019-09-12 22:00:00      FALSE         1                  0.00

我知道应该使用带有 lag() 的 cte，但不确定如何开始从最后一个 TRUE 开始降低 theoretical_bat_level > 每个 id

的值

CASE WHEN connection = FALSE AND 
LAG(connection) OVER(PARTITION BY id, timestamp ASC) = True AND
connection = LAG(bat_level) OVER(PARTITION BY id, timestamp ASC) THEN  ?
WHERE connection = FALSE

任何有关此处正确使用方法的指导都会有所帮助

Answer 1

我的解决方案需要一个辅助函数来计算间隔的长度(以分钟为单位):

CREATE OR REPLACE FUNCTION int_minutes(interval) RETURNS double precision
   LANGUAGE sql STRICT IMMUTABLE AS
'SELECT EXTRACT (minutes FROM $1)
       + 60 * extract (hours FROM $1)
       + 1440 * extract (days FROM $1)';

然后你可以使用窗口函数来获取手机连接的最新时间:

SELECT id, timestamp, connection, bat_level,
       greatest(bat_level
                - int_minutes(timestamp
                              - conn_ts_arr[cardinality(conn_ts_arr)]
                  ) * 0.05,
                0.0
       ) AS theoretical_bat_level
FROM (SELECT id, timestamp, connection, bat_level,
             array_agg(timestamp)
                FILTER (WHERE connection)
                OVER (PARTITION BY id
                      ORDER BY timestamp) AS conn_ts_arr
      FROM cellbat) AS s1
ORDER BY id, timestamp;