sql練習(xí)第二周（oracle）
Oracle從8.1.6開(kāi)始提供分析函數(shù)，分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是：對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
1、over函數(shù)的寫(xiě)法：
over（partition?by class?order?by?sroce）?按照sroce排序進(jìn)行累計(jì)，order?by是個(gè)默認(rèn)的開(kāi)窗函數(shù)，按照class分區(qū)。
2、開(kāi)窗的窗口范圍：
over（order?by?sroce range?between?5?preceding?and?5?following）：窗口范圍為當(dāng)前行數(shù)據(jù)幅度減5加5后的范圍內(nèi)的。
over（order?by?sroce rows?between?5?preceding?and?5?following）：窗口范圍為當(dāng)前行前后各移動(dòng)5行。
分析函數(shù)的語(yǔ)法結(jié)構(gòu)一般是：分析函數(shù)名(參數(shù))?OVER?(PARTITION?BY子句?ORDER?BY子句?ROWS/RANGE子句)。
即由以下三部分組成：
分析函數(shù)名：如sum、max、min、count、avg等聚集函數(shù)以及l(fā)ead、lag行比較函數(shù)等；
over： 關(guān)鍵字，表示前面的函數(shù)是分析函數(shù)，不是普通的集合函數(shù)；
分析子句：over關(guān)鍵字后面掛號(hào)內(nèi)的內(nèi)容；
分析子句又由下面三部分組成：
partition by ：分組子句，表示分析函數(shù)的計(jì)算范圍，不同的組互不相干；
ORDER?BY：?排序子句，表示分組后，組內(nèi)的排序方式；
ROWS/RANGE：窗口子句，是在分組(PARTITION?BY)后，組內(nèi)的子分組（也稱(chēng)窗口），此時(shí)分析函數(shù)的計(jì)算范圍窗口，而不是PARTITON。窗口有兩種，ROWS和RANGE；
例如如下語(yǔ)句：
（unbounded :無(wú)限的,current:當(dāng)前的）
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
ID|DEFAULT_SUM|RANGE_UNBOUND_SUM|ROWS_UNBOUND_SUM|RANGE_SUM|ROWS_SUM
------|------|------|------|------|------
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 子句同時(shí)使用，且如果指定了order by 子句未指定窗口子句，則默認(rèn)為RANGE?BETWEEN?unbounded preceding?AND?CURRENT?ROW，如上例結(jié)果集中的defult_sum等于range_unbound_sum；
2、如果分析函數(shù)沒(méi)有指定ORDER?BY子句，也就不存在ROWS/RANGE窗口的計(jì)算；
3、range是邏輯窗口，是指定當(dāng)前行對(duì)應(yīng)值的范圍取值，列數(shù)不固定，只要行值在范圍內(nèi)，對(duì)應(yīng)列都包含在內(nèi)，如上例中range_sum（即range 1 preceing and 2 following）例的分析結(jié)果：
當(dāng)id=1時(shí)，是sum為1-1<=id<=1+2 的和，即sum=1+1+3=5(取id為1,1,3);
當(dāng)id=3時(shí)，是sum為3-1<=id<=3+2 的和，即sum=3（取id為3）；
當(dāng)id=6時(shí)，是sum為6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id為6，6，6，7，8）;
以此類(lèi)推下去，結(jié)果如上例中所示。
4、rows是物理窗口，即根據(jù)order by 子句排序后，取的前N行及后N行的數(shù)據(jù)計(jì)算（與當(dāng)前行的值無(wú)關(guān)，只與排序后的行號(hào)相關(guān)），如上例中rows_sum例結(jié)果，是取前1行和后2行數(shù)據(jù)的求和，分析上例rows_sum的結(jié)果：
當(dāng)id=1（第一個(gè)1時(shí)）時(shí)，前一行沒(méi)數(shù)，后二行分別是1和3，sum=1+1+3=5；
當(dāng)id=3時(shí)，前一行id=1,后二行id都為6，則sum=1+3+6+6=16；
以此類(lèi)推下去，結(jié)果如上例所示。
注：行比較分析函數(shù)lead和lag無(wú)window(窗口)子句。
Oracle中row_number()、rank()、dense_rank() 的區(qū)別
表數(shù)據(jù)
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）查詢(xún)每個(gè)班的第一名的成績(jī)：如下
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;
結(jié)果為：
注意：在求第一名成績(jī)的時(shí)候，不能用row_number()，因?yàn)槿绻嘤袃蓚€(gè)并列第一，row_number()只返回一個(gè)結(jié)果。
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;
結(jié)果為：
可以看出，本來(lái)第一名是兩個(gè)人的并列，結(jié)果只顯示了一個(gè)。
（2）rank()和dense_rank()可以將所有的都查找出來(lái)，rank可以將并列第一名的都查找出來(lái)；rank()和dense_rank()區(qū)別：rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名。
求班級(jí)成績(jī)排名：
select????t.name,????t.class,????t.sroce,????rank()????over(partition????by????t.class????order????by????t.sroce????desc) mm????from????T2_TEMP t;
查詢(xún)結(jié)果：
dense_rank()l是連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名
select????t.name????,t.class,????t.sroce,????dense_rank()????over(partition????by????t.class????order????by????t.sroce????desc) mm????from????T2_TEMP t;
查詢(xún)結(jié)果：
3、sum()over（）的使用
根據(jù)班級(jí)進(jìn)行分?jǐn)?shù)求和
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;
分別求出第一個(gè)和最后一個(gè)成績(jī)。
