[SQL] 어떻게이 게임 통계 쿼리를 단순화 할 수 있습니까?

어떻게이 게임 통계 쿼리를 단순화 할 수 있습니까?

예상대로이 코드는 작동하지만 그것은 길고 오싹합니다.

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 개 부분으로 구성되어 있습니다 :

이것은 어떻게 단순화 할 수 있을까?

그 결과 외모가 너무 좋아 :

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

해결법

1. ==============================

   1.포스트 그레스 9.4 이상에서 집계 FILTER 절은 짧고 빠른 :

   포스트 그레스 9.4 이상에서 집계 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;
   

   포스트 그레스 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;
   

   세부:

2. ==============================

   2.이 상관 관계 서브 쿼리는 논리를 단순화 할 수있는 경우이다 :

   이 상관 관계 서브 쿼리는 논리를 단순화 할 수있는 경우이다 :

   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;
   

   이것은 아무 관계가없는 것으로 간주합니다.

3. ==============================

   3.

   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;
   

from https://stackoverflow.com/questions/27136251/how-can-i-simplify-this-game-statistics-query by cc-by-sa and MIT license