How Games Start
After loading Retrosheet's data into a database, it can be easy to extract information. As an example, let's take a look at how games started, based on the event files from Retrosheet. This includes information from games starting in 1957 and ending in 2006, excluding 1999.
[mek@olin retrosheet]$ psql retrosheet
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
retrosheet=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | boxscores | table | mek
public | events | table | mek
public | eventtypes | table | mek
public | games | table | mek
public | rosters | table | mek
public | teams | table | mek
(6 rows)
We'll need to worry about data from the
EventTypeTable and the
EventsTable.
SELECT DISTINCT e.eventtype AS event_type,
et.eventname AS event_name,
count(e.eventtype) AS new_game_events
FROM events e
INNER JOIN eventtypes et ON et.eventtype = e.eventtype
WHERE e.newgameflag='T'
GROUP BY e.eventtype,et.eventname;
retrosheet=# select distinct e.eventtype as event_type,
retrosheet-# et.eventname as event_name,
retrosheet-# count(e.eventtype) as new_game_events
retrosheet-# from events e
retrosheet-# inner join eventtypes et on et.eventtype = e.eventtype
retrosheet-# where e.newgameflag='T'
retrosheet-# group by e.eventtype,et.eventname;
After a few CPU cycles ...
event_type | event_name | new_game_events
------------+---------------+-----------------
2 | Generic out | 50022
3 | Strikeout | 14247
13 | Foul error | 53
14 | Walk | 7378
16 | Hit by | 540
17 | Interference | 10
18 | Error | 1163
20 | Single | 16409
21 | Double | 3828
22 | Triple | 780
23 | Home run | 1334
(11 rows)
Let's do a little sanity check
retrosheet=# select count(gameid) from events where newgameflag='T';
count
-------
95764
(1 row)
Adding up the 11 event types, we get 95764. We can be reasonably sure that if you went to a game and saw it started with interference ... you've seen something rare. Now for the heck of it, let's find out what games those were and the Retrosheet text that describes it.
SELECT e.gameid AS retrosheet_gameid,
g.visitingteam AS visitingteam,
e.eventtext AS retrosheet_eventtext,
e.pitcher AS pitcher,
e.batter AS batter,
g.homeplateumpire AS homeplateumpire
FROM events e
INNER JOIN games g ON e.gameid = g.gameid
WHERE e.newgameflag='T'
AND e.eventtype=17;
retrosheet=# select e.gameid as retrosheet_gameid,
retrosheet-# g.visitingteam as visitingteam,
retrosheet-# e.eventtext as retrosheet_eventtext,
retrosheet-# e.pitcher as pitcher,
retrosheet-# e.batter as batter,
retrosheet-# g.homeplateumpire as homeplateumpire
retrosheet-# from events e
retrosheet-# inner join games g on e.gameid = g.gameid
retrosheet-# where e.newgameflag='T'
retrosheet-# and e.eventtype=17;
retrosheet_gameid | visitingteam | retrosheet_eventtext | pitcher | batter | homeplateumpire
-------------------+--------------+----------------------+----------+----------+-----------------
SFN195806090 | CIN | C/E2 | antoj103 | tempj101 | delmv901
PHI196309010 | SLN | C/E2 | shorc102 | javij101 | walsf901
ATL196904120 | CIN | C/E2 | jarvp101 | rosep001 | venzt901
HOU196904220 | CIN | C/E2 | wilsd102 | rosep001 | gormt101
NYN196905070 | CIN | C/E2.B-1 | gentg101 | rosep001 | barla901
MON197006120 | HOU | C/E2.B-1 | mcgid101 | torrh101 | steim901
SFN197105100 | CIN | C/E2 | marij101 | rosep001 | craws901
HOU198407130 | PHI | C/E2 | ryann001 | maddg001 | davib902
PIT198609270 | NYN | C/E2 | kippb001 | wilsm001 | willc901
COL200009250 | ARI | C/E2 | roseb002 | counc001 | kulpr901
(10 rows)
All the games were in the National League, five involved the Cincinnati Reds and FOUR of those games involved Pete Rose!
Categories
CategoryDB
CategoryStats
There are no comments on this page. [Add comment]