blob: ae36cc894edf58af512ec1db926dfdf929e7a601 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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);
|