汇总带有其他(不同)过滤器的列 [英] Aggregate columns with additional (distinct) filters

问题描述

此代码可以正常工作，但是我又长又令人毛骨悚然.

This code works as expected, but I it's long and creepy.

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个重复的检索部分:

As you can see, it consists of 3 repetitive parts for retrieving:

• 玩家名称及其玩过的游戏次数
• 玩家名称和他们赢得的游戏数量
• 玩家名称和他们输掉的游戏数量

其中每一个还包括2个部分:

And each of those also consists of 2 parts:

• 玩家名称和以玩家_1身份参加的游戏数量
• 玩家名称和他们作为玩家_2参加的游戏数量

如何简化?

结果看起来像这样:

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

推荐答案

Postgres 9.4 或更高版本中的汇总FILTER子句越来越短:

The aggregate FILTER clause in Postgres 9.4 or newer is shorter and faster:

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;

• 手册
• Postgres Wiki
• Depesz博客帖子
• The manual
• Postgres Wiki
• Depesz blog post

在Postgres 9.3 (或 any 版本)中，它比嵌套子选择或CASE表达式还短和快:

In Postgres 9.3 (or any version) this is still shorter and faster than nested sub-selects or CASE expressions:

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;

详细信息:

• 对于绝对性能，SUM是更快还是COUNT?

这篇关于汇总带有其他(不同)过滤器的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！