转载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