8. Fluggesellschaft
Zur Planung ihres Flugbetriebes werten die Fluggesellschaften ebenfalls die Daten der Datenbank aus. Zur Übersicht der Auslastung ihrer Flüge benutzt die Fluggesellschaft folgende Sicht.
CREATE VIEW booking AS
SELECT airline_id,
flight_number,
seats,
count(*) AS booked,
(seats-count(*)) AS free,
round((count(*)::float/seats)*100) AS "average (%)"
FROM airlines
LEFT JOIN flights USING (airline_id)
LEFT JOIN passangers USING (flight_number)
WHERE airline_id = 'AB'
GROUP BY airline_id, flight_number, seats;
Diese Sicht verbindet die drei Tabellen airlines, flights und passangers. Die Ergebnismenge wird nach Kennung der Fluglinie, Flugnummer und Sitzzahl gruppiert. Die Angabe der Sitzzahl erfolgt, um damit zu rechnen. Dieses Feld könnte in der Auswahlliste der SELECT-Anweisung sonst nicht verwendet werden! Es findet durch dieses Feld keine weitere Unterteilung der Gruppen statt, da für jeden Datensatz eines Fluges die Sitzzahl gleich ist. Die WHERE-Bedingung schränkt die Sicht auf die betrachtende Fluggesellschaft ein. Die Sicht zählt die gebuchten Plätze und errechnet die freien Plätze sowie die prozentuale Auslastung des Fluges.
SELECT * FROM booking;
airline_id | flight_number | seats | booked | free | average (%)
------------+---------------+-------+--------+------+------------
AB | AB 8510 | 150 | 2 | 148 | 1
AB | AB 9172 | 150 | 3 | 147 | 2
AB | AB 9143 | 150 | 3 | 147 | 2
AB | AB 8550 | 150 | 1 | 149 | 1
(4 rows)
Für die Planung zukünftiger Produkte ist für die Fluggesellschaft das Passagieraufkommen an allen Flughäfen interessant. Dazu benutzt die Fluggesellschaft folgende Sicht top100_airports.
CREATE VIEW top100_airports AS
SELECT airport_id,
"name",
sum(cnt) AS total
FROM (
SELECT airport_id,
"name",
count(flight_number) AS cnt
FROM airports
JOIN flights ON (airport_id="from")
JOIN passangers USING (flight_number)
GROUP BY airport_id, "name"
UNION
SELECT airport_id,
"name",
count(flight_number) AS cnt
FROM airports
JOIN flights ON (airport_id="to")
JOIN passangers USING (flight_number)
GROUP BY airport_id, "name"
) "inner-join"
GROUP BY airport_id, "name"
ORDER BY total DESC
LIMIT 100;
Diese Sicht enthält eine in Klammern gesetzte Unterabfrage (subselect), die mit den Namen inner-join benannt ist. Diese Unterabfrage ist aus zwei SELECT-Anweisungen zusammengesetzt. Die Ergebnismengen der SELECT-Anweisungen werden durch den UNION-Operator zusammengefasst. Die erste SELECT-Anweisung der Unteranfrage zählt die Passagiere, die von einem Flughafen abfliegen. Dazu werden die Flughäfen mit dem Feld from aus der Tabelle flights verbunden, nach Flughafen gruppiert und mittels count ausgezählt. In der zweiten SELECT-Anweisung der Unterabfrage werden die ankommenden Passagiere gezählt, in dem die Flughäfen mit dem Feld to aus der Tabelle flights verbunden werden. Die Ergebnismenge wird ebenfalls gruppiert und ausgezählt. Wir betrachten die Vereinigungsmenge der beiden SELECT-Anweisungen als Ergebnis der Unterabfrage.
airport_id | name | cnt
------------+---------------------------+-----
TXL | Berlin - Tegel Airport | 5
TXL | Berlin - Tegel Airport | 4
FRA | Frankfurt Airport | 4
FRA | Frankfurt Airport | 3
GRZ | Graz Airport | 1
GRZ | Graz Airport | 2
LAX | Los Angeles Airport | 4
LAX | Los Angeles Airport | 3
PMI | Palma De Mallorca Airport | 3
(9 rows)
Nun werden für jeden Flughafen die Zahlen der ankommenden und abfliegenden Passagiere mittels der Aggregat-Funktion sum addiert. Die so gebildete Ergebnismenge wird noch nach dem Passagieraufkommen mittels der ORDER BY-Bedingung absteigend sortiert. SQL kennt neben dem Vereinigungsoperator UNION noch weitere Mengenoperatoren; INTERSECT (Schnittmenge) und EXCEPT (Differenzmenge).
SELECT * FROM ( VALUES (1,2), (3,4) ) AS M1
INTERSECT
SELECT * FROM ( VALUES (3,4) ) AS M2
-- (3,4)
UNION
SELECT * FROM ( VALUES (1,2) ) AS M4
-- (3,4) (1,2)
EXCEPT
SELECT * FROM ( VALUES (1,2) ) AS M3;
column1 | column2
---------+---------
3 | 4
(1 row)
Die Operation UNION ALL und INTERSECT ALL schließen Dubletten mit ein.
SELECT * FROM top100_airports;
airport_id | name | total
------------+---------------------------+-------
TXL | Berlin - Tegel Airport | 9
LAX | Los Angeles Airport | 7
FRA | Frankfurt Airport | 7
PMI | Palma De Mallorca Airport | 6
GRZ | Graz Airport | 3
(5 rows)