ORACLE 实用函数总结,oracle 分析函数总结--仅供参考

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

/*

目录:

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 客户号;

©著作权归作者所有:来自51CTO博客作者Tqy_zsh的原创作品,如需转载,请注明出处,否则将追究法律责任

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值