partition by是Oracle中的一個分析函數(shù)。它的功能有點兒像分組函數(shù)group by，但又有較大區(qū)別。本文通過示例的方式，介紹一下partition by的用法。

數(shù)據(jù)準備

建表

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

插入數(shù)據(jù)

partition by 的用法與含義

首先我們看一下group by的用法，比如根據(jù)省份分組。

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

使用了group by后，select語句中只能是分組的字段（比如上面的province）或者是一個聚合函數(shù)（比如count()、sum()、max()等等）。

partition從字面上看是分區(qū)、分塊的意思，所以partition by其實就是根據(jù)某個字段將數(shù)據(jù)分塊，然后可以對該分塊數(shù)據(jù)再做查詢（包括聚合查詢）。

例如，partition by常同row_number() over一起使用：

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

這個sql的作用就是根據(jù)province分組，并且分組后的每組的數(shù)據(jù)按照persons正序排序。

我們看到通過partition by分組后，select中是可以查出非分組的字段，這和group by是不一樣的。

如果我們要查找每個省份人數(shù)最少的城市，可以用如下sql：

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

其中，row_number()是對分組后的數(shù)據(jù)進行順序連續(xù)排序。

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

rank()、dense_rank()和row_number()排序的區(qū)別：

1. row_number()順序排序，依次從第一個至最后一個。

2. rank()跳躍排序，如果有并列第一個會直接跳到第三個。

3. dense_rank()連續(xù)排序，如果有兩個第一級別時仍然從第二級別開始。

具體看如下的例子，使用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

刪除表中重復(fù)的記錄

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

 

轉(zhuǎn)自https://www.cnblogs.com/xfeiyun/p/17765225.html