Database question

NCBeachBum · 01-10-2019, 11:33 AM

I've run into a rather odd one. In the database dump for pitchers, there are three separate abbreviations that would seem to apply to runs allowed for pitchers, R, RA, and ER. One would be total runs allowed, one would be earned runs, what the hell is the third? Is it something else entirely that I'm missing?

Even odder, none of the three correctly track to league ERA. This:

Round((HSPitchingTotals.er/HSPitchingTotals.ip*9),2) AS ERA

comes back with an ERA of 7.45 (important note, IP here is the sum of outs divided by 3 from the database, not the full inning excluding partials that the database itself calls IP... all others mentioned here are simple leaguewide sums of the stat in question), while this:

Round((HSPitchingTotals.r/HSPitchingTotals.ip*9),2) AS ERA

comes back as 8.47, and finally this:

Round((HSPitchingTotals.ra/HSPitchingTotals.ip*9),2) AS ERA

comes back as 2.70. A league FIP calc comes up to 5.01. But even if defense could somehow explain it, how could ra be the lowest of the three? I have to be missing something blindingly obvious here, but damned if I can see it. What are these three abbreviations?

NCBeachBum · 01-10-2019, 11:39 AM

Just to make certain I was clear there, I mean this is the items to which the ones above direct:

Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.outs/3,1)) AS IP

Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.r,0)) AS R

Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.er,0)) AS ER

Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.ra,0)) AS RA

cavebutter · 01-10-2019, 12:43 PM

I'm going to guess that RA is not what you think it is.

My evidence, on the weak side, but:
* RA is between blown saves and complete games in the dump, whereas R and ER are grouped together
* 'RA' is not a metric we're familiar with
* Your calc of ERA using RA is so wildly at variance with the other two that another explanation is necessary
* Wikipedia tells me this is called Run Average and is simply 9 * R/IP

Not sure why the game calcs this for us when it makes us do almost all of the other calculated stats on our own - also not sure why it's an integer. My advice is to ignore it.

Side question, what is your setup that you have level_id=11?

NCBeachBum · 01-10-2019, 01:09 PM

Quote:

Originally Posted by cavebutter

I'm going to guess that RA is not what you think it is.

My evidence, on the weak side, but:
* RA is between blown saves and complete games in the dump, whereas R and ER are grouped together
* 'RA' is not a metric we're familiar with
* Your calc of ERA using RA is so wildly at variance with the other two that another explanation is necessary
* Wikipedia tells me this is called Run Average and is simply 9 * R/IP

Not sure why the game calcs this for us when it makes us do almost all of the other calculated stats on our own - also not sure why it's an integer. My advice is to ignore it.

Side question, what is your setup that you have level_id=11?

Ah, no, that is a misunderstanding. My fault. I'm trying to automate figuring league ERA (actually, level ERA in this instance, for HS kids, which is why it's set for 11) to get a correct modifier for FIP. The calc for FIP I mentioned above used the basic 3.2, but I want to correctly scale it to league ERA. To do so, I need the actual level ERA, but none of those three work. If I use er, which at first blush seems the obvious choice, then I get a modifer of 7.95, which just seems utterly impossible, especially given that this data covers a 30+ year period.

Edit: Oops, I get what you're saying now. So that definitely eliminates ra as a possibility, and explains the huge difference there. So apparently it is r and er. Which would seem to point to high school defenses being Little League quality, but I've never noticed that being the case looking at draft classes. Hmm. I need to go look into that a bit deeper and see if that is indeed what is happening.

NCBeachBum · 01-10-2019, 01:28 PM

Well, damn, that is the answer. Somehow I've never before noticed how truly terrible HS defense is, likely because I've mostly only ever looked at the draft-worthy players and never saw the chaff dragging the median down. Just a quick defensive efficiency calc for each level:

HS: .591
COL: .643
Rookie: .671
A: .682
AA: .688
AAA: .688
MLB: .689

cavebutter · 01-10-2019, 02:04 PM

Maybe just me, but a High School ERA north of 7 doesn’t seem out of this world.

