iT邦幫忙

9

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

sql

今天在CSDN回答一個有趣的問題
原連結:MySQL 怎么统计连续数据的个数-CSDN论坛
分享給IT邦友。

內容:

怎麼統計玩家遊戲連勝連敗的資料

以下是資料結構:

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

玩家1,一開始連贏兩場,接著連輸兩場
玩家2,一開始連贏三場,接著連輸三場
玩家3,連贏三場


以下是我的解法:
SQL Fiddle DEMO 連結

/*
【第一步】
先按玩家來排出新的分組SID欄位
*/
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
  left join UserGroupData T2 
    on T1.userid = T2.userid and T1.sid = T2.sid + 1 and T1.result = T2.result
  left join UserGroupData T3 
    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
)
/*
【第三步】
皆由判斷前一場sid跟下一場sid是否都為同樣結果
判斷是不是中間的場次
舉例:
  1,2,3場都勝利,我們需要去掉第二場,因為我們只要開始跟結束的連續場次資料
並且排序給rank給第四部mod使用
*/
, 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)
)
/*
【第四步】
這邊以rank mod 2 取得的資料
mod 2 = 1 代表開始連勝(輸)的sid
mod 2 = 0 代表結束連勝(輸)的sid
以"結束sid - 開始sid + 1"就可以取得連續贏(輸)的場次
*/
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

大大們有不同資料庫、不同解法、或是要改進的地方
都可以提出、討論。


0
神Q超人
iT邦新手 2 級 ‧ 2018-05-30 00:46:20

我對SQL中的with一直感到很好奇,
因為他也可以用在遞迴的處理,
不過大大這個例子應該只是利用with先串出最後一段SQL需要的表吧/images/emoticon/emoticon33.gif

暐翰 iT邦大師 2 級‧ 2018-05-30 08:05:01 檢舉

我對SQL中的with一直感到很好奇,
因為他也可以用在遞迴的處理,

是 作部門階層
可以用CTE+遞迴來做

不過大大這個例子應該只是利用with先串出最後一段SQL需要的表吧

是,我這個例子只把CTE當臨時表使用
不使用遞迴

6
pcw
iT邦研究生 3 級 ‧ 2018-05-31 17:45:49

http://sqlfiddle.com/#!7/9387c/1
我用Sqlite做。

建一個tmp表,新增一欄,名稱為serialNo,性質為遞增的整數。

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

把資料以userid排序,塞進tmp表中。

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

從tmp表中抽出[result] = 'win'的紀錄,並以下一筆紀錄做self join。若連贏者,serialNo將會相差1。把serialNo的差異加總再加1即為連贏的次數。

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

如法炮製連輸的次數,最後用UNION把連贏與連輸的資料串接,並剔除重複的資料。

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
看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 2 級‧ 2018-05-31 21:55:16 檢舉

大大,厲害!
簡潔好多/images/emoticon/emoticon12.gif

pcw iT邦研究生 3 級‧ 2018-05-31 22:45:20 檢舉

我只會基礎的SQL。
WITH, CASE那些我都不會。/images/emoticon/emoticon16.gif

小魚 iT邦高手 1 級‧ 2018-06-01 12:31:34 檢舉

Sqlite好像也沒辦法做比較複雜的語法,
而且能裝的資料有限,
之前要從MySql備份到Sqlite就發生資料遺失的問題...

pcw iT邦研究生 3 級‧ 2018-06-01 13:30:04 檢舉

@小魚

免錢的又不必設定server,又內建在python裡面,別再計較了。/images/emoticon/emoticon15.gif

我的功力也沒到可以寫複雜的語法(老實說,樓主的解法我是看不懂的/images/emoticon/emoticon20.gif),在SQLITE光是用CASE子句來PIVOT我就吃不下了。/images/emoticon/emoticon06.gif

跟ACCESS的2GB相比,SQLITE已經好很多了。但是沒有datetime型別是比較令我困擾的。

另外,不嫌棄的話,請給個like。/images/emoticon/emoticon41.gif

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

發現如果資料再增加四筆 user1 win,結果和我想的不太一樣,
我預期 user1 連勝次數會變為 4,程式結果卻是 5。

應該是同玩家連勝或連敗紀錄如果有兩組以上的話,SUM 統計的時候會多算進去。

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');
pcw iT邦研究生 3 級‧ 2018-06-03 10:23:18 檢舉

play 3與play 4連贏被算進去了。
/images/emoticon/emoticon04.gif

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

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

先幫資料編 id,模擬時間欄位,後續排序用。

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')

接下來利用子查詢,將連續的資料標記 flag,
然後用 userid, result, flag 這三個欄位分組,算出連勝或連敗的次數,
最後只取連勝或連敗次數大於一的資料,就可以得到期望的結果。

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。 /images/emoticon/emoticon25.gif


發現好像誤會題意了,寫成統計每位玩家的連勝或連敗紀錄歷程,題目是要統計每位玩家的最大連勝或最大連敗次數,所以再多一層排序取最大值。

排序的部分因為經過 神Q超人 前幾周一連串的排序考試,所以很快就寫出來了。
/images/emoticon/emoticon39.gif

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

最後變成了五層 SELECT。 /images/emoticon/emoticon16.gif

暐翰 iT邦大師 2 級‧ 2018-06-03 17:54:14 檢舉

/images/emoticon/emoticon12.gif
酷!
先找出非此場同玩家不同結果的數量
接著以玩家、結果、跟上一步取得的數量分組,得出連勝輸數量
找出連勝輸數量大於1的就可以得到期望的結果


假如想線上測試大大的SQL可以點擊db fiddle demo link
推薦閱讀!

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

原來一開始寫的是對的,
您整理成 CTE 並加上註解後清楚很多。
/images/emoticon/emoticon41.gif

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

如果資料庫支援 Windows function 可以使用下面語法

因為是連續範圍問題

  1. RNT CTE 中 先幫原始資料 先建立一個連續編號 ROW_NUMBER() OVER(ORDER BY (select NULL)) rn

Results:

| 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)

Results:

| 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 |
暐翰 iT邦大師 2 級‧ 2018-06-04 11:31:37 檢舉

dog830228大大,好精簡、強! /images/emoticon/emoticon12.gif

dog830228 iT邦研究生 4 級‧ 2018-06-04 16:10:40 檢舉

謝謝分享題目 這個題目蠻有趣且實用的

我要留言

立即登入留言