Oracle中的线性插值(特殊情况)

问题描述

为了填充缺失的值，我需要对那些缺失的值进行插值.

In order to fill missing values I need to interpolate those missing ones.

我已经获得了类似以下的数据集(示例):

I have gotten a dataset like the following (example):

Country    Year    Value
A          2000    1.5
A          2001    2.5
A          2002    null
A          2003    4.5
B          2000    null
B          2000    null
B          2002    5.3
B          2003    6.3
C          2000    1
C          2001    null
C          2002    null
C          2003    4

因此，我很期待:

Country    Year    Value
A          2000    1.5
A          2001    2.5
A          2002    3.5
A          2003    4.5
B          2000    3.3
B          2000    4.3
B          2002    5.3
B          2003    6.3
C          2000    1
C          2001    2
C          2002    3
C          2003    4

如何通过线性插值来插值此值.我真的不知道如何在oracle中有效地做到这一点.

How can i possible interpolate this values by linear interpolation. I really cannot come to any idea how to efficiently do it in oracle.

推荐答案

Oracle具有用于线性插值的各种功能. REGR_SLOPE 和 REGR_INTERCEPT 在这里很有帮助.

Oracle has various functions for linear interpolation. REGR_SLOPE and REGR_INTERCEPT are helpful here.

您遇到的问题是，它不是价值和年份之间的线性回归.它是国家组中值和行号之间的线性回归.因此，在计算插值之前，需要先计算该行号.

The trick in your case is that it is not a linear regression between value and year. It is a linear regression between value and the row number within the country group. So we need to calculate that row number before we can calculate the interpolation.

with input_data (country, year, value) AS (
  SELECT 'A',          2000,    1.5  FROM DUAL UNION ALL
  SELECT 'A',          2001,    2.5  FROM DUAL UNION ALL
  SELECT 'A',          2002,    null FROM DUAL UNION ALL
  SELECT 'A',          2003,    4.5  FROM DUAL UNION ALL
  SELECT 'B',          2000,    null FROM DUAL UNION ALL
  SELECT 'B',          2000,    null FROM DUAL UNION ALL
  SELECT 'B',          2002,    5.3  FROM DUAL UNION ALL
  SELECT 'B',          2003,    6.3  FROM DUAL UNION ALL
  SELECT 'C',          2000,    1    FROM DUAL UNION ALL
  SELECT 'C',          2001,    null FROM DUAL UNION ALL
  SELECT 'C',          2002,    null FROM DUAL UNION ALL
  SELECT 'C',          2003,    4    FROM DUAL
), ordered_input as (
  SELECT
    i.*,
    row_number() over ( partition by country order by year) rn
  FROM input_data i
)
SELECT
  country,
  year,
  value,
  rn * regr_slope(value, rn) over ( partition by country) +
       regr_intercept(value, rn) over ( partition by country)
    as interpolated_value
FROM ordered_input
ORDER BY country, year, rn;

+---------+------+-------+--------------------+
| COUNTRY | YEAR | VALUE | INTERPOLATED_VALUE |
+---------+------+-------+--------------------+
| A       | 2000 |   1.5 |                1.5 |
| A       | 2001 |   2.5 |                2.5 |
| A       | 2002 |       |                3.5 |
| A       | 2003 |   4.5 |                4.5 |
| B       | 2000 |       |                3.3 |
| B       | 2000 |       |                4.3 |
| B       | 2002 |   5.3 |                5.3 |
| B       | 2003 |   6.3 |                6.3 |
| C       | 2000 |     1 |                  1 |
| C       | 2001 |       |                  2 |
| C       | 2002 |       |                  3 |
| C       | 2003 |     4 |                  4 |
+---------+------+-------+--------------------+

这篇关于Oracle中的线性插值(特殊情况)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！