转载http://duqiangatom.blog.163.com/blog/static/11502518201051083013421/
1、rank() over()、dense_rank() over()、row_number() over()
--排名有重复时，跳过一个，如若有2个第二名，则直接到4，无排名为3的
SELECT site_id, SUM(sale_lottery_cnt),rank() over(ORDER BY SUM(sale_lottery_cnt) DESC)
FROM test
WHERE report_date = trunc(SYSDATE - 14)
GROUP BY site_id;
--排名有重复时，如若有2个第二名，下一个还是排名为3，不跳跃，排名连续
SELECT site_id, SUM(sale_lottery_cnt),dense_rank() over(ORDER BY SUM(sale_lottery_cnt) DESC)
FROM test
WHERE report_date = trunc(SYSDATE - 14)
GROUP BY site_id;
--排名不可能有重复，即使相同，也会是不同的名次
SELECT site_id, SUM(sale_lottery_cnt),Row_number() over(ORDER BY SUM(sale_lottery_cnt) DESC)
FROM test WHERE report_date = trunc(SYSDATE - 14)
GROUP BY site_id;
应用1：删除重复记录
CREATE TABLE test1 AS
SELECT tt.object_id, tt.object_name FROM user_objects;
INSERT INTO test1
SELECT tt.object_id, tt.object_name FROM user_objects tt; --n条记录
SELECT COUNT(*) FROM user_objects; --2n条
DELETE FROM test1
WHERE ROWID IN(SELECT row_id FROM (SELECT ROWID AS row_id,
row_number() over(PARTITION BY tt.object_id, tt.object_name ORDER BY ROWID) AS rnn
FROM test1 tt) rn WHERE rn.rnn <>1);--剩下n条记录
更快的一种方法，用关联delete
delete from test1 t where t.rowid> (select min(rowid) from test1 d where t.rowid=d.rowid);
2、lag(.column, offset) +lead(column ,offset) over(partition by c1 order by c2) offset default is 1
ddl test
create table TEST2
(
SITE_ID NUMBER not null,
SALE_LOTTERY_CNT NUMBER
);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (132623, 10);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (633423, 4);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (132523, 2);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (183523, 2);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (183524, 4);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (183524, 19);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (183623, 4);
insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (132623, 19);
SELECT site_id, sale_lottery_cnt,
lag(sale_lottery_cnt, 1) over(PARTITION BY site_id ORDER BY sale_lottery_cnt DESC NULLS FIRST) AS "分组排序后取前一个记录字段值",
lead(sale_lottery_cnt, 1) over(PARTITION BY site_id ORDER BY sale_lottery_cnt DESC NULLS FIRST) AS "分组排序后取后一个记录字段值"
FROM test
WHERE report_date = trunc(SYSDATE - 14);
3、min(c1)/max/avg/sum over(partition by c2) 返回分组中的聚合值，但是行数不变，不像聚合函数行数变少
SELECT site_id, sale_lottery_cnt, MAX(sale_lottery_cnt) over(PARTITION BY site_id),
MIN(sale_lottery_cnt) over(PARTITION BY site_id),
AVG(sale_lottery_cnt) over(PARTITION BY site_id),
SUM(sale_lottery_cnt) over(PARTITION BY site_id)
FROM test2
ORDER BY 1;--行数无变化，前后行数相等
SELECT site_id, MAX(sale_lottery_cnt), SUM(sale_lottery_cnt), AVG(sale_lottery_cnt),
MIN(sale_lottery_cnt)
FROM test2
GROUP BY site_id;--返回聚合完的行数，与分组数相同
4、aggregate function（列） keep (dense_rank first [last] order by c2 desc) over(partition by c1 ) )
SELECT site_id, sale_lottery_cnt,
--先按分组排序，然后取第一个或者最后一个，但是也有可能第一个或者最后一个有多于1条记录，此时，前面的max聚合函数就起作用了
MAX(sale_lottery_cnt) keep(dense_rank LAST ORDER (BY sale_lottery_cnt DESC) over(PARTITION BY site_id),
MIN(sale_lottery_cnt) keep(dense_rank LAST ORDER BY sale_lottery_cnt DESC) over(PARTITION BY site_id)
FROM test2;
5、RATIO_TO_REPORT (column) over()计算 column/sum(column)
SELECT ID,SORT,color,ratio_to_report(NUM) OVER() FROM TEST;
1 book red 0.2
1 book blue 0.133333333333333
1 book green 0.133333333333333
1 book red 0.133333333333333
1 car blue 0.133333333333333
1 car red 0.133333333333333
2 car red 0.133333333333333
