Ygthkb

I have a table Schedule for a local high school football conference. This is in MySQL. My goal is to include the "streak" for each team. If a team wins 2 games in a row the streak is W2, or if a team loses 4 games in a row, the streak would be L4.

Here's my table schema:

CREATE TABLE `Schedule` (

  `ScheduleId` int(10) NOT NULL auto_increment COMMENT 'Schedule Id',

  `SportId` int(11) NOT NULL COMMENT 'Sport Id',

  `HomeTeamId` int(10) NOT NULL COMMENT 'Home Team Id',

  `AwayTeamId` int(11) NOT NULL COMMENT 'Away Team Id',

  `StartTime` datetime NOT NULL COMMENT 'Start Time of Event',

  `HomeTeamFinal` double default NULL COMMENT 'Final Score of Home Team',

  `AwayTeamFinal` double default NULL COMMENT 'Final Score of Away Team',

  `IsRegularSeason` bit(1) NOT NULL default b'1',

PRIMARY KEY  (`ScheduleId`)

)

I currently have a "Standings" query that shows the Won, Loss, Total, PF, PA, Percentage and Games Behind or each team. However, with this, I wanted to include the streak of each team.

My Standings SQL is as follows:

SELECT  T.SchoolName, R.Won AS W, R.Lost AS L, R.Total AS T, R.PF, R.PA, R.GB, R.Percentage AS P

FROM Teams AS T

INNER JOIN (

        SELECT  TeamId, 

                        SUM(Win) As Won, 

                        SUM(Loss) as Lost, 

                        SUM(Win+Loss) as Total,

                        PtsFor AS PF,

                        PtsAgainst AS PA,

                        CASE WHEN (SELECT SUM(Win-Loss) As WLSpan FROM WinsLosses GROUP BY TeamId Order By Win DESC LIMIT 1)-(SUM(Win)-SUM(Loss))=0 THEN '--'

                            ELSE ROUND(ABS((SELECT SUM(Win-Loss) As WLSpan FROM WinsLosses GROUP BY TeamId Order By Win DESC LIMIT 1)-(SUM(Win)-SUM(Loss)))/2,1) END GB,

                    ROUND((SUM(Win)/SUM(Win+Loss)*100),2) AS Percentage

        FROM WinsLosses AS R

        GROUP BY TeamId

        ORDER By Won DESC

) R ON T.Id = R.TeamId

ORDER BY R.PF DESC, T.SchoolName

And my WinsLosses view is:

select 

    `Schedule`.`HomeTeamId` AS `TeamId`,

    (case when (`Schedule`.`HomeTeamFinal` > `Schedule`.`AwayTeamFinal`) then 1 else 0 end) AS `Win`,

    (case when (`Schedule`.`HomeTeamFinal` < `Schedule`.`AwayTeamFinal`) then 1 else 0 end) AS `Loss`,

    `Schedule`.`HomeTeamFinal` AS `PtsFor`,

    `Schedule`.`AwayTeamFinal` AS `PtsAgainst` 

from `Schedule` 

where ((`Schedule`.`HomeTeamFinal` != '0') and (`Schedule`.`AwayTeamFinal` != '0')) 



union all 



select 

    `Schedule`.`AwayTeamId` AS `TeamId`,

    (case when (`Schedule`.`AwayTeamFinal` > `Schedule`.`HomeTeamFinal`) then 1 else 0 end) AS `Win`,

    (case when (`Schedule`.`AwayTeamFinal` < `Schedule`.`HomeTeamFinal`) then 1 else 0 end) AS `Loss`,

    `Schedule`.`AwayTeamFinal` AS `PtsFor`,

    `Schedule`.`HomeTeamFinal` AS `PtsAgainst` 

from `Schedule` 

where ((`Schedule`.`AwayTeamFinal` != '0') and (`Schedule`.`HomeTeamFinal` != '0'))

I've been trying to figure this out for a couple days and am stuck. How would you best go about doing a streak with this database schema.

So I actually figured this out in Postgres and then translated, my apologies if I missed a bit of syntax. This will show the win/loss streak for every game. I did not take different sportids into account here.

create table `winloss`

(

    `counter` int(10) auto_increment,

    `teamid` int(10),

    `starttime` datetime,

    `iswin` int(10),

    `winstreak` int(10),

    `losestreak` int(10)

);



--enter all teams in a single row with if they won or lost, order by teamid and gametime

insert into winloss (teamid, starttime, iswin)

select hometeamid teamid, starttime, case when hometeamfinal>awayteamfinal 

then 1 else 0 end iswin

from Schedule s

union all

select awayteamid teamid, starttime, case when hometeamfinal<awayteamfinal 

then 1 else 0 end iswin

from Schedule s

order by teamid, starttime;



DECLARE wonlost_counter int;

DECLARE wonlost_teamid int;



DECLARE prevwonlost_counter int;

DECLARE prevwonlost_iswin int;

DECLARE prevwonlost_winstreak int;

DECLARE prevwonlost_losestreak int;



DECLARE i int := 1;

DECLARE endi int;



--how many are in the table

SELECT endi=MAX(counter) FROM winloss;



--at each row, determine if this win/loss is a streak and add on

WHILE (i <= endi) DO

    SELECT wonlost_counter=counter, wonlost_teamid=teamid FROM winloss WHERE counter=i;



    select prevwonlost_counter=counter, prevwonlost_iswin=iswin, 

        prevwonlost_winstreak=winstreak, prevwonlost_losestreak=losestreak

    from winloss where counter = wonlost_counter-1 and teamid=wonlost_teamid;



    UPDATE winloss

    SET winstreak=

        CASE WHEN iswin=1 and prevwonlost_counter is not null and prevwonlost_iswin=1 then prevwonlost_winstreak+1

            when iswin=1 and prevwonlost_counter is not null and prevwonlost_iswin=0 then 1

            when iswin=0 then 0

            else 1

            end,

        losestreak= 

        CASE WHEN iswin=0 and prevwonlost_counter is not null and prevwonlost_iswin=0 then prevwonlost_losestreak+1

            when iswin=0 and prevwonlost_counter is not null and prevwonlost_iswin=1 then 1

            when iswin=1 then 0

            else 1

            end

    WHERE counter = wonlost_counter;



    i := i + 1;



END WHILE;



select * from winloss;