Saturday, February 23, 2013

Adding Rebounds and Turnovers to Super Shot Search


  1. Integrate the new SQL query for turnovers and rebounds into existing Super Shot Search SQL (SSSSQL). (30 minutes)
  2. Display the filtered data on my Super Shot Search (SSS) web site. (20 minutes)
  3. 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)
  4. If that gets done, add a UI for it on SSS.
  5. Ignore twitter.
  6. Don't spend more time blogging than coding.


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 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.