在Oracle / SQL中预测时间序列数据

Question

有没有一种方法可以使用Oracle分析功能为数据集生成时间序列预测？我们如何在SQL / ORACLE中执行外推。

下面是我的需要

我有如下数据集，我想预测/推断明年

Cust_id  Year  Revnue
1        2016  679862
1        2017  705365
1        2018  ?
2        2016  51074
2        2017  50611
2        2018  ?
3        2016  190706
3        2017  90393
3        2018  ?
4        2016  31649
4        2017  19566
4        2018  ?

Answer 1

您可以使用REGR线性回归函数创建简单的预测。

--Ordinary least squares forecast for each customer for the next year.
select
    cust_id,
    max(year) +1 forecast_year,
    -- y = mx+b
    regr_slope(revenue, year)
        * (max(year) + 1)
        + regr_intercept(revenue, year) forecasted_revenue
from customer_data
group by cust_id;

CUST_ID   FORECAST_YEAR   FORECASTED_REVENUE
-------   -------------   ------------------
1                  2018               730868
2                  2018                50148
4                  2018                 7483
3                  2018                -9920

下面是示例架构。或者，您可以使用此SQLFiddle。

create table customer_data
(
    cust_id number,
    year number,
    revenue number
);

insert into customer_data
select 1, 2016, 679862 from dual union all
select 1, 2017, 705365 from dual union all
select 2, 2016, 51074  from dual union all
select 2, 2017, 50611  from dual union all
select 3, 2016, 190706 from dual union all
select 3, 2017, 90393  from dual union all
select 4, 2016, 31649  from dual union all
select 4, 2017, 19566  from dual;

该REGR函数处理数字对，它不理解诸如“收入不能低于0”之类的业务规则。如果您希望将预测限制为始终保持在0或之上，则CASE表达式可能会有所帮助：

--Forecasted revenue, with minimum forecast of 0.
select cust_id, forecast_year,
    case when forecasted_revenue < 0 then 0 else forecasted_revenue end forecasted_revenue
from
(
    --Ordinary least squares forecast for each customer for the next year.
    select
        cust_id,
        max(year) +1 forecast_year,
        -- y = mx+b
        regr_slope(revenue, year)
            * (max(year) + 1)
            + regr_intercept(revenue, year) forecasted_revenue
    from customer_data
    group by cust_id
);

CUST_ID   FORECAST_YEAR   FORECASTED_REVENUE
-------   -------------   ------------------
1                  2018               730868
2                  2018                50148
4                  2018                 7483
3                  2018                    0