likes
comments
collection

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

作者站长头像
站长
· 阅读数 16

🗻 简介:

往期精选文章,精彩不容错过,求赞求关注!

🤣一文带你玩转Kubernetes🤣

Proxy 代理的源码和原理解析

Spring + LDAP + JPA 实现事务管理器

容器服务Istio实践 Istio Sidecar 注入方式

Java EasyPoi之复杂多表头多sheet页excel模板导出实例

🗺️ PG系列文章:

PG系列文章,想学习更多PG芝士,快来吧🦸🏻‍♂️!

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🔥 (一)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🍭(二)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🍌 (三)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🎵(四)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL✒️(五)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🔬(六)

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🐳(七)

本文用到的emoji:🦄🧚‍♀️🧚🏻‍♀️🧚🏻‍♂️

🚏简介: 在本文中,我们将学习如何使用PostgreSQL的窗口函数来执行与当前行相关的一组行的计算。

📋🚂🚄🚅🚏

Window Functions 概览

思维导图

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

表格总结:

Name说明
CUME_DIST返回当前行的相对排名。
DENSE_RANK在其分区中对当前行进行无间隙排名。
FIRST_VALUE返回一个针对其分区中第一行的计算值。
LAG返回一个在分区内当前行之前的指定物理偏移行的计算值。
LAST_VALUE返回一个针对其分区中最后一行的计算值。
LEAD返回一个在该行计算的值,该行的偏移量为
NTILE在分区中的当前行之后的行的值。
NTH_VALUE尽可能平均分配分区中的行,并给每行分配一个整数,从1开始到参数值。
PERCENT_RANK返回一个针对有序分区中第n行的计算值。
RANK返回当前行的相对排名(rank-1)/(总行-1)。
ROW_NUMBER对当前行在其分区中的空隙进行排序。

🚂PostgreSQL CUME_DIST 函数

🚏简介: 在本节中,我们将学习CUME_DIST()函数来计算一个数值在一组数值中的累积分布。

🚄语法

下面说明CUME_DIST()函数的语法:

 CUME_DIST() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🚄入参

CUME_DIST()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

CUME_DIST()函数返回大于0且小于或等于1的双精度值:

0 < CUME_DIST() <= 1

🚄示例

以下是使用CUME_DIST()函数的示例:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

首先,创建新表命名pro_rank存储选手的排位分数统计:

CREATE TABLE pro_rank (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  team CHAR(1) NOT NULL,
  line VARCHAR(20) NOT NULL,
  rank INT NOT NULL
);

第二,将一些行插入 pro_rank表:

INSERT INTO 
    pro_rank(name, team, line,rank)
VALUES
    ('369','JDG','Top',3699),
		('Kanavi','JDG','Jug',3700),
		('Yagao','JDG','Mid',3666),
		('Hope','JDG','Adc',2222),
		('Missing','JDG','Sup',3333),
		('Bin','BLG','Top',3500),
		('Ning','NULL','Jug',0),
		('Cream','OMG','Mid',2200),
		('Light','LNG','Adc',2500),
		('Hang','WBG','Sup',-2000);

以下示例可帮助我们更好地理解CUME_DIST()函数。

🚅1) 使用PostgreSQLCUME_DIST()函数累积分布示例

以下示例:

SELECT 
    name,
    team, 
    rank,
    CUME_DIST() OVER (
        ORDER BY rank
    ) 
FROM 
    pro_rank
WHERE 
    team = 'JDG';

输出如下

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

如上图中展示的,我们可以发现,JDG战队,有80% 的选手的rank分高于3K。

🚅2) 使用PostgreSQLCUME_DIST()函数分区示例

以下示例使用CUME_DIST()函数计算每个战队每名职业选手rank分百分比。

SELECT 
    name,
	team,
	rank,
    CUME_DIST() OVER (
		PARTITION BY team
        ORDER BY rank
    )
FROM 
    pro_rank;

以下是输出

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY子句将按队伍把行分为n个分区。
  • ORDER BY 子句对每个分区中每名选手的rank分从低到高进行了排序。

🚄备注

CUME_DIST() 函数返回当前行的累积分布,即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。

CUME_DIST() 函数常用于显示一个记录集中最高或者最低百分比数量的记录。比如,全国收入的前 10% 的人、此次考试最后 5% 的学生等。

🚂PostgreSQL DENSE_RANK 函数

