【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.提问者想要得出以下结构

以下是我的解法:
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 代表开始连胜(输)的sidmod 2 = 0 代表结束连胜(输)的sid以"结束sid - 开始sid + 1"就可以取得连续赢(输)的场次*/select   T1.userid,T1.result,(T2.sid-T1.sid + 1) 连续场次from (select * from  RankTable where rank %  2 = 1) T1left join (select * from  RankTable where rank % 2 = 0) T2  on T1.userid = T2.userid and T1.rank + 1 = T2.rank

大大们有不同资料库、不同解法、或是要改进的地方
都可以提出、讨论。