I think you’ve got to go with ER for the league ERA calc. Assuming the game accumulates the stats properly, and there’s no reason to suspect it doesn’t...

Sent from my iPhone using Tapatalk

BMW · 01-10-2019, 03:55 PM

I looked through my code and data, and I'm drawing a blank on ra as well. Typically "a" at the end of means "allowed" (ha = hits allowed, hra = home runs allowed).

I can see data in there, but I'm not connecting with what it could be.

As for ERA not calculating, ER*9/IP won't do it. There is an additional field called IPF which is "IP fractions". If a pitcher has 120 and 2/3 innings, he'll have 120 in IP and 2 in IPF. In my code, I always pre-calculate an ip_actual (120.666666) and an ip_visual (120.2) to quickly retrieve what I want.

Edit: Whoops, didn't notice that the OP was calculating their own field IP from Outs, and not referring to the IP field in the data.

cavebutter · 01-10-2019, 03:59 PM

Quote:

Originally Posted by BMW

As for ERA not calculating, ER*9/IP won't do it. There is an additional field called IPF which is "IP fractions". If a pitcher has 120 and 2/3 innings, he'll have 120 in IP and 2 in IPF. In my code, I always pre-calculate an ip_actual (120.666666) and an ip_visual (120.2) to quickly retrieve what I want.

The IPF isn’t needed if you’re using outs/3.

Sent from my iPhone using Tapatalk

Bluenoser · 01-10-2019, 04:01 PM

RA - "Relief Appearance" - A count of the games in which this pitcher has appeared but not as the starting pitcher.

From the OOTP Manual - the glossary at the very end has them.

http://manuals.ootpdevelopments.com/...php?man=ootp19

cavebutter · 01-10-2019, 04:07 PM

That totally makes sense. Who’d have thunk it...checking the manual.

Sent from my iPhone using Tapatalk

cavebutter · 01-10-2019, 04:31 PM

That totally makes sense. Who’d have thunk it...checking the manual.

Sent from my iPhone using Tapatalk

NCBeachBum · 01-11-2019, 04:35 PM

I'm not gonna lie, I stopped checking the manual about eight to ten years ago. At the time, a lot of the info in it was a version or two behind, and after I just forgot it existed.

01-10-2019, 11:33 AM	#1
NCBeachBum All Star Starter Join Date: Jul 2003 Location: Topsail Island, NC, USA Posts: 1,049	Database question I've run into a rather odd one. In the database dump for pitchers, there are three separate abbreviations that would seem to apply to runs allowed for pitchers, R, RA, and ER. One would be total runs allowed, one would be earned runs, what the hell is the third? Is it something else entirely that I'm missing? Even odder, none of the three correctly track to league ERA. This: Round((HSPitchingTotals.er/HSPitchingTotals.ip9),2) AS ERA comes back with an ERA of 7.45 (important note, IP here is the sum of outs divided by 3 from the database, not the full inning excluding partials that the database itself calls IP... all others mentioned here are simple leaguewide sums of the stat in question), while this: Round((HSPitchingTotals.r/HSPitchingTotals.ip9),2) AS ERA comes back as 8.47, and finally this: Round((HSPitchingTotals.ra/HSPitchingTotals.ip*9),2) AS ERA comes back as 2.70. A league FIP calc comes up to 5.01. But even if defense could somehow explain it, how could ra be the lowest of the three? I have to be missing something blindingly obvious here, but damned if I can see it. What are these three abbreviations? __________________ And ain't that a shame, shame, shame Shame, shame, the way you do Oh, it's a shame, shame, shame Shame, shame on you

01-10-2019, 11:39 AM	#2
NCBeachBum All Star Starter Join Date: Jul 2003 Location: Topsail Island, NC, USA Posts: 1,049	Just to make certain I was clear there, I mean this is the items to which the ones above direct: Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.outs/3,1)) AS IP Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.r,0)) AS R Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.er,0)) AS ER Sum(Iif(Players_career_pitching_stats.level_id=11 AND Players_career_pitching_stats.split_id=1, Players_career_pitching_stats.ra,0)) AS RA __________________ And ain't that a shame, shame, shame Shame, shame, the way you do Oh, it's a shame, shame, shame Shame, shame on you