🚏简介: 在本节中,我们将学习DENSE_RANK()函数,函数为结果集的分区中的每一条记录分配一个等级,而且等级值没有间隔。

🚄语法

下面说明DENSE_RANK()函数的语法:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🚄入参

DENSE_RANK()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

DENSE_RANK()函数返回函数返回当前行所在的分区内的排名,从 1 开始,但没有间隔。

🚄示例

以下是使用DENSE_RANK()函数的示例:

首先,创建表命名dense_ranks有一列:

CREATE TABLE dense_ranks (
	c VARCHAR(10)
);

第二,插入一些行到dense_ranks表:

INSERT INTO dense_ranks(c)
VALUES('A'),('A'),('B'),('C'),('C'),('D'),('E');

第三,从dense_ranks表查询数据:

SELECT c from dense_ranks;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

接下来,让我们借助数据库示例来更好的学习DENSE_RANK()函数

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

首先,创建新表命名pro_rank存储选手的排位分数统计:

CREATE TABLE pro_rank (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  team CHAR(1) NOT NULL,
  line VARCHAR(20) NOT NULL,
  rank INT NOT NULL
);

第二,将一些行插入 pro_rank表:

INSERT INTO 
    pro_rank(name, team, line,rank)
VALUES
    ('369','JDG','Top',3699),
		('Kanavi','JDG','Jug',3700),
		('Yagao','JDG','Mid',3666),
		('Hope','JDG','Adc',2222),
		('Missing','JDG','Sup',3333),
		('Bin','BLG','Top',3500),
		('Ning','NULL','Jug',0),
		('Cream','OMG','Mid',2200),
		('Light','LNG','Adc',2500),
		('Hang','WBG','Sup',-2000);

以下示例可帮助我们更好地理解DENSE_RANK()函数。

1) 使用PostgreSQLDENSE_RANK()函数分区排名示例

此语句使用DENSE_RANK()按rank分对选手进行排名:

SELECT
	id,
	name,
	rank,
	DENSE_RANK () OVER ( 
		ORDER BY rank DESC
	) pro_rank 
FROM
	pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中,我们跳过了PARTITION BY因此,DENSE_RANK()函数将整个结果集视为单个分区。

2) 使用PostgreSQLDENSE_RANK()分区函数示例

以下示例使用DENSE_RANK()函数计算每个战队每名职业选手rank排名。

SELECT
	id,
	name,
  team,
	rank,
	DENSE_RANK () OVER ( 
    PARTITION BY team
		ORDER BY rank DESC
	) pro_rank 
FROM
	pro_rank;

输出如下图所示:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中,DENSE_RANK()函数应用PARTITION BY子句将战队到组中。ORDER BY子句按ran从高到低对每组职业选手进行排名。

3) 使用PostgreSQLDENSE_RANK()函数和CTE的示例

以下语句使用DENSE_RANK()使用CTE返回每个战队中rank分最高的选手:

WITH cte AS(
	SELECT
    id,
    name,
    team,
    rank,
		DENSE_RANK () OVER ( 
			PARTITION BY team
			ORDER BY rank DESC
		) pro_rank 
	FROM
		pro_rank
) 
SELECT 
  id,
  name,
  rank
FROM 
	cte
WHERE 
	pro_rank = 1;

输出如下

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

🚄备注

DENSE_RANK()函数为结果集的分区中的每一条记录分配一个等级,而且等级值没有间隔。对于每个分区,DENSE_RANK()函数为具有相同值的行返回相同的等级。

🚂PostgreSQL FIRST_VALUE 函数

🚏简介: 在本节中,我们将学习FIRST_VALUE()函数来返回一个结果集的分类分区中的第一个值。

🚄语法

下面说明FIRST_VALUE()函数的语法:

FIRST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🚄入参

FIRST_VALUE()函数有3个入参:

expression

必须参数。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

FIRST_VALUE()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

ORDER BY 子句

ORDER BY 子句对FIRST_VALUE()函数每个分区中的行进行排序。

🚄返回值

FIRST_VALUE()函数返回从当前行关联的结果集的第一行中返回评估的值

🚄示例

以下是使用FIRST_VALUE()函数的示例:

接下来,让我们借助数据库示例来更好的学习FIRST_VALUE()函数

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

首先,创建新表命名pro_rank存储选手的排位分数统计:

