我想知道在哪里可以找到Sean Lahman数据库的样本查询(SELECT,UPDATE,DELETE)?我想看看这个数据库能做什么..
我曾经将数据库与示例查询一起提供。也许我应该重新考虑这个想法。以下是一些用于开始的查询。
一个简单的查询,显示所有名为“Sean”的球员:
SELECT nameLast, nameFirst, debut
FROM Master
WHERE (nameFirst="Sean")
ORDER BY nameLast;
SELECT Master.nameLast, Master.nameFirst, Batting.HR, Batting.yearID
FROM Batting INNER JOIN Master ON Batting.playerID = Master.playerID
WHERE (((Batting.HR)>=50))
ORDER BY Batting.HR DESC;
以下是史上投手三振排行榜:
SELECT Master.nameLast, Master.nameFirst, Sum(Pitching.SO) AS SumOfSO
FROM Pitching INNER JOIN Master ON Pitching.playerID = Master.playerID
GROUP BY Pitching.playerID, Master.nameLast, Master.nameFirst
ORDER BY Master.nameLast;
这个数据库几乎可以做任何事情,除了逐场比赛分析,您需要前往Retrosheet {http://www.retrosheet.org/game.htm}。
但是假设您想复制在Baseball-Reference.com上看到的总数,您可以轻松实现。
如果您想要一些高级指标(类似Sabermetric的统计数据),我推荐Tom Tango的网站。在那里,您可以找到有关为wOBA进行自己的查询的帮助。您还可以制定(尝试复制)FanGraph或Baseball-Reference的WAR。
基本上,您想要的任何内容(前提是您可以进行计算/掌握SQL语法),这个数据库都可以提供,除了逐场比赛或逐球数据类型。
以下是一个查询,用于确定按比赛(进攻和防守)计算薪水,以计算每场比赛球员的成本/收入。(使用SQL Server 2012 Express的T-SQL)
select
m.namefirst,
m.namelast,
s.yearID,
s.teamID,
s.salary,
Cast ('162' as Int) as FullSeason,
round(sum(s.salary)*1.00/162,0) as Game_Rate,
sum (case when s.playerID=b.playerID then f.g else 0 end) as Gm_App_Field,
b.g as Batting,
--sum(case when s.playerID=b.playerID and s.yearID=b.yearID then b.g else 0 end) as Gm_App_Hit,
sum (case when s.playerID=b.playerID then f.innouts else 0 end) as InnOuts,
sum(F.InnOuts)/27 as FullGames,
round((sum (case when s.playerID=b.playerID then f.g else 0 end)/162.0)*s.salary,0) as PayByGmFielding,
round(sum(b.g*s.salary)/162,0) as PayByGmHitting,
round((sum(F.InnOuts)/27)*(s.salary/162),0) as PlayingSalary
from Fielding f
inner join batting b
on f.playerID=b.playerID and f.yearID=b.yearID
inner join salaries s
on f.playerID=s.playerID and f.yearID=s.yearID
inner join [master] m
on b.playerID=m.playerID and f.playerID=m.playerID and s.playerID=m.playerID
where
f.yearID = '2013' and f.POS <> 'P' --b.playerID = 'zimmejo02'
group by
m.namefirst,m.namelast, s.yearID , s.teamID, s.salary, b.g
namefirst namelast yearID teamID salary FullSeason Game_Rate Gm_App_Field Batting InnOuts FullGames PayByGmFielding PayByGmHitting PlayingSalary
A.J. Pollock 2013 ARI 491000 162 9093 119 137 2897 107 360672 1245685 324302
您也可以创建自己的搜索,这里有一个使用完整球员卡片的更多BB/SO玩家的搜索,包括我想出来的WAR(可能与FanGraphs或Baseball Reference略有不同)-(T-SQL w/ SQL Server 2012 Express)
--1. Retrives Full Player Records of guys with more BB than SO
select
m.namefirst,
m.namelast,
b.yearID,
b.yearID-m.birthyear as Age,
b.G,b.AB,b.R,b.H,b.[2B],b.[3B],b.HR,b.RBI,b.SB,b.BB,b.SO, left(round((b.bb*1.000/b.SO),3),4) [BB/SO Rate], left(round((b.h*1.000/b.ab),3),5) as Average
,b.IBB,b.HBP,b.SH,b.SF,b.SF,b.GIDP,case when br.yearID=b.yearID and br.playerID=b.playerID then br.War else 'error' end as WAR
from [master] m
inner join batting b on b.playerID=m.playerID
inner join BR_WAR_2013 br on br.playerID=m.playerID
where b.SO <> 0 and b.AB > 300 and b.bb>b.SO
group by
m.namefirst,
m.namelast,
b.yearID,
b.yearID-m.birthyear,
b.G,b.AB,b.R,b.H,b.[2B],b.[3B],b.HR,b.RBI,b.SB,b.BB,b.SO, left(round((b.bb*1.000/b.SO),3),4), left(round((b.h*1.000/b.ab),3),5)
,b.IBB,b.HBP,b.SH,b.SF,b.SF,b.GIDP,case when br.yearID=b.yearID and br.playerID=b.playerID then br.War else 'error' end
having case when br.yearID=b.yearID and br.playerID=b.playerID then br.War else 'error' end <> 'error'
order by b.yearID desc, left(round((b.bb*1.000/b.SO),3),4) desc