我'm not sure how you'使用 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
(我在 d1.time 附近添加了一个 TO_CHAR 来减少SQL * Plus中过多的间距 . )
如果您使用的是 DATE 而不是 TIMESTAMP ,则不需要该功能:您只需减去日期即可 .