sql - 避免检查每一行，按功能替换查询？

Question

我有团队:

create table team (
    id      integer     primary key,
    type    text
);

此外，我还有玩家:

create table player
(
    id      integer     primary key,
    age     integer,
    team_id integer     references team(id)
);

团队的类型可以是“青年”或“成人”。在青年队中，只有 16 岁以上的球员才能参加正式比赛。在成年队中，只有 18 岁以上的球员才能参加正式比赛。

给定一个团队标识符，我想找到所有允许参加即将到来的比赛的球员。我有以下查询:

select    player.*
from      player
join      team
on        player.team_id = team.id
where     team.id = 1 and
          (
              (team.type = 'YOUTH' and player.age >= 16) or
              (team.type = 'ADULT' and player.age >= 18)
          );

这行得通。然而，在这个查询中，对于每个球员，我都在重复检查球队的类型。该值在整个查询期间将保持不变。

有没有办法改进这个查询？我是否应该将其替换为 pgplsql 函数，首先将团队存储到局部变量中，然后区分以下流程？

IF team.type = 'YOUTH' THEN <youth query> ELSE <adult query> END IF

对我来说，这感觉就像用火箭筒杀死一只苍蝇，但我现在看不到替代方案。

我创建了一个 SQL fiddle :http://rextester.com/TPFA20157

Answer 1

辅助表

在(严格的关系)理论中，您将有另一个表存储团队类型的属性，例如最小年龄。

不过，永远不要存储“年龄”，它是基础常量生日和当前时间的函数。始终存储生日。可能看起来像这样:

CREATE TABLE team_type (
   team_type text PRIMARY KEY
 , min_age   int NOT NULL  -- in years
);

CREATE TABLE team (
   team_id   integer PRIMARY KEY
 , team_type text NOT NULL REFERENCES team_type
);

CREATE TABLE player (
   player_id serial  PRIMARY KEY
 , birthday  date NOT NULL   -- NEVER store "age", it's outdated the next day
 , team_id   integer REFERENCES team
);

查询:

SELECT p.*, age(now(), p.birthday) AS current_age
FROM   player    p
JOIN   team      t  USING (team_id)
JOIN   team_type tt USING (team_type)
WHERE  t.team_id = 1
AND    p.birthday <= now() - interval '1 year' * tt.min_age;

使用函数 age() 显示当前年龄，符合传统的年龄判断算法。

但使用更有效的表达式 p.birthday <= now() - interval '1 year' * tt.min_age在WHERE条款。

另外:当前日期取决于当前时区，因此结果可能会在 +/- 12 小时内变化，具体取决于 session 的时区设置。详情:

• Ignoring timezones altogether in Rails and PostgreSQL

备选方案:函数

但是是的，您可以替换表格 team_tpye将逻辑封装在这样的函数中:

CREATE FUNCTION f_bday_for_team_type(text)
  RETURNS date AS
$func$
SELECT (now() - interval '1 year' * CASE $1 WHEN 'YOUTH' THEN 16
                                            WHEN 'ADULT' THEN 18 END)::date
$func$  LANGUAGE sql STABLE;

计算满足给定团队类型的最低年龄的最大生日。函数是STABLE (不是 VOLATILE )正如人们可能假设的那样。 The manual:

Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

查询:

SELECT p.*, age(now(), p.birthday) AS current_age
FROM   player p
JOIN   team   t USING (team_id)
     , f_bday_for_team_type(t.team_type) AS max_bday  -- implicit CROSS JOIN LATERAL
WHERE  t.team_id = 2
AND    p.birthday <= max_bday;

这不是关系理论的 chalice ，但它有效。

dbfiddle here