Ranking

Ranking Functions

• row_number
  • 返回排序，且当两个排序相同时，并不并列(每列的序号不同)
• rank
  • 返回排序，且当两个排序相同时，序号并列，且会根据并列的个数形成gaps，如(1,2,2,4,...)
• dense_rank
  • 返回排序，且当两个排序相同时，序号并列，且不会根据并列的个数形成gap，如(1,2,2,3,...)
• example

  SELECT customer_id, count(*) num_rentals,
         rank() OVER (ORDER BY count(*) desc ) rank_rnk,
         row_number() over (ORDER BY count(*) desc ) row_number_rnk,
         dense_rank() over (ORDER BY count(*) desc ) dense_rank_rnk
  FROM rental
  GROUP BY customer_id
  ORDER BY 2 desc;
  

Generating Multiple Rankings

• 结合partition by实现多列排序