row_number() over(partition by ) 一系列关于over()的用法

建立表，插入数据

create table PERSON
(
  firstname VARCHAR2(10),
  age       INTEGER,
  gender    CHAR(1),
  grade     NUMBER
)

 

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Ted', 23, 'M', 85);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('John', 40, 'M', 86);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('George', 6, 'M', 56);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Mary', 11, 'F', 80);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sam', 17, 'M', 72);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Doris', 6, 'F', 53);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Frank', 38, 'M', 56);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Larry', 5, 'M', 85);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sue', 29, 'F', 68);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sherry', 11, 'F', 60);

insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Marty', 23, 'F', 85);

 

row_number() over(),rank() over(),dense_rank() over的区别：

select row_number() over(order by age)    as num1,firstname,age,gender  from person;--按年龄升序排名，相同年龄位次递加
select rank() over(order by age)          as num1,firstname,age,gender  from person;--按年龄升序排名，找出并列，跳跃排名
select dense_rank() over(order by age)    as num1,firstname,age,gender  from person;--按年龄升序排名，找出并列，依次排名

 

以下为执行结果

 select row_number() over(order by age)    as num1,firstname,age,gender  from person;--按年龄升序排名，相同年龄位次递加
 
      NUM1 FIRSTNAME                                      AGE GENDER
---------- ---------- --------------------------------------- ------
         1 Larry                                            5 M
         2 Doris                                            6 F
         3 George                                           6 M
         4 Mary                                            11 F
         5 Sherry                                          11 F
         6 Sam                                             17 M
         7 Marty                                           23 F
         8 Ted                                             23 M
         9 Sue                                             29 F
        10 Frank                                           38 M
        11 John                                            40 M

 

 

select rank() over(order by age)          as num1,firstname,age,gender  from person;--按年龄升序排名，找出并列，跳跃排名
 
      NUM1 FIRSTNAME                                      AGE GENDER
---------- ---------- --------------------------------------- ------
         1 Larry                                            5 M
         2 Doris                                            6 F
         2 George                                           6 M
         4 Mary                                            11 F
         4 Sherry                                          11 F
         6 Sam                                             17 M
         7 Marty                                           23 F
         7 Ted                                             23 M
         9 Sue                                             29 F
        10 Frank                                           38 M
        11 John                                            40 M

 

 

select dense_rank() over(order by age)    as num1,firstname,age,gender  from person;--按年龄升序排名，找出并列，依次排名
/
 
      NUM1 FIRSTNAME                                      AGE GENDER
---------- ---------- --------------------------------------- ------
         1 Larry                                            5 M
         2 Doris                                            6 F
         2 George                                           6 M
         3 Mary                                            11 F
         3 Sherry                                          11 F
         4 Sam                                             17 M
         5 Marty                                           23 F
         5 Ted                                             23 M
         6 Sue                                             29 F
         7 Frank                                           38 M
         8 John                                            40 M

 

select row_number() over(partition by gender order by age) as part ,firstname,age,gender from person;--先按性别分组，再按年龄排序
 
      PART FIRSTNAME                                      AGE GENDER
---------- ---------- --------------------------------------- ------
         1 Doris                                            6 F
         2 Sherry                                          11 F
         3 Mary                                            11 F
         4 Marty                                           23 F
         5 Sue                                             29 F
         1 Larry                                            5 M
         2 George                                           6 M
         3 Sam                                             17 M
         4 Ted                                             23 M
         5 Frank                                           38 M
         6 John                                            40 M

 

关于sum()和over()的联合使用：

select sum(grade) over(partition by age order by firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄分组，再按名字排序后，按年龄“连续”求分数总和
select sum(grade) over(partition by age)                    as sum1 ,firstname,age,gender from person;-- 按年龄分组后，求分数总和
select sum(grade) over(order by age,firstname)              as sum1 ,firstname,age,gender,grade from person;--先按年龄排序，再按名字排序，“连续”求分数总和

select firstname,age,gender,sum(grade) over(partition by null) as sum1  from person;
=select firstname,age,gender,sum(grade) over() as sum1  from person;--不分组，求所有总和，等同于sum(grade)

 

以下为执行结果：

select sum(grade) over(partition by age order by firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄分组，再按名字排序后，按年龄“连续”求分数总和
 
      SUM1 FIRSTNAME                                      AGE GENDER      GRADE
---------- ---------- --------------------------------------- ------ ----------
        85 Larry                                            5 M              85
        53 Doris                                            6 F              53
       109 George                                           6 M              56
        80 Mary                                            11 F              80
       140 Sherry                                          11 F              60
        72 Sam                                             17 M              72
        85 Marty                                           23 F              85
       170 Ted                                             23 M              85
        68 Sue                                             29 F              68
        56 Frank                                           38 M              56
        86 John                                            40 M              86

 

select sum(grade) over(partition by age)                    as sum1 ,firstname,age,gender from person;-- 按年龄分组后，求分数总和
 
      SUM1 FIRSTNAME                                      AGE GENDER
---------- ---------- --------------------------------------- ------
        85 Larry                                            5 M
       109 Doris                                            6 F
       109 George                                           6 M
       140 Mary                                            11 F
       140 Sherry                                          11 F
        72 Sam                                             17 M
       170 Marty                                           23 F
       170 Ted                                             23 M
        68 Sue                                             29 F
        56 Frank                                           38 M
        86 John                                            40 M

 

select sum(grade) over(order by age,firstname)              as sum1 ,firstname,age,gender,grade from person;--先按年龄排序，再按名字排序，“连续”求分数总和
 
      SUM1 FIRSTNAME                                      AGE GENDER      GRADE
---------- ---------- --------------------------------------- ------ ----------
        85 Larry                                            5 M              85
       138 Doris                                            6 F              53
       194 George                                           6 M              56
       274 Mary                                            11 F              80
       334 Sherry                                          11 F              60
       406 Sam                                             17 M              72
       491 Marty                                           23 F              85
       576 Ted                                             23 M              85
       644 Sue                                             29 F              68
       700 Frank                                           38 M              56
       786 John                                            40 M              86

 

select firstname,age,gender,sum(grade) over(partition by null) as sum1  from person;

=select firstname,age,gender,sum(grade) over() as sum1  from person;--不分组，求所有总和，等同于sum(grade)
 
FIRSTNAME                                      AGE GENDER       SUM1
---------- --------------------------------------- ------ ----------
Ted                                             23 M             786
John                                            40 M             786
George                                           6 M             786
Mary                                            11 F             786
Sam                                             17 M             786
Marty                                           23 F             786
Frank                                           38 M             786
Larry                                            5 M             786
Sue                                             29 F             786
Sherry                                          11 F             786
Doris                                            6 F             786

 

存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示

select *
  from (select row_number() over(order by t.pour_open_date desc) num, t.*
          from dam_cell_info t)
 where num >= 21
   and num <= 30;