[SQL] 연승 구하는 SQL

문제

리그오브레전드 게임을 하던 현석이는 전적검색 데이터에서 날짜에 따른 승패 정보를 조회하였다. [테이블1]

DATE	RESULT
2022-03-24	WIN
2022-03-25	WIN
2022-03-27	LOSE
2022-03-29	WIN
2022-04-02	WIN
2022-04-07	WIN

 

연속해서 이기는("WIN") 것을 연승이라고 할 때 현석이가 여태까지 리그오브레전드 게임을 하면서 연승이 깨지지 않고 최대로 몇 번 연승을 하는지 출력하시오.

단, "2022-03-29" 와 "2022-04-02" 와 같이 날이 떨어져 있는 경우에도 연속해서 이겼다면 연승으로 간주합니다.

 

2022-03-29, 2022-04-02, 2022-04-07 에 연속해서 "WIN" 한 횟수가 3회 이므로 아래와 같이 출력이 되어야 합니다.

WIN_CNT

3

 

데이터 생성

데이터 생성 코드는 아래와 같다.

-- 1. 테이블 생성
CREATE TABLE game_results (
    DATE DATE PRIMARY KEY,
    RESULT ENUM('WIN', 'LOSE') NOT NULL
);

-- 2. 데이터 삽입
INSERT INTO game_results (DATE, RESULT) VALUES
('2022-03-24', 'WIN'),
('2022-03-25', 'WIN'),
('2022-03-27', 'LOSE'),
('2022-03-29', 'WIN'),
('2022-04-02', 'WIN'),
('2022-04-07', 'WIN')

 

풀이 (윈도우 함수 LAG() 사용)

0. game_results 테이블 조회

SELECT * 
FROM game_results

 

1. 이전 승/패 결과 기록 칼럼을 만든다.

SELECT *, LAG(result, 1) OVER (ORDER BY DATE) as 'prev_result'
FROM game_results

 

2. 현재 승/패 결과와 이전 승/패 결과 기록이 같은지 확인하는 칼럼을 만든다. (CTE로 둔다.)

• 이때, 같으면 0, 다르면 1을 저장한다. (추후 sum 함수를 통해 그룹화를 할 것이므로 이처럼 저장한다.)

-- 2. 승/패 결과가 이전과 같은지 확인하는 테이블
WITH check_prev_result as
(
	SELECT *, 
		LAG(result, 1) OVER (ORDER BY DATE) as 'prev_result',
		IF(LAG(result, 1) OVER (ORDER BY DATE) = result, 0, 1) as 'issame_prev_result'
	FROM game_results
)

SELECT *
FROM check_prev_result

 

3. sum() 윈도우 함수를 이용해 연속되는 것을 숫자로 나타내고 CTE로 만든다. (streak_group 칼럼)

use test;

-- 2. 승/패 결과가 이전과 같은지 확인하는 테이블
WITH check_prev_result as
(
	SELECT *, 
		LAG(result, 1) OVER (ORDER BY DATE) as 'prev_result',
		IF(LAG(result, 1) OVER (ORDER BY DATE) = result, 0, 1) as 'issame_prev_result'
	FROM game_results
),
-- 3. 연속되는 그룹은 같은 그룹으로 두도록 streak_group 칼럼을 만든다.
streak_group_tbl as
(
	SELECT date, result, prev_result, issame_prev_result,
		sum(issame_prev_result) OVER (ORDER BY DATE) as 'streak_group'
	FROM check_prev_result
)

SELECT *
FROM streak_group_tbl

 

4. 이긴 경기 중에서 연속으로 이기는 경우 몇 회 연속으로 이기는지를 구하는 CTE를 만든다.

