Calculating Stats & Optimize Query

[sionainn]

Hello, I am working on a web site for statistics and am hoping someone can help me with several issues:

1) I want to find the current streak for each time. For example, Team A has won the last 5 games, so their streak would be '5 wins', and Team B lost their last games, but won the game before, so their streak would be '1 loss'

2) I know how to calculate the ranking order/position for the teams in a season, but is there a way to make a temporary column with the rank in it so the table will be sortable by visitors, without having to use php to input the rank number?

3) How do I write the following query better? I am sure this will bring cringes, but I am fairly new to more advanced queries beyond simple selects, so please don't be too harsh. :)


SELECT
team_id AS id,
team_name AS name,
(SELECT COUNT(game_id) FROM nfl_games WHERE ((game_homeTeam = id && game_homeScore > game_awayScore) || (game_awayTeam = id && game_awayScore > game_homeScore)) && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS wins,
(SELECT COUNT(game_id) FROM nfl_games WHERE (game_homeTeam = id || game_awayTeam = id) && (game_homeScore > 0 || game_awayScore > 0) && game_awayScore=game_homeScore && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS ties,
(SELECT COUNT(game_id) FROM nfl_games WHERE (((game_homeTeam = id) && game_homeScore < game_awayScore) || ((game_awayTeam = id) && (game_awayScore < game_homeScore))) && (game_dateTime > 1136073600) && (game_dateTime < 1167609599)) AS losses,
(SELECT wins + ties + losses) as total_games,
(SELECT SUM(game_homeScore) FROM nfl_games WHERE game_homeTeam = id && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS home_points,
(SELECT SUM(game_awayScore) FROM nfl_games WHERE game_awayTeam = id && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS away_points,
(SELECT IFNULL(home_points,0) + IFNULL(away_points,0)) AS points_for,
(SELECT FORMAT((points_for/total_games),3)) as game_average,
(SELECT FORMAT((wins/total_games),3)) as win_percentage,
(SELECT SUM(game_awayScore) FROM nfl_games WHERE (game_homeTeam = id) && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS opp_home_points,
(SELECT SUM(game_homeScore) FROM nfl_games WHERE game_awayTeam = id && game_dateTime > 1136073600 && game_dateTime < 1167609599) AS opp_away_points,
(SELECT IFNULL(opp_home_points,0) + IFNULL(opp_away_points,0)) AS points_against
FROM nfl_teams
ORDER BY wins DESC, ties DESC, losses ASC


Thank you very much for any help you can give!

[guelphdad]

Question #3) There are a number of things to clear up in your query. First don't use && use AND. Don't use || use OR those are standard in SQL while && and || are not.

Don't use IF and IFNULL those aren't standard sql, use COALESCE where needed.

You also don't need to write each of those counts as separate selects. That is more resource intensive than need be.

Next remove your date calculations, since they are all the same, and move them to a where clause before your order by.

I will rewrite this query for you if I have time tonight and show you.

Question #2 you would calculate points earned by the teams as an aggregate column and allowing the user to order that would be the same as ordering by rank

as for Question #1 you would write a query that shows wins, losses and ties and use a group concat on that. you would then use EXPLODE in php and check each value of the arrray, from the most recent item on the end, and if the status changes from Win to Loss or vice-versa, then you know your streak has ended.

[guelphdad]

This should be a better, more effecient query for you, note it is only a single select and should you need to change your dates you can do that easily in the where clause:


SELECT
team_id AS id,

team_name AS name,

COUNT(case when
(game_homeTeam = id AND game_homeScore > game_awayScore) OR
(game_awayTeam = id AND game_awayScore > game_homeScore) then 1 else 0 end) AS wins,

COUNT(case when
(game_homeTeam = id AND game_homeScore = game_awayScore) OR
(game_awayTeam = id AND game_awayScore = game_homeScore) then 1 else 0 end) AS ties,

COUNT(case when
(game_homeTeam = id AND game_homeScore < game_awayScore) OR
(game_awayTeam = id AND game_awayScore < game_homeScore) then 1 else 0 end) AS losses,


sum(wins + ties + losses) as total_games,

SUM(case when game_homeTeam = id then game_homeScore else 0 end) AS home_points,

SUM(case when game_awayTeam = id then game_awayScore else 0 end) AS away_points,

SUM(case when game_awayTeam = id then game_homeScore else 0 end) AS opp_home_points,

SUM(case when game_homeTeam = id then game_awayScore else 0 end) AS opp_away_points,


sum(home_points + away_points) AS points_for,

sum(opp_home_points + opp_away_points) AS points_against,

round((points_for/total_games),3)) as game_average,

round((wins/total_games),3)) as win_percentage,


FROM nfl_teams
where game_dateTime between 1136073600 AND 1167609599

ORDER BY wins DESC, ties DESC, losses ASC

[sionainn]

guelphdad thank you very much for your help! :) I tried the query, but realized that this won't work since it is only pulling from one table, when it needs two. I will try to see if I can use your query as a reference for the join I will need.

I guess it would have helped if I had posted my table set up as well :(


table: nfl_teams
team_id | team_name

table: nfl_games
game_id | game_dateTime | game_awayTeam | game_homeTeam | game_homeScore | game_awayScore


Again, thank you very much for you help! :)

[guelphdad]

You can modify the query as follows:

change:

FROM nfl_teams
where game_dateTime between 1136073600 AND 1167609599

ORDER BY wins DESC, ties DESC, losses ASC


to this:


from
nfl_teams
left join nfl_games
on id in (game_homeTeam,game_awayTeam)

and game_dateTime between 1136073600 AND 1167609599

GROUP BY
id,
name

ORDER BY wins DESC, ties DESC, losses ASC