ITPub博客

首页 > 数据库 > Oracle > oracle下数据的排序分组row_number() over()--分析函数,可用于去重

oracle下数据的排序分组row_number() over()--分析函数,可用于去重

Oracle 作者:perfychi 时间:2014-12-28 23:41:45 0 删除 编辑

From:http://heshw.blog.51cto.com/5891747/1275140/
oracle下数据的排序分组row_number() over()--分析函数,可用于去重

   row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。


   与此功能相似的函数还有:rank/dense_rank/ntile

row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序)。 
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) 
dense_rank()也是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

oracle 分析函数 row_number(),返回一个整数值(>=1)


测试表/数据

CREATE TABLE test_course (

 student_name  VARCHAR(10),  -- 学生

 course_name   VARCHAR(10),  -- 课程

 grade         INT           -- 成绩

);



INSERT INTO test_course VALUES('甲', '语文', 95);

INSERT INTO test_course VALUES('乙', '语文', 85);

INSERT INTO test_course VALUES('丙', '语文', 75);

INSERT INTO test_course VALUES('丁', '语文', 65);

INSERT INTO test_course VALUES('戊', '语文', 55);

INSERT INTO test_course VALUES('己', '语文', 50);

INSERT INTO test_course VALUES('庚', '语文', 60);

INSERT INTO test_course VALUES('辛', '语文', 70);

INSERT INTO test_course VALUES('壬', '语文', 80);

INSERT INTO test_course VALUES('奎', '语文', 90);


INSERT INTO test_course VALUES('甲', '数学', 90);

INSERT INTO test_course VALUES('乙', '数学', 80);

INSERT INTO test_course VALUES('丙', '数学', 70);

INSERT INTO test_course VALUES('丁', '数学', 60);

INSERT INTO test_course VALUES('戊', '数学', 50);

INSERT INTO test_course VALUES('己', '数学', 50);

INSERT INTO test_course VALUES('庚', '数学', 60);

INSERT INTO test_course VALUES('辛', '数学', 70);

INSERT INTO test_course VALUES('壬', '数学', 85);

INSERT INTO test_course VALUES('奎', '数学', 95);




ROW_NUMBER 顺序编号


按照分数 编号 从高到底

SELECT

 ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO,

 student_name,

 SUM(grade) AS AllGrade

FROM

 test_course

GROUP BY

 student_name

ORDER BY

 SUM(grade) DESC


1 甲 185

2 奎 185

3 壬 165

4 乙 165

5 丙 145

6 辛 140

7 丁 125

8 庚 120

9 戊 105

10 己 100



按照分数 编号 从高到底(区分 课程)


SELECT

 ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,

 *

FROM

 test_course

ORDER BY

 course_name,

 grade DESC


1 奎 数学 95

2 甲 数学 90

3 壬 数学 85

4 乙 数学 80

5 丙 数学 70

6 辛 数学 70

7 庚 数学 60

8 丁 数学 60

9 戊 数学 50

10 己 数学 50

1 甲 语文 95

2 奎 语文 90

3 乙 语文 85

4 壬 语文 80

5 丙 语文 75

6 辛 语文 70

7 丁 语文 65

8 庚 语文 60

9 戊 语文 55

10 己 语文 50




RANK 排名不连续


按照分数 排名 从高到底

SELECT

 RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,

 student_name,

 SUM(grade) AS AllGrade

FROM

 test_course

GROUP BY

 student_name

ORDER BY

 SUM(grade) DESC


1 甲 185

1 奎 185

3 壬 165

3 乙 165

5 丙 145

6 辛 140

7 丁 125

8 庚 120

9 戊 105

10 己 100


按照分数 排名 从高到底(区分 课程)


SELECT

 RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,

 *

FROM

 test_course

ORDER BY

 course_name,

 grade DESC


1 奎 数学 95

2 甲 数学 90

3 壬 数学 85

4 乙 数学 80

5 丙 数学 70

5 辛 数学 70

7 庚 数学 60

7 丁 数学 60

