partition by语法

partition by是Oracle中的一个分析函数。它的功能有点儿像分组函数group by,但又有较大区别。本文通过示例的方式,介绍一下partition by的用法。

数据准备

建表

create table xzq_person (       --行政区人口表
    province varchar(10),
    city varchar(10),
    persons number(10)
);

插入数据

insert into xzq_person values ('江苏','苏州',10);
insert into xzq_person values ('江苏','南京',20);
insert into xzq_person values ('浙江','杭州',19);
insert into xzq_person values ('浙江','义乌',13);
insert into xzq_person values ('浙江','金华',11);
insert into xzq_person values ('广东','广州',18);
insert into xzq_person values ('广东','深圳',18);
insert into xzq_person values ('广东','中山',16);
insert into xzq_person values ('广东','惠州',16);
insert into xzq_person values ('广东','东莞',15);

partition by 的用法与含义

首先我们看一下group by的用法,比如根据省份分组。

select province, sum(persons) from xzq_person group by province;

使用了group by后,select语句中只能是分组的字段(比如上面的province)或者是一个聚合函数(比如count()、sum()、max()等等)。

partition从字面上看是分区、分块的意思,所以partition by其实就是根据某个字段将数据分块,然后可以对该分块数据再做查询(包括聚合查询)。

例如,partition by常同row_number() over一起使用:

select province, city, persons, row_number() over(partition by province order by persons) from xzq_person;

这个sql的作用就是根据province分组,并且分组后的每组的数据按照persons正序排序。

我们看到通过partition by分组后,select中是可以查出非分组的字段,这和group by是不一样的。

如果我们要查找每个省份人数最少的城市,可以用如下sql:

select * from (
    select province, city, persons, row_number() over(partition by province order by persons) forder from xzq_person
) temp
where temp.forder=1;

其中,row_number()是对分组后的数据进行顺序连续排序。

除了row_number() over,partition by还可以跟rank() over、dense_rank()一起使用。

rank()、dense_rank()和row_number()排序的区别:

  1. row_number()顺序排序,依次从第一个至最后一个。
  2. rank()跳跃排序,如果有并列第一个会直接跳到第三个。
  3. dense_rank()连续排序,如果有两个第一级别时仍然从第二级别开始。

具体看如下的例子,使用partition by和rank() over一起使用。

select province, city, persons, rank() over(partition by province order by persons) from xzq_person;

使用partition by和dense_rank() over一起使用。

select province, city, persons, dense_rank() over(partition by province order by persons) from xzq_person;

另外,也可以做分组+排序后,求累计值的功能等。

select province, city, persons, sum(persons) over(partition by province order by persons) forder from xzq_person;

用途举例

示例1

删除表中重复的记录

DELETE FROM T_PROD_CODE WHERE ID IN (
SELECT ID FROM (
    SELECT CODE,ID,ROW_NUMBER() over(PARTITION BY CODE ORDER BY ID) AS RN FROM T_PROD_CODE
    ) T WHERE RN>1
);

 

posted @ 2023-10-15 08:54  残城碎梦  阅读(6745)  评论(1编辑  收藏  举报