【Postgresql】查询分析——窗口函数

  窗口函数作用于一个数据行集合。窗口是标准的SQL术语，用来描述SQL语句内OVER子句划定的内容，这个内容就是窗口函数的作用域。而在OVER子句中，定义了窗口所覆盖的与当前行相关的数据行集、行的排序及其他的相关元素。

1.排名函数：row_number/rank/dense_rank

  窗口函数中，排名函数是最常用的。窗口排序主要是指非全表排序，需要在某个维度下进行排序。例如说现在想看各个部门内部收入最多的人，这时候不能全表order by了，该怎么取？

select
    *,
    row_number() over ( partition by department order by revenue desc ) as row_number_result,
    rank() over ( partition by department order by revenue desc) as rank_result,
    dense_rank() over (partition by department order by revenue desc) as dense_rank_result
from
    table

row_number() 在排序相同时不重复，会根据顺序排序；
rank()排序相同时会重复，总数不会变，意思是会出现1、1、3这样的排序结果；
dense_rank() 排序相同时会重复，总数会减少，意思是会出现1、1、2这样的排序结果。

2. 排序函数（分区最大/最小值）：first_value/last_value

  取分组内排序后，截止到当前行第一个/最后一个值

select
    *,
  first_value(name) over (PARTITION BY department ORDER BY revenue desc  ) as max_revenue_user, ## 分组取每个组的最大值对应的人
  last_value(name) over (PARTITION BY department ORDER BY revenue desc) as min_revenue_user ## 分组取每个组的最小值对应的人
from
    table

最后就会得到每个组里的revenue最多和最少的人

3. 分布函数（累积百分比）：cume_dist / sum() over

  累积百分比的应用场景也很多，比如说，想看前XX%的用户贡献了XX%的总额。这个地方需要两个函数的使用，1是XX%的用户，2是XX%的总额。

select
    *,
    cume_dist() OVER (PARTITION BY department ORDER BY revenue desc) as cum_dist,
    cume_dist(revenue) OVER (PARTITION BY department ORDER by revenue desc)/sum(revenue) OVER (PARTITION BY department) as s
from
     table
where
    department = ‘HR'

最后可以得到HR部的revenue由高到低排序，XX%的人累积贡献了XX%多少。

4.偏移函数：lead/lag

  一般用于计算差值，最适用的场景是计算花费时间。举个例子，有数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。lead是用于统计窗口内往下第n行值，lag是用于统计窗口内往上第n行值。虽然目前我们这个数据不是时间数据，也可以使用这个函数操作一下。例如说，现在计算按revenue排序后，每个department的人他们的收入，以及和比他们花费排名更高一名的人的值，可以计算差值。

select *,
    lead(revenue) over(partition by department order by revenue) next_revenue
from 
    table


窗口偏移函数包括两种类型的函数，一种是偏移量是相对于当前行的，这个类别的包括LAG和LEAD函数；另一个类别函数的偏移量是相对于窗口框架的开始和结尾的，这个类别包括FIRST_VALUE、LAST_VALUE和NTH_VALUE。第一类别中的函数（LAG和LEAD）支持窗口分区子句以及窗口排序子句。当然，后者的存在赋予偏移量以逻辑意义。第二类别中的函数（FIRST_VALUE、LAST_VALUE和NTH_VALUE）在支持窗口分区子句和排序子句的基础上，还支持窗口框架子句。