如何识别 PostgreSQL 中玩家团队成员资格的重叠日期范围？

识别 PostgreSQL 中的重叠日期范围

问题

用户试图检索属于某个玩家的列表给定年份范围内的特定团队。最初提出的查询如下：

SELECT * 
FROM   contract 
JOIN   team USING (name_team) 
JOIN   player USING(name_player) 
WHERE  name_team = ? 
AND    DATE_PART('YEAR',date_join)>= ? 
AND    DATE_PART('YEAR',date_leave)<= ?

但是，此查询无法有效识别重叠的日期范围。

正确解决方案

到要准确确定重叠的日期范围，必须修改查询以考虑以下条件：

1. 玩家在指定年份范围开始之前加入队伍。
2. 在指定年份范围结束之前没有离开队伍的玩家。
3. 从未离开队伍的玩家（以 NULL 表示） date_leave 值）。

优化查询

以下查询提供了更准确的解决方案：

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
       (date '2009-01-01', date '2010-01-01');  -- upper bound excluded

说明

• COALESCE 函数用于处理 NULL date_leave 值，将它们视为开放范围。
• OVERLAPS 运算符自动将每个间隔的开始设置为该对中较早的值。
• 日期范围 '[2009-01-01,2010-01-01)' 表示半开间隔，不包括上限已绑定。

额外注意事项

• 在 PostgreSQL 9.2 或更高版本中，范围类型可以与索引支持一起使用，为基于范围的查询提供改进的性能。
• 提供的查询可以进一步优化通过在 daterange(date_join, date_leave) 表达式上创建表达式索引。

以上是如何识别 PostgreSQL 中玩家团队成员资格的重叠日期范围？的详细内容。更多信息请关注PHP中文网其他相关文章！