Oracle语法：partition by

发表于 | 分类于 ， |
| 字数统计: 1,660

partition by是Oracle中的一个开窗函数（也称为分析函数、窗口函数）。它的功能有点儿像分组函数group by，但又有较大区别。本文通过示例的方式，介绍一下partition by的用法。

开窗函数的解释

对于from目标表中的数据集根据partition by后的关键字进行划分窗口，这也就是窗口的来源。如若over语句内的partition by关键字省略，则整个数据集看作一个窗口。
各个窗口内的数据相互独立，互不干涉。
而在over前面函数是在窗口划分之后针对窗口内的数据进行计算。
rows control 子句中是用来控制窗口的大小。

窗口划分图示

窗口内计算图示

窗口大小控制

开窗函数语法格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14

select 函数(表字段) over([partition by colname] [order by colname] [ROWS Control])
from tableName
 
ROWS Control 是用来控制窗口的
常用的组合如下:
    | --ROWS BETWEEN  x PRECEDING AND y FOLLOWING 
    当前行和前面x行以及后面y行构成窗口进行计算
 
    | --ROWS BETWEEN  x PRECEDING AND CURRENT ROW
    当前行和前面x行构成窗口进行计算
 
    | --ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW
    当前行和前面所有行进行计算，不会超过自己的窗口大小。（默认此种方式）

开窗函数的类型

开窗函数分为一下四类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
--'排序开窗'
 
        row_number() over()     --123，依次从第一个至最后一个
 
        rank() over()           --113，如果有并列第一个会直接跳到第三个
 
        dense_rank() over()     --112，如果有两个第一级别时仍然从第二级别开始
 
--'聚合开窗'
 
        sum() over()
 
        avg() over()
 
        max() over()
 
        min() over()
 
        count() over()
 
--'偏移开窗'
 
        lead() over()
 
        lag() over()
 
--'切片开窗'
 
        ntile() over()

排序开窗示例

数据准备

  1. 建表。

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

  2. 插入数据。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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的用法，比如根据省份分组。

1
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一起使用：

1
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

1
2
3
4
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一起使用。

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

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

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

聚合开窗示例

可以做分组+排序后，求累计值的功能等，注意这个累计值，只的是当前行所在的窗口内，当前行与前面所有行进行的累计，当前行后面的行不会计入。
例如下图中的浙江-义乌一行，sum函数的结果24=11+13。

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

偏移开窗示例

本小节的偏移开窗示例也即是LAG函数和LEAD函数的使用。
LAG函数的语法如下：

1
LAG(column_name, offset, default_value)

参数解释：

  • column_name：要获取值的列名。
  • offset：可选参数，表示在窗口内，取当前行向上偏移的行数，默认为1。例如，offset为1表示获取上一行的值，offset为2表示获取第上两行的值，以此类推。
  • default_value：可选参数，用于指定当没有前一行时的默认值，默认为NULL。

LEAD函数的语法如下：

1
LEAD(column_name, offset, default_value)

参数解释：

  • column_name：要获取值的列名。
  • offset：可选参数，表示在窗口内，取当前行向下偏移的行数，默认为1。例如，offset为1表示获取下一行的值，offset为2表示获取第下两行的值，以此类推。
  • default_value：可选参数，用于指定当没有后一行时的默认值，默认为NULL。

以下是一个示例，假设我们有一个学生成绩表scores，其中包含学生的成绩和考试日期：

student_id exam_date score
101 2023-01-01 85
101 2023-01-05 78
101 2023-01-10 92
101 2023-01-15 80

现在我们想要查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩，示例 SQL 如下：

1
2
3
4
5
6
7
8
SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;

执行结果如下：

student_id exam_date score previous_score next_score
101 023-01-01 85 NULL 78
101 2023-01-05 78 85 92
101 2023-01-10 92 78 80
101 2023-01-15 80 92 NULL

在上面的示例中，我们使用 Lag 函数获取每个学生的上一次考试成绩（previous_score），使用 Lead 函数获取每个学生的下一次考试成绩（next_score）。如果没有上一次或下一次考试，对应的列将显示为 NULL。

实际应用举例

示例1

删除表中重复的记录

1
2
3
4
5
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
);

------ 本文完 ------