目前被接受的答案没有回答这个问题。而且从原则上讲是错误的。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”作为上限。
但是,时间戳不那么简单,它允许小数位。现代Postgres版本在内部使用8字节整数存储高达6个小数秒(微秒分辨率)。虽然我们可以使其工作,但这不直观并且取决于实现细节。不好的主意。
此外,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';
但还有更多:
如果使用外键约束来执行引用完整性,表 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);
运算符优先级对我们不利,AND
在OR
之前绑定。我们需要括号。
相关答案使用优化的DISTINCT
(如果重复很常见):
通常,自然人的姓名不是唯一的,会使用替代主键。但显然,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
运算符
手册:
OVERLAPS
自动将一对值中的较早值作为起始值。每个时间段都被认为代表半开区间 start <= time < end
,除非start
和end
相等,此时它表示单个时间瞬间。
为了处理潜在的NULL
值,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');
带索引支持的范围类型
在Postgres9.2或更高版本中,您还可以使用实际的范围类型进行操作:
SELECT DISTINCT name_player
FROM contract
WHERE name_team = ?
AND daterange(date_join, date_leave) &&
daterange '[2009-01-01,2010-01-01)';
范围类型增加了一些开销并占用更多的空间。2 x date
= 8字节;1 x daterange
= 14字节(在磁盘上)或17字节(在RAM中)。但是与 重叠运算符&&
结合使用,可以通过GiST索引支持查询。
此外,不需要特殊处理NULL值。在范围类型中,NULL表示“开放范围”-正是我们所需要的。表定义甚至不必更改:我们可以即时创建范围类型,并使用匹配的表达式索引支持查询:
CREATE INDEX mv_stock_dr_idx ON mv_stock USING gist (daterange(date_join, date_leave));
相关: