常用窗口排序函数

一、ROW_NUMBER

• 简述：根据窗口分区中行的顺序，为每一行返回一个唯一的顺序号（从1开始）。

• 注意：不能用作于学生成绩的排名，一般多用于分页查询，比如求TopN。

• 按照订单金额排序获取序号

  select 
  	order_amount,
  	ROW_NUMBER() OVER(order by order_amount desc) rn
  from yp_dwb.dwb_order_detail;
  1
  2
  3
  4

  

  • 获取金额排行Top10的订单

  select * 
  from (
  	select order_amount,
  	ROW_NUMBER() OVER(order by order_amount desc) rn
  	from yp_dwb.dwb_order_detail
  )
  where rn <= 10;
  1
  2
  3
  4
  5
  6
  7

  

• 获取每个店铺金额排行Top10的订单

  select *
  from (
       select 
              order_amount,
              store_id,
              row_number() over (partition by store_id order by order_amount desc) rn
      from yp_dwb.dwb_order_detail
      )
  where rn <= 10;
  1
  2
  3
  4
  5
  6
  7
  8
  9

  

二、RANK

• 描述：返回值在一组值中的排名。当存在相同金额的订单时，ROW_NUMBER()会一次进行排序，每行数据的序号不相通，而RANK()则不一样，出现相同的金额时，他们的排名是一样的。

• 获取金额rank排名Top10订单

  select *
  from (
       select order_amount,
              row_number() over (order by order_amount desc) rn,
              rank() over(order by order_amount desc) rn_rank
      from yp_dwb.dwb_order_detail
           )
  where rn_rank <= 10;
  1
  2
  3
  4
  5
  6
  7
  8

• 获取各店铺rank排名Top10订单

  select *
  from (
       select order_amount, 
              store_id,
              row_number() over (partition by store_id order by order_amount desc) rn,
              rank() over(partition by store_id order by order_amount desc) rn_rank
      from yp_dwb.dwb_order_detail
           )
  where rn_rank <= 10;
  1
  2
  3
  4
  5
  6
  7
  8
  9

  

三、DENSE_RANK

返回值在一组值中的排名。这与rank()相似，不同之处在于排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就是RANK()。

• 获取金额dense_rank排名Top10订单

  select *
  from (
       select order_amount,
              row_number() over (order by order_amount desc) rn,
              rank() over(order by order_amount desc) rn_rank,
              dense_rank() over (order by order_amount desc) rn_dense_rank
      from yp_dwb.dwb_order_detail
           )
  where rn_dense_rank <= 10;
  1
  2
  3
  4
  5
  6
  7
  8
  9

• 获取各店铺金额dense——rank排名Top10订单

  select *
  from (
       select order_amount,
              store_id,
              row_number() over (partition by store_id order by order_amount desc) rn,
              rank() over(partition by store_id order by order_amount desc) rn_rank,
              dense_rank() over (partition by store_id order by order_amount desc) rn_dense_rank
      from yp_dwb.dwb_order_detail
           )
  where rn <= 10;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  

四、NTILE

• NTILE()函数它把有序的数据集合平均分配到指定的数量（num）个组中，如果不能平均分配，则优先分配较小编号的组，并且各个组中能放的行数最多的相差1。各个组有编号，编号从1开始，就像我们说的‘分区’一样，分为几个区，一个区会有多少个

• 每个组行数最多相差1的含义：

  • 每组的记录数不能大于它上一组的记录数，即编号小的桶放的记录数不能小于编号大的桶。也就是说，第1组的记录数只能大于等于第二组以及各组中的记录数。
  • 所有组中的记录数要么都相同，要么从一个记录较少的组（命名为X）开始后面是所有组的记录数都与该组（x组）的记录数相同。也就是说，如果有个组，前三组的记录数都是9，而第四组的记录数是8，那么第五组和第六组的记录数也必须是8

• 获取金额NTILE500的Top10订单

  select *
  from (
       select order_amount,
              row_number() over (order by order_amount desc) rn,
              rank() over(order by order_amount desc) rn_rank,
              dense_rank() over (order by order_amount desc) rn_dense_rank,
              ntile(500) over(order by order_amount desc) rn_ntile
      from yp_dwb.dwb_order_detail
           )
  where rn_ntile <=10;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  

• 获取各店铺金额ntile500的Top10订单

  select *
  from(
      select order_amount,
             store_id,
             row_number() over (partition by store_id order by order_amount desc) rn,
             rank() over(partition by store_id order by order_amount desc) rn_rank,
             dense_rank() over (partition by store_id order by order_amount desc) rn_dense_rank,
             ntile(500) over(partition by store_id order by order_amount desc) rn_ntile
      from yp_dwb.dwb_order_detail
          )
  where rn_ntile <= 10;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11