如何使用Oracle SQL执行线性插值? [英] How can I perform linear interpolation using oracle SQL?
问题描述
我正在尝试使用Oracle 11g(在开发中为11.1，在生产中为11.2)进行数值分析，特别是在具有三列感兴趣的表的表上进行线性插值:时间戳，设备ID和值.
I am trying to use Oracle 11g (11.1 in dev, 11.2 in production) for numeric analysis, specifically linear interpolation on a table which has three columns of interest: a timestamp, a deviceid, and value.
值列保存来自设备(标识为deviceid)的数据，该数据是在时间戳记中指定的时间获取的.例如，这是伪数据，但是它给出了这样的想法:
The value columns holds data from the device (with id deviceid), taken at the time given in the timestamp. For example, this is bogus data, but it gives the idea:
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的时间戳相匹配.我想结束什么像这样:
The timestamps from device 001 do not match the timestamps of device 002, but I need to have the values from both device 001 and 002 in one row, with one timestamp, matching the timestamp for device 001. What I want to end up with is something like this:
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
根据在设备001的每个时间戳的任一侧上的两个最接近的时间戳收集的设备002的值对设备002的值进行线性插值. 之所以会出现空值，是因为我在01:00:00.000的任何一侧都没有设备002的两个时间戳，并且我不想推断该值.
Where the value for device 002 was linearly interpolated based on the values for device 002 gathered at the two closest timestamps on either side of each timestamp for device 001. The null occurs because I don't have two timestamps for device 002 on either side of 01:00:00.000, and I don't want to extrapolate the value.
据我了解，我可以使用percentile_cont来执行此操作，但是我不理解在网上看到的示例.例如，percentile_cont使用的百分位数从何而来?
From what I understand I can use percentile_cont to do this, but I don't understand the examples I have seen online. For example, where would the percentile used by percentile_cont come from?
提前感谢您的帮助！
推荐答案
我不确定您将如何使用PERCENTILE_CONT进行所需的插值，但是借助其他分析功能，您可以实现你想要什么.
I'm not sure how you'd use PERCENTILE_CONT to do the interpolation you ask for, but with the help of a different analytic function you can achieve what you want.
首先，我们将创建以下函数，它将INTERVAL DAY TO SECOND的值转换为秒:
Firstly, we'll create the following function, which converts INTERVAL DAY TO SECOND values into seconds:
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;
/
使用此功能，我们可以使用如下查询:
With this function we can use a query such as the following:
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;
我在上面获取了您的数据，将时间戳记的日期部分设置为今天，当我运行上面的查询时，我得到了以下结果:
I took your data above, set the date component of the timestamps to today, and I got the following results when I ran the query above:
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
(我在d1.time周围添加了TO_CHAR，以减少SQL * Plus中过多的间距.)
(I added a TO_CHAR around d1.time to cut down on excessive spacing in SQL*Plus.)
如果您使用的是DATE而不是TIMESTAMP，则不需要此功能:您可以减去日期.
If you're using DATEs instead of TIMESTAMPs, you don't need the function: you can just subtract the dates.
这篇关于如何使用Oracle SQL执行线性插值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
