/*
目录：
1.语法
2.常见函数及用法
2.1排序函数
2.2wm_concat（）能实现不同行同字段的合并！！！！生产环境慎用
2.3开窗用法
2.4占比函数ratio_to_report()over(partitionby)
2.5前一个lag后一个lead
2.6第一个first_value最后一个last_value
2.7加1连乘再减1
*/

--1.分析函数的语法：函数名（）over(分区排序滑动窗口/开窗)
--1.1.函数指定要在over圈定的数据上做什么操作，
--可以做sum(),max()等常见的聚合函数，
--也可以用row_number(),first_value()等功能强大的分析函数
--1.2分区限定前面的函数操作的数据集是什么，类似于groupby，但是功能比它强大
用groupby时不做聚合的列就必须参与分组，不能单独显示出来。
--1.3排序指定函数计算的顺序，比如取排名，比如累加
--1.4开窗partitionby的基础上指定参与计算的行
--2.常见的函数及用法
--2.1排序函数
row_number()只返回一个结果123456
rank()是跳跃排序，有两个第二名时接下来就是第四名122456
dense_rank()是连续排序，有两个第二名时仍然跟着第三名122345
【比如想获取每位员工在所在部门的排序】
selectrow_number()over(partitionbydeptnoorderbysaldesc)no,ename,sal,deptnofromemp;
NOENAMESALDEPTNO
----------------------------------------
1KING500010
2CLARK245010
3MILLER130010

1SCOTT300020
2FORD300020
3JONES297520
4ADAMS110020
5SMITH80020

1BLAKE285030
2ALLEN160030
3TURNER150030
4MARTIN125030
5WARD125030
6JAMES95030
/*分区partitionby限定是按照部门作为一个数据集，这样就每个部门一个数据集
row_number()指明要在数据集合上做排序
orderby指定按照sal排序*/

--指定要每个部门的前3
select*
from(selectt.deptno,t.ename,t.sal,row_number()over(partitionbydeptno
orderbysaldesc)top_no
fromempt
)a
wherea.top_no<3;

--2.2wm_concat（）能实现不同行同字段的合并！！！！生产环境慎用
/*场景：2.1已经能统计出每个部门的前3
现在想将每个部门的前三按照：名字工资，名字工资，名字工资的格式合并为一列。*/
selecta.deptno,wm_concat(ename||sal)
---切记虽然格式上有逗号，但是vm_concat会自动加逗号隔开
from(
selectt.deptno,row_number()over(partitionbydeptno
orderbysaldesc)top_no
fromempt
)a
wherea.top_no<3


--2.3开窗用法向前preceding，向前5行5preceding，向前所有unboundedpreceding
当前CURRENTROW，
向后following，向后5行5following，向后所有unboundedfollowing
/*场景stock_market表中记录
（d_date日期，v_symbol股票代码，vc_exchange市场,f_price_close收盘价）
现在要取每个股票的信息及3天均值前后5天最高值*/
SELECTt.d_date,t.vc_symbol,t.vc_exchange,t.f_price_close,--按照股票代码和市场进行分组日期进行排序，当前行及向前2行进行avg
avg(t.f_price_close)over(PARTITIONBYt.vc_symbol,t.vc_exchange
ORDERBYt.d_date
ROWSbetween2precedingandCURRENTROW)avg_price,max(t.f_price_close)over(PARTITIONBYt.vc_symbol,t.vc_exchange
ORDERBYt.d_date
ROWSbetween5precedingand5following)max_price
FROMstock_markett
WHEREt.d_datebetween20161123-5and20161123+5

--2.4占比函数ratio_to_report()over(partitionby)
--计算员工工资占所在部门工资总和的百分之几。
selectename,deptno,ratio_to_report(sal)over(partitionbydeptno)fromemp;
--计算员工工资占所有工资总和的百分之几。
selectename,ratio_to_report(sal)over()fromemp;


--2.5前1lag后1lead
selectt.deptno,lag(sal)over(partitionbydeptnoorderbysaldesc)d_pre,lead(sal)over(partitionbydeptnoorderbysaldesc)d_flow
fromempt

--2.6第一个first_value最后一个last_value
selectt.deptno,first_value(sal)over(partitionbydeptno
orderbysaldesc
rowsbetweenunboundedprecedingandunboundedfollowing)asd_first,last_value(sal)over(partitionbydeptno
orderbysaldesc
rowsbetweenunboundedprecedingandunboundedfollowing)asd_last
fromempt;

--2.7日常工作中在计算一段时间累加收益时，将每天的收益+1，然后进行连乘，最后再-1
--每天算累加收益率
select日期,客户号,power(10,SUM(Sum(Log(10,(1+NVL(日收益率,0)))))
OVER(partationby客户号
ORDERBYt2.d_date
ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW))-1累加收益率
from客户收益表
where日期between20150101and20150531
groupby日期,客户号;


-----一段时间算累累计收益率
select客户号,SUM(Log10(1+NVL(日收益率,0)))-1累加收益率
from客户收益表
where日期between20150101and20150531
groupby客户号;