diff options
| author | emkael <emkael@tlen.pl> | 2015-03-03 11:54:10 +0100 |
|---|---|---|
| committer | emkael <emkael@tlen.pl> | 2015-03-03 11:54:10 +0100 |
| commit | ffd9d4fb6706e609a16b3a5a2fffe0959fd845d3 (patch) | |
| tree | 32d28a2d9254a18842312dd8e865b40068a6187b | |
| parent | 6c73fedbaad194fa47d92182f4005b963313a114 (diff) | |
* career length stats
* http://ergast.com/mrd/bugs/comment-page-2#comment-12128
| -rw-r--r-- | import/01a_integrity_fixes.sql | 2 | ||||
| -rw-r--r-- | import/10_fixes.sql | 1 | ||||
| -rw-r--r-- | views/career_length.sql | 10 |
3 files changed, 13 insertions, 0 deletions
diff --git a/import/01a_integrity_fixes.sql b/import/01a_integrity_fixes.sql index 560ab62..370220b 100644 --- a/import/01a_integrity_fixes.sql +++ b/import/01a_integrity_fixes.sql @@ -2,3 +2,5 @@ ALTER TABLE races CHANGE circuitId circuitId INT(11) NULL; UPDATE races SET circuitId = NULL WHERE raceId = 935; -- 2015 German GP, venue TBA UPDATE results SET position = NULL WHERE position = 0; + +UPDATE drivers SET dob = NULL WHERE driverId = 415; -- Ray Reed, date of birth unknown diff --git a/import/10_fixes.sql b/import/10_fixes.sql index 6759298..6735973 100644 --- a/import/10_fixes.sql +++ b/import/10_fixes.sql @@ -8,3 +8,4 @@ UPDATE circuits SET country = 'USA' WHERE country = 'United States'; UPDATE results SET grid = 15 WHERE resultId = 2939; -- Nick Heidfeld, Australia 2000 +UPDATE drivers SET dob = '1954-01-24' WHERE driverId = 189; -- Jo Gartner diff --git a/views/career_length.sql b/views/career_length.sql new file mode 100644 index 0000000..e950f32 --- /dev/null +++ b/views/career_length.sql @@ -0,0 +1,10 @@ +CREATE VIEW career_length AS + SELECT drivers.driverId, + COUNT(DISTINCT(results.raceId)) AS career_races, + DATEDIFF(MAX(races.date), MIN(races.date)) + 1 AS career_days, + DATEDIFF(MIN(races.date), drivers.dob) AS first_race_age, + DATEDIFF(MAX(races.date), drivers.dob) AS last_race_age + FROM results + JOIN races ON races.raceId = results.raceId + JOIN drivers ON drivers.driverId = results.driverId + GROUP BY results.driverId; |