天塌了！！！SQL竟也可以做预测分析？| 商品零售额的预测

会飞的一十六 2024-11-11

点击上方【蓝色】字体关注我们

01 场景描述

1960年—1985年全国社会商品零售额如图1 所示

表1全国社会商品零售额数据

问题：试用三次指数平滑法预测1983年和1985年全国社会商品零售额？

02 数据准备

create table sale_amount as      
select '1960' years, '696.6' sale_amount from dual union all
select '1961' years, '607.7' sale_amount from dual union all
select '1962' years, '604'   sale_amount from dual union all
select '1963' years, '604.5' sale_amount from dual union all
select '1964' years, '638.2' sale_amount from dual union all
select '1965' years, '670.3' sale_amount from dual union all
select '1966' years, '732.8' sale_amount from dual union all
select '1967' years, '770.5' sale_amount from dual union all
select '1968' years, '737.3' sale_amount from dual union all
select '1969' years, '801.5' sale_amount from dual union all
select '1970' years, '858'   sale_amount from dual union all
select '1971' years, '929.2'  sale_amount from dual union all
select '1972' years, '1023.3' sale_amount from dual union all
select '1973' years, '1106.7' sale_amount from dual union all
select '1974' years, '1163.6' sale_amount from dual union all
select '1975' years, '1271.1' sale_amount from dual union all
select '1976' years, '1339.4' sale_amount from dual union all
select '1977' years, '1432.8' sale_amount from dual union all
select '1978' years, '1558.6' sale_amount from dual union all
select '1979' years, '1800' sale_amount from dual union all
select '1980' years, '2140' sale_amount from dual union all
select '1981' years, '2350' sale_amount from dual union all
select '1982' years, '2570' sale_amount from dual

03 问题分析

2.1 模型构建

（1）符号规定

（2）基本假设

假设本问题考虑全社会商品零售额数据；
假设本问题只考虑销售，不考虑其余因素
假设本问题只考虑销售额总额，不考虑其余分支

（3）模型的分析与建立

令加权系数，则计算公式为

其中，表示一次指数的平滑值；表示二次次指数的平滑值；表示三次指数的平滑值。初始值为

三次指数平滑法的预测模型为：

其中，

2.2 模型求解

步骤1：计算初始值

select years
     , sale_amount
     , last_value(init_sale_amount ignore nulls) over (order by YEARS) init_sale_amount
     , rn
from (select years
           , sale_amount
           , case
                 when rn = 1 then cast(avg(sale_amount)
                                           over (order by years rows between current row and 2 following ) as decimal(18, 1)) end init_sale_amount
           , rn
      from (select years
                 , sale_amount
                 , row_number() over (order by years) rn
            from sale_amount) t
     ) t

步骤2 ：计算一次平滑值

