/*
目录：
1.语法
2.常见函数及用法
2.1 排序函数
2.2 wm_concat（）能实现不同行同字段的合并 ！！！！生产环境慎用
2.3 开窗用法
2.4 占比函数ratio_to_report( ) over(partition by )
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 分区 限定前面的函数操作的数据集是什么，类似于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
/*分区partition by 限定是按照部门作为一个数据集，这样就每个部门一个数据集
row_number() 指明要在数据集合上做排序
order by 指定按照sal排序 */
--指定要每个部门的前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（）能实现不同行同字段的合并 ！！！！生产环境慎用
/*场景：2.1已经能统计出每个部门的前3
现在想将每个部门的前三按照：名字工资，名字工资，名字工资的格式合并为一列。*/
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
/*场景 stock_market表中记录
（d_date 日期，v_symbol 股票代码，vc_exchange 市场,f_price_close 收盘价）
现在要取每个股票的信息及3天均值 前后5天最高值 */
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 客户号;
分享题目：oracle分析函数总结--仅供参考
网站链接：
http://bzwzjz.com/article/jcdesg.html
