Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Give this a try. <strong>Edited to take into account loss rows</strong></p> <pre><code>SELECT d.player_id, MAX(d.winStreak) AS maxWinStreak FROM ( SELECT @cUser := 0, @winStreak := 0 ) v, ( SELECT player_id, won, timestamp, @winStreak := IF(won=1,IF(@cUser=player_id,@winStreak+1,1),0) AS winStreak, @cUser := player_id FROM ( ( -- Get results where player == player_id SELECT player_id, won, timestamp FROM matchTable ) UNION ( -- Get results where player == opponent_id (loss=1 is good) SELECT opponent_id, loss, timestamp FROM matchtable ) ) m ORDER BY player_id ASC, timestamp ASC ) d GROUP BY d.player_id </code></pre> <p>This works by selecting all win/loses and counting the win streak as it goes through. The subquery is then grouped by player_id and the max winStreak as calculated as it looped through is output per-player.</p> <p>It seemed to work nicely against my test dataset anyway :)</p> <p>To do this more efficiently I would restructure, i.e.</p> <pre><code>matches ( matchID, winningPlayerID, timeStamp ) players ( playerID -- player name etc ) matchesHasPlayers ( matchID, playerID ) </code></pre> <p>Which would lead to an inner query of</p> <pre><code>SELECT matches.matchID, matchesHasPlayers.playerID, IF(matches.winningPlayerID=matchesHasPlayers.playerID,1,0) AS won matches.timestamp FROM matches INNER JOIN matchesHasPlayers ORDER BY matches.timestamp </code></pre> <p>resulting in</p> <pre><code>SELECT d.player_id, MAX(d.winStreak) AS maxWinStreak FROM ( SELECT @cUser := 0, @winStreak := 0 ) v, ( SELECT matchesHasPlayers.playerID, matches.timestamp, @winStreak := IF(matches.winningPlayerID=matchesHasPlayers.playerID,IF(@cUser=matchesHasPlayers.playerID,@winStreak+1,1),0) AS winStreak, @cUser := matchesHasPlayers.playerID FROM matches INNER JOIN matchesHasPlayers ORDER BY matchesHasPlayers.playerID ASC, matches.timestamp ASC ) d GROUP BY d.player_id </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POmySQL - winning streak
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSimon at mso.net
    1. USSimon at mso.net
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POmySQL - winning streak
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is giving some interesting results, but they are not correct. Is this just taking into account the player id and not the opponant id? if the opponant_id is the user in question and is down as a loss then it is the same as the user in question having a win
      singulars
      1. PO
      1. USPhil Jackson
    2. COOk so a "win" for player_id is == `player_id=xxx` AND `won=1` OR `oppnent_id=xxx` AND `lose=1`, if so this feels like a bit of a dodgy table design that could be normalised however it's salvagable
      singulars
      1. PO
      1. USSimon at mso.net
    3. COAbsolute geniussss!
      singulars
      1. PO
      1. USPhil Jackson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload