Как я могу упростить этот игровой статистический запрос?

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 повторяющихся частей для извлечения:

• имя игрока и количество игр, в которые они играли
• имя игрока и количество игр, которые они выиграли
• имя игрока и количество проигранных игр

И каждая из них также состоит из 2 частей:

• имя игрока и количество игр, в которых они участвовали как 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

Так как это квест против "длинных и жутких", запрос может быть значительно короче, пока. Даже в pg 9.3 (или вообще любой версии):

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?

В pg 9.4 это может быть лучше с новым предложением Aggregate FILTER (как уже упоминалось @Joe).

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

Answer 2

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;

Answer 3

Это тот случай, когда коррелированные подзапросы могут упростить логику:

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;

Это предполагает, что нет никаких связей.