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

3 回答

• 3

  我'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 ，则不需要该功能：您只需减去日期即可 .

  回复于 2024-05-05T08:13:15+08:00
• 0

  我正在使用@Luke Woodward的查询的修改版本：

  SELECT d1.time,
     d1.value AS value1,
     q2.prev_value + 
     (EXTRACT( SECOND FROM (d1.time - q2.prev_time)) +
      EXTRACT( MINUTE FROM (d1.time - q2.prev_time)) * 60 ) 
      * (q2.next_value - q2.prev_value)/
        (EXTRACT ( SECOND FROM (q2.next_time - q2.prev_time)) + 
        EXTRACT ( MINUTE FROM (q2.next_time - q2.prev_time)) * 60)  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
                        and time between '20100914 000000' and '20100915 000000'
                  ) q2
             ON d1.time BETWEEN q2.prev_time AND q2.next_time
   WHERE d1.deviceid = 1;
  

  但即使在日期范围内有设备2的数据，插值也始终为空 .

  注意，我必须在q2中为查询添加日期范围，这可能是正常连接丢失外部数据的原因 .

  如果我使用普通连接，则不会为插值数据获取空值，但在使用普通连接时，我会丢失设备2 endpoints 之外的设备1的数据（q2中的插值设备） . 建议？

  回复于 2024-05-05T08:13:15+08:00
• 0

  具有日期范围的最终解决方案：

  SELECT
      d1.time,
      d1.value AS value1,
      q2.prev_value + 
      (EXTRACT( SECOND FROM (d1.time - q2.prev_time)) +
       EXTRACT( MINUTE FROM (d1.time - q2.prev_time)) * 60 ) 
       * (q2.next_value - q2.prev_value)/
         (EXTRACT ( SECOND FROM (q2.next_time - q2.prev_time)) + 
          EXTRACT ( MINUTE FROM (q2.next_time - q2.prev_time)) * 60
      )  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
      AND time BETWEEN '20100914 000000' AND '20100915 000000'
  ) q2
  ON d1.time BETWEEN q2.prev_time AND q2.next_time
  WHERE d1.deviceid = 1
  AND time BETWEEN '20100914 000000' AND '20100915 000000';
  

  回复于 2024-05-05T08:13:15+08:00