andres-alvarado

WITH d AS (
  SELECT
    playerId,
    unintentionalWalks,
    weightUnintentionalWalk,
    singles,
    weightSingle,
    doubles,
    weightDouble,
    triples,

andres-alvarado

WITH homescored
     AS (SELECT majorleagueid,
                seasonid,
                venueid,
                teamid,
                runs,
                games
         FROM   agg_batting_stats
         WHERE  grouping_id = 126
                AND gametype2 = 'RS'

andres-alvarado

With we As 
(
Select *
From we_win_expectancy
Where seasonId = 2019
And majorLeagueId = 125
And groupingId = 13040
And perspective = 'BATTING'
And gameType2 = 'RS'
),

andres-alvarado

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'

andres-alvarado

WITH d AS 
(
   SELECT DISTINCT
      majorleagueid,
      gamepk,
      inning,
      battingteamscore,
      pitchingteamscore,
      battingteamscoreendgame,
      pitchingteamscoreendgame 

andres-alvarado

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

andres-alvarado

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,

andres-alvarado

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"

andres-alvarado

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,

andres-alvarado

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,