oracle计算收益率,oracle 分析函数总结--仅供参考

oracle 分析函数总结--仅供参考

--1.分析函数的语法：函数名() over (分区排序滑动窗口/开窗)

--1.1.函数指定要在over圈定的数据上做什么操作，

--可以做sum(),max()等常见的聚合函数，

--也可以用row_number(),first_value()等功能强大的分析函数

--1.2 分区限定前面的函数操作的数据集是什么，类似于group by，但是功能比它强大

用group by 时不做聚合的列就必须参与分组，不能单独显示出来。

--1.3 排序指定函数计算的顺序，比如取排名，比如累加

--1.4 开窗 partition by 的基础上指定参与计算的行

--2.常见的函数及用法

--2.1 排序函数

row_number() 只返回一个结果 123456

rank()是跳跃排序，有两个第二名时接下来就是第四名122456

dense_rank() 是连续排序，有两个第二名时仍然跟着第三名122345

【比如想获取每位员工在所在部门的排序】

select row_number() over (partition by deptno order by sal desc) no,

ename,

sal,

deptno from emp;

NO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

1 KING 5000 10

2 CLARK 2450 10

3 MILLER 1300 10

1 SCOTT 3000 20

2 FORD 3000 20

3 JONES 2975 20

4 ADAMS 1100 20

5 SMITH 800 20

1 BLAKE 2850 30

2 ALLEN 1600 30

3 TURNER 1500 30

4 MARTIN 1250 30

5 WARD 1250 30

6 JAMES 950 30

--指定要每个部门的前3

select *

from ( select t.deptno,

t.ename,

t.sal,

row_number() over (partition by deptno

order by sal desc) top_no

from emp t

) a

where a.top_no<3;

--2.2 wm_concat()能实现不同行同字段的合并！！！！生产环境慎用

select a.deptno,wm_concat(ename||sal)

---切记虽然格式上有逗号，但是vm_concat会自动加逗号隔开

from (

select t.deptno,t.ename,t.sal,

row_number() over (partition by deptno

order by sal desc) top_no

from emp t

) a

where a.top_no<3

--2.3 开窗用法向前preceding，向前5行 5 preceding ，向前所有unbounded preceding

当前CURRENT ROW，

向后following，向后5行 5 following，向后所有unbounded following

SELECT t.d_date,

t.vc_symbol,

t.vc_exchange,

t.f_price_close,

--按照股票代码和市场进行分组日期进行排序，当前行及向前2行进行avg

avg(t.f_price_close) over(PARTITION BY t.vc_symbol, t.vc_exchange

ORDER BY t.d_date

ROWS between 2 preceding and CURRENT ROW) avg_price,

max(t.f_price_close) over(PARTITION BY t.vc_symbol, t.vc_exchange

ORDER BY t.d_date

ROWS between 5 preceding and 5 following) max_price

FROM stock_market t

WHERE t.d_date between 20161123-5 and 20161123+5

--2.4 占比函数ratio_to_report( ) over(partition by )

--计算员工工资占所在部门工资总和的百分之几。

select ename,deptno,sal,ratio_to_report(sal) over(partition by deptno) from emp;

--计算员工工资占所有工资总和的百分之几。

select ename,deptno,sal,ratio_to_report(sal) over( ) from emp;

--2.5前1 lag 后1 lead

select t.deptno,t.ename,t.sal,

lag(sal) over(partition by deptno order by sal desc) d_pre,

lead(sal) over(partition by deptno order by sal desc) d_flow

from emp t

--2.6 第一个first_value 最后一个last_value

select t.deptno,

t.ename,

t.sal,

first_value(sal) over(partition by deptno

order by sal desc

rows between unbounded preceding and unbounded following ) as d_first,

last_value(sal) over(partition by deptno

order by sal desc

rows between unbounded preceding and unbounded following ) as d_last

from emp t;

--2.7日常工作中在计算一段时间累加收益时，将每天的收益+1，然后进行连乘，最后再-1

--每天算累加收益率

select 日期,

客户号,

power(10,SUM(Sum(Log(10, (1 + NVL(日收益率, 0)))))

OVER(partation by 客户号

ORDER BY t2.d_date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) - 1 累加收益率

from 客户收益表

where 日期 between 20150101 and 20150531

group by 日期, 客户号;

-----一段时间算累累计收益率

select 客户号,

power(10,SUM(Log10(1 + NVL(日收益率, 0)))-1 累加收益率

from 客户收益表

where 日期 between 20150101 and 20150531

group by 客户号;