美文网首页PostgreSQL
PostgreSQL 高级SQL(五) 内建窗口函数

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

作者: 码农大表哥3306 | 来源:发表于2019-04-01 01:17 被阅读60次

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

    通用窗口函数

    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;

    row_number函数

    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

    从上面的的结果我们可以看出 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

            从上面的结果我们可以看出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;

    percent_rank

    5、cume_dist函数

            官方文档解释:当前行的相对排名=(rank- 1) / (总行数 - 1)

            排名和rank值成正相关,rank值相同的行号 percent_rank获取的结果也一样,返回的结果是个小数范围在[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;

    cume_dist函数

    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;

    ntile

    7、lag函数

            官方文档解释:lag(value anyelement [, offset integer [, default anyelement ]]),返回value, 它在分区内当前行的之前offset个位置的行上计算;如果没有这样的行,返回default替代。 (作为value必须是相同类型)。 offsetdefault都是根据当前行计算的结果。如果忽略它们,则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

    从上图可以知道当前行的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;

    lag

    8、lead函数

    官方文档解释:lead(value anyelement [, offset integer [, default anyelement ]]) 返回value,它在分区内当前行的之后offset个位置的行上计算;如果没有这样的行,返回default替代。(作为value必须是相同类型)。offsetdefault都是根据当前行计算的结果。如果忽略它们,则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的文章能对大家在平时的开发中有所帮助

    相关文章

      网友评论

        本文标题:PostgreSQL 高级SQL(五) 内建窗口函数

        本文链接:https://www.haomeiwen.com/subject/fqvpbqtx.html