From a0f5e709cfc9b61a8919b9ddb3ce915028128287 Mon Sep 17 00:00:00 2001 From: emkael Date: Wed, 18 Feb 2015 12:11:37 +0100 Subject: * useful views, separated by theme --- ergast-views.sql | 138 ------------------------------------------ views/champions.sql | 22 +++++++ views/full_season_drivers.sql | 30 +++++++++ views/podiums.sql | 38 ++++++++++++ views/team_switches.sql | 34 +++++++++++ views/teammates.sql | 28 +++++++++ 6 files changed, 152 insertions(+), 138 deletions(-) delete mode 100644 ergast-views.sql create mode 100644 views/champions.sql create mode 100644 views/full_season_drivers.sql create mode 100644 views/podiums.sql create mode 100644 views/team_switches.sql create mode 100644 views/teammates.sql diff --git a/ergast-views.sql b/ergast-views.sql deleted file mode 100644 index a3aa8b8..0000000 --- a/ergast-views.sql +++ /dev/null @@ -1,138 +0,0 @@ --- view holds distinct driver-race entries --- (eliminating duplicate race entries from shared drives) -CREATE VIEW driver_races AS - SELECT driverId, raceId - FROM results - GROUP BY driverId, raceId; - --- view aggregates races by season for each driver -CREATE VIEW driver_season_races AS - SELECT driver_races.driverId, races.year, - GROUP_CONCAT(driver_races.raceId ORDER BY driver_races.raceId ASC) AS races - FROM driver_races - JOIN races ON races.raceId = driver_races.raceId - GROUP BY driver_races.driverId, races.year; - --- view aggregates races by season -CREATE VIEW season_races AS - SELECT year, GROUP_CONCAT(raceId ORDER BY raceId ASC) AS races - FROM races - GROUP BY year; - --- selecting only driver-season pairs with all races in a season -CREATE VIEW full_season_drivers AS - SELECT driver_season_races.driverId, driver_season_races.year - FROM season_races - JOIN driver_season_races - ON (season_races.races = driver_season_races.races) - AND (season_races.year = driver_season_races.year); - --- all drivers listed in the standings at position = 1 --- for last race of each season -CREATE VIEW world_drivers_champions AS - SELECT drivers.*, races.year - FROM driverStandings - JOIN races ON races.raceId = driverStandings.raceId - JOIN drivers ON driverStandings.driverId = drivers.driverId - WHERE driverStandings.raceId IN (SELECT MAX(raceId) FROM races GROUP BY year) - AND (driverStandings.position = 1); - --- all constructors listed in the standings at position = 1 --- for last race of each season -CREATE VIEW world_constructors_champions AS - SELECT constructors.*, races.year - FROM constructorStandings - JOIN races ON races.raceId = constructorStandings.raceId - JOIN constructors - ON constructorStandings.constructorId = constructors.constructorId - WHERE constructorStandings.raceId IN ( - SELECT MAX(raceId) FROM races GROUP BY year - ) - AND (constructorStandings.position = 1); - --- all driver pairs listed in the results as driving for the same constructor --- in the same race, for every race -CREATE VIEW teammates AS - SELECT r1.raceId, r1.constructorId, - r1.driverId driver1, r1.position d1position, - r1.positionText d1posText, r1.statusId d1status, - r2.driverId driver2, r2.position d2position, - r2.positionText d2posText, r2.statusId d2status - FROM results r1 - JOIN results r2 - ON r1.raceId = r2.raceId - AND r1.constructorId = r2.constructorId - AND r1.driverId <> r2.driverId; - --- tally of results from the previous view -CREATE VIEW teammate_tally AS - SELECT CONCAT(d1.forename, " ", d1.surname) driver, - SUM(teammates.d1position < teammates.d2position) wins, - SUM(teammates.d1position = teammates.d2position) draws, - SUM(teammates.d1position > teammates.d2position) loses, - CONCAT(d2.forename, " ", d2.surname) against, - SUM(teammates.d1position IS NOT NULL - AND teammates.d2position IS NOT NULL) compare_count, - COUNT(teammates.raceId) overall_count - FROM teammates - JOIN drivers d1 ON d1.driverId = teammates.driver1 - JOIN drivers d2 ON d2.driverId = teammates.driver2 - GROUP BY teammates.driver1, teammates.driver2; - --- constructors' standings at the end of each season -CREATE VIEW constructor_championship_results AS - SELECT * FROM constructorStandings - WHERE raceId IN ( - SELECT MAX(raceId) FROM races GROUP BY year - ); - --- every driver-constructor pair for each season -CREATE VIEW season_driver_constructor AS - SELECT results.driverId, results.constructorId, races.year - FROM results - JOIN races ON races.raceId = results.raceId - GROUP BY results.driverId, results.constructorId, races.year; - --- every team each driver would go to drive for the following year -CREATE VIEW next_season_driver_constructor AS - SELECT sdc.*, sdc2.constructorId AS nextConstructor - FROM season_driver_constructor sdc - LEFT JOIN season_driver_constructor sdc2 - ON (sdc.year = (sdc2.year - 1)) AND (sdc.driverId = sdc2.driverId); - --- every driver team switch with cosntructors' positions for the former season -CREATE VIEW driver_team_switches AS - SELECT nsdc.driverId, nsdc.year, nsdc.constructorId, nsdc.nextConstructor, - cs.position AS prevPosition, cs2.position AS nextPosition - FROM next_season_driver_constructor nsdc - JOIN constructor_championship_results cs - ON nsdc.constructorId = cs.constructorId - JOIN races ON (cs.raceId = races.raceId) AND (races.year = nsdc.year) - JOIN constructor_championship_results cs2 - ON nsdc.nextConstructor = cs2.constructorId - JOIN races r2 ON (r2.year = nsdc.year) and (r2.raceId = cs2.raceId) - WHERE nsdc.constructorId <> nsdc.nextConstructor; - -CREATE VIEW podium_sequences AS - SELECT raceId, GROUP_CONCAT(driverId ORDER BY position ASC, driverId ASC) AS podium - FROM results - WHERE position IN (1,2,3) - GROUP BY raceId; - -CREATE VIEW podium_sets AS - SELECT raceId, GROUP_CONCAT(driverId ORDER BY driverId) AS podium - FROM results - WHERE position IN (1,2,3) - GROUP BY raceId; - -CREATE VIEW non_champion_nationality_podiums AS - SELECT * FROM races - WHERE raceId NOT IN ( - SELECT results.raceId FROM results - JOIN drivers ON drivers.driverId = results.driverId - JOIN races ON races.raceId = results.raceId - WHERE drivers.nationality IN ( - SELECT nationality FROM world_drivers_champions - WHERE year < races.year - ) AND position IN (1,2,3) - ); diff --git a/views/champions.sql b/views/champions.sql new file mode 100644 index 0000000..d8e4d18 --- /dev/null +++ b/views/champions.sql @@ -0,0 +1,22 @@ +-- all drivers listed in the standings at position = 1 +-- for last race of each season +CREATE VIEW world_drivers_champions AS + SELECT drivers.*, races.year + FROM driverStandings + JOIN races ON races.raceId = driverStandings.raceId + JOIN drivers ON driverStandings.driverId = drivers.driverId + WHERE driverStandings.raceId IN (SELECT MAX(raceId) FROM races GROUP BY year) + AND (driverStandings.position = 1); + +-- all constructors listed in the standings at position = 1 +-- for last race of each season +CREATE VIEW world_constructors_champions AS + SELECT constructors.*, races.year + FROM constructorStandings + JOIN races ON races.raceId = constructorStandings.raceId + JOIN constructors + ON constructorStandings.constructorId = constructors.constructorId + WHERE constructorStandings.raceId IN ( + SELECT MAX(raceId) FROM races GROUP BY year + ) + AND (constructorStandings.position = 1); diff --git a/views/full_season_drivers.sql b/views/full_season_drivers.sql new file mode 100644 index 0000000..ae36cc8 --- /dev/null +++ b/views/full_season_drivers.sql @@ -0,0 +1,30 @@ +-- view holds distinct driver-race entries +-- (eliminating duplicate race entries from shared drives) +CREATE VIEW driver_races AS + SELECT driverId, raceId + FROM results + GROUP BY driverId, raceId; + +-- view aggregates races by season for each driver +CREATE VIEW driver_season_races AS + SELECT driver_races.driverId, races.year, + GROUP_CONCAT(driver_races.raceId + ORDER BY driver_races.raceId ASC) + AS races + FROM driver_races + JOIN races ON races.raceId = driver_races.raceId + GROUP BY driver_races.driverId, races.year; + +-- view aggregates races by season +CREATE VIEW season_races AS + SELECT year, GROUP_CONCAT(raceId ORDER BY raceId ASC) AS races + FROM races + GROUP BY year; + +-- selecting only driver-season pairs with all races in a season +CREATE VIEW full_season_drivers AS + SELECT driver_season_races.driverId, driver_season_races.year + FROM season_races + JOIN driver_season_races + ON (season_races.races = driver_season_races.races) + AND (season_races.year = driver_season_races.year); diff --git a/views/podiums.sql b/views/podiums.sql new file mode 100644 index 0000000..6e677da --- /dev/null +++ b/views/podiums.sql @@ -0,0 +1,38 @@ +-- podium for each race, in order of finishing position +CREATE VIEW podium_sequences AS + SELECT races.*, + GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname) + ORDER BY results.position ASC, results.driverId ASC) + AS podium + FROM results + JOIN races ON results.raceId = races.raceId + JOIN drivers ON results.driverId = drivers.driverId + WHERE results.position IN (1,2,3) + GROUP BY results.raceId; + +-- podium for each race, in alphabetical order +CREATE VIEW podium_sets AS + SELECT races.*, + GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname) + ORDER BY drivers.surname ASC, drivers.forename ASC, + drivers.driverRef ASC) + AS podium + FROM results + JOIN races ON results.raceId = races.raceId + JOIN drivers ON results.driverId = drivers.driverId + WHERE results.position IN (1,2,3) + GROUP BY results.raceId; + +-- races in which the entire podium consisted of driver of nationality +-- yet to produce a drivers' world champion +CREATE VIEW non_champion_nationality_podiums AS + SELECT raceId, year, name, podium FROM podium_sequences + WHERE raceId NOT IN ( + SELECT results.raceId FROM results + JOIN drivers ON drivers.driverId = results.driverId + JOIN races ON races.raceId = results.raceId + WHERE drivers.nationality IN ( + SELECT nationality FROM world_drivers_champions + WHERE year < races.year + ) AND position IN (1,2,3) + ); diff --git a/views/team_switches.sql b/views/team_switches.sql new file mode 100644 index 0000000..2514911 --- /dev/null +++ b/views/team_switches.sql @@ -0,0 +1,34 @@ +-- constructors' standings at the end of each season +CREATE VIEW constructor_championship_results AS + SELECT * FROM constructorStandings + WHERE raceId IN ( + SELECT MAX(raceId) FROM races GROUP BY year + ); + +-- every driver-constructor pair for each season +CREATE VIEW season_driver_constructor AS + SELECT results.driverId, results.constructorId, races.year + FROM results + JOIN races ON races.raceId = results.raceId + GROUP BY results.driverId, results.constructorId, races.year; + +-- every team each driver would go to drive for in the following year +CREATE VIEW next_season_driver_constructor AS + SELECT sdc.*, sdc2.constructorId AS nextConstructor + FROM season_driver_constructor sdc + LEFT JOIN season_driver_constructor sdc2 + ON (sdc.year = (sdc2.year - 1)) AND (sdc.driverId = sdc2.driverId); + +-- every driver team switch with both cosntructors' positions +-- for the initial season +CREATE VIEW driver_team_switches AS + SELECT nsdc.driverId, nsdc.year, nsdc.constructorId, nsdc.nextConstructor, + cs.position AS prevPosition, cs2.position AS nextPosition + FROM next_season_driver_constructor nsdc + JOIN constructor_championship_results cs + ON nsdc.constructorId = cs.constructorId + JOIN races ON (cs.raceId = races.raceId) AND (races.year = nsdc.year) + JOIN constructor_championship_results cs2 + ON nsdc.nextConstructor = cs2.constructorId + JOIN races r2 ON (r2.year = nsdc.year) and (r2.raceId = cs2.raceId) + WHERE nsdc.constructorId <> nsdc.nextConstructor; diff --git a/views/teammates.sql b/views/teammates.sql new file mode 100644 index 0000000..772d59d --- /dev/null +++ b/views/teammates.sql @@ -0,0 +1,28 @@ +-- all driver pairs listed in the results as driving for the same constructor +-- in the same race, for every race +CREATE VIEW teammates AS + SELECT r1.raceId, r1.constructorId, + r1.driverId driver1, r1.position d1position, + r1.positionText d1posText, r1.statusId d1status, + r2.driverId driver2, r2.position d2position, + r2.positionText d2posText, r2.statusId d2status + FROM results r1 + JOIN results r2 + ON r1.raceId = r2.raceId + AND r1.constructorId = r2.constructorId + AND r1.driverId <> r2.driverId; + +-- tally of results from the previous view +CREATE VIEW teammate_tally AS + SELECT CONCAT(d1.forename, " ", d1.surname) driver, + COALESCE(SUM(teammates.d1position < teammates.d2position), 0) wins, + COALESCE(SUM(teammates.d1position = teammates.d2position), 0) draws, + COALESCE(SUM(teammates.d1position > teammates.d2position), 0) loses, + CONCAT(d2.forename, " ", d2.surname) against, + SUM(teammates.d1position IS NOT NULL + AND teammates.d2position IS NOT NULL) compare_count, + COUNT(teammates.raceId) overall_count + FROM teammates + JOIN drivers d1 ON d1.driverId = teammates.driver1 + JOIN drivers d2 ON d2.driverId = teammates.driver2 + GROUP BY teammates.driver1, teammates.driver2; -- cgit v1.2.3