PostgreSQL 高级SQL(五) 内建窗口函数

2020-07-13 15:29 abce 阅读(1148) 评论(0) 编辑收藏举报

本文是转载，原文地址是：https://www.jianshu.com/p/50292ad0d7eb

前面俩个章节我们介绍了窗口函数、滑动窗口函数的概念，接下来我们介绍一下PG支持的原生通用窗口函数，总共11个(11版本的官网文档地址）

1、row_number 函数
row_number函数可以给每个数据行返回一个虚拟的自增ID,也就是相当于给行分配一个编号，这些编号不会出现重复，即使over()里面没有按照字段排序字段也能正常工作，

SELECT
    country_name,
    "year",
    gdp,
    ROW_NUMBER ( ) OVER ( ORDER BY country_name, "year" ) 
FROM
    country_gdp_year_final  
WHERE
    country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
    AND "year" BETWEEN 2012 AND 2017;

2、rank函数
rank的官方解释是：带间隙的当前行排名；与该行的第一个同等行的row_number相同

SELECT
    country_name,
    "year",
    gdp,
    RANK ( ) OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' );

从上面的的结果我们可以看出 rank函数和row_number一样可以将行编号，但是号码可能重复，比如我们按照年份排序，年份相同的话rank值相同，2017年的数据rank直接跳到了7，这就相当于上学的时候考试，用rank计算排名的话，如果同年级出现三个并列的第一名的话，那么计算的结果将是三个人的rank值都是第一，但是实际上的第二高的分数的同学会被rank排名为第四名，如果我们想第二高的分数的排名为2,我们可以使用dense_rank函数；

3、dense_rank函数

SELECT
    country_name,
    "year",
    gdp,
    DENSE_RANK ( ) OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' );

从上面的结果我们可以看出dense_rank函数会把编号弄得更加紧密，中间不会出现像rank那样的断层编码。

4、percent_rank函数
官方文档解释：当前行的相对排名=(rank- 1) / (总行数 - 1) ,
排名和rank值成正相关，rank值相同的行号 percent_rank获取的结果也一样，返回的结果是个小数范围在[0,1]之间，可以等于0或者1

SELECT
    country_name,
    "year",
    gdp,
    PERCENT_RANK ( ) OVER ( ORDER BY "year" DESC ),
    RANK ( ) OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA' ) 
    AND "year" BETWEEN 2014 AND 2018;

5、cume_dist函数
官方文档解释：当前行的相对排名=(rank- 1)/(总行数 - 1) ，
排名和rank值成正相关，rank值相同的行号cume_dist获取的结果也一样，返回的结果是个小数范围在[0,1]之间，可以等于0或者1

SELECT
    country_name,
    "year",
    gdp,
    PERCENT_RANK ( ) OVER ( ORDER BY "year" DESC ),
    CUME_DIST ( ) OVER ( ORDER BY "year" DESC ),
    RANK ( ) OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA' ) 
    AND "year" BETWEEN 2014 AND 2018;

6、ntile函数
官方文档解释：从1到参数值的整数范围，尽可能等分分区，
ntile(num_buckets)，num_buckets的值表示将结果集分成num_buckets组，有限填满前面的组，最后一组可能出现个数不足(非等分)情况，实际上就是把每行分个组号。

SELECT
    country_name,
    "year",
    gdp,
    PERCENT_RANK ( ) OVER ( ORDER BY "year" DESC ),
    CUME_DIST ( ) OVER ( ORDER BY "year" DESC ),
    RANK ( ) OVER ( ORDER BY "year" DESC ),
    NTILE ( 4 )   OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA' ) 
    AND "year" BETWEEN 2014 AND 2018;

7、lag函数
官方文档解释：lag(value anyelement [, offset integer [, default anyelement ]])，返回value，它在分区内当前行的之前offset个位置的行上计算；如果没有这样的行，返回default替代。 (作为value必须是相同类型)。 offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值
官方文档的解释很晦涩难懂，我们直接使用用例执行一下看一下数据分布就好了

SELECT
    country_name,
    "year",
    gdp,
    LAG ( gdp, 1 ) OVER ( ORDER BY "year" DESC ) 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA' ) 
    AND "year" BETWEEN 2014 AND 2017;

从上图可以知道当前行的lag值是当前行的前offset行的值，没有的话就返回default,default不想存在的话就返回null,从数据结果再去看官方文档的解释的话可能清晰很多，lag函数可以在结果集的行内移动，经常使用到的场景是计算今年和全年的年产量的差值，

SELECT
    country_name,
    "year",
    gdp,
    LAG ( gdp, 1 ) OVER ( ORDER BY country_name, "year" DESC ) - gdp 
FROM
    country_gdp_year_final  
WHERE
    country_code  IN ( 'CHN', 'JPN', 'USA' ) 
    AND "year" BETWEEN 2014 AND 2017;

8、lead函数
官方文档解释：lead(value anyelement [, offset integer [, default anyelement ]]) 返回value，它在分区内当前行的之后offset个位置的行上计算；如果没有这样的行，返回default替代。(作为value必须是相同类型)。offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值

其实lead函数和lag函数的作用是相同的，如果lead的offset参数值为-N,那么lag的offset的参数为N的话计算结果是相同的，lag(gdp,-1)是lead(gdp,1)的替代

9、first_value、last_value、nth_value函数较为简单不做介绍

至此我们讲完了几乎所有的窗口函数，希望这五篇关于PostgreSQL的文章能对大家在平时的开发中有所帮助