winning streak query
pat67
I am trying to do is get a winning or losing streak query based on
date and game id. meaning a player can play 4 times in one nite so i
would need to first look to date then to game id. so if he one the
first 3 and lost the last one he would have a winning streak of 3. I
hope i am explaining enough. Any ideas? Thanks
Clifford Bass via AccessMonster.com
Interesting question. Here is one way that presumes that three or more
games in a row of winning or losing out of an unlimited number of games on a
particular day consitutes a streak. It gives you the start and end game ID
and the number of games in the streak. It is broken down by day, player and
type of streak. As you did not give your table definition here is the one I
used:
tblGamesPlayers
GameDate
PlayerID
GameID
Winner (Yes/No field)
"qryWin Lose Streaks-Part 1" which determines each consecutive triplet of
wins and loses:
SELECT A.GameDate, A.PlayerID, A.Winner, A.GameID, B.GameID, C.GameID
FROM (tblGamesPlayers AS A INNER JOIN tblGamesPlayers AS B ON (A.Winner = B.
Winner) AND (A.PlayerID = B.PlayerID) AND (A.GameDate = B.GameDate)) INNER
JOIN tblGamesPlayers AS C ON (B.Winner = C.Winner) AND (B.PlayerID = C.
PlayerID) AND (B.GameDate = C.GameDate)
WHERE (((B.GameID)=(select Min(GameID) from tblGamesPlayers as D where D.
GameDate = A.GameDate and D.PlayerID = A.PlayerID and D.GameID > A.GameID))
AND ((C.GameID)=(select Min(GameID) from tblGamesPlayers as E where E.
GameDate = B.GameDate and E.PlayerID = B.PlayerID and E.GameID > B.GameID)));
"qryWin Lose Streaks-Part 2" which gives the desired information by
summarizing the above results:
SELECT Z.GameDate, Z.PlayerID, Z.Winner, Count(*)+2 AS GamesInStreak, Min(Z.A.
GameID) AS StreakStart, Max(Z.C.GameID) AS StreakEnd
FROM [qryWin Lose Streaks-Part 1] AS Z
GROUP BY Z.GameDate, Z.PlayerID, Z.Winner;
It might be possible to condense that all into one query. I leave that
to you to attempt if you wish.
Clifford Bass
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
vanderghast
player, over the date_time, the two sequences: rank all games, rank only
winning games (again, by player). You can then compute Max(rank all
games) - Min(rank all games) +1, over the GROUP BY player, rank_all_games -
rank_only_winning_games, to get the various streak.
Than can be (easily) done in multiple queries:
Final query:
-------------
SELECT playerID, MAX(streak)
FROM qStreaks
GROUP BY playerID
------------------
returns the maximum number of consecutive wins, by players.
Query: qStreaks:
------------------
SELECT playerID, 1+MAX(rank_all) - MIN(rank_all) AS streak
FROM qRanks
GROUP BY playerID, rank_all - rank_onlyWins
----------------
which returns the various streaks, by player.
Query: qRanks:
-----------------------
SELECT a.playerID,
a.gameDateTimeStamp,
(SELECT COUNT(*)
FROM myData AS b
WHERE b.playerID=a.playerID
AND b.gameDateTimeStamp <= a.gameDateTimeStamp)
AS rank_all,
(SELECT COUNT(*)
FROM myData AS c
WHERE c.playerID=a.playerID
AND c.isWin
AND c.gameDateTimeStamp <= a.gameDateTimeStamp)
AS rank_onlyWins
FROM myData AS a
WHERE a.isWin
GROUP BY a.playerID, a.gameDateTimeStamp
--------------------------
which ranks each (wnning) game, by player, over all the game and also over
only the winning games.
Note that I assumed the original table has at least the fields: playerID,
gameDateTimeStamp, and isWin (Boolean, = true for a win, = false for a
lost).
The idea to substract two ranks may seems 'weird', but consider that the
player won all his games: the difference between the two ranks will always
be 0. On the other hand, if the player lost one game in the middle of the
sequence, then the difference (for the two ranks) for the first games will
be 0 as before, but for the later games, the difference will be 1 (since the
rank_all will see a game that rank_onlyWins won't see) and thus, you have to
count the number of records where the difference is 0 to get the first
streak of wins, and count the number of records where the difference is 1 to
get the last streak of wins. Exactly what qStreaks does, for a general
sequence of wins/lost sequences of games. (instead of 'counting' the
records, which will require a sub-query, we take an alternative route,
playing with MIN and MAX, but that elaborate formulation implyinng Max and
Min just does our 'count', in the end).
The last query, qRanks, compute the two required ranks using sub-query. it
can be quite slow if you have a large amount of data. If you use MS SQL
Server 2005 or later, you could preferably use the pre-defined rank operator
(Jet does not have such operator).
Vanderghast, Access MVP
"pat67" <pbu...@comcast.net> wrote in message
news:8ffe0e6c-8345-4c4b...@z7g2000vbl.googlegroups.com...
Clifford Bass via AccessMonster.com
Interesting solution. I will have to look at it some more to totally
understand it.
Under the presumption from pat67's examples that a steak is three or
more wins/loses I think you will want to add "HAVING MAX(streak) >= 3" to the
final query. Your data set would need to be expanded or adjusted if there
are multiple, simultaneous games happening. Also, I think pat67 wanted both
winning and losing streaks. And I think the streaks were streaks on a
particular day, not over multiple days.
pat67: correct me if I am wrong there.
Clifford Bass
vanderghast
To get only one day, replace myData (deep most query) by a query that will
limit the data to that one day, something with WHERE
DateValue(gameDateTimeStamp) = someDate.
To get losing streaks, replace isWin by NOT isWin (2 places in the deep
most query).
As long as the couple (playerID, gameDateTimeStamp) has no dup, the logic
stands, even if there are many games with the same datetime stamp. It won't
if the same player can be in many simultaneous games, like a chess master
player playing simultaneous chess games.
Vanderghast, Access MVP
"Clifford Bass via AccessMonster.com" <u48370@uwe> wrote in message
news:a1aa817b9720f@uwe...
pat67
> Indeed, you need to add the proposed HAVING clause in that case
>
> To get only one day, replace myData (deep most query) by a query that will
> limit the data to that one day, something with WHERE
> DateValue(gameDateTimeStamp) = someDate.
>
> To get losing streaks, replace isWin by NOT isWin (2 places in the deep
> most query).
>
> As long as the couple (playerID, gameDateTimeStamp) has no dup, the logic
> stands, even if there are many games with the same datetime stamp. It won't
> if the same player can be in many simultaneous games, like a chess master
> player playing simultaneous chess games.
>
> Vanderghast, Access MVP
>
>
>
>
> > Hi,
>
> > Interesting solution. I will have to look at it some more to totally
> > understand it.
>
> > Under the presumption from pat67's examples that a steak is three or
> > more wins/loses I think you will want to add "HAVING MAX(streak) >= 3" to
> > the
> > final query. Your data set would need to be expanded or adjusted if there
> > are multiple, simultaneous games happening. Also, I think pat67 wanted
> > both
> > winning and losing streaks. And I think the streaks were streaks on a
> > particular day, not over multiple days.
>
> > pat67: correct me if I am wrong there.
>
> > Clifford Bass
>
> > --
> > Message posted via AccessMonster.com
>
> - Show quoted text -
Wow. let me clarify more if I can. I just used 3 as an example. If a
players longest winning streak is 1 or 100, that's what I want to
show.
My data table has these fields:
GameID Date Winner Loser
I then have a union query with these fields:
Date GameID Player Opponent Result
result being Won or Lost
So that means that there are 2 results for each game id.
does that make it easier?
pat67
wrote:
Since I am using a union query I adjusted your sql. but it still isn't
working. GameDate, GameID, Player, Result (won or lost) are my
fields. here is the query
SELECT A.GameDate, A.Player, A.Result, A.GameID, B.GameID, C.GameID
FROM (qryUnion AS A INNER JOIN qryUnion AS B ON (A.Result = B.
Result) AND (A.Player = B.Player) AND (A.GameDate = B.GameDate))
INNER
JOIN qryUnion AS C ON (B.Result = C.Result) AND (B.Player = C.
Player) AND (B.GameDate = C.GameDate)
WHERE (((B.GameID)=(select Min(GameID) from qryUnion as D where D.
GameDate = A.GameDate and D.Player = A.Player and D.GameID >
A.GameID))
AND ((C.GameID)=(select Min(GameID) from qryUnion as E where E.
GameDate = B.GameDate and E.Player = B.Player and E.GameID >
B.GameID)));
the error says invalid use of '.', '!', or '()' in query expression
'A.Result=B.Resul'.
vanderghast
> Wow. let me clarify more if I can. I just used 3 as an example. If a
> players longest winning streak is 1 or 100, that's what I want to
> show.
Then you use the unmodified query (ie, without any HAVING clause; the use of
HAVING was only a remark from Clifford)
> My data table has these fields:
> GameID Date Winner Loser
> I then have a union query with these fields:
>
> Date GameID Player Opponent Result
>
> result being Won or Lost
>
> So that means that there are 2 results for each game id.
>
> does that make it easier?
What you need is (at least)
DateOfTheGame, PlayerID, IsPlayerWinOrLost
which probably could be obtained from a query like:
SELECT [date] as gameDateTimeStamp, Winner As PlayerID, true AS isWin
FROM dataTable
UNION ALL
SELECT [date], Loser, false
FROM dataTable
assuming that, from your original data table, Winner and Loser return a
PlayerID (who is the winner and who is the loser). Saving that query as
myData and using the proposed query should return what you want (the highest
winning-streak for each player).
Vanderghast, Access MVP
Clifford Bass via AccessMonster.com
Just to be sure: Is that per day? Or across all days? Do you want all
of the streaks? Or just the longest? Is that just the longest of either
winning or losing? Or the longest of both? Is a single win or loss really a
streak? That seems to be contradictory. Can a player play more than one
game at once? Do later games always have higher GameIDs then earlier games?
Other factors of importance?
Clifford Bass
pat67 wrote:
>Wow. let me clarify more if I can. I just used 3 as an example. If a
>players longest winning streak is 1 or 100, that's what I want to
>show.
>
>My data table has these fields:
>
>GameID Date Winner Loser
>
>I then have a union query with these fields:
>
>Date GameID Player Opponent Result
>
>result being Won or Lost
>
>So that means that there are 2 results for each game id.
>
>does that make it easier?
--
pat67
wrote:
>
> - Show quoted text -
longest streak. GameID is from an autonumber field in my table so it
is always increasing. 1 is a winning streak, yes technically.
pat67
Ok. that gives a result of -1 for the winners and 0 for the losers. Is
that correct? Then i use your queries you stated originally?
vanderghast
anything thing not null neither 0, but the result of a comparison is -1:
? int(3=3)
-1
Vanderghast, Access MVP
"pat67" <pbu...@comcast.net> wrote in message
news:e79cea76-fd48-4aa5...@r26g2000vbi.googlegroups.com...
pat67
> Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is
> anything thing not null neither 0, but the result of a comparison is -1:
>
> ? int(3=3)
> -1
>
> Vanderghast, Access MVP
>
>
> - Show quoted text -
That looks like it worked. It takes a while to run, but it looks right
pat67
>
> - Show quoted text -
Ok there is a problem. The queries work however i see at least one
player not right. The first night of the year he lost then won 2 in a
row and lost again so his win streak should at least be 2. it is
showing as 1. Is that because the first query returned a -1?
pat67
>
> - Show quoted text -
I checked the top rated guy who shows 12, which he is currently on. In
reality it should be 15? what could be the problem?
pat67
>
> - Show quoted text -
I think the problem is that players play multiple games on one night
and by just using the date and not a game id the software doesn't know
how many where in a row. i.e. Player A was 2 wins and 2 losses on
12/15. Without using game id the software would know he won 2 then
lost 2. it puts the streak at 1. So i added game id to the first 2
queries but the last 2 I am not sure about. here is what the first 2
look like now
myData
SELECT ID as GameID, [Date] as GameDateTimeStamp, Winner As PlayerID,
true AS isWin
FROM tblResults
UNION ALL SELECT ID, [Date], Loser, false
FROM tblResults;
qRanks
SELECT a.playerID, a.GameID, a.gameDateTimeStamp, (SELECT COUNT(*)
FROM myData AS b
WHERE b.playerID=a.playerID
AND b.gameDateTimeStamp <= a.gameDateTimeStamp) AS rank_all,
(SELECT COUNT(*)
FROM myData AS c
WHERE c.playerID=a.playerID
AND c.isWin
AND c.gameDateTimeStamp <= a.gameDateTimeStamp) AS
rank_onlyWins
FROM myData AS a
WHERE (((a.isWin)<>False))
GROUP BY a.playerID, a.GameID, a.gameDateTimeStamp;
vanderghast
value (other than 0), and a DIFFERENT one for each 'game' :
Player date isWin
john 2010.1.1 -1
tom 2010.1.1 0
john 2010.1.1 -1
mary 2010.1.1 0
is wrong for John, since (Player, date) is duplicated (first and third
record). The following would be nice, though:
Player date isWin
john 2010.1.1 10:00:00 -1
tom 2010.1.1 10:00:00 0
john 2010.1.1 11:00:00 -1
mary 2010.1.1 11:00:00 0
since then, (Player, date) has no dup anymore: John played at 10AM and at
11AM, that makes 2 different games. Without the time part, the presented
algorithm will see only one game for John, for the first of January. So the
importance to have a time part.
And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK
operator.
Vanderghast, Access MVP
"pat67" <pbu...@comcast.net> wrote in message
news:56dbfcb0-3997-49d4...@j1g2000vbm.googlegroups.com...
pat67
> If a player plays twice the same day, the [date] field must have a TIME
> value (other than 0), and a DIFFERENT one for each 'game' :
>
> Player date isWin
> john 2010.1.1 -1
> tom 2010.1.1 0
> john 2010.1.1 -1
> mary 2010.1.1 0
>
> is wrong for John, since (Player, date) is duplicated (first and third
> record). The following would be nice, though:
>
> Player date isWin
> john 2010.1.1 10:00:00 -1
> tom 2010.1.1 10:00:00 0
> john 2010.1.1 11:00:00 -1
> mary 2010.1.1 11:00:00 0
>
> since then, (Player, date) has no dup anymore: John played at 10AM and at
> 11AM, that makes 2 different games. Without the time part, the presented
> algorithm will see only one game for John, for the first of January. So the
> importance to have a time part.
>
> And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK
> operator.
>
> Vanderghast, Access MVP
>
>
> - Show quoted text -
can I use game id as opposed to time?
pat67
>
> - Show quoted text -
I mean can i concatenate the date and id to look like this 9/15/2009-1?
Clifford Bass via AccessMonster.com
I will putter around with this when I get a chance. Unless someone else
gets you to a functioning solution before I get the chance.
Clifford Bass
pat67 wrote:
>On Jan 5, 4:39 pm, "Clifford Bass via AccessMonster.com" <u48370@uwe>
>wrote:
>
>longest streak. GameID is from an autonumber field in my table so it
>is always increasing. 1 is a winning streak, yes technically.
--
pat67
>
> - Show quoted text -
ok i concatenated the date and game id. that made some changes but
still not correct. sql now like this
myData
SELECT [Date] & '-' & ID as GameDateTimeStamp, Winner As PlayerID,
true AS isWin
FROM tblResults
UNION ALL SELECT [Date] & '-' & ID, Loser, false
FROM tblResults;
problem is now top player says 13. but should be 15. I think i need to
change the date format to be 09/15/2009. with just 9/15/2009, any 12
or 11 or 10 is sorted before.
vanderghast
Indeed, if GameID is unique, then (PlayerID, GameID) will also be unique
(unless a player plays against himself!). You will have to change the SQL
statements to replace [Date] by GameID.
Vanderghast, Access MVP
vanderghast
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.
You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.
Vanderghast, Access MVP
pat67
game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?
pat67
The next question would be to get from the same tblResults, a player's
current streak, whether it's wins or losses.
i.e.
Player Streak
Bob Won 6
Jim Lost 3
Frank Won 2
I am a pain I know.
vanderghast
lost,
----------------------------------------
SELECT playerID, MAX(gameID) AS mgame
FROM data
WHERE NOT isWin
GROUP BY playerID
----------------------------------------
saved as qlatestLost
and counting the number of records coming after that date stamp, for that
player,
-------------------------------
SELECT playerID,
COUNT(qlatestLost.PlayerID) AS actualWinStreak
FROM data LEFT JOIN qlatestLost
ON data.playerID = qlatestLost.playerID
AND data.gameID > qlatestLost.mgame
-------------------------------
Sure, if a player has lost his last game, the actualWinStreak is 0, thanks
to the outer join and the COUNT(field) behavior,
but
there is a problem: if a player has not lost a single game, qlatestLoast
will return nothing too and so, the final query will also return 0 for this
player. To correct that problem, we can modify the last query to (untested)
:
---------------------------
SELECT playerID,
iif(
playerID IN(SELECT playerID FROM qlatestLost),
COUNT(qlatestLost.PlayerID),
(SELECT COUNT(*)
FROM data as b
WHERE b.playerID = a.playerID)
) AS actualWinStreak
FROM data AS a LEFT JOIN qlatestLost
ON data.playerID = qlatestLost.playerID
AND data.gameID > qlatestLost.mgame
------------------------------
or, much much less verbose, only modify the first query into:
-----------------------------
SELECT playerID, MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM data
GROUP BY playerID
----------------------------
and keep the second query unchanged. This modification simply mark the
winning games as negative (for the purpose of that query) so that if a
player never lost a game, the returned MAX will be negative and all the
games, for that player, will be counted by the final query, as we want. If
the player lost a game, the queries behave as before. So, less
modifications, but less "self documented" , enven if the first solution
(modifying the second query) is hardly what I call 'self documented' either.
Vanderghast, Access MVP
vanderghast
field (check the table design).
Vanderghast, Access MVP
"pat67" <pbu...@comcast.net> wrote in message
news:19ba49a5-0d8f-48bd...@g18g2000vbr.googlegroups.com...
pat67
Just so you know, I have a union query from my original table showing
gameID, date, player, opponent, and result. either won or lost. I need
to rank the results by player and gameID to get what is the current
streak. I am unsure how to do that. Can you help? Thanks
vanderghast
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.
SELECT PlayerID,
MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID
saved as q1. I assume your original data is in table (or query) called
data. Then a second query:
SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak
FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID
GROUP BY a.PlayerID
should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".
Vanderghast, Access MVP
pat67
my data is in a union query qryUnion with the field Player and Result.
i have a tblRosters with a Player Name field. Can you substitue those
into your query so I don't screw it up?
pat67
Let me show you my qryUnion example
GameID Player Oponnent Result
1 Bob Steve Won
2 Joe Frank Won
3 Jim Al Won
1 Steve Bob Lost
2 Frank Joe Lost
3 Al Jim Lost
Obviously it is much larger but you get the gist.What I am looking for
is this
Player W L Current Streak
Bob 1 0 Won 1
Joe 1 0 Won 1
Jim 1 0 Won 1
Steve 0 1 Lost 1
Frank 0 1 Lost 1
Al 0 1 Lost 1
Or something similar
pat67
the top query tells me the specified field playerID could refer to
more than one table in the from clause
vanderghast
MAX( iif(Result="win", -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID
saved as q1, then
SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak
FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID
GROUP BY a.PlayerID
where ListOfPlayers is a query returning all players, once.
Vanderghast, Access MVP
Clifford Bass via AccessMonster.com
So here is another way to try:
qryGame Results
SELECT GameID, [Date] AS GameDate, Winner AS Player, True AS IsWinner
FROM tblResults
UNION ALL SELECT GameID, [Date], Loser, False
FROM tblResults;
qryLongest Streaks-Part A
SELECT A.GameDate, A.Player, A.GameID AS StartGameID, B.GameID AS EndGameID,
A.IsWinner, (select count(*) from [qryGame Results] as C where C.GameDate = A.
GameDate and C.Player = A.Player and C.GameID between A.GameID and B.GameID)
AS WinLoseCount
FROM [qryGame Results] AS A INNER JOIN [qryGame Results] AS B ON (A.IsWinner
= B.IsWinner) AND (A.Player = B.Player) AND (A.GameDate = B.GameDate)
WHERE (((B.GameID)>=[A].[GameID]) AND ((Not Exists (select * from [qryGame
Results] as D where D.GameDate = A.GameDate and D.Player = A.Player and D.
GameID between A.GameID and B.GameID and D.IsWinner <> A.IsWinner))=True))
ORDER BY A.GameDate, A.Player, A.GameID, B.GameID;
qryLongest Streaks-Part B
SELECT E.GameDate, E.Player, Max(E.WinLoseCount) AS LongestStreakLength
FROM [qryLongest Streaks-Part A] AS E
GROUP BY E.GameDate, E.Player;
qryLongest Streaks-Part C
SELECT F.GameDate, F.Player, G.LongestStreakLength AS StreakLength, F.
StartGameID, F.EndGameID, IIf([IsWinner],"Winning","Losing") AS StreakType
FROM [qryLongest Streaks-Part A] AS F INNER JOIN [qryLongest Streaks-Part B]
AS G ON (F.GameDate = G.GameDate) AND (F.Player = G.Player) AND (F.
WinLoseCount = G.LongestStreakLength)
ORDER BY F.GameDate, F.Player, F.StartGameID;
This will report the length of the longest streak for each person on a
particular day. It will display the start and end GameIDs of the streak and
whether or not the streak was a winning or losing streak. If the person had
several streaks of that longest streak length, all of those streaks will show.
Clifford Bass
--
Message posted via http://www.accessmonster.com