网站建设案例模板/杭州网站优化效果

【拼多多面试题】

两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一张两队分数的明细表：

该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。

问题：

请你写一个sql语句统计出，连续三次（及以上）为球队得分的球员名单

【解题步骤】

1.窗口函数

连续三次（及以上）为球队得分的球员名单，用大白话翻译这句话就是：找出【每个球队】里为该球队连续三次（及以上）得分的球员【姓名】。

涉及到“每个”要想到《猴子 从零学会SQL》里讲过的用分组或者窗口函数。

因为该问题是“连续问题”，也就是得分连续三次以上是指比赛按得分时间从前到后排序。所以要用窗口函数，先根据球队分组，再按得分时间排序。

例如，下图按球队分组后，再按照得分时间降序排序后，我们可以看出，A队中的A1球员，B队中的B3球员，其姓名均连续出现3次。

对应的窗口函数如下：

select *,rank() over(partition by 球队 order by 得分时间) as 排名
from 分数表;

查询结果：

上述结果中，我们能用肉眼看出A1连续出现3次，但是如何用SQL语句得出所有连续出现3次的球员姓名呢？

2.找出连续出现3次的值

如果我们将第1列“球员姓名”向上错位1行到第2列，向上错位2行到第3列，那么原本第1列连续的3个值会到同一行中去。例如下图，第1列三个连续A1值，现在到了同一行。

经过这种变化以后，此时我们只需要一个where子句限制三列的值相等，就可以筛选出连续出现三次的球员姓名。

那么，如何用SQL实现上述错位两列的效果呢？

可以用窗口函数lag或者lead：

向上窗口函数lead：取出字段名所在的列，向上N行的数据，作为独立的列

向下窗口函数lag：取出字段名所在的列，向下N行的数据，作为独立的列

窗口函数语法如下：

lag(字段名,N,默认值) over(partion by …order by …)

lead(字段名,N,默认值) over(partion by …order by …)

默认值是指，当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null。

这么说还是太抽象，下面我们通过一个例子来看下就明白了。

下图是用向上窗口函数lead，得到球员姓名向上1行的列（第2列），因为A1向上1行超出了表行列的范围，所以这里对应的值就是默认值（不设置默认值就是null）。

对应的SQL语句：

select 球员姓名,lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 下一项
from 分数表;

下图是用向下窗口函数lag，得到球员姓名向下1行的列（第2列），

对应的SQL语句如下：

select 球员姓名,lag(球员姓名,1) over(partition by 球队 order by 得分时间) as 上一行
from 分数表;

根据前面的分析，我们要得到球员姓名向上1行，和向上2行的值，也就是：

lead(球员姓名,1)

lead(球员姓名,2)

对应SQL如下：

select 球员姓名,lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
from 分数表;

查询结果：

3.SQL运行顺序

完成上面工作，现在就可以使用where子句筛选出出三个值都相同的行，也就是球员姓名 = 姓名1 and 球员姓名 = 姓名2。

但是需要注意，根据我们之前讲过的SQL运行顺序，不能直接在上述步骤后加入where子句。因为根据SQL的运行顺序，会先运行from和where子句，再运行select子句。

因此姓名1和姓名2两列要最后运行select时才会出现，我们需要用子查询来解决，同时最后的球员姓名需要去重（disitinct）。

select distinct 球员姓名
from(
select 球员姓名,
lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,
lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
from 分数表
) as a
where (a.球员姓名 = a.姓名1 and a.球员姓名 = a.姓名2);

查询结果：

本案例中也可以用下窗口函数lag，也可以得到一样的结果，原理类似，你可以自己画个图实践完可以发我分享你的学习成果。

【本题考点】

1.考查SQL的运行顺序和子查询

2.什么问题可以用到窗口函数？

《猴子 从零学会SQL》里讲过以下业务场景要用到窗口函数：

1）经典topN问题

2）经典排名问题

3）在每个组里比较的问题

4）累计求和问题

5）移动平均问题

6）连续出现N次的问题

3.考查窗口函数lag、lead的用法

这两个函数一般用于计算差值，例如：

1）计算花费时间。例如：某数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。

2）计算与上次相比薪水涨幅。

【举一反三】

以后遇到这种连续出现N次的问题，可以用下面的万能模板来解决：

select distinct 列1
from(
select 列1,
lead(列1,1) over(order by 序号) as 列2,
lead(列1,2) over(order by 序号) as 列3,
...
lead(列1,n-1) over(order by 列) as 列n,
from 表名
) as a
where (a.列1 = a.列2 and ... and a.列1 = a.列n);

例题：

下面是学生的成绩表（表名score，列名：学号、成绩），使用SQL查找所有至少连续出现3次的成绩。

该题我们使用lag函数：

对应实现SQL如下：

select 成绩,
lag(成绩,1) over(order by 学号) as 成绩1,
lag(成绩,2) over(order by 学号) as 成绩2
from 成绩表;

查询结果：

最终答案：

select distinct 成绩
from(
select 成绩,
lag(成绩,1) over(order by 学号) as 成绩1,
lag(成绩,2) over(order by 学号) as 成绩2
from 成绩表)t
where (t.成绩 = t.成绩1 and t.成绩 = t.成绩2);

查询结果：

 ⬇️点击「阅读原文」

 免费报名 数据分析训练营