sql - 使用额外的(不同的)过滤器聚合列

Question

此代码按预期工作，但我觉得它又长又令人毛骨悚然。

select p.name, p.played, w.won, l.lost from

(select users.name, count(games.name) as played
from users
inner join games on games.player_1_id = users.id
where games.winner_id > 0
group by users.name
union
select users.name, count(games.name) as played
from users
inner join games on games.player_2_id = users.id
where games.winner_id > 0
group by users.name) as p

inner join

(select users.name, count(games.name) as won
from users
inner join games on games.player_1_id = users.id
where games.winner_id = users.id
group by users.name
union
select users.name, count(games.name) as won
from users
inner join games on games.player_2_id = users.id
where games.winner_id = users.id
group by users.name) as w on p.name = w.name

inner join

(select users.name, count(games.name) as lost
from users
inner join games on games.player_1_id = users.id
where games.winner_id != users.id
group by users.name
union
select users.name, count(games.name) as lost
from users
inner join games on games.player_2_id = users.id
where games.winner_id != users.id
group by users.name) as l on l.name = p.name

如您所见，它由 3 个用于检索的重复部分组成:

• 玩家姓名和他们玩的游戏数量
• 球员姓名和他们赢得的比赛数量
• 玩家姓名和他们输掉的游戏数量

每一个也由两部分组成:

• 玩家姓名和他们作为 player_1 参加的游戏数量
• 玩家姓名和他们作为 player_2 参加的游戏数量

如何简化这一切？

结果是这样的:

           name            | played | won | lost 
---------------------------+--------+-----+------
 player_a                  |      5 |   2 |    3
 player_b                  |      3 |   2 |    1
 player_c                  |      2 |   1 |    1

Answer 1

Postgres 9.4 或更新版本中的标准 SQL 聚合 FILTER 子句 更短更快:

SELECT u.name
     , count(*) FILTER (WHERE g.winner_id  > 0)    AS played
     , count(*) FILTER (WHERE g.winner_id  = u.id) AS won
     , count(*) FILTER (WHERE g.winner_id <> u.id) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

• The manual
• Postgres Wiki
• Depesz blog post

在 Postgres 9.3(或 任何 版本)中，这仍然比嵌套子选择或 CASE 表达式更短更快:

SELECT u.name
     , count(g.winner_id  > 0 OR NULL)    AS played
     , count(g.winner_id  = u.id OR NULL) AS won
     , count(g.winner_id <> u.id OR NULL) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

参见:

• For absolute performance, is SUM faster or COUNT?