根据重叠的活动时间间隔（有效起始时间和有效终止时间）对 SQL 行进行分组

Question

我正在使用这个模拟数据在bigquery中工作：

create schema if not exists dbo;
create table if not exists dbo.player_history(team_id INT, player_id INT, active_from TIMESTAMP, active_to TIMESTAMP);
truncate table dbo.player_history;
INSERT INTO dbo.player_history VALUES(1,1,'2020-01-01', '2020-01-08');
INSERT INTO dbo.player_history VALUES(1,2,'2020-06-01', '2020-09-08');
INSERT INTO dbo.player_history VALUES(1,3,'2020-06-10', '2020-10-01');
INSERT INTO dbo.player_history VALUES(1,4,'2020-02-01', '2020-02-15');
INSERT INTO dbo.player_history VALUES(1,5,'2021-01-01', '2021-01-08');
INSERT INTO dbo.player_history VALUES(1,6,'2021-01-02', '2021-06-08');
INSERT INTO dbo.player_history VALUES(1,7,'2021-01-03', '2021-06-08');
INSERT INTO dbo.player_history VALUES(1,8,'2021-01-04', '2021-06-08');
INSERT INTO dbo.player_history VALUES(1,9,'2020-01-02', '2021-02-05');
INSERT INTO dbo.player_history VALUES(1,10,'2020-10-01', '2021-04-08');
INSERT INTO dbo.player_history VALUES(1,11,'2020-11-01', '2021-05-08');


select *
 from dbo.player_history
order by 3, 4

我想要得到的是活跃的阵容。输出如下所示：

其背后的逻辑是：

我几乎已经使用 valid_to 和 valid_from 之间的某种引导（valid_from）破解了它，并且做了一个案例，如果它是新阵容 0，则将其设置为 1，否则，然后执行某种累积总和来获取 ID，但我无法 100% 解决它......我非常绝望，不知道该去哪里寻找了。

**更正：阵容 4 & 5实际上应该只是一个阵容。

Answer 1

鉴于我们在评论部分中讨论过，一个玩家可以属于多个阵容，您可以使用 JOIN 尝试以下方法：

WITH LINEUPS AS 
    (SELECT a.*,b.player_id as b_player_id
    FROM `dbo.player_history` a
    INNER JOIN  `dbo.player_history` b on b.active_from BETWEEN a.active_from AND a.active_to
    ORDER BY 3, 4)
SELECT 
    team_id,
    ROW_NUMBER () OVER (PARTITION BY team_id ORDER BY  active_from, active_to) AS lineup_id,
    active_from, 
    active_to, 
    ARRAY_AGG(DISTINCT b_player_id) as player_ids
FROM LINEUPS 
GROUP BY team_id, active_from, active_to
ORDER BY active_from, active_to

因为输出对我来说太长通过 Bigquery 控制台中的屏幕截图向您展示，我将结果提取到 Google 表格中。请参阅下面的输出屏幕截图：