请你写一个sql语句统计出,连续三次(及以上)为球队得分的球员名单 | 您所在的位置:网站首页 › 篮球得分的定义 › 请你写一个sql语句统计出,连续三次(及以上)为球队得分的球员名单 |
【拼多多面试题】 两只篮球队进行了激烈的比赛,比分交替上升。比赛结束后,你有一张两队分数的明细表: 该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。 问题: 请你写一个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);查询结果: ⬇️点击「阅读原文」 免费报名 数据分析训练营 |
CopyRight 2018-2019 实验室设备网 版权所有 |