01-10-2019, 12:43 PM	#3
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,163	I'm going to guess that RA is not what you think it is. My evidence, on the weak side, but: * RA is between blown saves and complete games in the dump, whereas R and ER are grouped together * 'RA' is not a metric we're familiar with * Your calc of ERA using RA is so wildly at variance with the other two that another explanation is necessary * Wikipedia tells me this is called Run Average and is simply 9 * R/IP Not sure why the game calcs this for us when it makes us do almost all of the other calculated stats on our own - also not sure why it's an integer. My advice is to ignore it. Side question, what is your setup that you have level_id=11? __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

01-10-2019, 01:28 PM	#5
NCBeachBum All Star Starter Join Date: Jul 2003 Location: Topsail Island, NC, USA Posts: 1,049	Well, damn, that is the answer. Somehow I've never before noticed how truly terrible HS defense is, likely because I've mostly only ever looked at the draft-worthy players and never saw the chaff dragging the median down. Just a quick defensive efficiency calc for each level: HS: .591 COL: .643 Rookie: .671 A: .682 AA: .688 AAA: .688 MLB: .689 __________________ And ain't that a shame, shame, shame Shame, shame, the way you do Oh, it's a shame, shame, shame Shame, shame on you

01-10-2019, 02:04 PM	#6
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,163	Maybe just me, but a High School ERA north of 7 doesn’t seem out of this world. I think you’ve got to go with ER for the league ERA calc. Assuming the game accumulates the stats properly, and there’s no reason to suspect it doesn’t... Sent from my iPhone using Tapatalk __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

01-10-2019, 03:55 PM	#7
BMW Hall Of Famer Join Date: May 2003 Location: New Jersey Posts: 2,030	I looked through my code and data, and I'm drawing a blank on ra as well. Typically "a" at the end of means "allowed" (ha = hits allowed, hra = home runs allowed). I can see data in there, but I'm not connecting with what it could be. As for ERA not calculating, ER9/IP won't do it. There is an additional field called IPF which is "IP fractions". If a pitcher has 120 and 2/3 innings, he'll have 120 in IP and 2 in IPF. In my code, I always pre-calculate an ip_actual (120.666666) and an ip_visual (120.2) to quickly retrieve what I want. Edit: Whoops, didn't notice that the OP was calculating their own field IP from Outs, and not referring to the IP field in the data. __________________ Last edited by BMW; 01-10-2019 at 04:13 PM.*

01-10-2019, 04:01 PM	#9
Bluenoser Hall Of Famer Join Date: Mar 2002 Location: In The Moment Posts: 14,341	RA - "Relief Appearance" - A count of the games in which this pitcher has appeared but not as the starting pitcher. From the OOTP Manual - the glossary at the very end has them. http://manuals.ootpdevelopments.com/...php?man=ootp19 Last edited by Bluenoser; 01-10-2019 at 04:02 PM.

01-10-2019, 04:07 PM	#10
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,163	That totally makes sense. Who’d have thunk it...checking the manual. Sent from my iPhone using Tapatalk __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

01-10-2019, 04:31 PM	#11
cavebutter All Star Starter Join Date: Dec 2005 Location: Los Angeles Posts: 1,163	That totally makes sense. Who’d have thunk it...checking the manual. Sent from my iPhone using Tapatalk __________________ MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP Logo Gallery

01-11-2019, 04:35 PM	#12
NCBeachBum All Star Starter Join Date: Jul 2003 Location: Topsail Island, NC, USA Posts: 1,049	I'm not gonna lie, I stopped checking the manual about eight to ten years ago. At the time, a lot of the info in it was a version or two behind, and after I just forgot it existed. __________________ And ain't that a shame, shame, shame Shame, shame, the way you do Oh, it's a shame, shame, shame Shame, shame on you