Juanito Banca andres-alvarado

## gist:05f69509b5bb2230024ee565c038d24a
1. Crear base de datos. "Create database baseball"
2. Correr: mysql -u juanito -pbanca baseball2 < baseball.sql

## bajo_porcentaje.csv

          
            Jugador
            Strikeouts
            Temporadas
            AtBats
            Strikeouts/AtBats

            
              Hector Garcia
              4
              5
              43
              0.0930

            
              Fabian Lopez
              5
              2
              49
              0.1020

            
              Wilfredo Arano
              10
              6
              95
              0.1053

            
              Rafael Castaneda
              5
              3
              43
              0.1163

            
              Dabiel Flores
              6
              3
              46
              0.1304

            
              Fernando Rodriguez
              6
              6
              46
              0.1304

            
              Noe Becerra
              9
              7
              68
              0.1324

            
              Ricardo Vazquez
              14
              7
              104
              0.1346

            
              Valentin Gamez
              10
              7
              73
              0.1370

## alto_porcentaje.csv

          
            Jugador
            Strikeouts
            Temporadas
            AtBats
            Strikeouts/AtBats

            
              Baltazar Lopez
              32
              6
              79
              0.4051

            
              Carlos Morales
              17
              5
              44
              0.3864

            
              Carlos Rodriguez
              21
              6
              55
              0.3818

            
              Lorenzo Mercado
              13
              5
              40
              0.3250

            
              Gregorio Angulo
              13
              5
              41
              0.3171

            
              Juvenal Ibarra
              13
              2
              42
              0.3095

## ph.sql
WITH d AS (
  SELECT
    CONCAT(p.firstName, ' ', p.lastName) playerName,
    SUM(IF(ab.event = 'Strikeout', 1, 0)) strikeouts,
    COUNT(DISTINCT g.seasonId) seasons,
    COUNT(1) total
  FROM atbats ab
  INNER JOIN actions s
    ON ab.gamePk = s.gamePk
    AND ab.atBatIndex = s.atBatIndex

## datos.csv

          
            Temporada
            Liga
            Bateador 1
            BA
            Bateador 2
             BA
            Diferencia

            
              2016
              LIDOM
              Ronny Rodriguez
              0.30625
              Ramon Torres
              0.305732484
              5.18E-04

            
              2018.2
              LMB
              Olmo Rosario
              0.40825688
              Yuniesky Betancourt
              0.40625
              0.00200688

            
              2013
              LIDOM
              Junior Lake
              0.342857142
              Juan Lagares
              0.342105263
              7.52E-04

            
              2019
              LIDOM
              Moises Sierra
              0.348148148
              Jordany Valdespin
              0.340277777
              0.007870371

## Promedios.sql
WITH agg AS (
  SELECT
    seasonId,
    majorLeagueId,
    playerId,
    atBats,
    battingAverage,
    RANK() OVER(
      PARTITION BY
        seasonId,

## calculo de woba paso por paso.sql
WITH d AS (
  SELECT
    playerId,
    unintentionalWalks,
    weightUnintentionalWalk,
    singles,
    weightSingle,
    doubles,
    weightDouble,
    triples,

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

## 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'
),

## 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'
	1. Crear base de datos. "Create database baseball"
	2. Correr: mysql -u juanito -pbanca baseball2 < baseball.sql
Jugador	Strikeouts	Temporadas	AtBats	Strikeouts/AtBats
Hector Garcia	4	5	43	0.0930
Fabian Lopez	5	2	49	0.1020
Wilfredo Arano	10	6	95	0.1053
Rafael Castaneda	5	3	43	0.1163
Dabiel Flores	6	3	46	0.1304
Fernando Rodriguez	6	6	46	0.1304
Noe Becerra	9	7	68	0.1324
Ricardo Vazquez	14	7	104	0.1346
Valentin Gamez	10	7	73	0.1370
	WITH d AS (
	SELECT
	CONCAT(p.firstName, ' ', p.lastName) playerName,
	SUM(IF(ab.event = 'Strikeout', 1, 0)) strikeouts,
	COUNT(DISTINCT g.seasonId) seasons,
	COUNT(1) total
	FROM atbats ab
	INNER JOIN actions s
	ON ab.gamePk = s.gamePk
	AND ab.atBatIndex = s.atBatIndex
Temporada	Liga	Bateador 1	BA	Bateador 2	BA	Diferencia
2016	LIDOM	Ronny Rodriguez	0.30625	Ramon Torres	0.305732484	5.18E-04
2018.2	LMB	Olmo Rosario	0.40825688	Yuniesky Betancourt	0.40625	0.00200688
2013	LIDOM	Junior Lake	0.342857142	Juan Lagares	0.342105263	7.52E-04
2019	LIDOM	Moises Sierra	0.348148148	Jordany Valdespin	0.340277777	0.007870371
	WITH agg AS (
	SELECT
	seasonId,
	majorLeagueId,
	playerId,
	atBats,
	battingAverage,
	RANK() OVER(
	PARTITION BY
	seasonId,
	WITH homescored
	AS (SELECT majorleagueid,
	seasonid,
	venueid,
	teamid,
	runs,
	games
	FROM agg_batting_stats
	WHERE grouping_id = 126
	AND gametype2 = 'RS'
	With we As
	(
	Select *
	From we_win_expectancy
	Where seasonId = 2019
	And majorLeagueId = 125
	And groupingId = 13040
	And perspective = 'BATTING'
	And gameType2 = 'RS'
	),
	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'