From a0f5e709cfc9b61a8919b9ddb3ce915028128287 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Wed, 18 Feb 2015 12:11:37 +0100
Subject:  * useful views, separated by theme

---
 views/full_season_drivers.sql | 30 ++++++++++++++++++++++++++++++
 1 file changed, 30 insertions(+)
 create mode 100644 views/full_season_drivers.sql

(limited to 'views/full_season_drivers.sql')

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);
-- 
cgit v1.2.3