sql decimal函数例子_leetcode题库-sql练习精讲系列--三、经典排名问题

这是一个系列文章，这个系列的理念是通过一道题，搞懂一类题。涵盖了SQL面试最常考的知识点。搞懂这些题，面试时工作中sql不可能有问题。

文章分为引入问题-完整解析-答案-leetcode题和答案-知识点拓展-BAT等大厂面试真题几个部分。

希望能帮你全方位的弄懂。有问题可以留言，码字不易，写一篇要好几个小时，希望能得到点赞收藏哦。

一、问题

下图是"01课程成绩"表中的内容，记录了每个学生学生编号，课程编号和成绩。

现在需要根据成绩来排名，如果两个分数相同，那么排名要是并列的。

比如题目中的成绩从大到小排序应该是80,80,76,70,50,31。分数相同排名并列，那么6位同学的排序应该是1,1,3,4,5,6。

二、数据准备

create table 成绩
(`学生编号` varchar(10),
`课程编号` varchar(10),
`成绩` decimal(18,1));

insert into `成绩` values('01' , '01' , 80);
insert into `成绩` values('02' , '01' , 70);
insert into `成绩` values('03' , '01' , 80);
insert into `成绩` values('04' , '01' , 50);
insert into `成绩` values('05' , '01' , 76);
insert into `成绩` values('06' , '01' , 31);

【解答】

[解题思路]

1. 处理分组排名问题，可以使用窗口函数
2. 根据题干的要求两个分数相同，那么排名要是并列的。我们根据 rank, dense_rank, row_number 这三个窗口函数的结果看一下它们的区别。

select *,
   rank() over (order by 成绩 desc) as `rank`,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 成绩

从上面的结果图中的红色框可以看出：

• rank()：跳跃排序；同分并列，但会占用下一个排名名额。
• dense_rank()：连续排序；单词直译为密集排序。同分并列，不占用下一个排名名额。
• row_number()：没有重复值的排序（记录相等也是不重复的），可以进行分页使用。

3.根据题目中要求的分数相同，排名并列，且占用下一个名额。所以我们使用rank()函数。

[代码]

select *,
   rank() over (order by 成绩 desc) as `rank`
from 成绩

[leetcode题库问题-178. 分数排名]

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

[参考答案]

select score, 
       dense_rank() over(order by Score desc) as Ranking
from Scores;

[知识点讲解]

[窗口函数是什么]

窗口的概念非常重要，它可以理解为记录集合，或者理解为分区。窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

• 聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
• 聚合函数也可以用于窗口函数中。

[窗口函数语法]

函数名（[expr]） over 子句 ：

over是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下四种语法来设置窗口：

​```sql
select * from
        (   select row_number()over w as row_num,

  order_id,user_no,amount,create_date

  from order_tab

  WINDOW w AS (partition by user_no order by amount desc)

)t ;
​```

• window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。上面例子中如果指定一个别名w，则改写如下：
• partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。
• order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的订单金额排序来生成序号。
• frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口：

[窗口函数汇总]

• CUME_DIST(): 函数计算一组值中的值的累积分布。
• LAG(): 对当前行之前的指定物理偏移量的行的访问。
• LEAD(): 对当前行之后的指定物理偏移量的行的访问。
• NTILE(): 有序分区的行分配到指定数量的大致相等的组或桶中。
• PERCENT_RANK(): 函数计算结果集的分区中值的相对位置。
• DENSE_RANK()
• RANK()
• ROW_NUMBER()

四、大厂面试真题

[支付宝面试真题] 找出支付金额在累计前20%的用户

现有交易数据表user_sales_table如下：

user_name 用户名

pay_amount 用户支付额度

找出支付金额在累计前20%的用户。

输出要求如下：

user_name 用户名（前20%的用户）

/*
ntile(5) over(order by sum(pay_amount) desc) as level 按照支付金额累计倒序排列后分成大致相同的5组。
*/

select b.user_name from

    (select
    user_name,
    ntile(5) over(order by sum(pay_amount) desc) as level -- 分成5组
    from user_sales_table group by user_name ) b

where b.level = 1