【拼多多面试题】
两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一张两队分数的明细表：
该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。
问题：
请你写一个sql语句统计出，连续三次（及以上）为球队得分的球员名单
【解题步骤】
1.窗口函数
连续三次（及以上）为球队得分的球员名单，用大白话翻译这句话就是：找出【每个球队】里为该球队连续三次（及以上）得分的球员【姓名】。
涉及到“每个”要想到《猴子 从零学会SQL》里讲过的用分组或者窗口函数。
因为该问题是“连续问题”，也就是得分连续三次以上是指比赛按得分时间从前到后排序。所以要用窗口函数，先根据球队分组，再按得分时间排序。
例如，下图按球队分组后，再按照得分时间降序排序后，我们可以看出，A队中的A1球员，B队中的B3球员，其姓名均连续出现3次。
对应的窗口函数如下：
代码语言：javascript复制1 select *,
2 rank() over(partition by 球队
3 order by 得分时间) as 排名
4 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语句：
代码语言：javascript复制1 select 球员姓名,
2 lead(球员姓名,1) over(partition by 球队
3 order by 得分时间) as 下一项
4 from 分数表;下图是用向下窗口函数lag，得到球员姓名向下1行的列（第2列），
对应的SQL语句如下：
代码语言：javascript复制1 select 球员姓名,
2 lag(球员姓名,1) over(partition by 球队
3 order by 得分时间) as 上一行
4 from 分数表;根据前面的分析，我们要得到球员姓名向上1行，和向上2行的值，也就是：
lead(球员姓名,1) lead(球员姓名,2)
对应SQL如下：
代码语言：javascript复制1 select 球员姓名,
2 lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,
3 lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
4 from 分数表;查询结果：
3.SQL运行顺序
完成上面工作，现在就可以使用where子句筛选出出三个值都相同的行，也就是球员姓名 = 姓名1 and 球员姓名 = 姓名2。
但是需要注意，根据我们之前讲过的SQL运行顺序，不能直接在上述步骤后加入where子句。因为根据SQL的运行顺序，会先运行from和where子句，再运行select子句。
因此姓名1和姓名2两列要最后运行select时才会出现，我们需要用子查询来解决，同时最后的球员姓名需要去重（disitinct）。
代码语言：javascript复制1 select distinct 球员姓名
2 from(
3 select 球员姓名,
4 lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,
5 lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
6 from 分数表
7 ) as a
8 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次的问题，可以用下面的万能模板来解决：
1 select distinct 列1
2 from(
3 select 列1,
4 lead(列1,1) over(order by 序号) as 列2,
5 lead(列1,2) over(order by 序号) as 列3,
6 ...
7 lead(列1,n-1) over(order by 列) as 列n,
8 from 表名
9 ) as a
10 where (a.列1 = a.列2 and ... and a.列1 = a.列n);例题：
下面是学生的成绩表（表名score，列名：学号、成绩），使用SQL查找所有至少连续出现3次的成绩。
该题我们使用lag函数：
对应实现SQL如下：
代码语言：javascript复制1 select 成绩,
2 lag(成绩,1) over(order by 学号) as 成绩1,
3 lag(成绩,2) over(order by 学号) as 成绩2
4 from 成绩表;查询结果：
最终答案：
代码语言：javascript复制1 select distinct 成绩
2 from(
3 select 成绩,
4 lag(成绩,1) over(order by 学号) as 成绩1,
5 lag(成绩,2) over(order by 学号) as 成绩2
6 from 成绩表)t
7 where (t.成绩 = t.成绩1 and t.成绩 = t.成绩2);查询结果：
推荐：如何从零学会sql？