with init as (
    select years
         , sale_amount
         , last_value(init_sale_amount ignore nulls) over (order by YEARS) init_sale_amount
         , rn
    from (select years
               , sale_amount
               , case
                     when rn = 1 then cast(avg(sale_amount)
                                               over (order by years rows between current row and 2 following ) as decimal(18, 1)) end init_sale_amount
               , rn
          from (select years
                     , sale_amount
                     , row_number() over (order by years) rn
                from sale_amount) t
         ) t
)
--计算一次平滑值
   , s1 as (select t1.years
                 , t1.sale_amount
                 , t1.init_sale_amount
                 , t1.rn
                 , cast(sum(case when t2.rn <= t1.rn then t2.sale_amount * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                        power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s1_p3
            from init t1,
                 init t2
            group by t1.years
                   , t1.sale_amount
                   , t1.init_sale_amount
                   , t1.rn
)
select * from s1 order by  years;

步骤3：计算二次平滑值

with init as (
    select years
         , sale_amount
         , last_value(init_sale_amount ignore nulls) over (order by YEARS) init_sale_amount
         , rn
    from (select years
               , sale_amount
               , case
                     when rn = 1 then cast(avg(sale_amount)
                                               over (order by years rows between current row and 2 following ) as decimal(18, 1)) end init_sale_amount
               , rn
          from (select years
                     , sale_amount
                     , row_number() over (order by years) rn
                from sale_amount) t
         ) t
)
--计算一次平滑值
   , s1 as (select t1.years
                 , t1.sale_amount
                 , t1.init_sale_amount
                 , t1.rn
                 , cast(sum(case when t2.rn <= t1.rn then t2.sale_amount * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                        power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s1_p3
            from init t1,
                 init t2
            group by t1.years
                   , t1.sale_amount
                   , t1.init_sale_amount
                   , t1.rn
)
--计算二次平滑值
, s2 as (select t1.years
              , t1.sale_amount
              , t1.init_sale_amount
              , t1.rn
              , t1.s1_p3
              , cast(sum(case when t2.rn <= t1.rn then t2.s1_p3 * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                     power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s2_p3
         from s1 t1,
              s1 t2
         group by t1.years
                , t1.sale_amount
                , t1.init_sale_amount
                , t1.rn
                , t1.s1_p3
)
select * from s2 order by  years;

步骤4：计算三次平滑值

 
with init as (
    select years
         , sale_amount
         , last_value(init_sale_amount ignore nulls) over (order by YEARS) init_sale_amount
         , rn
    from (select years
               , sale_amount
               , case
                     when rn = 1 then cast(avg(sale_amount)
                                               over (order by years rows between current row and 2 following ) as decimal(18, 1)) end init_sale_amount
               , rn
          from (select years
                     , sale_amount
                     , row_number() over (order by years) rn
                from sale_amount) t
         ) t
)
--计算一次平滑值
   , s1 as (select t1.years
                 , t1.sale_amount
                 , t1.init_sale_amount
                 , t1.rn
                 , cast(sum(case when t2.rn <= t1.rn then t2.sale_amount * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                        power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s1_p3
            from init t1,
                 init t2
            group by t1.years
                   , t1.sale_amount
                   , t1.init_sale_amount
                   , t1.rn
)
--计算二次平滑值
, s2 as (select t1.years
              , t1.sale_amount
              , t1.init_sale_amount
              , t1.rn
              , t1.s1_p3
              , cast(sum(case when t2.rn <= t1.rn then t2.s1_p3 * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                     power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s2_p3
         from s1 t1,
              s1 t2
         group by t1.years
                , t1.sale_amount
                , t1.init_sale_amount
                , t1.rn
                , t1.s1_p3
)
   --计算三次平滑值
,s3 as (select t1.years
             , t1.sale_amount
             , t1.init_sale_amount
             , t1.rn
             , t1.s2_p3
             , cast(sum(case when t2.rn <= t1.rn then t2.s2_p3 * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                    power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s3_p3
        from s2 t1,
             s2 t2
        group by t1.years
               , t1.sale_amount
               , t1.init_sale_amount
               , t1.rn
               , t1.s2_p3
)
select * from s3 order by  years;

步骤5：计算二次函数模型系数

 
with init as (
    select years
         , sale_amount
         , last_value(init_sale_amount ignore nulls) over (order by YEARS) init_sale_amount
         , rn
    from (select years
               , sale_amount
               , case
                     when rn = 1 then cast(avg(sale_amount)
                                               over (order by years rows between current row and 2 following ) as decimal(18, 1)) end init_sale_amount
               , rn
          from (select years
                     , sale_amount
                     , row_number() over (order by years) rn
                from sale_amount) t
         ) t
)
--计算一次平滑值
   , s1 as (select t1.years
                 , t1.sale_amount
                 , t1.init_sale_amount
                 , t1.rn
                 , cast(sum(case when t2.rn <= t1.rn then t2.sale_amount * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                        power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s1_p3
            from init t1,
                 init t2
            group by t1.years
                   , t1.sale_amount
                   , t1.init_sale_amount
                   , t1.rn
)
--计算二次平滑值
, s2 as (select t1.years
              , t1.sale_amount
              , t1.init_sale_amount
              , t1.rn
              , t1.s1_p3
              , cast(sum(case when t2.rn <= t1.rn then t2.s1_p3 * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                     power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s2_p3
         from s1 t1,
              s1 t2
         group by t1.years
                , t1.sale_amount
                , t1.init_sale_amount
                , t1.rn
                , t1.s1_p3
)
   --计算三次平滑值
,s3 as (select t1.years
              , t1.sale_amount
              , t1.init_sale_amount
              , t1.rn
              , t1.s1_p3
              , t1.s2_p3
              , cast(sum(case when t2.rn <= t1.rn then t2.s2_p3 * power(0.7, t1.rn - t2.rn) else 0 end) * 0.3 +
                     power(0.7, t1.rn) * t1.init_sale_amount as decimal(18, 4)) s3_p3
         from s2 t1,
              s2 t2
         group by t1.years
                , t1.sale_amount
                , t1.init_sale_amount
                , t1.rn
                , t1.s1_p3
                , t1.s2_p3
)
 
--计算二次趋势模型系数
select years
     , sale_amount
     , init_sale_amount
     , rn
     , s1_p3
     , s2_p3
     , s3_p3
 
     , cast(case when rk=1 then 3*s1_p3 - 3*s2_p3 + s3_p3 else 0 end as decimal(18,4)) a_p3
     , cast(case when rk=1 then ((6-5*0.3)*s1_p3 - 2*(5-4*0.3)*s2_p3 + (4-3*0.3)*s3_p3 ) * 0.3/(2*power(0.7,2))  else 0 end as decimal(18,2))  b_p3
     , cast(case when rk=1 then (s1_p3 - 2*s2_p3 + s3_p3 ) * power(0.3,2)/(2*power(0.7,2))  else 0 end as decimal(18,4))  c_p3
from (select years
           , sale_amount
           , init_sale_amount
           , rn
           , s1_p3
           , s2_p3
           , s3_p3
           , row_number() over (order by rn desc) rk
      from s3
     ) t
order by years