9 戊 数学 50

9 己 数学 50

1 甲 语文 95

2 奎 语文 90

3 乙 语文 85

4 壬 语文 80

5 丙 语文 75

6 辛 语文 70

7 丁 语文 65

8 庚 语文 60

9 戊 语文 55

10 己 语文 50




DENSE_RANK 排名连续


按照分数 排名 从高到底


SELECT

 DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,

 student_name,

 SUM(grade) AS AllGrade

FROM

 test_course

GROUP BY

 student_name

ORDER BY

 SUM(grade) DESC


1 甲 185

1 奎 185

2 壬 165

2 乙 165

3 丙 145

4 辛 140

5 丁 125

6 庚 120

7 戊 105

8 己 100


按照分数 排名 从高到底(区分 课程)

SELECT

 DENSE_RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO, *

FROM

 test_course

ORDER BY

 course_name,

 grade DESC


1 奎 数学 95

2 甲 数学 90

3 壬 数学 85

4 乙 数学 80

5 丙 数学 70

5 辛 数学 70

6 庚 数学 60

6 丁 数学 60

7 戊 数学 50

7 己 数学 50

1 甲 语文 95

2 奎 语文 90

3 乙 语文 85

4 壬 语文 80

5 丙 语文 75

6 辛 语文 70

7 丁 语文 65

8 庚 语文 60

9 戊 语文 55

10 己 语文 50




NTILE 分组


按照分数 划分5个区间 从高到底

SELECT

 NTILE(5) OVER( ORDER BY SUM(grade) DESC) AS NO,

 student_name,

 SUM(grade) AS AllGrade

FROM

 test_course

GROUP BY

 student_name

ORDER BY

 SUM(grade) DESC


1 甲 185

1 奎 185

2 壬 165

2 乙 165

3 丙 145

3 辛 140

4 丁 125

4 庚 120

5 戊 105

5 己 100


按照分数 划分区间 从高到底(区分 课程)

SELECT

 NTILE(5) OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,

 *

FROM

 test_course

ORDER BY

 course_name,

 grade DESC


1 奎 数学 95

1 甲 数学 90

2 壬 数学 85

2 乙 数学 80

3 丙 数学 70

3 辛 数学 70

4 庚 数学 60

4 丁 数学 60

5 戊 数学 50

5 己 数学 50

1 甲 语文 95

1 奎 语文 90

2 乙 语文 85

2 壬 语文 80

3 丙 语文 75

3 辛 语文 70

4 丁 语文 65

4 庚 语文 60

5 戊 语文 55

5 己 语文 50


   使用分析函数来进行去重的例子:

product loid time
aaa AAABBBCCC 2013-4-5
aaa CCC55SSADD 2013-4-13
bbb FFFF223SSSA 2013-8-8
bbb GSAAASSFBB 2013-8-1


   数据结构如上图,其中第1列有重复,第2列唯一,第3列为时间,有大小唯一。需求:一个product只能有一个loid,现需要根据时间大小,保留最后一次增加的数据,将时间小的product删除。最后得到结果如下:

product loid time
aaa CCC55SSADD 2013-4-13
bbb FFFF223SSSA 2013-8-8

   思路:根据product来进行分组编号,通过时间大小来排序。筛选出时间最大的loid,再根据loid将product重复的删除)

select row_number() over(partition by product order by time desc) as no,product,loid,time from test;    --通过分析函数进行分组编号

select loid from (select row_number() over(partition by product order by time desc) as no,product,loid,time from test) where no=1;  ---得出满足条件的loid,因为为降序,所以不管product重复的有几个,编号为1的始终为分组内时间最大的

delete from test where loid not in (select loid from (select row_number() over(partition by product order by time desc) as no,product,loid,time from test) where no=1);    --根据得出的满足条件的loid来进行筛选删除。


   此为通过分析函数来去重的例子,其他重复数据的查找与删除,可通过rowid或分组来进行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-1383187/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-06

  • 博文量
    486
  • 访问量
    2689303