postgresql 使用其他(非重复)筛选器聚合列

ccrfmcuu 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(3)|浏览(143)

这段代码和预期的一样好用，但是它很长而且令人毛骨悚然。

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

postgresql

来源：https://stackoverflow.com/questions/27136251/aggregate-columns-with-additional-distinct-filters

3条答案

按热度按时间

sg24os4d1#

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;

本手册
Postgres维客
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?

赞(0）回复(0）举报 2022-11-29

4xrmg8kj2#

在这种情况下，相关子查询可以简化逻辑：

select u.*, (played - won) as lost
from (select u.*,
             (select count(*)
              from games g
              where g.player_1_id = u.id or g.player_2_id = u.id
             ) as played,
             (select count(*)
              from games g
              where g.winner_id = u.id
             ) as won
      from users u
     ) u;

这是假设没有连接。

赞(0）回复(0）举报 2022-11-29

nimxete23#

select users.name, 
       count(case when games.winner_id > 0 
                  then games.name 
                  else null end) as played,
       count(case when games.winner_id = users.id 
                  then games.name 
                  else null end) as won,
       count(case when games.winner_id != users.id 
                  then games.name 
                  else null end) as lost
from users inner join games 
     on games.player_1_id = users.id or games.player_2_id = users.id
group by users.name;

赞(0）回复(0）举报 2022-11-29

我来回答

postgresql 使用其他(非重复)筛选器聚合列

3条答案

相关问题

热门标签

最新问答