时间戳

首页 > 解决方案 > 如何在 SQL Server 中获取插值？

sql - 如何在 SQL Server 中获取插值？

问题描述

我想获得 NULL 的插值。插值是一种统计方法，通过该方法，相关的已知值用于估计证券的未知价格或潜在收益。插值是通过使用与未知值顺序定位的其他已建立值来实现的。

这是我的示例表和代码。

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=673fcd5bc250bd272e8b6da3d0eddb90

我想得到这个结果：

    | SEQ | cat01 | cat02 |   dt_day   | price |  coeff |
    +-----+-------+-------+------------+-------+--------+
    |  1  |  230  |   1   | 2019-01-01 | 16000 |    0   |
    |  2  |  230  |   1   | 2019-01-02 |  NULL |    1   |
    |  3  |  230  |   1   | 2019-01-03 | 13000 |    0   |
    |  4  |  230  |   1   | 2019-01-04 |  NULL |    1   |
    |  5  |  230  |   1   | 2019-01-05 |  NULL |    2   |    
    |  6  |  230  |   1   | 2019-01-06 |  NULL |    3   | 
    |  7  |  230  |   1   | 2019-01-07 | 19000 |    0   |
    |  8  |  230  |   1   | 2019-01-08 | 20000 |    0   |
    |  9  |  230  |   1   | 2019-01-09 | 21500 |    0   |
    | 10  |  230  |   1   | 2019-01-10 | 21500 |    0   |
    | 11  |  230  |   1   | 2019-01-11 |  NULL |    1   |
    | 12  |  230  |   1   | 2019-01-12 |  NULL |    2   |
    | 13  |  230  |   1   | 2019-01-13 | 23000 |    0   |     
    |  1  |  230  |   2   | 2019-01-01 |  NULL |    1   |
    |  2  |  230  |   2   | 2019-01-02 |  NULL |    2   |
    |  3  |  230  |   2   | 2019-01-03 | 12000 |    0   |
    |  4  |  230  |   2   | 2019-01-04 | 17000 |    0   |
    |  5  |  230  |   2   | 2019-01-05 | 22000 |    0   |    
    |  6  |  230  |   2   | 2019-01-06 |  NULL |    1   | 
    |  7  |  230  |   2   | 2019-01-07 | 23000 |    0   |
    |  8  |  230  |   2   | 2019-01-08 | 23200 |    0   |
    |  9  |  230  |   2   | 2019-01-09 |  NULL |    1   |
    | 10  |  230  |   2   | 2019-01-10 |  NULL |    2   |
    | 11  |  230  |   2   | 2019-01-11 |  NULL |    3   |
    | 12  |  230  |   2   | 2019-01-12 |  NULL |    4   |
    | 13  |  230  |   2   | 2019-01-13 | 23000 |    0   |

我使用此代码。我认为这段代码不正确。

coeff是 NULL 是按顺序设置的。

此代码用于实现插值。

我试图找出空值之间的值并将它们除以空格数。

但是，这段代码是不正确的。

WITH ROW_VALUE AS
(
    SELECT SEQ
    , dt_day
    , cat01
    , cat02
    , price
    , ROW_NUMBER() OVER (ORDER BY dt_day) AS sub_seq
FROM (
    SELECT SEQ
        , cat01
        , cat02
        , dt_day
        , dt_week
        , dt_month
        , price
    FROM temp01
    WHERE price IS NOT NULL
        )val
)
,STEP_CHANGE AS(
SELECT RV1.SEQ AS id_Start
    , RV1.SEQ - 1 AS id_End
    , RV1.cat01
    , RV1.cat02
    , RV1.dt_day
    , RV1.price      
    , (RV2.price - RV1.price)/(RV2.SEQ - RV1.SEQ) AS change1
FROM ROW_VALUE RV1
LEFT JOIN ROW_VALUE RV2 ON RV1.cat01 = RV2.cat01
AND RV1.cat02 = RV2.cat02
AND RV1.SEQ = RV2.SEQ - 1
)
SELECT *
FROM STEP_CHANGE
ORDER BY cat01, cat02, dt_day

请让我知道使用线性关系填充 NULL 的好方法。

如果还有其他好方法，请推荐。

标签： sqldatabase

解决方案

如果我假设您的意思是根据过去的天数在前一个价格和下一个价格之间进行线性插值，那么您可以使用以下方法：

  • 使用窗口函数来获取每一行的下一天和前一天的价格。
  • 也可以使用窗口函数或连接来获取那些日子的价格。
  • 使用算术计算线性插值。

您的 SQL Fiddle 使用 SQL Server，所以我假设这是您正在使用的数据库。代码如下所示：

select t.*,
       coalesce(t.price, 
                (tprev.price +
                 (tnext.price - tprev.price) / datediff(day, prev_price_day, next_price_day) *
                 datediff(day, t.prev_price_day, t.dt_day)
                )
               ) as imputed_price
from (select t.*,
             max(case when price is not null then dt_day end) over (partition by cat01, cat02 order by dt_day asc) as prev_price_day,
             min(case when price is not null then dt_day end) over (partition by cat01, cat02 order by dt_day desc) as next_price_day
      from temp01 t
     ) t left join 
     temp01 tprev
     on tprev.cat01 = t.cat01 and
        tprev.cat02 = t.cat02 and
        tprev.dt_day = t.prev_price_day left join
     temp01 tnext
     on tnext.cat01 = t.cat01 and
        tnext.cat02 = t.cat02 and
        tnext.dt_day = t.next_price_day 
order by cat01, cat02, dt_day;

这是一个 db<>fiddle。

推荐阅读