mysql - 确定 Lahman 数据库中的新秀年限

Question

我正在使用 Lahman Baseball Database 的 MySQL 版本而且我无法确定一名球员失去新秀地位的那一年。 MLB 球员失去新秀资格的规则是:

A player shall be considered a rookie unless, during a previous season or seasons, he has (a) exceeded 130 at-bats or 50 innings pitched in the Major Leagues; or (b) accumulated more than 45 days on the active roster of a Major League club or clubs during the period of 25-player limit (excluding time in the military service and time on the disabled list).

是否可以运行查询来为击球手和投手执行此操作，或者这是否可以通过编程方式完成？

Answer 1

使用 Lahman 数据库，您可以通过击球数 (>130) 和投球局数 (>50) 计算出新秀，但是在 25 人名单(非 9 月)限制期间没有任何服务时间。

你需要 retrosheets { http://www.retrosheet.org/game.htm } 数据来做到这一点。

下面的查询将为您提供 At Bats 和 Innings Pitched 的所有新秀，但服务时间新秀除外。只有少数这样的球队不会让新秀留在美国职棒大联盟的名单上而不让他们上场。失去开发时间(不玩)并加速他们的服务时间以失去受控年份。因此，如果您对此感到满意，这些表就可以了。

您可以将其用作带有击球手或投手的外部参照表，以突出他们的新秀年。或者，您可以为击球手和投手添加一个额外的列，以区分 RookieYr(建议不要这样做，就好像您想向 Lahman DB 添加新赛季一样 - 无需进行定制)。

/************************************ Create MLB Rookie Xref Table **********************************************
-- Sort Out Batters who accumulate 130 AB
-- Sort Out Pitchers who accumulate 50 IP
-- Define Rookie Year, Drop off years previous and years after
-- Can be updated Annually using "player ID not in (select distinct playerID from Xref_RookieYr)
-- Using the Sean Lahman Database 
-- Authored By Paul DeVos {www.linkedin.com/in/devosp/}
*****************************************************************************************************************/

/****** Query uses T-SQL, Query ran in MS SQL 2012 - you may need to tweek for other platorms or versions. ******/

--Step 1 - Run this for hitter accumulated ABs and when Rookie Year (130 Career At Bats)
Select
    concat(m.nameFirst, ' ', m.nameLast) as Name,
    b.PlayerID,
    b.yearID,
    m.debut,
    sum(b.ab) over (partition by b.playerID order by b.playerID, b.yearID) as CumulativeAB,
    null as CumulativeIP,  -- Place Holder for Rookie Pitchers Insert
    case when sum(b.ab) over (partition by b.playerID order by b.playerID, b.yearID) >= 130 then b.yearID end as RookieYR
into #temp_rookie_year
from
    [master] m
    inner join Batting b
    on m.playerID=b.playerID
-- Selects Position Players 
where b.playerID not in (select distinct f.playerID from Fielding f where f.pos = 'P')


--Step 2 - Run this to get accumulated IP and Rookie Year (50 Career IP)
Insert into #temp_rookie_year
    (
        Name, PlayerID, YearID, Debut, CumulativeAB, CumulativeIP, RookieYR
    )
Select
    concat(m.nameFirst, ' ', m.nameLast) as Name,
    p.PlayerID,
    p.yearID,
    m.debut,
    null as CumulativeAB,
    sum(p.IPouts) over (partition by p.playerID order by p.playerID, p.yearID) as CumulativeIP,
    case when sum(p.IPouts) over (partition by p.playerID order by p.playerID, p.yearID) >= 150 then p.yearID end as RookieYR
from [master] m
    inner join pitching p
    on m.playerID=p.playerID
--Chooses Pitchers
where p.playerID in (select distinct f.playerID from Fielding f where f.pos = 'P')


--Step 3 Run this - sorts out the rookie year into Rookie Xref Table
select Name, PlayerID, min(RookieYr) as RookieYear
into #Xref_RookieYr
from #temp_rookie_year
--where name = 'Hank Aaron'
group by Name, PlayerID
order by RookieYear desc

--Step 4 - run IF you want to remove players who never lost rookie status (cup of cofee players, etc - anyone under 130 AB or 50 IP)
select * from #Xref_RookieYr
order by playerID

Delete from #Xref_RookieYr where RookieYear is null


select * from #Xref_RookieYr
order by playerID

/*****************************************************************************************************************
You can change drop the "#" in front of the table (and name it whatever you want) when you want a permanent table. 
If you leave it, it'll drop off when you close the program. e.g. Xref_Rookie_2013
*****************************************************************************************************************/