oracle函数学习（⼀）
1. partition by函数
lect
deptno, ename, sal,
sum (sal) over(partition by deptno order by ename) 部门连续求和, --各部门的薪⽔ "连续" 求和
sum (sal) over(partition by deptno) 部门总和, -- 部门统计的总和，同⼀部门总和不变
100 * round(sal / sum (sal) over(partition by deptno), 4 ) "部门份额(%)" ,
sum (sal) over( order by deptno, ename) 连续求和, --所有部门的薪⽔ "连续" 求和 sum (sal) over() 总和, -- 此处 sum (sal) over () 等同于sum (sal)，所有员⼯的薪⽔总和
100 * round(sal / sum (sal) over(), 4 ) "总份额(%)"
from emp
1. group by是分组函数，partition by是分析函数（然后像sum()等是聚合函数）；
2. 在执⾏顺序上，
以下是常⽤sql关键字的优先级
from>where>group by>having>order by
⽽partition by应⽤在以上关键字之后，实际上就是在执⾏完lect之后，在所得结果集之上进⾏partition。
3. partition by相⽐较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），⽽group by则只保留参与分组的字段和聚合函数的结果（类似excel中的pivot）。
2、rank over() 函数
rank () over ( [query_partition_clau] order_by_clau )
den_rank () over ( [query_partition_clau] order_by_clau )
【功能】聚合函数RANK 和 den_rank 主要的功能是计算⼀组数值中的排序值。
【参数】den_rank与rank()⽤法相当
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过.
rank()是跳跃排序，有两个第⼆名时接下来就是第四名（同样是在各个分组内）
den_rank()l是连续排序，有两个第⼆名时仍然跟着第三名。
3. row_number()函数
语法
【语法】
ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表⽰根据COL1分组，在分组内部根据 COL2排序，⽽这个值就表⽰每组内部排序后的顺序编号（组内连续的唯⼀的）
row_number() 返回的主要是“⾏”的信息，并没有排名.
【参数】
【说明】Oracle分析函数
【主要功能】：⽤于取前⼏名，或者最后⼏名等
4.lag( )和lead( )函数
这两个函数是偏移量函数，可以查出⼀个字段的上⼀个值或者下⼀个值，配合over来使⽤。
lead函数，这个函数是向上偏移.
lag函数是向下偏移⼀位.
语法
【语法】
lag(EXPR, < OFFSET > , < DEFAULT > )
LEAD(EXPR, < OFFSET > , < DEFAULT > )
【功能】表⽰根据COL1分组，在分组内部根据 COL2排序，⽽这个值就表⽰每组内部排序后的顺序编号（组内连续的唯⼀的）
lead () 下⼀个值 lag() 上⼀个值
【参数】
EXPR是从其他⾏返回的表达式
OFFSET是缺省为1 的正数，表⽰相对⾏数。希望检索的当前⾏分区的偏移量
DEFAULT是在OFFSET表⽰的数⽬超出了分组的范围时返回的值。
expr 是要做对⽐的字段
offt 是expr字段的偏移量 ⽐如说 offt 为2 则拿expr的第⼀⾏和第三⾏对⽐，第⼆⾏和第四⾏，依次类推，offt的默认值为1！
5、with ... as函数
当在sql查询时需要对有规律的⼀批数据进⾏分析处理⽽⼜不想将这批数据存⼊实体表时，我们可以使⽤with关键字临时构建⼀个虚拟的数据集，以便对其进⾏与实体表相似的sql操作，如：
（注：with关键字构造的虚拟数据集临时存放于⽤户的临时表空间中）
6、 exists/in及not exists/not in
仅从语法上来讲，在⼤多数情况下exists与in及not exists与not in可相互转换使⽤，但其各⾃的适⽤场景略有不同，执⾏效率也有差异。当⼦查询数据量较⼩时应使⽤in或not in，反之则使⽤exists或not exist⽐较合适，因为使⽤in时，Oracle查询是执⾏顺序是由内⽽外的，但如果使⽤exist的话Oracle则
会“先外后内”，查询开销相对较⼤，现举例如下：
查询所有绑定了部门ID的⽤户（适合⽤in的场景）
适合⽤in的场景
查询未被账户绑定的部门（适合⽤not exists的场景）
7、union与union all
union，查询返回多表去重后的结果集：
union，查询结果去重union all，查询返回多表未重的结果集：
union all并集查询，不去重8、 nvl与decode
lect ename ,sal+comm total from emp;
//nvl处理null，nvl(列名称|具体的值，默认值)
lect ename, sal+nvl(comm, 0) total from emp;
查询所有的员⼯信息，显⽰员⼯的中⽂职位 CLERK=办事⼈员，SALESMAN=销售⼈员，PRESIDENT=董事长,MANAGER=部门经理,ANALYST=分析⼈员
⽅法1：
lect ename,
ca when job='CLERK'
then '办事⼈员'
when job='SALESMAN'
when job='SALESMAN'
then '销售⼈员'
when job='PRESIDENT'
then '董事长'
when job='MANAGER'
then '部门经理'
el '分析⼈员'
end
from emp;
⽅法2：
lect ename,decode(job,'CLERK','办事⼈员','SALESMAN','销售⼈员','PRESIDENT','董事长','MANAGER','部门经理','ANALYST','分析师') from emp;
9、start with函数
SELECT ... FROM + 表名
[START WITH + 条件1]
CONNECT BY PRIOR + 条件2
WHERE + 条件3
条件1：是根节点的限定语句，当然可以放宽限定条件，以取得多个根节点，也就是多棵树；在连接关系中，除了可以使⽤列明外，还允许使⽤列表达式。START WITH ⼦句为可选项，⽤来标识哪个节点作为查找树形结构的根节点。 若该⼦句省略，则表⽰所有满⾜查询条件的⾏作为根节点。
条件2：是连接条件，其中⽤PRIOR表⽰上⼀条记录，例如CONNECT BY PRIOR STUDENT_ID = GRADE_ID，意思就是上⼀条记录的STUDENT_ID是本条记录的GRADE_ID，即本记录的⽗亲是上⼀条记录。CONNECT BY⼦句说明每⾏数据将是按照层次顺序检索，并规定将表中的数据连⼊树形结构的关系中。
PRIOR运算符必须放置在连接关系的2列中某⼀个的前⾯。对于节点间的⽗⼦关系 ，PRIOR运算符在⼀侧表⽰⽗节点，在另⼀侧表⽰⼦节点，从⽽确定查找树结构的顺序是⾃顶向下，还是⾃底向上。
条件3：是过滤条件，⽤于对返回的记录进⾏过滤。
10、rollup函数
rollup()是group by的⼀个扩展函数，初步的感觉是，可以多个列进⾏group by，然后分别进⾏统计。
⽰例：
1. 证券池配置表，按池⼦类型统计：
lect t.o_type,count(*)
from tpool_bond_config t
group by rollup(t.o_type)
跟普通的group by相⽐，就是最后多了⼀个总的统计
10、grouping函数
grouping（columnA）：当前⾏如果是由rollup汇总产⽣的，那么columnA这个字段值为1否则为0
元数据：
sql：
lect decode(grouping(f_line)+grouping(f_workarea),1,'⼩计',2,'总计',f_workarea) f_workarea, decode(grouping(f_line),1,count(*)||'条',f_line) f_line,
sum(f_pagesnumber) sum_pagesnumbers
from t_testcount
group by rollup (f_workarea,f_line);
通过grouping查询后的数据：
