Skip to content

Instantly share code, notes, and snippets.

Avatar
Bateando y programando.

Juanito Banca andres-alvarado

Bateando y programando.
View GitHub Profile
View calculo de woba paso por paso.sql
WITH d AS (
SELECT
playerId,
unintentionalWalks,
weightUnintentionalWalk,
singles,
weightSingle,
doubles,
weightDouble,
triples,
View ivan_pf.sql
WITH homescored
AS (SELECT majorleagueid,
seasonid,
venueid,
teamid,
runs,
games
FROM agg_batting_stats
WHERE grouping_id = 126
AND gametype2 = 'RS'
View win_expectancy_Dummy.sql
With we As
(
Select *
From we_win_expectancy
Where seasonId = 2019
And majorLeagueId = 125
And groupingId = 13040
And perspective = 'BATTING'
And gameType2 = 'RS'
),
View win_expectancy_test.sql
With b As
(
Select majorLeagueId, seasonId, winexpectancy, groupingdescription, gametype2, startseason, endseason, scoreDifference
From we_win_expectancy
Where perspective = 'BATTING'
), p As
(
Select majorLeagueId, seasonId, winexpectancy, groupingdescription, gametype2, startseason, endseason, scoreDifference
From we_win_expectancy
Where perspective = 'PITCHING'
View win_expectancy.sql
WITH d AS
(
SELECT DISTINCT
majorleagueid,
gamepk,
inning,
battingteamscore,
pitchingteamscore,
battingteamscoreendgame,
pitchingteamscoreendgame
View scores_per_inning.sql
WITH data2 AS (
SELECT
CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) season,
game_id,
fld_team_id team_id,
inn_ct inning,
SUM(IF(bat_dest_id >= 4, 1, 0) + IF(run1_dest_id >= 4, 1, 0) + IF(run2_dest_id >= 4, 1, 0) + IF(run3_dest_id >= 4,1,0)) runs
FROM s_retrosheet_events
WHERE
CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) = 2019
View pitcher_cumulative.sql
WITH data AS (
SELECT
CAST(SUBSTR(game_id, 4, 4) AS UNSIGNED) season,
CAST(SUBSTR(game_id, 4) AS UNSIGNED) date,
game_id,
pit_id,
SUM(event_outs_ct) AS outs,
SUM(IF(bat_dest_id >= 4, 1, 0) + IF(run1_dest_id >= 4, 1, 0) + IF(run2_dest_id >= 4, 1, 0) + IF( run3_dest_id >= 4, 1, 0 )) runs,
SUM(IF(event_cd = 3, 1, 0 )) strikeouts,
SUM(IF(event_cd = 23, 1, 0 )) homeruns,
View script.py
import pandas as pd
import glob
from sqlalchemy import create_engine as ce, types
def insertToDatabase( p_df, p_tbl ):
# Idealmente, poner en un archivo .const.
# Se pone aqui para mas facilidad y hacer menos lineas.
con = "mysql://usuario:contrasenia@localhost/nombreDeBaseDeDatos?use_unicode=1&charset=utf8"
View s_retrosheet_events.sql
CREATE TABLE `s_retrosheet_events` (
`GAME_ID` TEXT,
`AWAY_TEAM_ID` TEXT,
`INN_CT` BIGINT DEFAULT NULL,
`BAT_HOME_ID` BIGINT DEFAULT NULL,
`OUTS_CT` BIGINT DEFAULT NULL,
`BALLS_CT` BIGINT DEFAULT NULL,
`STRIKES_CT` BIGINT DEFAULT NULL,
`PITCH_SEQ_TX` TEXT,
`AWAY_SCORE_CT` BIGINT DEFAULT NULL,
View s_retrosheet_games.sql
CREATE TABLE `s_retrosheet_games` (
`GAME_ID` TEXT,
`GAME_DT` BIGINT DEFAULT NULL,
`GAME_CT` BIGINT DEFAULT NULL,
`GAME_DY` TEXT,
`START_GAME_TM` BIGINT DEFAULT NULL,
`DH_FL` TEXT,
`DAYNIGHT_PARK_CD` TEXT,
`AWAY_TEAM_ID` TEXT,
`HOME_TEAM_ID` TEXT,