玩转PostgreSQL（八）：窗口函数执行计算

2023-06-0610:44:00数据库教程Comments1,355 views字数 11661阅读模式

本文中，我们将学习如何使用PostgreSQL的窗口函数来执行与当前行相关的一组行的计算。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

Window Functions 概览

思维导图文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

表格总结：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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()函数来计算一个数值在一组数值中的累积分布。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明CUME_DIST()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

CUME_DIST()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

CUME_DIST()函数返回大于0且小于或等于1的双精度值:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

0 < CUME_DIST() <= 1

?示例

以下是使用CUME_DIST()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

首先，创建新表命名pro_rank存储选手的排位分数统计:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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()函数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

如上图中展示的，我们可以发现，JDG战队，有80% 的选手的rank分高于3K。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下示例使用CUME_DIST()函数计算每个战队每名职业选手rank分百分比。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下是输出文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?备注

CUME_DIST() 函数返回当前行的累积分布，即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

CUME_DIST() 函数常用于显示一个记录集中最高或者最低百分比数量的记录。比如，全国收入的前 10% 的人、此次考试最后 5% 的学生等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?PostgreSQL DENSE_RANK 函数

?简介： 在本节中，我们将学习DENSE_RANK()函数，函数为结果集的分区中的每一条记录分配一个等级，而且等级值没有间隔。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明DENSE_RANK()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

DENSE_RANK()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

DENSE_RANK()函数返回函数返回当前行所在的分区内的排名，从 1 开始，但没有间隔。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用DENSE_RANK()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

首先，创建表命名dense_ranks有一列:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

CREATE TABLE dense_ranks (
	c VARCHAR(10)
);

第二，插入一些行到dense_ranks表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

第三，从dense_ranks表查询数据:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

SELECT c from dense_ranks;

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

接下来，让我们借助数据库示例来更好的学习DENSE_RANK()函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

首先，创建新表命名pro_rank存储选手的排位分数统计:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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()函数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此语句使用DENSE_RANK()按rank分对选手进行排名:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中，我们跳过了PARTITION BY因此，DENSE_RANK()函数将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下示例使用DENSE_RANK()函数计算每个战队每名职业选手rank排名。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下图所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中，DENSE_RANK()函数应用PARTITION BY子句将战队到组中。ORDER BY子句按ran从高到低对每组职业选手进行排名。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下语句使用DENSE_RANK()使用CTE返回每个战队中rank分最高的选手:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?备注

DENSE_RANK()函数为结果集的分区中的每一条记录分配一个等级，而且等级值没有间隔。对于每个分区，DENSE_RANK()函数为具有相同值的行返回相同的等级。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?PostgreSQL FIRST_VALUE 函数

?简介： 在本节中，我们将学习FIRST_VALUE()函数来返回一个结果集的分类分区中的第一个值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明FIRST_VALUE()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

FIRST_VALUE()函数有3个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

expression文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

必须参数。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

partition_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与分区的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

order_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与排序的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

FIRST_VALUE()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

ORDER BY 子句文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

ORDER BY 子句对FIRST_VALUE()函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

FIRST_VALUE()函数返回从当前行关联的结果集的第一行中返回评估的值文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用FIRST_VALUE()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

接下来，让我们借助数据库示例来更好的学习FIRST_VALUE()函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

首先，创建新表命名pro_rank存储选手的排位分数统计:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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()函数：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下语句使用FIRST_VALUE()功能查询rank分最低的选手文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • 因为我们跳过了FIRST_VALUE()函数中的PARTITION BY子句，该函数将整个结果集视为单个分区。
  • ORDER BY 子句按rank从低到高对选手进行分类。
  • FIRST_VALUE() 函数应用于整个结果集，并返回 name 第一行的列.

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

此语句使用FIRST_VALUE()函数返回按战队分组的所有选手。对于每个战队，它返回rank最低的选手:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?PostgreSQL LAG函数

?简介： 在本节中，我们将学习LAG()函数返回来自当前行所在的分区内当前行之前的指定行之内的行的值.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明LAG()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

LAG()函数有5个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

expr文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

必需的。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

offset文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

可选的。相对于当前行的偏移的行数。默认值为 1。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

default文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

可选的。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

partition_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与分区的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

order_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与排序的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

LAG()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

LAG()函数返回来自当前行所在的分区内当前行之前的指定行之内的行的值文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用LAG()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用LAG()返回各个战队的选手总rank的函数:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用两个公共表表达式来返回当前战队和前几个战队之间的rank总分差异:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用LAG()函数将 当前战队的rank分与每个战队rank进行比较:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?备注