CREATE TABLE pro_rank (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  team CHAR(1) NOT NULL,
  line VARCHAR(20) NOT NULL,
  rank INT NOT NULL
);

第二,将一些行插入 pro_rank表:

INSERT INTO 
    pro_rank(name, team, line,rank)
VALUES
    ('369','JDG','Top',3699),
		('Kanavi','JDG','Jug',3700),
		('Yagao','JDG','Mid',3666),
		('Hope','JDG','Adc',2222),
		('Missing','JDG','Sup',3333),
		('Bin','BLG','Top',3500),
		('Ning','NULL','Jug',0),
		('Cream','OMG','Mid',2200),
		('Light','LNG','Adc',2500),
		('Hang','WBG','Sup',-2000);

以下示例可帮助我们更好地理解FIRST_VALUE()函数:

🚅1) 使用PostgreSQLFIRST_VALUE()函数结果集上的示例

以下语句使用FIRST_VALUE()功能查询rank分最低的选手

SELECT 
    id,
    name,
    team,
    rank,
    FIRST_VALUE(name) 
    OVER(
        ORDER BY rank
    ) lowest_pro
FROM 
    pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • 因为我们跳过了FIRST_VALUE()函数中的PARTITION BY子句,该函数将整个结果集视为单个分区。

  • ORDER BY 子句按rank从低到高对选手进行分类。

  • FIRST_VALUE() 函数应用于整个结果集,并返回 name 第一行的列.

🚅2) 使用FIRST_VALUE()分区函数示例

此语句使用FIRST_VALUE()函数返回按战队分组的所有选手。对于每个战队,它返回rank最低的选手:

SELECT 
    id,
    name,
    team,
    rank,
    FIRST_VALUE(name) 
    OVER(
	PARTITION BY team
        ORDER BY rank
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) team_lowest_pro
FROM 
    pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY 子句按战队分配选手。
  • ORDER BY 子句按rank从低到高对每个战队 (分区) 中的选手进行排序。
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 子句定义每个分区中的帧,从第一行开始,到最后一行结束。
  • FIRST_VALUE() 函数分别应用于每个分区.

🚂PostgreSQL LAG函数

🚏简介: 在本节中,我们将学习LAG()函数返回来自当前行所在的分区内当前行之前的指定行之内的行的值.

🚄语法

下面说明LAG()函数的语法:

LAG(expression [,offset [,default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🚄入参

LAG()函数有5个入参:

expr

必需的。它可以是一个列名或者表达式。

offset

可选的。相对于当前行的偏移的行数。默认值为 1。

default

可选的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

LAG()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

LAG()函数返回来自当前行所在的分区内当前行之前的指定行之内的行的值

🚄示例

以下是使用LAG()函数的示例:

🚅1) 使用PostgreSQLLAG()结果集上的函数示例

此示例使用LAG()返回各个战队的选手总rank的函数:

WITH cte AS (
	SELECT 
		team, 
		SUM(rank) rank
	FROM pro_rank
	GROUP BY team
) 
SELECT
	team, 
	rank,
	LAG(rank,1) OVER (
		ORDER BY team
	) team_pro_rank
FROM
	cte;

输出如下

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • 首先,CTE返回按战队分组的总rank分。

  • 然后,外部查询使用LAG()函数返回战队rank总分。第一行在team_pro_rank列,因为第一行没有上一年。

此示例使用两个公共表表达式来返回当前战队和前几个战队之间的rank总分差异:

WITH cte AS (
	SELECT 
		team, 
		SUM(rank) rankall
	FROM pro_rank
	GROUP BY team
), cte2 AS (
	SELECT
		team, 
		rank,
		LAG(rank,1) OVER (
			ORDER BY team
		) team_pro_teams
	FROM
		cte
)	
SELECT 
	team, 
	rank, 
	team_pro_teams,  
	(team_pro_teams - rank) variance
FROM 
	cte2;

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

🚅2) 使用PostgreSQLLAG()分区函数示例

此示例使用LAG()函数将 当前战队的rank分与每个战队rank进行比较:

SELECT
	team, 
	rank,
	group_id,
	LAG(amount,1) OVER (
		PARTITION BY group_id
		ORDER BY year
	) previous_team_ranks
FROM
	pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY子句将战队分组。
  • ORDER BY子句对战队成员进行排序。
  • LAG()函数应用于每个分区以返回上一战队成员选手的rank。

🚄备注

PostgreSQL的LAG()函数提供对当前行之前的指定物理偏移的行的访问。换句话说,从当前行开始,LAG()函数可以访问前一行的数据,或者前一行之前的数据,以此类推。

🚏LAG()函数对于比较当前行和前一行的值将非常有用。

🚂PostgreSQL LAST_VALUE 函数

🚏简介: 在本节中,我们将学习LAST_VALUE()函数

🚄语法

下面说明LAST_VALUE()函数的语法:

LAST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

🚄入参

LAST_VALUE()函数有3个入参:

expr

必需的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

LAST_VALUE()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

LAST_VALUE()函数从当前行结果集的最后一行中返回评估的值。

🚄示例

以下是使用LAST_VALUE()函数的示例:

🚅1) 使用PostgreSQLLAST_VALUE()结果集示例

