2019-04-21

sql练习第二周（oracle）

OVER(PARTITION BY)函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

　　1、over函数的写法：

　　over（partition by class order by sroce）按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

　　2、开窗的窗口范围：

　　over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

　　over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

分析函数用法及窗口子句 range/rows差别

分析函数的语法结构一般是：分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。

即由以下三部分组成：

分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等；

over：关键字，表示前面的函数是分析函数，不是普通的集合函数；

分析子句：over关键字后面挂号内的内容；

分析子句又由下面三部分组成：

partition by ：分组子句，表示分析函数的计算范围，不同的组互不相干；

ORDER BY：排序子句，表示分组后，组内的排序方式；

ROWS/RANGE：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；

例如如下语句：

（unbounded :无限的,current:当前的）

SELECT id,

SUM(ID) over(ORDER BY ID) default_sum,

SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,

SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,

SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,

SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum

FROM t

------|------|------|------|------|------

1|2|2|1|5|5

1|2|2|2|5|11

3|5|5|5|3|16

6|23|23|11|33|21

6|23|23|17|33|25

6|23|23|23|33|27

7|30|30|30|42|30

8|38|38|38|24|24

9|47|47|47|17|17

从上面的例子可知：

1、窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，如上例结果集中的defult_sum等于range_unbound_sum；

2、如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算；

3、range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如上例中range_sum（即range 1 preceing and 2 following）例的分析结果：

当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5(取id为1,1,3);

当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3）；

当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id为6，6，6，7，8）;

以此类推下去，结果如上例中所示。

4、rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），如上例中rows_sum例结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果：

当id=1（第一个1时）时，前一行没数，后二行分别是1和3，sum=1+1+3=5；

当id=3时，前一行id=1,后二行id都为6，则sum=1+3+6+6=16；

以此类推下去，结果如上例所示。

注：行比较分析函数lead和lag无window(窗口)子句。

Oracle中row_number()、rank()、dense_rank() 的区别

表数据

T2_TEMP (NAME, CLASS, SROCE)

values('cfe','2',74); values('dss','1',95); values('ffd','1',95);

values('fda','1',80); values('gds','2',92); values('gf','3',99);

values('ddd','3',99); values('adf','3',45); values('asdf','3',55);

values('3dd','3',78);

（1）查询每个班的第一名的成绩：如下

SELECT * FROM (select t.name, t.class, t.sroce, rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t)where mm=1;

　　结果为：

　　注意：在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。

SELECT * FROM(select t.name, t.class, t.sroce, row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t)where mm=1;

　　结果为：

　　可以看出，本来第一名是两个人的并列，结果只显示了一个。

　　（2）rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

　　求班级成绩排名：

select t.name, t.class, t.sroce, rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　查询结果：

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

select t.name ,t.class, t.sroce, dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　查询结果：

　　3、sum()over（）的使用

　　根据班级进行分数求和

select t.name, t.class, t.sroce, sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　4、first_value() over()和last_value() over()的使用

select t.name,t.class, t.sroce, first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

select t.name, t.class, t.sroce, last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　分别求出第一个和最后一个成绩。