重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
/* 目录: 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 客户号;