[SQL] 연승 연패 구하는 SQL

---

연승/연패 구하기

Player마다 게임 승패를 기록한 히스토리가 있다.
다음과 같이 각 플레이어의 최대 연승와 최대 연패를 구한다. 

player	최고 연승	최고 연패
carry	5	1
john	3	1
sam	4	2

1) 테스트용 게임 히스토리 데이터 

-- Make test-data 
/* 
	game_history table 
		player 	: Play name.
		game_id : Game Identifier
		is_win 	: 1=Win, 0=Lose
*/
CREATE TABLE game_history 
AS 
SELECT 'john'  player , 1 	game_id , 1  is_win  UNION ALL  -- is_win : 1=Win, 0=Lose 
SELECT 'john'	  , 2 		, 0 		 UNION ALL 
SELECT 'john'	  , 3 		, 1 		 UNION ALL 
SELECT 'john'	  , 4 		, 1 		 UNION ALL 
SELECT 'john'	  , 5 		, 1 		 UNION ALL 
SELECT 'john'	  , 6 		, 0 		 UNION ALL 
SELECT 'john'	  , 7 		, 1 		 UNION ALL 
SELECT 'john'	  , 8 		, 1 		 UNION ALL 
SELECT 'sam' 	  , 1		, 1 		 UNION ALL 
SELECT 'sam' 	  , 2		, 1 		 UNION ALL 
SELECT 'sam' 	  , 3		, 1 		 UNION ALL 
SELECT 'sam' 	  , 4		, 1 		 UNION ALL 
SELECT 'sam' 	  , 5		, 0 		 UNION ALL 
SELECT 'sam' 	  , 6		, 0 		 UNION ALL 
SELECT 'sam' 	  , 7		, 1 		 UNION ALL 
SELECT 'sam' 	  , 8		, 1 		 UNION ALL 
SELECT 'carry'	  , 1 		, 1		 UNION ALL 
SELECT 'carry'	  , 2 		, 1		 UNION ALL 
SELECT 'carry'	  , 3 		, 1		 UNION ALL 
SELECT 'carry'	  , 4 		, 1		 UNION ALL 
SELECT 'carry'	  , 5 		, 1		 UNION ALL 
SELECT 'carry'	  , 6 		, 0		 UNION ALL 
SELECT 'carry'	  , 7 		, 1		 UNION ALL 
SELECT 'carry'	  , 8 		, 1		 UNION ALL 
SELECT 'carry'	  , 9 		, 1		 UNION ALL 
SELECT 'carry'	  , 10		, 1		 UNION ALL 
SELECT 'carry'	  , 11		, 1

 

 

2) 각 플레이어의 연승 또는 연패를 그룹핑

SELECT 	player
,	game_id
,	is_win
,	prev_is_win
,	SUM(prev_same_tag) OVER (PARTITION BY player ORDER BY game_id) straight_group  -- 연속된 값의 그룹핑
FROM   
(
 -- 이전 매치와의 승리
 SELECT  player
 ,	game_id  
 ,	is_win 
 ,	LAG(is_win, 1) OVER(PARTITION BY player ORDER BY game_id)                    AS prev_is_win  	-- 이전 행의 win_flag 값을 가져온다. mysql LAG 함수 참고.
 ,	IF (LAG(is_win, 1) OVER(PARTITION BY player ORDER BY game_id)=is_win, 0, 1)  AS prev_same_tag   -- 이전 행과 같으며 0, 다르면 1
 FROM 	game_history 
 ORDER BY player, game_id -- player, game_id 
) Z

각 플레이어의 연승 또는 연패를 그룹핑 결과셋

 

 

3) 각 플레이어의 최고 연승, 최고 연패 구하기 

"2) 각 플레이어의 연승 또는 연패를 그룹핑" 결과셋을 player, straight_group, is_win으로 group by 하여 연승, 연패 리스트을 구한다.( = X )

이 결과셋을 player로 다시 group by 하여 각 플레이어의 최고 연승, 최고 연패 기록을 구한다. 

WITH straight_grouping AS (
  -- 각 플레이어의 연승 또는 연패를 그룹핑 결과셋
SELECT player
,	game_id
,	is_win
,	prev_is_win
,	SUM(prev_same_tag) OVER (PARTITION BY player ORDER BY game_id) straight_group  -- 연속된 값의 그룹핑
FROM   
  (
    -- 이전 매치와의 승리
    SELECT player
    ,	game_id  
    ,	is_win 
    ,	LAG(is_win, 1) OVER(PARTITION BY player ORDER BY game_id)                    AS prev_is_win  	-- 이전 행의 win_flag 값을 가져온다. mysql LAG 함수 참고.
    ,	IF (LAG(is_win, 1) OVER(PARTITION BY player ORDER BY game_id)=is_win, 0, 1)  AS prev_same_tag   -- 이전 행과 같으며 0, 다르면 1
    FROM 	game_history 
    ORDER BY player, game_id -- player, game_id 
  ) Z
)
SELECT player 
,	MAX(IF(is_win=1, straight_num, 0))		AS straight_win
,	MAX(IF(is_win=0, straight_num, 0))		AS straight_lose 
FROM   ( 
		SELECT player
  		,	straight_group
		, 	is_win
		,  	MIN(game_id) 	AS from_game_id
		,  	MAX(game_id) 	AS to_game_id 
		,  	COUNT(*)		AS straight_num			-- 연속된 횟수  
		FROM   straight_grouping
		GROUP BY player, straight_group, is_win
	   ) X
GROUP BY player

728x90

 

player	straight_win	straight_lose
carry	5	1
john	3	1
sam	4	2

---