ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法
今天在使用多字段去重時(shí)，由于某些字段有多種可能性，只需根據部分字段進(jìn)行去重，在網(wǎng)上看到了rownumber() over(partition by col1 order by col2)去重的方法，很不錯，在此記錄分享下：
row_number() OVER ( PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續的唯一的). 與rownum的區別在于：使用rownum進(jìn)行排序的時(shí)候是先對結果集加入偽列rownum然后再進(jìn)行排序，而此函數在包含排序從句后是先排序再計算行號碼．- row_number()和rownum差不多，功能更強一點(diǎn)（可以在各個(gè)分組內從1開(kāi)時(shí)排序）．
- rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名（同樣是在各個(gè)分組內）．
- dense_rank()l是連續排序，有兩個(gè)第二名時(shí)仍然跟著(zhù)第三名。相比之下row_number是沒(méi)有重復值的.
- lag（arg1,arg2,arg3):
- arg1是從其他行返回的表達式
- arg2是希望檢索的當前行分區的偏移量。是一個(gè)正的偏移量，是一個(gè)往回檢索以前的行的數目。
- arg3是在arg2表示的數目超出了分組的范圍時(shí)返回的值。
函數語(yǔ)法：
OPAP函數語(yǔ)法四部分：
1.function 本身用于對窗口中的數據進(jìn)行操作；
2.partitioning clause 用于將結果集分區；
3.order by clause 用于對分區中的數據進(jìn)行排序；
4.windowing clause 用于定義function在其上操作的行的集合，即function所影響的范圍；
RANK()dense_rank()【語(yǔ)法】RANK ( ) OVER ( [query_partition_clause] order_by_clause ) dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )【功能】聚合函數RANK 和 dense_rank 主要的功能是計算一組數值中的排序值?！緟怠縟ense_rank與rank()用法相當，【區別】dence_rank在并列關(guān)系是，相關(guān)等級不會(huì )跳過(guò)。rank則跳過(guò)rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名（同樣是在各個(gè)分組內） dense_rank()l是連續排序，有兩個(gè)第二名時(shí)仍然跟著(zhù)第三名?！菊f(shuō)明】Oracle分析函數
ROW_NUMBER()【語(yǔ)法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 【功能】表示根據COL1分組，在分組內部根據 COL2排序，而這個(gè)值就表示每組內部排序后的順序編號（組內連續的唯一的） row_number() 返回的主要是“行”的信息，并沒(méi)有排名【參數】【說(shuō)明】Oracle分析函數主要功能：用于取前幾名，或者最后幾名等
sum(...) over ...【功能】連續求和分析函數【參數】具體參示例【說(shuō)明】Oracle分析函數
lag()和lead()【語(yǔ)法】lag(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )LEAD(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )【功能】表示根據COL1分組，在分組內部根據 COL2排序，而這個(gè)值就表示每組內部排序后的順序編號（組內連續的唯一的） lead （） 下一個(gè)值 lag（） 上一個(gè)值【參數】EXPR是從其他行返回的表達式 OFFSET是缺省為1 的正數，表示相對行數。希望檢索的當前行分區的偏移量DEFAULT是在OFFSET表示的數目超出了分組的范圍時(shí)返回的值?！菊f(shuō)明】Oracle分析函數
---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
DROP TABLE TEST_Y
CREATE TABLE TEST_Y(
ID VARCHAR2 (32) PRIMARY KEY ,
NAME VARCHAR2 (20),
AGE NUMBER(3 ),
DETAILS VARCHAR2 (1000)
);
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',20 ,'面朝大海，春暖花開(kāi)');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',30 ,'面朝大海，春暖花開(kāi)');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '貝多芬',43 ,'致愛(ài)麗絲');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',34 ,'假如生活欺騙了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '楊過(guò)',23 ,'黯然銷(xiāo)魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '小龍女',32 ,'神雕俠侶');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '李清照',21 ,'尋尋覓覓、冷冷清清');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '周芷若',18 ,'峨眉');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '趙敏',18 ,'自由');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '張無(wú)忌',20 ,'倚天屠龍記');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '張無(wú)忌',30 ,'倚天屠龍記');
SELECT * FROM TEST_Y;
----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
---查詢(xún)所有姓名，如果同名，則按年齡降序
SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;
----通過(guò)上面的語(yǔ)句可知，ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分組，按AGE字段排序的。
----如果只需查詢(xún)出不重復的姓名即可，則可使用如下的語(yǔ)句
SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;
----由查詢(xún)結果可知，姓名相同年齡小的數據被過(guò)濾掉了；可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)對部分子彈進(jìn)行去重處理
----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----跳躍排序
SELECT NAME ,AGE,DETAILS , RANK() OVER (PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;
----由查詢(xún)結果可知，相同的并列，下一個(gè)則跳躍到并列所替的序列后：如有兩個(gè)并列1，那么下一個(gè)則直接排為3，跳過(guò)2；
----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----連續排序，當有多個(gè)并列時(shí)，下一個(gè)仍然連續有序
----由查詢(xún)結果可知，當兩個(gè)并列為1時(shí)，下一個(gè)仍連續有序為2，不跳躍到3
Lag和Lead函數可以在一次查詢(xún)中取出同一字段的前N行的數據和后N行的值。這種操作可以使用對相同表的表連接來(lái)實(shí)現，不過(guò)使用LAG和LEAD有更高的效率.
Lag和Lead偏移量函數，其用途是：可以查出同一字段下一個(gè)值或上一個(gè)值，并作為新列存在表中.
-----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回顯示的字段；offset是exp_str字段的偏移量，默認是1，如offset=1表示返回當前exp_str的下一個(gè)exp_str；defval當該函數無(wú)值可用的情況下返回該值。
(1) SELECT NAME ,AGE,DETAILS, LAG(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y;
(2) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y;
(3) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;
----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回顯示的字段；offset是exp_str字段的偏移量，默認是1，如offset=1表示返回當前exp_str的上一個(gè)exp_str；
-----defval當該函數無(wú)值可用的情況下返回該值。
(1)SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y;
(2) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y;
(3) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;
-----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3])
(1) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;
(2) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PARTITION BY NAME) FROM TEST_Y;
(3)SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( ORDER BY AGE DESC NULLS LAST ) FROM TEST_Y;
(4) SELECT NAME ,AGE,DETAILS, SUM(AGE) OVER () FROM TEST_Y;
