oracle之函数(2)

入正题前首先先提出一个问题：有一张关于各门课学生成绩的表。如何查出各门课成绩前几的学生信息？

窗口函数

首先为什么会叫窗口函数？因为他实际上是将查询结果隔开一个个小窗口(区域)，然后在这一个个小窗口上进行操作，最后再汇总在一起形成最终结果
Oracle用over(partition by xxx order by xxx)将整体查询结果分组/排序从而实现开辟出一个个小窗口
但是over()并不是窗口函数，他只负责圈定一个范围供窗口函数生效。另外已有窗口函数帮忙排序，语句后面无须再进行排序，可能造成矛盾
切记：窗口函数一定要与over 并用，即使参与算数运算也要带上并且还要各论各的；即使是以整体为尺度，也要保证over()

rank() dense_rank() row_number()

这三个窗口函数是将窗口内的记录编号，涉及编号，所以over开辟窗口时一般是排序的。
三者编号使不一样的：
rank 编号是遇到相同数据就编号一致，但是会跨位。如 1,2,2,4,5,6,6,8，...
row_number 编号是无视相同，只管编号。如 1,2，3,4,5,6,7,8，...
dense_rank 编号是遇到相同数据编号一致，但是不跨位。如 1,2，2,3,4,5,6,6,7，...

上述提到取每组前几可以用这个窗口函数简洁明了处理

count() sum() avg() max() min()

这5个在一般分组中也能使用，作为窗口函数也是正常不过
但是要提一下sum()求和函数 作为窗口函数的意义。
sum(xx) over() ——over里面空白就是整体为一窗口，sum就是单纯求整体和
sum(xx) over(order by xx) ——over不分组只排序，就是在整体尺度上连续求和。每条记录都要与前面的所有记录求一次和
sum(xx) over(partition by xx) ——对各分组整体求总和
sum(xx) over(partition by xx order by yy) ——对各分组内部连续求和

cume_dist()

求窗口内各记录最大排名/总排名

lag() lead()

这是个动态窗口函数，以每条记录为准取当前记取一组录前/后某个偏移量的记录值(注：只能取一个，很遗憾不能取一组)
这么个窗口函数往往是要参与算术运算的
lag 是往前取；lead是往后取

目前常用的就是这几个窗口函数。当然还有其他若干函数如数学上常用的方差等分析公式都被作为窗口函数中的一员。只是专业性太强用的不多就是了。

除了前面所讲的几类函数还有一种比较冷门的函数——其他函数。
好家伙，大杂烩。
如：
greatest(),least() ——取列表中最大最小值，类型不同以第一个元素类型为准
nvl(expr,value) nvl2(expr,value1,value2) ——相当于MySQL中的ifnull

nvl2是提供一个备用，如果value1仍是null，就用value2

user,usid,userenv ——是当前用户的一些属性

SQL> select user,uid from dual; 

USER
--------------------------------------------------------------------------------
       UID
----------
CM2
        86
//userenvsahib返回当前会话的一些属性
//userenv(isdba) 是否具有dba权限
//userenv(language) userenv(lang)
//userenv(terminal) userenv(sessionid) userenv(client_info)

decode(expr,choo1,value1,...,valuedefault) ——类似switch选择

case when expr then
when expr then
else
end
也是等同效果

SQL> select decode(1,1,‘sd‘,2,‘fd‘,‘we‘) from dual;

DECODE
------
sd

nullif(expr1,expr2)——两者相等则返回null，否则返回expr1
sys_guid() ——随机生成一串32位的序列(包括0-9和A-Z)
dbms_random——生成随机数

dbms_random.value(n1,n2) 上下限，结果为小数，一般会用round/trunc去除小数
dbms_random.string(mode,length) 生成随机字符串。前面是模式，后面是生成的长度。模式主要是：U，全部是大写字符；L，全部是小写字符；A,大小写混合，常用；X,大写字符+数字；P,能打印出来的任意字符

oracle之函数(2)

原文：https://www.cnblogs.com/wyboke/p/15177273.html