-- 2. 승/패 결과가 이전과 같은지 확인하는 테이블
WITH check_prev_result as
(
	SELECT *, 
		LAG(result, 1) OVER (ORDER BY DATE) as 'prev_result',
		IF(LAG(result, 1) OVER (ORDER BY DATE) = result, 0, 1) as 'issame_prev_result'
	FROM game_results
),
-- 3. 연속되는 그룹은 같은 그룹으로 두도록 streak_group 칼럼을 만든다.
streak_group_tbl as
(
	SELECT date, result, prev_result, issame_prev_result,
		sum(issame_prev_result) OVER (ORDER BY DATE) as 'streak_group'
	FROM check_prev_result
),
-- 4. 이긴 경기 중에서 연속으로 이기는 경우 몇 회 연속으로 이기는지를 구한다.
combo_win_tbl as
(
	SELECT streak_group, count(*) as 'combo_win_cnt'
	FROM streak_group_tbl
	WHERE result = 'WIN'
	GROUP BY streak_group
)

SELECT *
FROM combo_win_tbl

 

5. 연승 횟수가 최대인 것을 출력한다.

-- 2. 승/패 결과가 이전과 같은지 확인하는 테이블
WITH check_prev_result as
(
	SELECT *, 
		LAG(result, 1) OVER (ORDER BY DATE) as 'prev_result',
		IF(LAG(result, 1) OVER (ORDER BY DATE) = result, 0, 1) as 'issame_prev_result'
	FROM game_results
),
-- 3. 연속되는 그룹은 같은 그룹으로 두도록 streak_group 칼럼을 만든다.
streak_group_tbl as
(
	SELECT date, result, prev_result, issame_prev_result,
		sum(issame_prev_result) OVER (ORDER BY DATE) as 'streak_group'
	FROM check_prev_result
),
-- 4. 이긴 경기 중에서 연속으로 이기는 경우 몇 회 연속으로 이기는지를 구한다.
combo_win_tbl as
(
	SELECT streak_group, count(*) as 'combo_win_cnt'
	FROM streak_group_tbl
	WHERE result = 'WIN'
	GROUP BY streak_group
)

-- 5. 그 중 최대 연승을 한 횟수를 출력한다.
SELECT max(combo_win_cnt) as 'win_cnt'
FROM combo_win_tbl

 

Test Case 생각해보기

1. 이긴 적이 없는 경우

테스트 케이스 데이터1

 

다음과 같이 지기만 한 경우에는 win_cnt 값이 0이 나와야 할 것이다.

하지만 결과는 의도와 다르게 NULL 값이 나온다.

 

이런 경우 5번의 마지막 결과에 if를 사용하여 null인 경우 0이 나오도록 코드를 수정하면 된다.

-- 5. 그 중 최대 연승을 한 횟수를 출력한다. (엣지 케이스에 맞춰서 수정)
SELECT IF(max(combo_win_cnt) is null, 0, max(combo_win_cnt)) as 'win_cnt'
FROM combo_win_tbl

 

2. 날짜를 순서대로 저장하지 않은 경우

-- 1. 테이블 생성
CREATE TABLE game_results5 (
    DATE DATE PRIMARY KEY,
    RESULT ENUM('WIN', 'LOSE') NOT NULL
);

-- 2. 데이터 삽입
INSERT INTO game_results5 (DATE, RESULT) VALUES
('2022-03-24', 'WIN'),
('2022-09-25', 'WIN'),
('2022-06-27', 'LOSE'),
('2022-05-29', 'WIN'),
('2022-04-02', 'WIN'),
('2022-11-07', 'WIN')

 

위와 같은 테이블에서 자료가 날짜 순서대로 되어 있지 않더라도 연승/ 연패를 구해야 하므로 DATE가 정렬된 상태에서 연승/연패를 구해야 할 것이다.

즉, 위와 같은 날짜로 정렬된 상태에서 봤을 때 최대 연승 횟수는 3회가 나와야 한다.

우리는 윈도우 함수를 사용하면서 DATE를 기준으로 항상 ORDER BY를 했으므로 이 엣지 케이스는 이미 통과가 되어 있다.