sql - 如何使用 oracle SQL 执行线性插值？

Question

我正在尝试使用 Oracle 11g(开发中为 11.1，生产中为 11.2)进行数值分析，特别是对具有三列感兴趣的表的线性插值:时间戳、设备 ID 和值。

值列保存来自设备的数据(带有 id deviceid)，在时间戳中给定的时间获取。例如，这是虚假数据，但它给出了以下想法:

     time       |  deviceid  |  value   
----------------|------------|-----------
 01:00:00.000   |  001       | 1.000
 01:00:01.000   |  001       | 1.030
 01:00:02.000   |  001       | 1.063 
 01:00:00.050   |  002       | 553.10
 01:00:01.355   |  002       | 552.30
 01:00:02.155   |  002       | 552.43 

设备 001 的时间戳与设备 002 的时间戳不匹配，但我需要将设备 001 和 002 的值排成一行，其中一个时间戳与设备 001 的时间戳匹配。我想要结束的是像这样:

     time       |  device 001  |  device 002   
----------------|--------------|------------
 01:00:00.000   |  1.000       |  null
 01:00:01.000   |  1.030       |  552.520
 01:00:02.000   |  1.063       |  552.405

其中设备 002 的值是基于在设备 001 的每个时间戳两侧的两个最接近的时间戳收集的设备 002 的值进行线性插值的。
出现空值是因为我在 01:00:00.000 的任一侧都没有设备 002 的两个时间戳，而且我不想推断该值。

据我所知，我可以使用 percentile_cont 来做到这一点，但我不明白我在网上看到的例子。例如，percentile_cont 使用的百分位数从何而来？

在此先感谢您的帮助!

Answer 1

我不确定你会如何使用 PERCENTILE_CONT做你要求的插值，但在不同的分析函数的帮助下，你可以实现你想要的。

首先，我们将创建以下函数，用于转换 INTERVAL DAY TO SECOND值转换为秒:

CREATE OR REPLACE FUNCTION intvl_to_seconds(
    p_interval INTERVAL DAY TO SECOND
) RETURN NUMBER DETERMINISTIC
AS
BEGIN
  RETURN EXTRACT(DAY FROM p_interval) * 24*60*60
       + EXTRACT(HOUR FROM p_interval) * 60*60
       + EXTRACT(MINUTE FROM p_interval) * 60
       + EXTRACT(SECOND FROM p_interval);
END;
/

使用此函数，我们可以使用如下查询:

SELECT d1.time,
       d1.value AS value1,
       q2.prev_value + intvl_to_seconds(d1.time - q2.prev_time) * (q2.next_value - q2.prev_value)/intvl_to_seconds(q2.next_time - q2.prev_time) AS value2
  FROM devices d1
  LEFT OUTER JOIN (SELECT d2.time AS prev_time,
                          d2.value AS prev_value,
                          LEAD(d2.time, 1) OVER (ORDER BY d2.time) AS next_time,
                          LEAD(d2.value, 1) OVER (ORDER BY d2.time) AS next_value
                     FROM devices d2
                    WHERE d2.deviceid = 2) q2
               ON d1.time BETWEEN q2.prev_time AND q2.next_time
 WHERE d1.deviceid = 1;

我把你上面的数据，把时间戳的日期部分设置为今天，当我运行上面的查询时，我得到了以下结果:

TO_CHAR(D1.TIME) VALUE1 VALUE2
------------------------------------- ---------- --- -------
09-SEP-11 01.00.00.000000 1
09-SEP-11 01.00.01.000000 1.03 552.517625
09-SEP-11 01.00.02.000000 1.063 552.404813

(我在 TO_CHAR 周围添加了一个 d1.time 以减少 SQL*Plus 中过多的间距。)

如果您使用的是 DATE s 而不是 TIMESTAMP s，您不需要该功能:您可以减去日期。