3390. 最长团队传球连击 🔒

题目描述

表：Teams

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| player_id   | int     |
| team_name   | varchar | 
+-------------+---------+
player_id 是这张表的唯一主键。
每行包含队员的唯一标识符以及在该场比赛中参赛的某支队伍的名称。

表：Passes

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| pass_from   | int     |
| time_stamp  | varchar |
| pass_to     | int     |
+-------------+---------+
(pass_from, time_stamp) 是这张表的唯一主键。
pass_from 是 Teams 表中 player_id 的外键。
每一行代表比赛中的一次传球，time_stamp 表示传球发生的分钟时间（00:00-90:00）。
pass_to 是接收传球队员的 player_id。

编写一个解决方案以找到比赛中 每个队伍 的 最长连续成功传球。规则如下：

成功连击的定义为连续传球，其中：
- pass_from 和 pass_to 表示的队员来自同一队伍
当出现以下情况时，连击就会中断：
- 传球被截获（由对方球队的一名球员接住）

返回结果表以 team_name 升序排序。

结果格式如下所示。

示例：

输入：

Teams 表：

+-----------+-----------+
| player_id | team_name |
+-----------+-----------+
| 1         | Arsenal   |
| 2         | Arsenal   |
| 3         | Arsenal   |
| 4         | Arsenal   |
| 5         | Chelsea   |
| 6         | Chelsea   |
| 7         | Chelsea   |
| 8         | Chelsea   |
+-----------+-----------+

Passes 表：

+-----------+------------+---------+
| pass_from | time_stamp | pass_to |
+-----------+------------+---------+
| 1         | 00:05      | 2       |
| 2         | 00:07      | 3       |
| 3         | 00:08      | 4       |
| 4         | 00:10      | 5       |
| 6         | 00:15      | 7       |
| 7         | 00:17      | 8       |
| 8         | 00:20      | 6       |
| 6         | 00:22      | 5       |
| 1         | 00:25      | 2       |
| 2         | 00:27      | 3       |
+-----------+------------+---------+

输出：

+-----------+----------------+
| team_name | longest_streak |
+-----------+----------------+
| Arsenal   | 3              |
| Chelsea   | 4              |
+-----------+----------------+

解释：

阿森纳的 连击：
- 第一次连击：3 次传球（1→2→3→4）当队员 4 传球给切尔西的队员 5 时结束
- 第二次连击：2 次传球（1→2→3）
- 最长连击 = 3
切尔西的 连击：
- 第一次连击：3 次传球（6→7→8→6→5）
- 最长连击 = 4

解法

方法一

MySQL

WITH
    PassesWithTeams AS (
        SELECT
            p.pass_from,
            p.pass_to,
            t1.team_name AS team_from,
            t2.team_name AS team_to,
            IF(t1.team_name = t2.team_name, 1, 0) same_team_flag,
            p.time_stamp
        FROM
            Passes p
            JOIN Teams t1 ON p.pass_from = t1.player_id
            JOIN Teams t2 ON p.pass_to = t2.player_id
    ),
    StreakGroups AS (
        SELECT
            team_from AS team_name,
            time_stamp,
            same_team_flag,
            SUM(
                CASE
                    WHEN same_team_flag = 0 THEN 1
                    ELSE 0
                END
            ) OVER (
                PARTITION BY team_from
                ORDER BY time_stamp
            ) AS group_id
        FROM PassesWithTeams
    ),
    StreakLengths AS (
        SELECT
            team_name,
            group_id,
            COUNT(*) AS streak_length
        FROM StreakGroups
        WHERE same_team_flag = 1
        GROUP BY 1, 2
    ),
    LongestStreaks AS (
        SELECT
            team_name,
            MAX(streak_length) AS longest_streak
        FROM StreakLengths
        GROUP BY 1
    )
SELECT
    team_name,
    longest_streak
FROM LongestStreaks
ORDER BY 1;

3390. 最长团队传球连击 🔒

题目描述

解法

方法一

评论