Baseball and SQL

Two great tastes that go great together.

One of the greatest things ever is the Lanham Baseball Database. It’s a database of all baseball statistics since 1871, suitable for random SQL queries. Not quite pitch by pitch, but season by season. Note that retrosheet has made their boxscore database for all games since 1971 available, at least in limited fashion, so you can do a day by day database, which Baseball Musings has done. One can only imagine what sort of amazing data we’ll be able to get in 2071.

As an example of the kind of fun you can have, here’s a query to figure out the most recent world series win, loss and appearance for every team in the league:

SELECT
teams.franchid,
MAX(winyear) AS lastwin,
MAX(loseyear) AS lastloss,
MAX(COALESCE(winyear, loseyear)) as lastappearance
FROM teams
LEFT JOIN (
SELECT a.yearid AS winyear,
null as loseyear,
franchid
FROM seriespost a
JOIN teams b
ON (a.yearid = b.yearid AND
a.teamidwinner = b.teamid AND
a.lgidwinner = b.lgid AND a.round = 'WS')
UNION SELECT null AS winyear,
a.yearid AS loseyear,
franchid
FROM seriespost a JOIN
teams b ON (
a.yearid = b.yearid AND
a.teamidloser = b.teamid AND
a.lgidloser = b.lgid AND
a.round = 'WS')) years on (teams.franchid = years.franchid)
WHERE teams.yearid = 2004
GROUP BY teams.franchid
ORDER BY lastappearance desc;


And here’s the output:

(30 rows)

Note that the database doesn’t yet have 2005 data. But you get the picture.