Oracle求同比SQL

 

计算需求:
1.求原始数据JS.TOPARTYNAME(购电方)的同比增长率

2.求原始数据华东购电(总计)=上海+江苏+浙江+福建+安徽的同比增长率

分析过程:
1.首先,我们要理解什么是同比?
同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响,
用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。如,本期2月比去年2月,本期6月比去年6月等。
那么,我们求的是同比增长率,其公式见下:

同比增长率=(本期数-同期数)/同期数*100% 指和去年同期相比较的增长率

 

2.理解同比的基本概念后,我们要知道在Oracle同比的基本求法?

这里我要介绍下Oracle的Lag函数:
Lag分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)作为独立的列。
这种操作可以代替表的自联接,并且LAG有更高的效率。
/*语法*/
lag(exp_str,offset,defval) over()
--exp_str要取的列
--offset取偏移后的第几行数据
--defval:没有符合条件的默认值

 

同时使用NVL和Round函数:

NVL函数格式为:

NVL( string1, replace_with)

功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。

Round函数语法:
round(number,digits) 
参数:
number,要四舍五入的数,digits是要小数点后保留的位数
  --如果 digits 大于 0,则四舍五入到指定的小数位。 
  --如果 digits 等于 0,则四舍五入到最接近的整数

 --如果 digits 小于 0,则在小数点左侧进行四舍五

3.了解oracle求同比的基本用法,我们需要具体应用到源是数据查询中,但是分析原始数据,发现源数据并不是规律连续的,见下图

那么这带来的问题是我们并不能向上面讲的那样简单的去使用lag函数了,那怎么办呢?

想到先整理数据后,再去使用函数分析查询是一个好办法,于是有了下方的解决办法:

A.求原始数据JS.TOPARTYNAME(购电方)的同比增长率

with tb as(
 select to_char(t.t_date, 'YYYY') year, to_char(t.t_date, 'MM') month, t.*
 from TRY_OPT_MON_ELEC_JS t
 where t.FYGZ = 'Z'
   and t.js_elec <> 0
   and t.js_kind in ('四川水电送华东月度1', '四川水电送华东短期2', '四川水电送华东短期3', '四川水电送浙江年度',
        '四川水电送江苏年度', '三峡送华东', '向家坝送上海交易', '锦东锦西官地送江苏交易',
        '四川水电送华东短期1', '溪洛渡送浙江', '锦东锦西官地送江苏交易', '溪洛渡左岸送浙江交易')
)
select max(t.t_date) 日期,
       t.topartyname 购电方,
       t.js_elec 结算电量,
       t.js_kind 结算品种,
       nvl(round(((t.js_elec - (lag(t.js_elec,1) over(partition by t.month, t.topartyname,t.js_kind order by t.year)))
       /((lag(t.js_elec,1) over(partition by t.month, t.topartyname,t.js_kind order by t.year))))*100,2),'0')||'%' as 同比
 from tb t
 where 1=1
 group by t.js_kind,t.topartyname, t.year, t.month, t.js_elec
 order by t.month

思路分析:
1.创建临时表tb 
2.把t_date日期字段拆分为年、月两个字段方便分组,筛去js_elec(售电量)为零的记录是,筛去不需要的js_kind(结算品种)
3.使用lag分析函数求得同比值
4.使用round函数格式化两位小数

5.使用nvl函数满足空值补零

查询结果:

B.求原始数据华东购电(总计)=上海+江苏+浙江+福建+安徽的同比增长率

with tb as(
 select to_char(t.t_date, 'YYYY') year, to_char(t.t_date, 'MM') month, t.*
 from TRY_OPT_MON_ELEC_JS t
   where t.FYGZ = 'Z'
   and t.js_elec <> 0
   and t.js_kind in ('四川水电送华东月度1', '四川水电送华东短期2', '四川水电送华东短期3', '四川水电送浙江年度',
        '四川水电送江苏年度', '三峡送华东', '向家坝送上海交易', '锦东锦西官地送江苏交易',
        '四川水电送华东短期1', '溪洛渡送浙江', '锦东锦西官地送江苏交易', '溪洛渡左岸送浙江交易')
   and t.topartyname in ('上海','江苏','浙江','福建','安徽')
)
,tb2 as(
select max(t.t_date) t_date, sum(t.js_elec) js_elec, t.year, t.month
from tb t
group by t.year, t.month
)
select max(t.t_date) 日期,
       '华东' 华东,
       t.js_elec 结算电量,
       nvl(round(((t.js_elec - (lag(t.js_elec,1) over(partition by t.month order by t.year)))
       /((lag(t.js_elec,1) over(partition by t.month order by t.year))))*100,2),'0')||'%' as 同比
 from tb2 t
 where 1=1
 group by t.year, t.month, t.js_elec
 order by t.year,t.month

思路分析:
1.创建临时表tb 
2.把t_date日期字段拆分为年、月两个字段方便分组,筛去js_elec(售电量)为零的记录是,筛去不需要的js_kind(结算品种)和不是华东区的购电单位
3.使用lag分析函数求得同比值
4.使用round函数格式化两位小数

5.使用nvl函数满足空值补零

查询结果:

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页