Geek asks and answers

MySQL - winning streak

Hey. I am trying to find a way to find the biggest winning streak for each participant in my table. When the table was built, it was never planned, so I need help on how I can achieve this.

My structure is as follows:

id  player_id   opponant_id     won     loss    timestamp

If it is a game with people, the player identifier is their identifier. If someone challenges them, their identifier is the identifier of the opponent, and the loss (1 or 0) won refers to player_id.

I want to find the largest winning streak for each user.

Anyone have ideas on how to do this with the current table structure.

considers

EDIT

here are some test data where id 3 is the player in question:

id  player_id   won     loss    timestamp
1   6           0       1       2012-03-14 13:31:00
13  3           0       1       2012-03-15 13:10:40
17  3           0       1       2012-03-15 13:29:56
19  4           0       1       2012-03-15 13:37:36
51  3           1       0       2012-03-16 13:20:05
53  6           0       1       2012-03-16 13:32:38
81  3           0       1       2012-03-21 13:14:49
89  4           1       0       2012-03-21 14:01:28
91  5           0       1       2012-03-22 13:14:20
+3
source share
2

. 

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

, / . player_id, max winStreak, , , .

, :)

, , ..

matches (
    matchID,
    winningPlayerID,
    timeStamp
)

players (
    playerID
    -- player name etc
)

matchesHasPlayers (
    matchID,
    playerID
)


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


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
+4
SELECT * FROM
(
    SELECT player_id, won, loss, timestamp
    FROM games
    WHERE player_id = 123
    UNION
    SELECT opponant_id as player_id, loss as won, won as loss, timestamp
    FROM games
    WHERE opponant_id = 123
)
ORDER BY timestamp

, . , , 11111 . , , .

0

More articles:


All Articles