本文介绍了汇总带有其他(不同)过滤器的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此代码可以正常工作,但是我又长又令人毛骨悚然.
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;
详细信息:
这篇关于汇总带有其他(不同)过滤器的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!