PostgreSQL的LAG()函数提供对当前行之前的指定物理偏移的行的访问。换句话说，从当前行开始，LAG()函数可以访问前一行的数据，或者前一行之前的数据，以此类推。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?LAG()函数对于比较当前行和前一行的值将非常有用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?PostgreSQL LAST_VALUE 函数

?简介： 在本节中，我们将学习LAST_VALUE()函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明LAST_VALUE()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

LAST_VALUE()函数有3个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

expr文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

必需的。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

partition_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与分区的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

order_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与排序的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

LAST_VALUE()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

LAST_VALUE()函数从当前行结果集的最后一行中返回评估的值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用LAST_VALUE()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下示例使用LAST_VALUE()查询所有选手rank分，和最高rank分选手姓名:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • 我们跳过了LAST_VALUE()函数中的PARTITION BY子句，因此，LAST_VALUE()函数将整个结果集视为单个分区。
  • ORDER BY子句按rank从低到高对选手进行分类。
  • LAST_VALUE()选择结果集中最后一行的rank分。

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

以下示例使用LAST_VALUE()返回所有战队以及每个战队中rank最高选手的功能:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?PostgreSQL LEAD 函数

?简介： 在本节中，我们将学习LEAD()函数对当前行之后的指定物理偏移的行的访问。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明LEAD()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

LEAD()函数有5个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

expression文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

必需的。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

offset文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

可选的。相对于当前行的偏移的行数。默认值为 1。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

default _value文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

可选的。它可以是一个列名或者表达式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

partition_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与分区的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

order_column_list文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

参与排序的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

LEAD()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

LEAD()函数返回文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用LEAD()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下查询按战队返回总rank:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • 首先，CTE返回按战队汇总的rank。
  • 然后，外部查询使用LEAD()函数返回下一战队每一行的rank。

以下示例使用两个公共表表达式返回当前战队和下一战队之间的rank差异:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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
;

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

以下语句使用 LEAD() 函数将每个战队的当前rank与下一战队的rank进行比较:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

下图展示输出：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?备注

PostgreSQL的LEAD()函数提供了对当前行之后的指定物理偏移的行的访问。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

这意味着从当前行开始，LEAD()函数可以访问下一行的数据，下一行之后的行，等等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

LEAD()函数对于比较当前行的值和当前行之后的行的值非常有用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?PostgreSQL NTILE函数

?简介： 在本节中，我们将学习NTILE()函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明NTILE()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

NTILE()函数有3个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

NTILE()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

NTILE()函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的排名桶，并返回当前行所在的桶的排名。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用NTILE()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用NTILE()函数将行分配到3个存储桶中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用NTILE()功能划分行pro_rank表分为两个分区，每个分区3个存储桶:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?备注

PostgreSQL的NTILE()函数允许你将分区中的有序记录划分为指定数量的排序组，其大小尽可能相等。这些排序的组被称为桶。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

NTILE()函数给每个组分配一个从1开始的桶号，对于一个组中的每一条记录，NTILE()函数分配一个桶号，代表该记录所属的组。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?PostgreSQL NTH_VALUE 函数

?简介： 在本节中，我们将学习NTH_VALUE()函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?语法

下面说明NTH_VALUE()函数的语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?入参

NTH_VALUE()函数有4个入参：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

参与排序的列的列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • frame_clause

定义了当前分区的子集（或结果集）。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

NTH_VALUE()函数有2个子句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • PARTITION BY

PARTITION BY 子句按行划分为多个应用函数的分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

PARTITION BY 子句是可选的。如果你不使用它，函数会将整个结果集视为单个分区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY

ORDER BY 子句对函数每个分区中的行进行排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?返回值

NTH_VALUE()函数从一个结果集的有序分区中的第n行返回一个值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

?示例

以下是使用NTH_VALUE()函数的示例:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

此示例使用NTH_VALUE()将所有选手与第二高分选手一起返回的查询:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

  • ORDER BY 子句按rank从高到低对所有选手进行排序
  • frame子句定义的帧从结果集的开始行开始，到结束行结束。
  • NTH_VALUE() 函数在排序和帧后返回结果集第二行的name列中的值。

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

此示例使用NTH_VALUE()返回每个战队中第二高rank选手和所有选手的功能:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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;

输出如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

玩转PostgreSQL（八）：窗口函数执行计算文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

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

?备注

nth_value(expr, 1) 等效于 first_value(expr)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html

作者：只注视着你
来源：稀土掘金
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45065.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/45065.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定