Todo:
- Integrate the new SQL query for turnovers and rebounds into existing Super Shot Search SQL (SSSSQL). (30 minutes)
- Display the filtered data on my Super Shot Search (SSS) web site. (20 minutes)
- If I get that done in a reasonable time, look into getting the rebound data for all teams in tabular form with a quick query. (30 minutes)
- If that gets done, add a UI for it on SSS.
- Ignore twitter.
- Don't spend more time blogging than coding.
Integrating
I just opened up my usual MySql IDE, MySql Workbench, but then remembered there's a much nicer IDE called HeidiSQL which has table and field auto-completion which should really be a standard for any IDE. So let's install that. (Took 2 minutes to install and configure and run...sweet) Oh cool HeidiSQL even shows the size on disk of the tables in your schemas. Wicked....Looking at the code and I should have commented which is the working piece of code and a data set to compare it too...I'm running some of these queries and they are taking a long time which makes me think I need a new computer...So I think I have a working query but I need to compare it to a game...so opening up a play-by-play, and I'll compare my calculated results with what's on paper. Well I couldn't believe there were no rebounds in this game for Toronto, but I checked the play by play and there were none. Now checking Buffalo's..they have exactly 1 rebound. Well, that's a large enough sample size to check :).
Rebound times for all teams
This was more interesting to me than adding the UI, so I did this yet. First, a note on performance. I went from a query that was wrong, and took 55 s, to a correct query that took 4 s, to an even better query that takes a half of a second, to a query for all teams that takes 51 seconds (performance will be better on a production database). The obvious clue I missed when designing the query was you just have to check the play immediately before a short or goal: if that play was a shot, and the time is less than 3 seconds, that is a rebound.
What teams got the most rebounds last season?
| Rebounds | Team |
|---|---|
| 146 | Colorado Avalanche |
| 143 | San Jose Sharks |
| 140 | Philadelphia Flyers |
| 138 | Carolina Hurricanes |
| 128 | Los Angeles Kings |
| 122 | St. Louis Blues |
| 117 | Phoenix Coyotes |
| 114 | Calgary Flames |
| 114 | New Jersey Devils |
| 112 | Pittsburgh Penguins |
| 111 | New York Islanders |
| 105 | Montreal Canadiens |
| 103 | Florida Panthers |
| 103 | New York Rangers |
| 102 | Chicago Blackhawks |
| 99 | Winnipeg Jets |
| 94 | Minnesota Wild |
| 94 | Boston Bruins |
| 93 | Ottawa Senators |
| 93 | Columbus Blue Jackets |
| 92 | Buffalo Sabres |
| 86 | Tampa Bay Lightning |
| 84 | Nashville Predators |
| 83 | Anaheim Ducks |
| 79 | Washington Capitals |
| 77 | Edmonton Oilers |
| 76 | Vancouver Canucks |
| 74 | Dallas Stars |
| 72 | Toronto Maple Leafs |
| 71 | Detroit Red Wings |
I don't see much correlation between good teams and getting rebounds...That's all for tonight.
