查找重叠信息

sbtkgmzw 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(336)

CREATE temp TABLE players
( 
    id                    smallint,
    player_id            smallint,
    team                 varchar(20),
    start_dt             date,
    end_dt               date
);

insert into players (id, player_id, team, start_dt, end_dt) values 
(1,100,'TEAM-A','2018-01-01','2018-08-15'),
(2,100,'TEAM-B','2018-11-15',NULL),
(3,101,'TEAM-B','2018-05-15','2019-02-15'),
(4,101,'TEAM-C','2019-04-01','2019-09-15'),
(5,101,'TEAM-A','2019-11-01',NULL),
(6,102,'TEAM-B','2018-01-15','2019-02-15'),
(7,102,'TEAM-C','2019-05-15','2019-08-01'),
(8,102,'TEAM-A','2019-09-01', NULL  ),
(9,103,'TEAM-C','2019-01-01','2019-06-15')
;

从上面的示例输入数据中，我需要找到如下重叠信息-

我尝试使用重叠选项来实现这一点，但它没有给我预期的结果。任何意见都非常感谢。

sql postgresql Join Date Database

来源：https://stackoverflow.com/questions/62925039/find-the-overlapping-information

1条答案

按热度按时间

hs1ihplo1#

您正在寻找在给定时间点确实属于同一球队的成对球员。
您可以使用适当的日期重叠条件自联接表，并使用 least() 以及 greatest() 计算重叠范围。

select 
    p1.player_id player_id_1,
    p2.player_id player_id_2,
    p1.team,
    greatest(p1.start_dt, p2.start_dt) start_dt,
    least(p1.end_dt, p2.end_dt) end_dt,
    least(p1.end_dt, p2.end_dt) - greatest(p1.start_dt, p2.start_dt) overlaps_interval
from players p1
inner join players p2
    on  p1.team = p2.team
    and p1.player_id < p2.player_id
    and coalesce(p1.end_dt, current_date) >= p2.start_dt
    and coalesce(p2.end_dt, current_date) >= p1.start_dt
order by 1, 2

我通常倾向于避免 overlaps ，因为不等式条件可以更细粒度地控制边界是包含的还是独占的。但如果你想用它，那么：

select 
    p1.player_id player_id_1,
    p2.player_id player_id_2,
    p1.team,
    greatest(p1.start_dt, p2.start_dt) start_dt,
    least(p1.end_dt, p2.end_dt) end_dt,
    least(p1.end_dt, p2.end_dt) - greatest(p1.start_dt, p2.start_dt) overlaps_interval
from players p1
inner join players p2
    on  p1.team = p2.team
    and p1.player_id < p2.player_id
    and (p1.start_dt, coalesce(p1.end_dt, current_date)) 
        overlaps (p2.start_dt, coalesce(p2.end_dt, current_date))
order by 1, 2

db fiddle上的演示-两个查询都产生：

player_id_1 | player_id_2 | team   | start_dt   | end_dt     | overlaps_interval
----------: | ----------: | :----- | :--------- | :--------- | ----------------:
        100 |         101 | TEAM-B | 2018-11-15 | 2019-02-15 |                92
        100 |         102 | TEAM-B | 2018-11-15 | 2019-02-15 |                92
        101 |         102 | TEAM-A | 2019-11-01 | null       |              null
        101 |         102 | TEAM-B | 2018-05-15 | 2019-02-15 |               276
        101 |         102 | TEAM-C | 2019-05-15 | 2019-08-01 |                78
        101 |         103 | TEAM-C | 2019-04-01 | 2019-06-15 |                75
        102 |         103 | TEAM-C | 2019-05-15 | 2019-06-15 |                31

赞(0）回复(0）举报 2021-07-26

我来回答

查找重叠信息

1条答案

相关问题

热门标签

最新问答