안녕하세요. 이스트럭(강동운)입니다.
연승쿼리 관련해서 정리해서 올려드립니다~!
Result: 1(승리), 0(패배)
WITH GameResult AS
(
SELECT 1 AS GameIDX, 1 AS UserIDX, 1 AS Result UNION ALL
SELECT 1 AS GameIDX, 2 AS UserIDX, 0 AS Result UNION ALL
SELECT 2 AS GameIDX, 1 AS UserIDX, 1 AS Result UNION ALL
SELECT 2 AS GameIDX, 3 AS UserIDX, 0 AS Result UNION ALL
SELECT 3 AS GameIDX, 3 AS UserIDX, 1 AS Result UNION ALL
SELECT 3 AS GameIDX, 2 AS UserIDX, 0 AS Result UNION ALL
SELECT 4 AS GameIDX, 1 AS UserIDX, 0 AS Result UNION ALL
SELECT 4 AS GameIDX, 3 AS UserIDX, 1 AS Result UNION ALL
SELECT 5 AS GameIDX, 1 AS UserIDX, 1 AS Result UNION ALL
SELECT 5 AS GameIDX, 2 AS UserIDX, 0 AS Result UNION ALL
SELECT 6 AS GameIDX, 1 AS UserIDX, 0 AS Result UNION ALL
SELECT 6 AS GameIDX, 3 AS UserIDX, 1 AS Result UNION ALL
SELECT 7 AS GameIDX, 2 AS UserIDX, 1 AS Result UNION ALL
SELECT 7 AS GameIDX, 3 AS UserIDX, 0 AS Result
UNION ALL SELECT 7 AS GameIDX, 1 AS UserIDX, 1 AS Result
UNION ALL SELECT 8 AS GameIDX, 1 AS UserIDX, 1 AS Result
UNION ALL SELECT 9 AS GameIDX, 1 AS UserIDX, 1 AS Result
UNION ALL SELECT 10 AS GameIDX, 1 AS UserIDX, 1 AS Result
UNION ALL SELECT 11 AS GameIDX, 1 AS UserIDX, 1 AS Result
)
SELECT
UserIDX
, MAX(TotalCount)
FROM
(
SELECT
UserIDX
, COUNT(*) AS TotalCount
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY UserIDX ORDER BY UserIDX, ROWNUM) - ROWNUM AS GROUPKEY
, ROWNUM
, UserIDX
, GameIDX
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY UserIDX ORDER BY GameIDX) AS ROWNUM
, UserIDX
, GameIDX
, Result
FROM GameResult
) AAA
WHERE Result = 1
) BBB
GROUP BY UserIDX, GROUPKEY
) CCC
GROUP BY UserIDX
쿼리를 간단히 설명 드리면.. GameResult 라는 테이블에 ROW_NUMBER를 붙여서.. AAA 인라인 뷰를 생성하게 됩니다.
그럼 아래와 같은 테이블이 됩니다.
그 값을 토대로.. Result=1(승리인 것)만 필터링을 구하고 ..거기에 다시 ROWNUM(ROWNUM_NEW) 을 구하면 아래와 같은 테이블이 됩니다.
여기서 신기한 것은.. ROWNUM_NEW - ROWNUM을 하면.. 연승인 녀석들은 같은 그룹으로 묶이게 됩니다.
연속이 된다는 말은.. 처음 구한 rownum과 두번째 구한 rownum을 빼도 같은 값이 나오기 때문입니다. ^^
이 다음부터는.............. 설명드리지 않겠습니다~ㅋㅋ
연구좀 하면서 재미있었네요~~ 연구의 핵심은.. 연승인 놈들을 그룹을 지을 방법에 대해서 연구해봤네요~!
분명 방법이 있을 것 같은데...라고 ^^;
Deep Inside T-SQL 에 선분처리(507페이지 연결된 좌석번호 찾는 쿼리를 응용)를 참고해서 보다가 알게되었네요 ㅋㅋㅋ..
감사합니다.
http://www.sqler.com/138840 도 비슷