ORACLE基础知识2
八、分析函数
8.1 分析函数
（1）分析函数
分析函数是Oracle专门用于 解决复杂报表统计需求 的功能强大的函数，它可以在数据中进行 分组 然后计算 基于组的某种统计值 ，并且 每一组的每一行都可以返回一个统计值 。
（2）分析函数和聚合函数的区别
1） 普通的聚合函数用 group by 分组， 每个分组返回一个统计值。
2） 分析函数采用 partition by 分组，并且 每组每行都可以返回一个统计值 。
（3）分析函数的形式
分析函数带有一个 开窗函数over() ，包含 分析子句 。
分析子句又由下面三部分组成：
partition by ： 分组子句 ，表示分析函数的计算范围，不同的组互不相干；
ORDER BY ： 排序子句 ，表示分组后，组内的排序方式；
ROWS/RANGE ： 窗口子句 ，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；
使用形式如下：
OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd)注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。
8.2 OVER() 函数
(1)sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执行顺序
分析函数是在整个sql查询结束后 (sql语句中的order by的执行比较特殊) 再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果 ：
[1] 两者一致：如果sql语句中的order by满足分析函数分析时要求的排序，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
[2] 两者不一致：如果sql语句中的order by不满足分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执行排序。
（2）分析函数中的分组/排序/窗口
分析函数包含三个分析子句：分组(partition by)， 排序(order by)， 窗口(rows/range)
窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。
[A] range是逻辑窗口（值） ：是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内。
如：
SUM(id) OVER ( ORDER BY id RANGE BETWEEN 1 PRECEING AND 2 FOLLOWING )分析：
当id=1时，是 1-1<=id<=1+2 的所有id值求和，即sum=1+1+3=5(取id为1,1,3);
当id=3时，是3-1<=id<=3+2 的所有id值求和，即sum=3（取id为3）；
[B] rows是物理窗口（行） ：即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），
如：
SUM(id) OVER ( ORDER BY id ROWS BETWEEN 1 PRECEING AND 2 FOLLOWING )是取前1行到后2行的数据求和，分析：
当id=1时，前一行没数，,后二行是第3行，就是1到3行；
当id=3时，前一行是第2行,后二行是第5行，就是2到5行；
（3）带有窗口子句
窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE。 若使用窗口子句，必须有ORDER BY 子句 。
窗口子句中经常用到指定行：第一行（ unbounded preceding ），当前行（ current row ），最后一行（ unbounded following ），以及前num1行（ num1 preceding ）和 后num2行（ num2 following ）。
窗口子句不能单独出现，必须有order by子句时才能出现，如:
last_value(sal)
over(partition by deptno
order by sal
rows between unbounded preceding and unbounded following)以上示例指定窗口为整个分组。而出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！
（4）当省略窗口子句时:
[1] 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行
[2] 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
（5）如果省略分组,则把全部记录当成一个组:
[1] 如果存在order by则默认窗口是unbounded preceding and current row --当前组的第一行到当前行
[2]如果这时省略order by则窗口默认为unbounded preceding and unbounded following --整个组
8.3 常用分析函数
(1)排名函数
函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
| 函数 | 作用 |
|---|---|
| row_number() over() | row_number()返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 |
| rank() over() | rank()返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。 |
| dense_rank() over() | dense_rank()返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间紧邻递增。 |
（2）聚合函数
| 函数 | 作用 |
|---|---|
| count() over() | |
| max() over() | |
| min() over() | |
| sum() over() | |
| avg() over() |
（3）求最值对应的其他属性
| 函数 | 作用 |
|---|---|
| first_value() over() | 第一个值 |
| last_value() over() | 最后一个值 |
（4）求之前或之后的第N行
注：行比较分析函数lead和lag无window(窗口)子句。
lag(arg1,arg2,arg3) 和 lead(arg1,arg2,arg3) 可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
[1] arg1:参数是列名，
[2] arg2:参数是偏移的offset，
[3] arg3:参数是超出记录窗口时的默认值。
| 函数 | 作用 |
|---|---|
| lag() over() | lag()函数向下偏移。 |
| lead() over() | lead()函数是向上偏移 |
举例如下：
select * from kkk;
ID NAME
---------- --------------------
1 1name
2 2name
3 3name
4 4name
5 5name
select id,name,lag(name,1,0) over(order by id) from kkk;
ID NAME LAG(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- ----------------------------
1 1name 0
2 2name 1name
3 3name 2name
4 4name 3name
5 5name 4name
select id,name,lead(name,1,0) over(order by id) from kkk;
ID NAME LEAD(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name 2name
2 2name 3name
3 3name 4name
4 4name 5name
5 5name 0
select id,name,lead(name,2,0) over(order by id) from kkk;
ID NAME LEAD(NAME,2,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name 3name
2 2name 4name
3 3name 5name
4 4name 0
5 5name 0
select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;
ID NAME LEAD(NAME,1,'ALSDFJLASDJFSAF')
---------- -------------------- ------------------------------
1 1name 2name
2 2name 3name
3 3name 4name
4 4name 5name
5 5name linjiqin （5）排列组合函数，只能用在 GROUP BY 后边
| 函数 | 作用 |
|---|---|
| group by rollup(a, b, c) |
首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，其后再对(a)进行group by，最后对全表进行汇总操作。 |
| group by cube(a, b, c) |
首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，最后对全表进行汇总操作。 |
示例如下：
普通 group by 分组
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;
GROUP BY ROLLUP(A, B, C)：
首先会对(A、B、C)进行GROUP BY，
然后再对(A、B)进行GROUP BY，
其后再对(A)进行GROUP BY，
最后对全表进行汇总操作。
select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);
GROUP BY CUBE(A, B, C)：
首先会对(A、B、C)进行GROUP BY，
然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，
最后对全表进行汇总操作。
select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);
九、行列转换
9.1 wm_concat函数
wm_concat能够实现同样的功能，但是有时在11g中使用需要用to_char()进行转换，否则会出现不兼容现象(WMSYS.WM_CONCAT: 依赖WMSYS 用户，不同oracle环境时可能用不了，返回类型为CLOB，可用substr截取长度后to_char转化为字符类型)。
测试用表test_org_bak
select * from test_org_bak;
（1）:行转列（默认逗号隔开）
select wm_concat(level_four_name) name from test_org_bak;
（2）更换分隔符
select replace(wm_concat(level_four_name),',','|') from test_org_bak;（3）:按照事业部分组合并大区
select level_three_name,wm_concat(level_four_name) name from test_org_bak group by level_three_name;
select level_three_name,wm_concat(distinct level_four_name) name from test_org_bak group by level_three_name;--可去重
9.2 listagg函数
注意:listagg函数的第一个参数是需要显示的字段，也就是log_name；第二个参数是数值之间的分隔符；同时还需要进行排序和分组within group (order by name)
一般推荐使用LISTAGG()方法去列转行。或者自定义个方法去实现行转列。
select listagg(level_four_name, ',') within group(order by level_four_name) level_four_name1 from test_org_bak;结果同上1
select level_three_name,
listagg(level_four_name, ',') within group(order by level_four_name) level_four_name1
from test_org_bak
group by level_three_name;
解决使用聚合函数LISTAGG进行查询后，对查询结果乱码问题进行转码处理
listagg(to_char(列名))；还需注意：LISTAGG:11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000;
ps(一个实用方法:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单
select 'create or replace view as select '|| wm_concat(column_name) || ' from test'from user_tab_columns where table_name='TEST';
9.3 行转列pivot
关键函数pivot，其用法如下 pivot（聚合函数 for 列名 in（类型））
select * from table_name pivot(max(column_name) --行转列后的列的值value，聚合函数是必须要有的
for column_name in(value_1,value_2,value_3) --需要行转列的列及其对应列的属性1/2/3
)(1)首先举一个简单的例子，创建一个数据表
create table tmp as select * from (
select '张三' student,'语文' course ,78 score from dual union all
select '张三','数学',87 from dual union all
select '张三','英语',82 from dual union all
select '张三','物理',90 from dual union all
select '李四','语文',65 from dual union all
select '李四','数学',77 from dual union all
select '李四','英语',65 from dual union all
select '李四','物理',85 from dual);
先使用decode或case when方法
select
student,
max(decode(course, '语文', score)) 语文,
max(decode(course, '数学', score)) 数学,
max(decode(course, '英语', score)) 英语,
max(decode(course, '物理', score)) 物理,
sum(score) total
from tmp
group by student;
-----------------------------------------
select
student,
max(case when course = '语文' then score end) 语文,
max(case when course = '数学' then score end) 数学,
max(case when course = '英语' then score end) 英语,
max(case when course = '物理' then score end) 物理,
sum(score) total
from tmp
group by student;
pivot的使用
select t.*,
(t.语+t.数+t.外+t.物) as total
(select *
from tmp pivot ( max(score) for course in ('语文' as 语 , '数学' as 数, '英语' as 外,'物理' as 物) )
) t;结果同上
(2)实际开发遇到的问题
有一张目标值表，年、月、日的值都是分开多行显示，现需合并成一行显示，具体数据如下：（type：1-->日，2-->月，3-->年；targetvalue：目标值）
select * from MOVEBI.T_GMS_MBI_TARGET_DATA where targetcode = '31227061'
此数据必须先进性处理，要保证数据可以聚合成一条，若直接使用会出现下列情况：
select * from MOVEBI.T_GMS_MBI_TARGET_DATA pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061';
这不是我们想要的结果，具体改进法法如下：
--方法一：对结果处理
select max(datatime) datatime
,usercode
,deptcode
,deptname
,targetcode
,targetname
,sum(coalesce(day_value,0)) day_value
,sum(coalesce(mon_value,0)) mon_value
,sum(coalesce(year_value,0)) year_value
from(
select datatime,usercode,deptcode,deptname,targetcode,targetname,day_value,mon_value,year_value
from MOVEBI.T_GMS_MBI_TARGET_DATA
pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061')
group by usercode
,deptcode
,deptname
,targetcode
,targetname;
--方法二：对原始表处理
select *
from (select '20181017' datatime,
usercode,
deptcode,
deptname,
targetcode,
targetname,
targetvalue,
from MOVEBI.T_GMS_MBI_TARGET_DATA
where datatime in ('20181017', '201810')
and targetcode = '31227061') t
pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061';
9.4 列转行unpivot
根据上面的例子创建tmp_2测试用表
select student,科目,成绩 from tmp_2 unpivot (成绩 for 科目 in (语文, 数学, 英语, 物理));
同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
select student,'语文' 科目, (select 语文 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'数学' 科目, (select 数学 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'英语' 科目, (select 英语 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'物理' 科目, (select 物理 from tmp_2 where student=f.student) 成绩 from tmp_2 f
-------------------------------------------
select student,'语文' 科目,语文 from tmp_2
union
select student,'数学' 科目,语文 from tmp_2
union
select student,'英语' 科目,语文 from tmp_2
union
select student,'物理' 科目,语文 from tmp_2
十、connect by
10.1 基本语法
select * from table [start with condition1]
connect by [prior] id=parentid一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。
- start with condition1 是用来 限制第一层的数据 ，或者叫根节点数据； 以这部分数据为基础 来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
- connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
prior还有一种用法：
select * from table [start with condition1]
connect by id= [prior] parentid- 这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。
connect by 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。
语法格式：
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}start with: 指定起始节点的条件
connect by: 指定父子行的条件关系
prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ，
nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条
循环行: 该行只有一个子行，而且子行又是该行的祖先行
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是。
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是
level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点
其他特性
- level关键字，代表树形结构中的层级编号；第一层是数字1，第二层数字2，依次递增。
- CONNECT_BY_ROOT方法，能够获取第一层集结点结果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。
10.2 connect by用法
先用scott用户下的emp表做实验.
emp表有个字段，一个是empno(员工编号)，另一个是mgr(上级经理编号)
下面是表中所有数据
select * from emp start with empno=7698 connect by mgr=prior empno;执行结果如下：
得到的结果是empno=7698的数据，以及会得到mgr=7698的数据。
它是向下递归的, 即我们从empno=7698开始遍历，去找出mgr=7698的所有数据S(用S代表查出的所有数据.), 然后在从S中的empno的值去匹配查找是否还有满足，mgr in (s.empno)的数据。一直遍历进去到没有数据为止。
下面的这个可以详细的表述效果。
--向下递归遍历
select * from emp connect by mgr= prior empno start with empno=7839;执行结果如下：
--向上递归遍历
select * from emp connect by prior mgr=empno start with empno=7844;
这样直到没有匹配的数据为止。
以上只是简单的举了个例子。
connect by是结构化查询中用到的，其基本语法是：
select ... from tablename
start by cond1
connect by cond2
where cond3简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段(如emp表中的empno和mgr字段):empno, mgr那么通过表示每一条记录的mgr是谁，就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中：
- cond1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
- cond2是连接条件，其中用prior表示上一条记录，比如connect by prior id=praentid就是说上一条记录的id是本条记录的praentid，即本记录的父亲是上一条记录。
- cond3是过滤条件，用于对返回的所有记录进行过滤。
- prior和start with关键字是可选项
- prior运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，prior运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。
- start with子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
十一、事务
在数据库中事务是 工作的逻辑单元 ，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作 要么都成功执行，完成整个工作单元操作，要么一个也不执行 。
11.1 事务特性
原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做。
一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
11.2 数据异常
因为Oracle中支持多个事务并发执行，所以会出现下面的数据异常。
（1）脏读
当一个事务修改数据时，另一事务读取了该数据，但是第一个事务由于某种原因取消对数据修改，使数据返回了原状态，这是第二个事务读取的数据与数据库中数据不一致，这就叫脏读。
如：事务T1修改了一条数据，但是还未提交，事务T2恰好读取到了这条修改后了的数据，此时T1将事务回滚，这个时候T2读取到的数据就是脏数据。
（2）不可重复读
是指一个事务读取数据库中的数据后，另一个事务则更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生了改变，这就是不可重复读取。不可重复读取所导致的结果就是一个事务前后两次读取的数据不相同。
如：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同。
（3）幻读
如果一个事务基于某个条件读取数据后，另一个事务则更新了同一个表中的数据，这时第一个事务再次读取数据时，根据搜索的条件返回了不同的行，这就是幻读。
如：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻读。
事务中遇到的这些异常与事务的隔离性设置有关，事务的隔离性设置越多，异常就出现的越少，但并发效果就越低，事务的隔离性设置越少，异常出现的越多，并发效果越高。
11.3 事务隔离级别
针对读取数据时可能产生的不一致现象，在SQL92标准中定义了4个事务的隔离级别：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted(读未提交) | 是 | 是 | 是 |
| Read committed（读已提交） | 否 | 是 | 是 |
| Repeatable read（可重复读） | 否 | 否 | 是 |
| Serializable（串行读） | 否 | 否 | 否 |
Oracle默认的隔离级别是 read committed 。
Oracle支持上述四种隔离级别中的两种: read committed 和 serializable 。除此之外，Oralce中还定义 Read only 和 Read write 隔离级别。
Read only ：事务中不能有任何修改数据库中数据的操作语句，是Serializable的一个子集。
Read write ：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。
设置隔离级别
设置一个事务的隔离级别：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;