本文介绍了使用其他(不同的)过滤器聚合列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此代码按预期工作,但我很长而且令人毛骨悚然.
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:
- 玩家姓名和他们玩过的游戏数量
- 玩家姓名和他们赢得的游戏数量
- 玩家姓名和他们输掉的游戏数量
每一个也由两部分组成:
And each of those also consists of 2 parts:
- 玩家姓名和他们作为 player_1 参与的游戏数量
- 玩家姓名和他们作为 player_2 参与的游戏数量
如何简化?
结果如下:
name | played | won | lost ---------------------------+--------+-----+------ player_a | 5 | 2 | 3 player_b | 3 | 2 | 1 player_c | 2 | 1 | 1 推荐答案Postgres 9.4 或更新版本中的 aggregate 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 博文
- 对于绝对性能,SUM 更快还是 COUNT 更快?
在 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;详情:
更多推荐
使用其他(不同的)过滤器聚合列
发布评论