SQL描述（2）

很久之前就想写出来，就是因为自己太懒，憋了怎么久。本文关于使用ORACLE分析函数对一些经济指标进行计算。表indi_value有3个关键的字段：indi_date,indi_value,indi_id分别对应 指标日期，指标值，指标ID。这个表中保存了很多种类的经济指标，如CPI,RPI,GDP，这些指标通过 指标ID来标识。下面是针对CPI做的处理，公式如下：

公式说明：将2010年1月份当作基期。分别计算基期前后的定基价格指数。直接看代码

 

with base as ( select indi_date,indi_value 
               from indi_value 
			   -- 获取20060101  -  20131101区间内要计算的数据
               where  indi_id='C0A0102' and indi_date between  '20060101'  and  '20131101' 
             ),
   ---获取大于基期的数据，数据按日期升序，并且使基期=100，往后的值都为空，然后通过行间计算，进行填补
      up as  (select indi_date,indi_value,
	                 case when indi_date like '201001%' 
	                 then 100 else null end as v,rank() OVER(order by indi_date) as d
              from base
              where indi_date between   '20100101'  and '20131101'),
      upx as (select d,v
              from up  model
              dimension by (d)
              measures (indi_value,v)
              RULES UPDATE
              (
                V[d>1]=v[cv(d)-1]*(1+(indi_value[cv(d)]-100)/100) --大于基期的计算公式
              )
             ),
 --获取小于基期的数据，结尾日期要能够将基期数据包含到要计算的数据中，数据按日期倒序，
 --并且使基期=100，往后的值都为空，然后通过行间计算，进行填补  
       down as (select indi_date,indi_value,
	                   case when indi_date like '201001%' 
					   then 100 else null end as v,rank() OVER(order by indi_date desc) as d
              from base
              where indi_date  between '20060101' and  '20100201'),
       downx as (select d,v
              from down  model
              dimension by (d)
              measures (indi_value,v)
              RULES UPDATE
              (
                V[d>1]=v[cv(d)-1]/(1+(indi_value[cv(d)-1]-100)/100) --小于基期的计算公式
              )
             )                  
     select u.indi_date,null as v,null as r,x.v as b 
	 from upx x join up u on x.d=u.d
     union
     select d.indi_date,null as v,null as r, x.v as b 
	 from downx x join down d on x.d=d.d

 

以上语句的结果即为对CPI的定基价格指数计算结果。其中 C0A0102是 CPI在表中的指标ID。语句基本上分位4部分

第一部分：主要是获取要计算的时间区间内的所有数据，并放到一个名叫base的临时表中。

 

with base as ( select indi_date,indi_value 
               from indi_value 
			   -- 获取20060101  -  20131101区间内要计算的数据
               where  indi_id='C0A0102' and indi_date between  '20060101'  and  '20131101' 
             ),

第二部分：计算大于基期的那部分数据

 

 ---获取大于基期的数据，数据按日期升序，并且使基期=100，往后的值都为空，然后通过行间计算，进行填补
      up as  (select indi_date,indi_value,
	                 case when indi_date like '201001%' 
	                 then 100 else null end as v,rank() OVER(order by indi_date) as d
              from base
              where indi_date between   '20100101'  and '20131101'),
      upx as (select d,v
              from up  model
              dimension by (d)
              measures (indi_value,v)
              RULES UPDATE
              (
                V[d>1]=v[cv(d)-1]*(1+(indi_value[cv(d)]-100)/100) --大于基期的计算公式
              )
             )

第三部分：计算小于基期的那部分数据，原理同第二部分。

 

第四部分：将第二，第三部分的计算结果整合起来

 

     select u.indi_date,null as v,null as r,x.v as b 
	 from upx x join up u on x.d=u.d
     union
     select d.indi_date,null as v,null as r, x.v as b 
	 from downx x join down d on x.d=d.d

其中第二，第三部分又可以分为2个步骤。

 

（1）是还是获取要计算的数据，并添加一列作为计算结果，且基期对应的值为100。添加一列排序号作为（2）步的取值下标。

（2）使用oracle提供的行间计算函数 model 来根据（1）中的indi_value，和 第一行的基期结果100来计算第二行的定基价格指数。

这样就会一行一行的计算下去，后一行的结果总是基于前一行的值来计算。（1）中的列 d 可以起类似于数组的作用。

measures 后面的 rules update是可以去掉的。