oracle 排序的分析函数,oracle下数据的排序分组row_number() over()--分析函数，可用于去重...
row_number()over(partition by col1 order by col2)表示根據(jù)col1分組，在分組內(nèi)部根據(jù)col2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)(組內(nèi)連續(xù)的唯一的)。
與rownum的區(qū)別在于：使用rownum進(jìn)行排序的時(shí)候是先對(duì)結(jié)果集加入偽劣rownum然后再進(jìn)行排序，而此函數(shù)在包含排序從句后是先排序再計(jì)算行號(hào)碼。
與此功能相似的函數(shù)還有：rank/dense_rank/ntile
row_number()和rownum差不多，功能更強(qiáng)一點(diǎn)(可以在各個(gè)分組內(nèi)從1開(kāi)始排序)。
rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來(lái)就是第四名(同樣是在各個(gè)分組內(nèi))
dense_rank()也是連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名。相比之下row_number是沒(méi)有重復(fù)值的
oracle 分析函數(shù) row_number(),返回一個(gè)整數(shù)值(>=1)
測(cè)試表/數(shù)據(jù)
CREATE TABLE test_course (
student_name ?VARCHAR(10), ?-- 學(xué)生
course_name ? VARCHAR(10), ?-- 課程
grade ? ? ? ? INT ? ? ? ? ? -- 成績(jī)
);
INSERT INTO test_course VALUES('甲', '語(yǔ)文', 95);
INSERT INTO test_course VALUES('乙', '語(yǔ)文', 85);
INSERT INTO test_course VALUES('丙', '語(yǔ)文', 75);
INSERT INTO test_course VALUES('丁', '語(yǔ)文', 65);
INSERT INTO test_course VALUES('戊', '語(yǔ)文', 55);
INSERT INTO test_course VALUES('己', '語(yǔ)文', 50);
INSERT INTO test_course VALUES('庚', '語(yǔ)文', 60);
INSERT INTO test_course VALUES('辛', '語(yǔ)文', 70);
INSERT INTO test_course VALUES('壬', '語(yǔ)文', 80);
INSERT INTO test_course VALUES('奎', '語(yǔ)文', 90);
INSERT INTO test_course VALUES('甲', '數(shù)學(xué)', 90);
INSERT INTO test_course VALUES('乙', '數(shù)學(xué)', 80);
INSERT INTO test_course VALUES('丙', '數(shù)學(xué)', 70);
INSERT INTO test_course VALUES('丁', '數(shù)學(xué)', 60);
INSERT INTO test_course VALUES('戊', '數(shù)學(xué)', 50);
INSERT INTO test_course VALUES('己', '數(shù)學(xué)', 50);
INSERT INTO test_course VALUES('庚', '數(shù)學(xué)', 60);
INSERT INTO test_course VALUES('辛', '數(shù)學(xué)', 70);
INSERT INTO test_course VALUES('壬', '數(shù)學(xué)', 85);
INSERT INTO test_course VALUES('奎', '數(shù)學(xué)', 95);
ROW_NUMBER 順序編號(hào)
按照分?jǐn)?shù) 編號(hào) 從高到底
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
按照分?jǐn)?shù) 編號(hào) 從高到底(區(qū)分 課程)
SELECT
ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
*
FROM
test_course
ORDER BY
course_name,
grade DESC
1 奎 數(shù)學(xué) 95
2 甲 數(shù)學(xué) 90
3 壬 數(shù)學(xué) 85
4 乙 數(shù)學(xué) 80
5 丙 數(shù)學(xué) 70
6 辛 數(shù)學(xué) 70
7 庚 數(shù)學(xué) 60
8 丁 數(shù)學(xué) 60
9 戊 數(shù)學(xué) 50
10 己 數(shù)學(xué) 50
1 甲 語(yǔ)文 95
2 奎 語(yǔ)文 90
3 乙 語(yǔ)文 85
4 壬 語(yǔ)文 80
5 丙 語(yǔ)文 75
6 辛 語(yǔ)文 70
7 丁 語(yǔ)文 65
8 庚 語(yǔ)文 60
9 戊 語(yǔ)文 55
10 己 語(yǔ)文 50
RANK 排名不連續(xù)
按照分?jǐn)?shù) 排名 從高到底
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
按照分?jǐn)?shù) 排名 從高到底(區(qū)分 課程)
SELECT
RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
*
FROM
test_course
ORDER BY
course_name,
grade DESC
1 奎 數(shù)學(xué) 95
2 甲 數(shù)學(xué) 90
3 壬 數(shù)學(xué) 85
4 乙 數(shù)學(xué) 80
5 丙 數(shù)學(xué) 70
5 辛 數(shù)學(xué) 70
7 庚 數(shù)學(xué) 60
7 丁 數(shù)學(xué) 60
9 戊 數(shù)學(xué) 50
9 己 數(shù)學(xué) 50
1 甲 語(yǔ)文 95
2 奎 語(yǔ)文 90
3 乙 語(yǔ)文 85
4 壬 語(yǔ)文 80
5 丙 語(yǔ)文 75
6 辛 語(yǔ)文 70
7 丁 語(yǔ)文 65
8 庚 語(yǔ)文 60
9 戊 語(yǔ)文 55
10 己 語(yǔ)文 50
DENSE_RANK 排名連續(xù)
按照分?jǐn)?shù) 排名 從高到底
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
按照分?jǐn)?shù) 排名 從高到底(區(qū)分 課程)
SELECT
DENSE_RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,*
FROM
test_course
ORDER BY
course_name,
grade DESC
1 奎 數(shù)學(xué) 95
2 甲 數(shù)學(xué) 90
3 壬 數(shù)學(xué) 85
4 乙 數(shù)學(xué) 80
5 丙 數(shù)學(xué) 70
5 辛 數(shù)學(xué) 70
6 庚 數(shù)學(xué) 60
6 丁 數(shù)學(xué) 60
7 戊 數(shù)學(xué) 50
7 己 數(shù)學(xué) 50
1 甲 語(yǔ)文 95
2 奎 語(yǔ)文 90
3 乙 語(yǔ)文 85
4 壬 語(yǔ)文 80
5 丙 語(yǔ)文 75
6 辛 語(yǔ)文 70
7 丁 語(yǔ)文 65
8 庚 語(yǔ)文 60
9 戊 語(yǔ)文 55
10 己 語(yǔ)文 50
NTILE 分組
按照分?jǐn)?shù) 劃分5個(gè)區(qū)間 從高到底
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
按照分?jǐn)?shù) 劃分區(qū)間 從高到底(區(qū)分 課程)
SELECT
NTILE(5) OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
*
FROM
test_course
ORDER BY
course_name,
grade DESC
1 奎 數(shù)學(xué) 95
1 甲 數(shù)學(xué) 90
2 壬 數(shù)學(xué) 85
2 乙 數(shù)學(xué) 80
3 丙 數(shù)學(xué) 70
3 辛 數(shù)學(xué) 70
4 庚 數(shù)學(xué) 60
4 丁 數(shù)學(xué) 60
5 戊 數(shù)學(xué) 50
5 己 數(shù)學(xué) 50
1 甲 語(yǔ)文 95
1 奎 語(yǔ)文 90
2 乙 語(yǔ)文 85
2 壬 語(yǔ)文 80
3 丙 語(yǔ)文 75
3 辛 語(yǔ)文 70
4 丁 語(yǔ)文 65
4 庚 語(yǔ)文 60
5 戊 語(yǔ)文 55
5 己 語(yǔ)文 50
使用分析函數(shù)來(lái)進(jìn)行去重的例子：
product
loid
time
aaa
AAABBBCCC
2013-4-5
aaa
CCC55SSADD
2013-4-13
bbb
FFFF223SSSA
2013-8-8
bbb
GSAAASSFBB
2013-8-1
數(shù)據(jù)結(jié)構(gòu)如上圖，其中第1列有重復(fù)，第2列唯一，第3列為時(shí)間，有大小唯一。需求:一個(gè)product只能有一個(gè)loid，現(xiàn)需要根據(jù)時(shí)間大小，保留最后一次增加的數(shù)據(jù)，將時(shí)間小的product刪除。最后得到結(jié)果如下：
product
loid
time
aaa
CCC55SSADD
2013-4-13
bbb
FFFF223SSSA
2013-8-8
思路：根據(jù)product來(lái)進(jìn)行分組編號(hào)，通過(guò)時(shí)間大小來(lái)排序。篩選出時(shí)間最大的loid，再根據(jù)loid將product重復(fù)的刪除)
select row_number() over(partition by product order by time desc) as no,product,loid,time from test; ? ?--通過(guò)分析函數(shù)進(jìn)行分組編號(hào)
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，因?yàn)闉榻敌?#xff0c;所以不管product重復(fù)的有幾個(gè)，編號(hào)為1的始終為分組內(nèi)時(shí)間最大的
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); ? ?--根據(jù)得出的滿足條件的loid來(lái)進(jìn)行篩選刪除。
此為通過(guò)分析函數(shù)來(lái)去重的例子，其他重復(fù)數(shù)據(jù)的查找與刪除，可通過(guò)rowid或分組來(lái)進(jìn)行。
總結(jié)
以上是生活随笔為你收集整理的oracle 排序的分析函数,oracle下数据的排序分组row_number() over()--分析函数，可用于去重...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： python以追加方式打开文件 线程安全
- 下一篇： oracle开发项目流程,如何开发ORA