以下示例使用LAST_VALUE()查询所有选手rank分,和最高rank分选手姓名:

SELECT 
    id,
    name,
    rank,
    LAST_VALUE(name) 
    OVER(
        ORDER BY rank
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_rank
FROM 
    pro_rank;

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • 我们跳过了LAST_VALUE()函数中的PARTITION BY子句,因此,LAST_VALUE()函数将整个结果集视为单个分区。

  • ORDER BY子句按rank从低到高对选手进行分类。

  • LAST_VALUE()选择结果集中最后一行的rank分。

🚅2) 使用PostgreSQLLAST_VALUE()分区示例

以下示例使用LAST_VALUE()返回所有战队以及每个战队中rank最高选手的功能:

SELECT 
    id,
    name,
    team,
    rank,
    LAST_VALUE(name) 
    OVER(
	PARTITION BY team
        ORDER BY rank
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_rank
FROM 
    pro_rank;

输出如下

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY 子句按战队将数据分区。
  • ORDER BY 子句将每个战队(或分区) 中的选手按rank分从低到高排序。
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 子句定义了从第一行开始到每个分区的最后一行结束的最小单位。
  • LAST_VALUE() 函数分别应用于每个分区,并返回每个分区中最后一行的数据。

🚂PostgreSQL LEAD 函数

🚏简介: 在本节中,我们将学习LEAD()函数对当前行之后的指定物理偏移的行的访问。

🚄语法

下面说明LEAD()函数的语法:

LEAD(expression[, offset[, default_value]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

🚄入参

LEAD()函数有5个入参:

expression

必需的。它可以是一个列名或者表达式。

offset

可选的。相对于当前行的偏移的行数。默认值为 1。

default _value

可选的。它可以是一个列名或者表达式。

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

LEAD()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

LEAD()函数返回

🚄示例

以下是使用LEAD()函数的示例:

🚅1) 使用PostgreSQL LEAD()结果集示例

以下查询按战队返回总rank:

SELECT 
	team, 
	SUM(rank)
FROM pro_rank
GROUP BY team
ORDER BY sum desc;

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

WITH cte AS (
	SELECT 
		team, 
		SUM(rank)
	FROM pro_rank
	GROUP BY team
	ORDER BY sum desc
) 
SELECT
	team, 
	sum,
	LEAD(sum,1) OVER (
		ORDER BY sum desc
	) next_team_ranks
FROM
	cte;

输出如下

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • 首先,CTE返回按战队汇总的rank。

  • 然后,外部查询使用LEAD()函数返回下一战队每一行的rank。

以下示例使用两个公共表表达式返回当前战队和下一战队之间的rank差异:

WITH cte AS (
	SELECT 
		team, 
		SUM(rank)
	FROM pro_rank
	GROUP BY team
	ORDER BY sum desc
), cte2 AS (
	SELECT
		team, 
		sum,
		LEAD(sum,1) OVER (
			ORDER BY team
		) next_team_ranks
	FROM
		cte
	ORDER BY sum desc

)	
SELECT 
	team, 
	sum, 
	next_team_ranks,  
	(next_team_ranks - sum) variance
FROM 
	cte2
;

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

🚅2) 使用PostgreSQL LEAD()分区示例

以下语句使用 LEAD() 函数将每个战队的当前rank与下一战队的rank进行比较:

SELECT
	team, 
	rank,
	LEAD(rank,1) OVER (
		PARTITION BY team
		ORDER BY rank
	) next_team_ranks
FROM
	pro_rank;

下图展示输出:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY 子句将行分配到战队指定的产品组 (或分区) 中。
  • ORDER BY 子句把战队按rank升序对每个战队组中的行进行排序。
  • LEAD() 函数返回每个分区的下一个行数据,如果没有,则返回null

🚄备注

PostgreSQL的LEAD()函数提供了对当前行之后的指定物理偏移的行的访问。

这意味着从当前行开始,LEAD()函数可以访问下一行的数据,下一行之后的行,等等。

LEAD()函数对于比较当前行的值和当前行之后的行的值非常有用。

🚂PostgreSQL NTILE函数

🚏简介: 在本节中,我们将学习NTILE()函数

🚄语法

下面说明NTILE()函数的语法:

NTILE(buckets)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list [ASC | DESC]]
)

