SQL拼多多面试题：累计求和问题如何分析？（多维度拆解业务问题、分组排序、累计求和、窗口函数lag、lead）

最新推荐文章于 2024-08-05 16:50:24 发布

Begin to change

最新推荐文章于 2024-08-05 16:50:24 发布

阅读量538

点赞数 4

分类专栏： # SQL面试题文章标签： sql 数据库

SQL面试题专栏收录该内容

69 篇文章 15 订阅

订阅专栏

【拼多多面试题】

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

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

问题：

请你写一个sql语句统计出：比赛中帮助各自球队反超比分的球员姓名以及对应时间

【解题步骤】

1.什么是反超比分？

我们首先来确定“反超比分”的含义：反超指体育比赛中比分由落后转为领先。

例如A、B两队进行比赛，如下图所示，该场比赛（第2场）与上一场比赛（第1场）相比，胜方发生了变化，那么该场比赛的胜方队伍成功反超。

如该图左侧，第1场胜方为A，第2场胜方为B，那么这次比赛B反超成功。该图右侧，第1场胜方为B，第2场胜方为A，那么这次比赛A反超成功。

那么，如果第2场是平局呢？

由下图可知，在上一场（第2场）是平局的情况下，需要和上两场（第1场）的胜方进行比较。如果胜方发生变化，则该场比赛的队伍反超成功。

如上图左侧，第2场比赛两队打平，第3场胜方为B，那么此次比赛B反超成功。该图右侧，第2场比赛两队打平，第3场胜方仍为A，那么此次比赛B未能反超。

到这里，我们可以总结下：

1）上一场不是平局的情况下，该场的胜方与上一场的不同，则该场的队伍反超。

2）上一场是平局的情况下，该场的胜方与上两场的不同，则该场的队伍反超。

2.如何知道A队得分、B队得分？

但是，我们观察题目给出的图表，只有某个时刻A队或B队的得分分数。

而得分分数是指该时刻某队得了几分，而不知道它们之前的比分是多少。我们需要在之前比分的基础上加上此次得分的分数，才能判断两队该时刻得分后的胜负情况。

例如，之前A : B的比分是10 : 13，在2020/8/28 9:01:14的时刻，A队得了1分，所以此时两队的比分变为11 : 13，因此我们判断两队的胜负是通过比较两队在得分时间的累计得分所得出的。

而表中的得分分数即包含A队也包含B队，无法分别对A、B队得分进行累计，因此我们需要先将得分分数拆分成，A队的得分分数和B队的得分分数，再分别对两队的得分分数进行累计。

也就是在【A队分数】列中，将【得分分数】为B队的变为0即可，【B队分数】列同理。如下图，红色部分为A队的得分，绿色部分为B队的得分，将其拆分成两列：

该步骤可以用条件判断（case表达式）得出：

select *,
(case when 球队='A' 
      then 得分分数 
      else 0 
      end) as A队分数,
(case when 球队='B' 
      then 得分分数 
      else 0 
      end) as B队分数
from 分数表
order by 得分时间;

3.如何获得累计得分？

累计求和问题之前我们有讲过该类问题如何分析，可以回顾复习，这里不再重复说这个知识点：累计求和问题如何分析？

接着分别求A、B两列的累计分数：

需要将上一步的结果作为子查询：


select 球员姓名,得分时间,A队分数,B队分数,
sum(A队分数) over(order by 得分时间) as A队累计,
sum(B队分数) over(order by 得分时间) as B队累计
from(
select *,
(case when 球队='A' 
      then 得分分数 
      else 0 
      end) as A队分数,
(case when 球队='B' 
      then 得分分数 
      else 0 
      end) as B队分数
from 分数表
order by 得分时间
) as t;

4.如何得到反超？

知道了两队的累计分数后，我们已经能够通过比较两队的累计得分从而得出该时刻的胜方。而根据前面总结：

1）上一场不是平局的情况下，该场的胜方与上一场的不同，则该场的队伍反超。
2）上一场是平局的情况下，该场的胜方与上两场的不同，则该场的队伍反超。

我们将每个得分时刻两队的得分情况看作是每一场比赛两队的得分情况，也需要分上述两种情况讨论。

（备注：由于该题中将每个时刻两队得分看作是每一场比赛两队的得分情况，则不会出现连续两次或者以上平局的情况，例如某场即某个时刻A：B=10：10，而每个时刻只有一个球队得分，因此下一场即下个时刻一定不会还是平局）

如何用SQL来表示呢？

1）上一场不是平局的情况：

判断胜方为A的条件即为：A-B>0，差为正数

判断胜方为B的条件即为：A-B<0，差为负数

该场的胜方与上一场的不同即满足：【A-B的差】*【A-B的差的上一项】<0

2）上一场是平局的情况：

平局即满足【A-B的差的上一项】=0

该场的胜方与上两场的不同即满足：【A-B的差】*【A-B的差的上两项】<0

因此我们需要求出【A-B的差】、【差的上一项】和【差的上两项】，【差的上一项】和【差的上两项】即可用《猴子从零学会SQL》里讲过的lag函数。

需要将上一步的查询结果作为子查询：

select *,
A队累计-B队累计 as 差,
lag(A队累计-B队累计,1) over(order by 得分时间) as 差的上一项,
lag(A队累计-B队累计,2) over(order by 得分时间) as 差的上两项
from(
select 球员姓名,得分时间,A队分数,B队分数,
sum(A队分数) over(order by 得分时间) as A队累计,
sum(B队分数) over(order by 得分时间) as B队累计
from(
select *,
(case when 球队='A' 
      then 得分分数 
      else 0 
      end) as A队分数,
(case when 球队='B' 
      then 得分分数 
      else 0 
      end) as B队分数
from 分数表
order by 得分时间
) as t
) as t1;

用where子句添加限制条件：【差】*【差的上一项】<0

或者【差的上一项】=0 且【差】*【差的上两项】<0

对应SQL如下：


where (差 * 差的上一项 < 0) or 
(差的上一项=0 and 差*差的上两项<0)

将上一步的查询果作为子查询，并用select语句筛选出帮助各自球队反超比分的球员姓名以及对应时间两列即可：

select 球员姓名,得分时间
from(
select *,
A队累计-B队累计 as 差,
lag(A队累计-B队累计,1) over(order by 得分时间) as 差的上一项,
lag(A队累计-B队累计,2) over(order by 得分时间) as 差的上两项
from(
select 球员姓名,得分时间,A队分数,B队分数,
sum(A队分数) over(order by 得分时间) as A队累计,
sum(B队分数) over(order by 得分时间) as B队累计
from(
select *,
(case when 球队='A' 
      then 得分分数 
      else 0 
      end) as A队分数,
(case when 球队='B' 
      then 得分分数 
      else 0 
      end) as B队分数
from 分数表
order by 得分时间
) as t
) as t1
) as t2
where (差 * 差的上一项 < 0) or (差的上一项=0 and 差*差的上两项<0);