9

## 【SQL分享】 統計玩家遊戲連勝連敗的資料

#### 內容:

``````CREATE TABLE TestTable
( [userid] int, [result] varchar(4))
;

INSERT INTO TestTable
( [userid], [result])
VALUES
( 1, 'win'),( 1, 'loss'),( 1, 'win'),( 1, 'win'),( 2, 'win')
,( 1, 'loss'),( 2, 'win'),( 1, 'loss'),( 2, 'win'),( 3, 'win')
,( 2, 'loss'),( 2, 'loss'),( 2, 'loss'),( 3, 'win'),( 3, 'win')
;
``````

1.資料按時間從舊到新排序
2.提問者想要得出以下結構

userid result recode
1 win 2
1 loss 2
2 win 3
2 loss 3
3 win 3

SQL Fiddle DEMO 連結

``````/*
【第一步】

*/
with UserGroupData as (
select row_number() over (partition by T.userid order by T.userid)  as sid
,T.userid,T.result
from TestTable T
)
/*
【第二步】

藉由left join sid-1取得的資料不為null代表前一場跟本場同樣結果
同理left join sid+1取得的資料不為null代表下一場跟本場同樣結果
得出是否為連續勝(輸)場次
假如不為連續場次，T2,T3的資料都會為null
*/
,SerialData as (
select T1.*,T2.sid PreviousGameID, T3.sid NextGameID from UserGroupData T1
on T1.userid = T2.userid and T1.sid = T2.sid + 1 and T1.result = T2.result
on T1.userid = T3.userid and T1.sid = T3.sid - 1 and T1.result = T3.result
where T2.sid is not null or T3.sid is not null
)
/*
【第三步】

1,2,3場都勝利，我們需要去掉第二場，因為我們只要開始跟結束的連續場次資料

*/
, RankTable as (
select row_number() over (partition by T.userid order by T.sid)  as rank
,T.*
from  SerialData T
where not (PreviousGameID is not null and NextGameID is not null)
)
/*
【第四步】

mod 2 = 1 代表開始連勝(輸)的sid
mod 2 = 0 代表結束連勝(輸)的sid

*/
select
T1.userid,T1.result,(T2.sid-T1.sid + 1) 連續場次
from (select * from  RankTable where rank %  2 = 1) T1
left join (select * from  RankTable where rank % 2 = 0) T2
on T1.userid = T2.userid and T1.rank + 1 = T2.rank
``````

### 4 則留言

0

iT邦新手 1 級 ‧ 2018-05-30 00:46:20

6
paicheng0111
iT邦高手 1 級 ‧ 2018-05-31 17:45:49

http://sqlfiddle.com/#!7/9387c/1

``````CREATE TABLE t_tmp (serialNo integer primary key autoincrement, userid int, result text);
``````

``````INSERT INTO t_tmp(userid, result) select userid, result from testtable order by userid;
``````
id userid result
1 1 win
2 1 loss
3 1 win
4 1 win
5 1 loss
6 1 loss
7 2 win
8 2 win
9 2 win
10 2 loss
11 2 loss
12 2 loss
13 3 win
14 3 win
15 3 win

``````SELECT  A.userid, sum(B.serialNo - A.serialNo) + 1 AS win, 'WIN'
FROM
(select * from t_tmp where result = 'win') A
LEFT JOIN
(select * from t_tmp where result = 'win') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1
WHERE NOT B.id IS NULL
GROUP BY A.userid;
``````
userid win 'WIN'
1 2 WIN
2 3 WIN
3 3 WIN

``````SELECT  A.userid, 'WIN' as Result, sum(B.serialNo - A.serialNo) + 1 AS maxNo
FROM
(select * from t_tmp where result = 'win') A
LEFT JOIN
(select * from t_tmp where result = 'win') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1
WHERE NOT B.serialNo IS NULL
GROUP BY A.userid
UNION
SELECT  A.userid, 'LOSS', sum(B.serialNo - A.serialNo) + 1 AS loss
FROM
(select * from t_tmp where result = 'loss') A
LEFT JOIN
(select * from t_tmp where result = 'loss') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1
WHERE NOT B.serialNo IS NULL
GROUP BY A.userid;
``````
userid Result maxNo
1 LOSS 2
1 WIN 2
2 LOSS 3
2 WIN 3
3 WIN 3

WITH, CASE那些我都不會。

Sqlite好像也沒辦法做比較複雜的語法,

@小魚

fysh711426 iT邦研究生 2 級‧ 2018-06-03 05:08:26 檢舉

``````CREATE TABLE TestTable
([userid] int, [result] text);

INSERT INTO TestTable
( [userid], [result])
VALUES
( 1, 'win'),( 1, 'loss'),( 1, 'win'),( 1, 'win'),( 2, 'win')
,( 1, 'loss'),( 2, 'win'),( 1, 'loss'),( 2, 'win'),( 3, 'win')
,( 2, 'loss'),( 2, 'loss'),( 2, 'loss'),( 3, 'win'),( 3, 'win')
,( 1, 'win'),( 1, 'win'),( 1, 'win'),( 1, 'win');
``````

