Oracle窗口函数和分析函数

一、前言描述：

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by),窗口(rows) ，他们的使用形式如下：分析函数名(参数) over (partition by 子句 order by 子句 rows/range… 子句)
(注：若窗口函数内和sql语句末尾共存在两个order by）
a) order by 字段两者一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) order by 字段两者不一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，那么sql语句中的排序将最后在分析函数分析结束后执行排序。)

二、窗口函数的由来：partition by:分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是窗口函数的由来。通过PARTITION BY分组后的记录集合称为“窗口”

三、 函数使用语法及含义

1，case when else end
select empno,sal,
case when sal <= 1000 then ‘平均工资’
when sal >= 2500 then ‘最高工资’
else ‘得好好加油丫丫吖’ end
from nball order by sal desc;

2，min/max和over子句的配合使用
select empno,ename,job,mgr,hiredate,sal,
min(sal) over(partition by empno order by sal desc) 最小金额,
max(sal) over(partition by empno order by empno asc) 最大金额
from nball order by sal desc;
3，统计并排序使用
select empno, ename, mgr, sal, deptno, count(*) over(order by empno)
from nball;

4，ntile：切片函数的使用
将数据切分成n组（不够等分的按顺序添加到每个组内）自我简称为：切片
注：必须要加order by
select empno, ename, mgr, sal, deptno, ntile(3) over(order by sal desc)
from nball;
5，last_value：返回表中最后一行数据
注意： ignore nulls 在 last_value、first_value 中 （ignore nulls 忽略空值)

select empno 编号, ENAME 姓名, last_value(sal ignore nulls) over() 薪资
from nball where empno = ‘8301’;

下面是原表数据：

6，first_value：返回表中第一行数据

select empno 编号, ENAME 姓名, first_value(sal) over( ) from nball
where rownum = 1;

注意：上段SQL加入了Oracle特有的函数 rownum 用来限定返回的行数，需要加上where 关键字。不然会报错；

7，分析函数：lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

select empno 编号, ENAME 姓名, lag(sal, 5, 2) over(order by sal) 序号品牌
from nball;

8，Lead：函数可以在一次查询中取出当前行的同一字段的后面第N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
select empno 编号,ENAME 姓名,sal,lead(ename, 7, 4) over(order by sal desc)
from nball;

9，RANK函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
语法： rank（）over（partition by … order by ）from table

如下图红色标注点：按照Sal排的序，如果出现相同工资的则并列排序第8、15、19名

select empno,ename,job,hiredate,sal,rank()over(order by sal desc) ID from nball

10，DENSE_RANK函数同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
select empno,ename,job,hiredate,sal,dense_rank()over(order by sal desc ) ID from nball;

11，ROW_NUMBER函数：赋予唯一的连续位次
select empno,ename,job,hiredate,sal,deptno,row_number() over(order by sal desc )薪资 from nball;
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……:仅供参考
select empno,ename,job,hiredate,sal,deptno,row_number() over(partition by deptno order by sal desc ) 排名数 from nball;

12，Oracle数据库中并集，交集，差集的作用
union 去重，合并后的结果都是唯一
union all 不去重，合并后的结果有可能出现重复的
select classid from student
union
select classid from class;
select classid from student
union all
select classid from class; – Oracle 和 MySQL都支持这种方法查询

intersect 交集，取相同的记录
minus 差集，返回第一个查询结果中与第二个查询结果不相同的那部分记录。
minus 差集，主表 减去 从表中与主表相同的记录 。

四、记录一些自己平常不明白的术语：

1. 什么是笛卡尔积:实际上就是两张表的乘积
2. Oracle两张表连接实际上是就在一张表中筛选记录，在另外一张表中一行行去扫描字段所对应的记录，筛选到所匹配的条件后，返回给客户端，接着继续下一行扫描。
3. rowid : 伪劣 每行记录所存放的真实物理地址
4. rownum: 行号, 每查询出记录之后，就会添加一个行号
   Oracle 创建用户授权。

五、基本的一些语法记录
1.修改表名称
alter table student1 rename to student;
2.复制表结构
create table wei as select * from zq where 1=2; 空表

思考：为什么能够实现只复制表结构和没有复制表数据？
那是因为查询条件：1=2，只能查出的数据为空。

3.复制表结构和表数据
create table zq as select * from student;

4.模糊查询
select empno,ename job,sal from nball where ename like 'J%'order by sal desc;
5.Oracle 使用技巧：

6. Oracle 免密登录。
1、打开本地cmd命令行输入：sqlplus /nolog

2、使用管理员角色连接：conn /as sysdba

3、修改密码：alter user 用户名 identified by 密码：