🚄入参

NTILE()函数有3个入参:

  • buckets 必需的。桶的数量。桶的数量最大为此分区内的行的数量。
  • partition_column_list 参与分区的列的列表。
  • order_column_list 参与排序的列的列表。

NTILE()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

NTILE()函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的排名桶,并返回当前行所在的桶的排名。

🚄示例

以下是使用NTILE()函数的示例:

🚅1) 使用PostgreSQL NTILE()结果集示例

此示例使用NTILE()函数将行分配到3个存储桶中:

SELECT 
	team,
	rank,
	NTILE(3) OVER(
		ORDER BY rank
	)
FROM
	pro_rank
WHERE
	team = 'JDG';

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

🚅2) 使用PostgreSQL NTILE()分区示例

此示例使用NTILE()功能划分行pro_rank表分为两个分区,每个分区3个存储桶:

SELECT 
	team,
	rank,
	NTILE(3) OVER(
		PARTITION BY team
		ORDER BY rank
	)
FROM
	pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

🚄备注

PostgreSQL的NTILE()函数允许你将分区中的有序记录划分为指定数量的排序组,其大小尽可能相等。这些排序的组被称为桶。

NTILE()函数给每个组分配一个从1开始的桶号,对于一个组中的每一条记录,NTILE()函数分配一个桶号,代表该记录所属的组。

🚂PostgreSQL NTH_VALUE 函数

🚏简介: 在本节中,我们将学习NTH_VALUE()函数

🚄语法

下面说明NTH_VALUE()函数的语法:

NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_column_list]
    [ ORDER BY order_column_list [ASC | DESC]
    frame_clause ]
)

🚄入参

NTH_VALUE()函数有4个入参:

  • expression 必需的。它可以是一个列名或者表达式。
  • offset 必需的。指定行的编号。
  • partition_column_list 参与分区的列的列表。
  • order_column_list

参与排序的列的列表。

  • frame_clause

定义了当前分区的子集(或结果集)。

NTH_VALUE()函数有2个子句:

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。

PARTITION BY 子句是可选的。如果你不使用它,函数会将整个结果集视为单个分区。

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。

🚄返回值

NTH_VALUE()函数从一个结果集的有序分区中的第n行返回一个值。

🚄示例

以下是使用NTH_VALUE()函数的示例:

🚅1) 使用PostgreSQL NTH_VALUE()结果集示例

此示例使用NTH_VALUE()将所有选手与第二高分选手一起返回的查询:

SELECT 
    id,
    name,
		team,
    rank,
    NTH_VALUE(name, 2) 
    OVER(
        ORDER BY rank DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    pro_rank;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • ORDER BY 子句按rank从高到低对所有选手进行排序

  • frame子句定义的帧从结果集的开始行开始,到结束行结束。

  • NTH_VALUE() 函数在排序和帧后返回结果集第二行的name列中的值。

🚅2) 使用PostgreSQL NTH_VALUE()分区示例

此示例使用NTH_VALUE()返回每个战队中第二高rank选手和所有选手的功能:

SELECT 
    product_id,
    product_name,
    price,
    group_id,
    NTH_VALUE(product_name, 2) 
    OVER(
        PARTITION BY group_id
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;

输出如下:

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)

在此示例中:

  • PARTITION BY 子句将选手分发到每个战队。
  • ORDER BY 子句按rank从高到低对所有选手进行排序
  • frame子句将每个分区定义为结果集。
  • NTH_VALUE() 函数返回每个战队的第二行的选手名称。

🚄备注

nth_value(expr, 1) 等效于 first_value(expr)。

未完待续..既然看到这里了,不妨来个大大的点赞吧[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️(八)