oracle-OVER(PARTITION BY... ORDER BY...)的使用

insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',0.1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',0.1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx1',1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx2',1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx1',0.1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx2',0.1,'湖北');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',1,'湖南');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',1,'长沙');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',0.1,'上海');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx',0.1,'长沙');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx1',1,'湖南');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx2',1,'上海');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx1',0.1,'长沙');
insert into DEMO.TEST(ID,NAME,AGE,ADDRESS) VALUES(1,'cx2',0.1,'上海');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

例如这是一组数据，我们对其进行查询：

---

由于数据没有完全能够标识其唯一性的，我们引入伪劣ROWNUM,来标识他们：

select ID,NAME,AGE,ADDRESS,ROWNUM FROM DEMO.TEST;
1

OVER(ORDER BY …)的意思是以按照某个字段排序，所以和直接对表的order by效果是一样的，但是over(order by …)并不能单独使用，我们这里先使用 ROW_NUMBER() OVER (ORDER BY .. ASC)

• row_number() :从1开始，为每一条分组记录返回一个数字;
• OVER (ORDER BY .. ASC): 按所给字段列进行升序排列
  完整含义就是：按所给字段进行升序排列，并给排序好的字段从上往下，从1开始，为每一条数据返回一个递增的数字；

select ID,NAME,AGE,ADDRESS,ROWNUM,row_number() over(ORDER BY age asc) FROM DEMO.TEST;
1

---

对上述结果还是不太满意，我们想得到的是，将name值相同的放在一块展示，并且按照age进行排序,此时我们可以使用：
row_number() over(PARTITION BY .. ORDER BY .. asc)来控制

• PARTITION BY ..:会按照指定的字段，把相同值的排在一起，分为一个块，也就是分组
  完整含义就是：按照指定的字段，把相同值的排在一起，再按所给字段进行升序排列，并给排序好的字段从上往下，从1开始，为每一条数据返回一个递增的数字,需要注意的是，对于每一块，都是从1开始；

select ID,NAME,AGE,ADDRESS,ROWNUM,row_number() over(PARTITION BY name ORDER BY age asc) FROM DEMO.TEST;
1

---

上述排序结果虽然是我们想看到的，但是我们希望如果name项目age也相同的，希望他们的排序值能够一样，这样我们可以采用另外一种方法：ROWNUM,rank() over(PARTITION BY .. ORDER BY .. asc) ;

• ROWNUM,rank(): row_number()是按照分完组之后，组内从1开始的递增排序，而rank()是按照分完组之后，组内从1开始排序的同时会比较字段值是否一样，字段值一样的记录排序值也一样，排序值以该组第一个相同值在当前组内的具体位置确定

select ID,NAME,AGE,ADDRESS,ROWNUM,rank() over(PARTITION BY name ORDER BY age asc) FROM DEMO.TEST;
1

---

可以看出上述确如我们预想的一样，但是如果我们希望，排序值也能保证连续的，那么可以使用：

• dense_rank():dense_rank()是按照分完组之后，组内从1开始排序的同时会比较字段值是否一样，字段值一样的记录排序值也一样，排序值以前一个相同值为准进行排序

select ID,NAME,AGE,ADDRESS,ROWNUM,dense_rank() over(PARTITION BY name ORDER BY age asc) FROM DEMO.TEST;
1

---