sql - 在 PostgreSQL 中查找重叠的日期范围

Question

这是正确的吗？

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)<= ?

我的表格contract有球员姓名、球队名称和他加入和离开俱乐部的日期。
我想制作一个函数，列出特定年份球队中的所有球员。
上面的查询似乎不起作用......

Answer 1

currently accepted answer不回答问题。而且原则上是错误的。 a BETWEEN x AND y翻译成:

<strike>a >= x AND a <b><=</b> y</strike>

包括上限，而人们通常需要排除它:

a >= x AND a <b><</b> y

使用日期，您可以轻松调整。对于 2009 年，使用“2009-12-31”作为上限。
但是对于允许小数位的 timestamps 就没那么简单了。现代 Postgres 版本在内部使用 8 字节整数来存储最多 6 个小数秒(µs 分辨率)。知道了这一点，我们可以仍然让它工作，但这不是直观的并且取决于实现细节。坏主意。

此外，a BETWEEN x AND y没有找到重叠的范围。我们需要:

<b>b</b> >= x AND a <b><</b> y

从未离开的玩家还没有被考虑在内。

正确答案

假设年份 2009 ，我将在不改变其含义的情况下改写问题:

“查找 2010 年之前加入且 2009 年之前未离开的给定球队的所有球员。”

基本查询:

SELECT p.*
FROM   team     t
JOIN   contract c USING (name_team) 
JOIN   player   p USING (name_player) 
WHERE  t.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND    c.date_leave >= date '2009-01-01';

但还有更多:

如果使用 FK 约束强制执行参照完整性，则表 team本身只是查询中的噪音，可以删除。

虽然同一名球员可以离开并重新加入同一支球队，但我们还需要弃掉可能的重复球员，例如 DISTINCT .

而且我们可能需要提供一种特殊情况:从未离开过的玩家。假设那些玩家在 date_leave 中有 NULL .

“一个不知道已经离开的球员被假定为今天为球队效力。”

优化查询:

SELECT DISTINCT p.* 
FROM   contract c
JOIN   player   p USING (name_player) 
WHERE  c.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND   (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);

Operator precedence对我们不利，AND在 OR 之前绑定(bind).我们需要括号。

优化的相关答案 DISTINCT (如果重复很常见):

• Many to Many Table - Performance is bad

通常，自然人的姓名不是唯一的，因此会使用代理主键。但是，显然，name_player是 player 的主键.如果您只需要玩家姓名，我们不需要表格 player在查询中，要么:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    date_join  <  date '2010-01-01'
AND   (date_leave >= date '2009-01-01' OR date_leave IS NULL);

SQL OVERLAPS运营商

The manual:

OVERLAPS automatically takes the earlier value of the pair as thestart. Each time period is considered to represent the half-openinterval start <= time < end, unless start and end are equal inwhich case it represents that single time instant.

照顾潜能NULL值(value)观， COALESCE 似乎最简单:

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

支持索引的范围类型

在 Postgres 9.2 或更高版本 中，您还可以使用实际的 range types 进行操作:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    daterange(date_join, date_leave) &&
       daterange '[2009-01-01,2010-01-01)';  -- upper bound excluded

范围类型会增加一些开销并占用更多空间。 2 x date = 8 字节； 1 x daterange = 磁盘上的 14 个字节或 RAM 中的 17 个字节。但结合 overlap operator && GiST 索引可以支持查询。

另外，不需要特例 NULL 值。 NULL 表示范围类型中的“开放范围”——这正是我们所需要的。表定义甚至不必更改:我们可以即时创建范围类型 - 并支持具有匹配表达式索引的查询:

CREATE INDEX mv_stock_dr_idx ON mv_stock USING gist (daterange(date_join, date_leave));

相关:

• Average stock history table