1.题目基本信息

1.1.题目描述

Table: Teams

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| player_id | int |

| team_name | varchar |

+-------------+---------+

player_id is the unique key for this table.

Each row contains the unique identifier for player and the name of one of the teams participating in that match.

Table: Passes

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| pass_from | int |

| time_stamp | varchar |

| pass_to | int |

+-------------+---------+

(pass_from, time_stamp) is the unique key for this table.

pass_from is a foreign key to player_id from Teams table.

Each row represents a pass made during a match, time_stamp represents the time in minutes (00:00-90:00) when the pass was made,

pass_to is the player_id of the player receiving the pass.

Write a solution to find the longest successful pass streak for each team during the match. The rules are as follows:

A successful pass streak is defined as consecutive passes where:

Both the pass_from and pass_to players belong to the same team

A streak breaks when either:

The pass is intercepted (received by a player from the opposing team)

Return the result table ordered by team_name in ascending order.

1.2.题目地址

https://leetcode.cn/problems/longest-team-pass-streak/description/

2.解题方法

2.1.解题思路

窗口函数+分组聚合

2.2.解题步骤

第一步，将team_name绑定到passes表格中，记字段flag为传球的状态（成功传球为0，失败为1），得到表格T1

第二步，使用窗口函数SUM，按team1分组、time_stamp进行升序排列，计算flag字段的前缀和，即为flag_prefix字段，得到表格T2

第三步，按team1,flag_prefix进行聚合，如果flag_prefix=0，COUNT(*)即为连续成功次数，否则COUNT(*)-1即为连续成功次数，记为字段streak，得到表格T3

第四步，按team_name进行分组聚合，统计streak的最大值记为longest_streak字段，即为题解

3.解题代码

sql代码

# Write your MySQL query statement below

WITH T1 AS (
    # 第一步，将team_name绑定到passes表格中，记字段flag为传球的状态（成功传球为0，失败为1），得到表格T1
    SELECT 
        pass_from, time_stamp, pass_to, 
        t1.team_name AS team1, 
        IF(t1.team_name = t2.team_name, 0, 1) AS flag
    FROM Passes 
        LEFT JOIN Teams AS t1 ON Passes.pass_from = t1.player_id
        LEFT JOIN Teams AS t2 ON Passes.pass_to = t2.player_id
), T2 AS (
    # 第二步，使用窗口函数SUM，按team1分组、time_stamp进行升序排列，计算flag字段的前缀和，即为flag_prefix字段，得到表格T2
    SELECT 
        team1, 
        SUM(flag) OVER (PARTITION BY team1 ORDER BY time_stamp) AS flag_prefix
    FROM T1
), T3 AS (
    # 第三步，按team1,flag_prefix进行聚合，如果flag_prefix=0，COUNT(*)即为连续成功次数，否则COUNT(*)-1即为连续成功次数，记为字段streak，得到表格T3
    SELECT 
        team1, 
        IF(flag_prefix = 0, COUNT(flag_prefix), COUNT(flag_prefix) - 1) AS streak
    FROM T2
    GROUP BY team1, flag_prefix
)
# 第四步，按team_name进行分组聚合，统计streak的最大值记为longest_streak字段，即为题解
SELECT 
    team1 AS team_name, 
    MAX(streak) AS longest_streak
FROM T3
GROUP BY team1
HAVING MAX(streak) != 0
ORDER BY team1

4.执行结果