526互联

partition by语法

发布时间 2023-10-15 09:00:39作者: 百里浅暮

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
);