MySQL: How To Sum Innings Pitched
Posted Tuesday April 15, 2014 at 12:36:54 am in Technology
Okay, a bit of an odd title I know. But this is where the intersection of programming and baseball happen. And when someone asks when does 0.1 plus 0.2 equal 1...you will know why. Read on.
Okay, so we know in an inning of baseball there are 3 outs. Yes, you could argue in a full inning there are 6 outs (when including both teams), but I'm speaking strictly from a single team's perspective. So keep that in mind as you read the rest of this.
Let's take the case of a single inning. A pitcher could get:
- 0 outs
- 1 out
- 2 outs
- 3 outs
In each of those situations, mathematically you can represent them as follows:
- 0.0 IP
- 0.1 IP
- 0.2 IP
- 1.0 IP
What you might notice here is a pattern. Quite simply a pattern that allows you to add innings in such a way that 0.1 plus 0.2 equals 1.0. And 0.1 plus 0.1 equals 0.2. And so on and so forth. This also means that you can have a situation where someone says: Pitcher A pitched a third of an inning. And what that means is that they pitched 0.1 innings. It's quite interesting because in basic mathematics we know that a third is 1/3. And one third of 1 is 0.3333 (repeating, of course). Of course this is a Leeroy Jenkins quote:
This might seem rudimentary to anyone that doesn't want to sum innings pitched from a database standpoint. But once you start involving databases...it actually gets a little more complicated (not tremendously, just a little more).
The reason being is that the concept of the SUM function when it comes to database systems like that of MySQL (and most, really) assume that you're doing basic addition. To those systems, 0.1 + 0.2 is actually 0.3 not 1.0. So, we run into quite a dilemma if we trying summing 3 games pitched for a pitcher in which the database records look like this:
- ID: 1, IP: 0.2
- ID: 2, IP: 0.3
- ID: 3, IP: 0.1
If we then did a typical SQL statement like this:
SELECT SUM(IP) FROM PlayerGame;
You'll actually get the following returned: 0.6
Which is, of course, incorrect for our case. The correct answer is 2.0.
And before you ask...well, can't we just divide 0.6 by 0.3 to net us 2.0? YES! You can! Unfortunately this kind of voodoo witchcraft doesn't apply to this scenario:
- ID: 1, IP: 1.0
- ID: 2, IP: 1.0
- ID: 3, IP: 1.0
If you ran that SQL statement and got 3.0 and then divided by 0.3 you'd get 10 which is ridiculously incorrect.
Yes, sadly, we can't JUST use the built in SUM function. Of course, there are alternatives. One alternative is to go the route of creating our own user defined aggregate function. But, this is actually rather tedious (especially in MySQL). In case you're curious on how to do such, you can read this great Database Administrators Stack Exchange post here:
Here's a hint: you're not writing one in SQL. It's going to be either in C or C++. Read more from the MySQL 5.5 reference manual here.
So to appease your fears of me writing a blog entry n creating user defined functions in C/C++ for MySQL, I'm actually going to tell you right now we can accomplish adding innings pitched in a MySQL statement by using TRUNCATE, SUM, some addition, some subtraction and some division.
- But here's a fair warning: It's not pretty.
- And here's another warning: I'm damn sure it can be cleaned up and optimized.
- And a final warning: I'm not going to clean it up. Yet.
Now that that's out of the way. I'm going to actually paste the SQL that will solve our problem. And if you're bored you can read further to see how I accomplished it.
SELECT TRUNCATE(((SUM(TRUNCATE(IP,0)) + TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)) + (TRUNCATE((((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3) - TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)),1) / 3)),1) AS IP FROM PlayerGame;
Breaking It Down
In case you're counting (you're probably not) that's a total of:
- 8 TRUNCATE calls
- 7 SUM calls
- 2 addition operations
- 4 subtraction operations
- 4 division operations
25 total calls/operations just so we can sum up innings pitched properly.
What we basically need to do is break this down into a different approach. We need to do the following:
- Add all whole number portions as you'd typically do. SUM works just fine here.
- Any fractional portions will need to be treated differently.
Okay, so how do we just SUM the whole number portion? This is actually really easy
SELECT SUM(TRUNCATE(IP,0)) FROM PlayerGame;
Yup, that'll get us the sum of all whole number innings pitched. So easy, right?
Well, unfortunately those damn partial innings are going to come back to haunt us. And they're responsible for the rest of that SQL statement from hell. Read on.
How The Hell Do I Sum Partial Innings???
It's actually a lot easier than you think. Let me breakdown The Answer into meaningful chunks so we can envision what the following SQL statement means:
TRUNCATE(((SUM(TRUNCATE(IP,0)) + TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)) + (TRUNCATE((((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3) - TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)),1) / 3)),1) AS IP
For this example:
- ID: 1, IP: 1.2
- ID: 2, IP: 1.1
- ID: 3, IP: 1.2
This means we:
- Sum only the whole number (truncating each innings pitched record)
- Subtract that result from the sum of all the innings pitched, this leaves just a fraction that we need to convert to get a whole number (and fraction of an inning if necessary)
- 3.5 - 3.0 = 0.5
- SUM(IP) - SUM(TRUNCATE(IP,0))
- Divide 0.3 and then truncate so get the whole number from that result. The 0.3 is a representation of a full inning.
- 0.5 / 0.3 = 1.66666666667 truncated is 1
- TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)
- Add that value to the existing whole number value
- 3.0 + 1.0 = 4.0
- SUM(TRUNCATE(IP,0)) + TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0))
- Get the decimal value of the remainder (from 1.66666666667 we want to get 0.66666666667 and then truncate to 1 decimal place to get 0.6)
- (TRUNCATE((((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3) - TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)),1)
- Take that number and then divide by 3
- 0.6/3 = 0.2
- (TRUNCATE((((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3) - TRUNCATE(((SUM(IP) - SUM(TRUNCATE(IP,0))) / 0.3),0)),1) / 3))
- Add that decimal value to the whole number truncate to 1 decimal place.
- 4.0 + 0.2 = 4.2
There are more TRUNCATE's than you might think because MySQL will return many significant digits...when in baseball we really only need 1 decimal point.
And you can verify this because 1.2 + 1.1 + 1.2 does indeed equal 4.2 IP in baseball.
Anyways, hope this was interesting! Happy coding.
The opinions expressed herein are my own personal opinions and do not represent my employer’s view
© Copyright 2012, Stephen Adams