play 3與play 4連贏被算進去了。

1
fysh711426
iT邦研究生 2 級 ‧ 2018-06-03 03:14:06

MS SQL，我想了好久，不知道正不正確，分享一下。

``````DECLARE @Temp TABLE
(
id int,
userid int,
result varchar(4)
)

INSERT INTO @Temp
(id, userid, result)
VALUES
(1, 1, 'win'),
(2, 1, 'loss'),
(3, 1, 'win'),
(4, 1, 'win'),
(5, 2, 'win'),
(6, 1, 'loss'),
(7, 2, 'win'),
(8, 1, 'loss'),
(9, 2, 'win'),
(10, 3, 'win'),
(11, 2, 'loss'),
(12, 2, 'loss'),
(13, 2, 'loss'),
(14, 3, 'win'),
(15, 3, 'win')
``````

``````SELECT A.userid, A.result, A.recode
FROM
(
SELECT A.userid,
A.result,
A.flag,
COUNT(A.userid) AS recode
FROM
(
SELECT A.*,
(
SELECT COUNT(B.id)
FROM @Temp AS B
WHERE B.userid=A.userid AND
B.id < A.id AND
B.result<>A.result
) AS flag
FROM @Temp AS A
) AS A
GROUP BY A.userid, A.result, A.flag
) AS A
WHERE A.recode > 1
ORDER BY A.userid, A.flag
``````

userid result recode
1 win 2
1 loss 2
2 win 3
2 loss 3
3 win 3

``````SELECT A.userid, A.result, A.recode
FROM
(
SELECT A.userid,
A.result,
A.recode,
ROW_NUMBER() OVER (PARTITION BY userid, result
ORDER BY recode DESC) AS rerank
FROM
(
SELECT A.userid,
A.result,
A.flag,
COUNT(A.userid) AS recode
FROM
(
SELECT A.*,
(
SELECT COUNT(B.id)
FROM @Temp AS B
WHERE B.userid=A.userid AND
B.id < A.id AND
B.result<>A.result
) AS flag
FROM @Temp AS A
) AS A
GROUP BY A.userid, A.result, A.flag
) AS A
WHERE A.recode > 1
) AS A
WHERE A.rerank=1
ORDER BY A.userid, A.result DESC
``````

fysh711426 iT邦研究生 2 級‧ 2018-06-03 19:32:34 檢舉

2

iT邦研究生 2 級 ‧ 2018-06-04 04:56:16

1. `RNT` CTE 中 先幫原始資料 先建立一個連續編號 `ROW_NUMBER() OVER(ORDER BY (select NULL)) rn`
``````| userid | result | rn |
|--------|--------|----|
|      1 |    win |  1 |
|      1 |   loss |  2 |
|      1 |    win |  3 |
|      1 |    win |  4 |
|      2 |    win |  5 |
|      1 |   loss |  6 |
|      2 |    win |  7 |
|      1 |   loss |  8 |
|      2 |    win |  9 |
|      3 |    win | 10 |
|      2 |   loss | 11 |
|      2 |   loss | 12 |
|      2 |   loss | 13 |
|      3 |    win | 14 |
|      3 |    win | 15 |
``````
1. `GrpT` CTE 中 取得分組邊跟連續勝利編號差別 並設置為`grp`欄位(之後可以來group by)
``````| userid | result | rn | grp |
|--------|--------|----|-----|
|      1 |    win |  1 |   0 |
|      1 |    win |  3 |   1 |
|      1 |    win |  4 |   1 |
|      1 |   loss |  2 |   1 |
|      1 |   loss |  6 |   3 |
|      1 |   loss |  8 |   3 |
|      2 |    win |  5 |   0 |
|      2 |    win |  7 |   0 |
|      2 |    win |  9 |   0 |
|      2 |   loss | 11 |   3 |
|      2 |   loss | 12 |   3 |
|      2 |   loss | 13 |   3 |
|      3 |    win | 10 |   0 |
|      3 |    win | 14 |   0 |
|      3 |    win | 15 |   0 |
``````

``````;WITH RNT AS(
SELECT *,ROW_NUMBER() OVER(ORDER BY (select NULL)) rn
FROM TestTable
),
GrpT AS(
SELECT *,
(ROW_NUMBER() OVER(PARTITION BY userid ORDER BY rn) -
ROW_NUMBER() OVER(PARTITION BY userid,result ORDER BY rn)) grp
FROM RNT
)

SELECT userid,result,COUNT(*) 'recode'
FROM GrpT T1
GROUP BY grp,userid,result
HAVING COUNT(*) > 1
ORDER BY userid
``````

SQLFiddle

``````| userid | result | recode |
|--------|--------|--------|
|      1 |    win |      2 |
|      1 |   loss |      2 |
|      2 |    win |      3 |
|      2 |   loss |      3 |
|      3 |    win |      3 |
``````

dog830228大大，好精簡、強!