{"id":108,"date":"2017-09-27T09:09:27","date_gmt":"2017-09-27T16:09:27","guid":{"rendered":"http:\/\/www.ootp.cavebutter.net\/blog\/?p=108"},"modified":"2017-09-27T09:10:02","modified_gmt":"2017-09-27T16:10:02","slug":"run-environment-4-run-values-a-different-way","status":"publish","type":"post","link":"http:\/\/www.ootp.cavebutter.net\/blog\/archives\/108","title":{"rendered":"Run Environment 4: Run Values A Different Way"},"content":{"rendered":"

My attempt in the last post<\/a> resulted in a view of linear weights that, while it seemed to work, was very unwieldy. \u00a0It was so because for each of the linear weights, I had to spell out fully the calculations for runPlus, runMinus, and wobaScale.<\/p>\n

I’ve been aware of this code<\/a>\u00a0from Colin Wyers for a while now, but wanted to try my hand at translating Tango’s views into MySQL on my own. \u00a0I’ve done that. \u00a0Now, I’m going to try to pick apart how Wyers uses variables to simplify the process and configure this back to my OOTP tables. \u00a0Probably best to have that link open in another tab while I walk through this.<\/p>\n

I’ll be breaking this across several posts.<\/p>\n

We’re going to ignore the first statement that creates a primary position (PrimPos) table. \u00a0As mentioned before, this table would serve to eliminate stats created by non-pitchers pitching. \u00a0My feeling is that they are part of the run environment and should be included and, besides, this is a rarer event in OOTP than it is in real life.<\/p>\n

The second thing to note is that Wyers is creating a table rather than a view. \u00a0This is actually a good idea. \u00a0Views save disk space but have slower performance than tables. \u00a0That’s because views are essentially stored queries that are run every time they are accessed. \u00a0Tables are created once and saved as a data object in their own right. \u00a0Since space is not a concern for me and all of the advanced stats will require complex queries, I think that opting for performance here is the right thing to do.<\/p>\n

Next, I am going to leave in place my view vLeagueRunsPerOut – the foundational view\/table. \u00a0They’re too similar for me to make any changes. \u00a0Besides, I like that I thought to total up league Plate Appearances in mine. \u00a0It comes in handy later.<\/p>\n

Minor changes to the RunValues table give me this:<\/p>\n

DROP TABLE IF EXISTS tblRunValues;\r\nCREATE TABLE IF NOT EXISTS tblRunValues\r\nAS SELECT year\r\n, league_id\r\n, RperOut \r\n, @rb := RperOut+0.14 AS runBB \r\n, @rb+0.025 AS runHB \r\n, @rs := @rb+0.155 AS run1B \r\n, @rd := @rs+0.3 AS run2B \r\n, @rd+0.27 AS run3B \r\n, 1.4 AS runHR \r\n, 0.2 AS runSB \r\n, 2*RperOut+0.075 AS runCS \r\nFROM vLeagueRunsPerOut;<\/code><\/pre>\n
 <\/p>\n
Two things are happening here that I didn’t think we could do. \u00a0First, declaring variables with just the ‘@’ and the ‘:=’ operator. \u00a0Murach<\/a>, whose book I’ve been using to learn MySQL, doesn’t mention this as an option. \u00a0He does usually point out shortcuts that some devs take before advising against using them yourself. \u00a0This squares with the other web searches I’ve done regarding variables in MySQL. \u00a0But, it seems to work, so…<\/p>\n
Second, I could have sworn that I had read that you can’t rely on how MySQL will evaluate variables when used like this. \u00a0That is, there’s not a guarantee that it will evaluate ‘@rb’ first and use that value to evaluate ‘@rs’. \u00a0It could decide to do\u00a0‘@rs’ first and force ‘@rb’ to null. \u00a0That didn’t happen here, and I am a little confused. (NOTE: I emailed Wyers about this and will update if he gets back to me.)<\/p>\n
Bottom line, though, is that this worked. \u00a0Comparing the results from my original view without variables to Wyers’ table with them shows identical results if we disregard all of the trailing 0’s:<\/p>\n
\"\"
Oirignal View created without variables<\/figcaption><\/figure>\n
\"\"
Results from modified RunValues table with variables<\/figcaption><\/figure>\n
OK then. \u00a0On to the next one.<\/p>\n","protected":false},"excerpt":{"rendered":"
My attempt in the last post resulted in a view of linear weights that, while it seemed to work, was very unwieldy. \u00a0It was so because for each of the linear weights, I had to spell out fully the calculations for runPlus, runMinus, and wobaScale. I’ve been aware of this code\u00a0from Colin Wyers for a… Continue reading Run Environment 4: Run Values A Different Way<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[23],"tags":[14,17,7,4,24],"class_list":["post-108","post","type-post","status-publish","format-standard","hentry","category-run-environment","tag-batting","tag-leagues","tag-mysql","tag-ootp","tag-view"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9cxb5-1K","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/comments?post=108"}],"version-history":[{"count":7,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/108\/revisions"}],"predecessor-version":[{"id":117,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/108\/revisions\/117"}],"wp:attachment":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/media?parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/categories?post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/tags?post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}