【拼多多面试题】
两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一张两队分数的明细表：
该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。
问题：
请你写一个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》里讲过以下业务场景要用到窗口函数：
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);
查询结果：
转载：https://blog.csdn.net/zhongyangzhong/article/details/113362428