Zum Inhalt springen

Berechnete Spalten

Links hinzufügen
Aus Wikibooks

Seitentitel: Einführung in SQL: Berechnete Spalten
(Einführung in SQL: Berechnete Spalten)
(Einführung in SQL: Berechnete Spalten)


Mit Abfragen kann man nicht nur Spalten auswählen, sondern auch völlig neue Spalten aus anderen Spalten oder mit Funktionen erstellen.

Allgemeine Regel

[Bearbeiten]

Eine neue Spalte wird als Teil einer Abfrage wie folgt erstellt: 

 <Ausdruck>  [AS]  <Name der neuen Spalte>

Ausdruck ist allgemein der Hinweis auf etwas, das an der betreffenden Stelle verwendet wird: ein konstanter Wert, der Inhalt einer oder mehrerer Spalten, eine Berechnung mit diesen Spalten, das Ergebnis einer Funktion oder das Ergebnis einer Unterabfrage. Der Spalte, die das Ergebnis aufnimmt, wird mit AS ein eigener Name, ein Alias zugewiesen; das AS kann auch entfallen.

In manchen DBMS ist bei berechneten Spalten die Angabe eines Alias Pflicht, und das Vergessen führt zu einem Fehler, manche erzeugen automatisch einen zufälligen Namen, wenn kein Alias eingegeben wird, und andere meckern erst, wenn man versucht, eine physische Tabelle mit einer namenlosen Spalte zu füllen.

Wenn eine Unterabfrage als berechnete Spalte benutzt wird, ist es unbedingt wichtig, dass sie – wie bei einer Funktion, Berechnung oder Verknüpfung – für jede Zeile der Hauptabfrage genau einen Wert ergibt. In dem untenstehenden Beispiel wird dies durch eine passende WHERE-Bedingung erreicht.

Hinweis: Fast alles, was in diesem Abschnitt über Ausdrücke, Funktionen und Konvertierungen gesagt wird, ist auch bei der Manipulation oder Verarbeitung von Daten wichtig. Die Funktionen und Konvertierungen wurden bereits in den Kapiteln Funktionen und Funktionen (2) behandelt.

Ergebnis von Berechnungen

[Bearbeiten]

Alle Werte einer Spalte werden für eine Berechnung verwendet, vorzugsweise eine der Grundrechenarten. Das folgende Beispiel errechnet für alle Zeilen der Tabelle Schadensfall aus dem Feld Schadenshoehe den Nettobetrag (ohne MWSt) und fügt alle weiteren Felder an: 

SELECT Schadenshoehe / 1.19 AS Netto,
       *
  FROM Schadensfall;

Dieses Ergebnis kann auch für weitere Berechnungen verwendet werden, wenn auch nicht direkt mit dem Alias:

 Der Alias-Name der neuen Spalte Netto kann nicht nochmals verwendet werden.
SELECT Schadenshoehe         AS Brutto,
       Schadenshoehe / 1.19  AS Netto,
       Schadenshoehe - Netto AS MWSt
  FROM Schadensfall;
 Aber die Berechnung kann erneut zugewiesen werden.
SELECT Schadenshoehe                          AS Brutto,
       Schadenshoehe / 1.19                   AS Netto,
       Schadenshoehe - (Schadenshoehe / 1.19) AS MWSt
  FROM Schadensfall;

Man kann davon ausgehen, dass das DBMS die Abfrage soweit optimiert, dass die Berechnung tatsächlich nur einmal ausgeführt werden muss.

Zeichenketten verbinden und bearbeiten

[Bearbeiten]

Ebenso wie Zahlen für Berechnungen verwendet werden, gibt es die Bearbeitung von Zeichenketten. Das einfachste ist die Verknüpfung (Verkettung) von Strings durch den Operator || bzw. +: 

SELECT Name || ', ' || Vorname AS Gesamtname
  FROM Mitarbeiter;
Ausgabe
Gesamtname
Müller, Kurt
Schneider, Daniela

In diesem Beispiel wird zwischen die Spalten noch das Komma und ein Leerzeichen als konstanter Text gesetzt.

Bei Textspalten mit fester Feldlänge, die wir in der Beispieldatenbank nicht haben, sollten überzählige Leerzeichen mit der Funktion TRIM abgeschnitten werden: 

SELECT TRIM(Name) || ', ' || TRIM(Vorname) AS Gesamtname
  FROM Mitarbeiter;

Auf diese Weise kann man auch Teile von Texten übernehmen und den Rest abschneiden: 

SELECT Name || ', ' || TRIM( SUBSTRING(Vorname FROM 1 FOR 1) ) || '.' AS Name_kurz
  FROM Mitarbeiter;
Ausgabe
Name_kurz
Müller, K.
Schneider, D.

Hinweis: Nach SQL-Standard liefert das Ergebnis von SUBSTRING einen Text von gleicher Länge wie der ursprüngliche Text; die jetzt zwangsläufig folgenden Leerzeichen müssen mit TRIM entfernt werden. Vielleicht ist Ihr DBMS „pfiffiger“ und macht das automatisch.

Ergebnis von Funktionen

[Bearbeiten]

In den bisherigen Beispielen werden Feldinhalte direkt modifiziert und ausgegeben. In den beiden Kapiteln über „Funktionen“ werden neue Informationen berechnet und unabhängig von vorhandenen Zeilen und Spalten ausgegeben: 

SELECT COUNT(Farbe) AS Anzahl_Farbe 
  FROM Fahrzeug;

Bei diesem Beispiel mit einer Aggregatfunktion besteht das Ergebnis aus einer einzelnen Zahl in einer einzigen Zeile. Dies muss aber nicht so sein; vor allem mit Gruppierungen gibt es viele Varianten.

Viele andere Funktionen können besondere Abfragen ermöglichen oder stark erleichtern. EXTRACT nach Monat liefert die Liste aller Geburtstage, sortiert nach Monat: 

SELECT Name, Vorname,
       EXTRACT(MONTH FROM Geburtsdatum) AS Monat,
       Geburtsdatum
  FROM Mitarbeiter
 ORDER BY Monat;
Ausgabe
NAME            VORNAME    MONAT  GEBURTSDATUM
--------------  ---------  -----  ------------
Aagenau         Karolin        1  02.01.1950
Langer          Norbert        1  13.01.1968
Wagner          Gaby           1  18.01.1970
Müller          Kurt           1  05.01.1977
Kolic           Ivana          2  14.02.1971
Schneider       Daniela        2  16.02.1980  // usw.

Unterabfragen

[Bearbeiten]

Diese werden ausführlich im Kapitel Unterabfragen besprochen. Hier soll deshalb ein Beispiel genügen, wie es für eine berechnete Spalte geeignet ist.

Aufgabe
Aufgabe

Gesucht sind alle Abteilungen zusammen mit der Anzahl ihrer Mitarbeiter: 

SELECT ab.ID,
       ab.Kuerzel,
       ab.Ort,
       ( SELECT COUNT(*) 
           FROM Mitarbeiter mi
          WHERE mi.Abteilung_ID = ab.ID
       ) AS Mitarbeiterzahl
  FROM Abteilung ab;

Von innen nach außen heißt das: In den Klammern gehört zu jeder ab.ID die Anzahl der Einträge in Mitarbeiter mit dieser Abteilung; dies wird mit den anderen Spalten von Abteilung zusammengefasst. Weil es ID in beiden Tabellen gibt, muss die gewünschte Tabelle ausdrücklich erwähnt werden.

Zusammenfassung

[Bearbeiten]

In diesem Kapitel bekamen wir verschiedene Erläuterungen dafür, wie aus Berechnungen, Verkettung von Zeichenketten oder als Ergebnis von Skalar- oder Spaltenfunktionen neue Spalten für die Ergebnistabelle entstehen.

Übungen

[Bearbeiten]

Übung 1 Zusatzspalten durch Berechnung Zur Lösung

Zur Tabelle Versicherungsvertrag sollen Versicherungsnummer, Basisprämie und Prämiensatz angegeben sowie die aktuelle Prämie berechnet werden.

Übung 2 Zusatzspalten durch Berechnung Zur Lösung

Geben Sie (unter Verwendung der Lösung von Übung 1) die Gesamtzahl der Versicherungsverträge sowie den Gesamtbetrag aller aktuellen Prämien an.

Übung 3 Zusatzspalten durch String-Verknüpfung Zur Lösung

Erstellen Sie zur Tabelle Versicherungsnehmer per Abfrage die Druckanschrift:

  • Zeile 1 mit Anrede (22 Zeichen, basierend auf der Spalte Geschlecht mit den Inhalten 'W' bzw. 'M') und der ID am rechten Rand (8 Zeichen rechtsbündig)
  • Zeile 2 mit Vorname und Name
  • Zeile 3 mit Straße und Hausnummer
  • Zeile 4 mit PLZ und Ort

Benutzen Sie CASE, CAST, RPAD und CONCAT; auf TRIM können Sie verzichten.

Übung 4 Neue Spalten durch Spaltenfunktion Zur Lösung

Bestimmen Sie, wie viele Fahrzeuge in Bochum ('BO') und wie viele in Gelsenkirchen ('GE') angemeldet sind.

Gruppierungen werden erst im nächsten Kapitel behandelt; verwenden Sie stattdessen UNION.

Lösungen

Lösung zu Übung 1 Zusatzspalten durch Berechnung Zur Übung 
select Vertragsnummer,
       Basispraemie,
       Praemiensatz,
       Basispraemie * Praemiensatz / 100 as Aktuell
  from Versicherungsvertrag;

Lösung zu Übung 2 Zusatzspalten durch Berechnung Zur Übung 
select COUNT(*) as Gesamtzahl,
       SUM(Basispraemie * Praemiensatz / 100) as Praemiensumme
  from Versicherungsvertrag;

Lösung zu Übung 3 Zusatzspalten durch String-Verknüpfung Zur Übung 
select CONCAT( CAST( ( CASE Geschlecht
                            WHEN 'M' THEN 'Herrn'
                            WHEN 'W' THEN 'Frau'
                            ELSE ''
                       END ) AS CHAR(22) ),
               RPAD( CAST( ID as VARCHAR(8) ), 8 )
             )                          as Zeile1,
       CASE
            WHEN Vorname is null THEN Name
            ELSE CONCAT(Vorname, ' ', Name)
       END                              as Zeile2,
       CONCAT(Strasse, ' ', Hausnummer) as Zeile3,
       CONCAT(PLZ,     ' ', Ort)        as Zeile4
  from Versicherungsnehmer vn;

Lösung zu Übung 4 Neue Spalten durch Spaltenfunktion Zur Übung 
select COUNT(*) as Anzahl, 'BO' as Kreis
  from Fahrzeug
 where Kennzeichen STARTING WITH 'BO-'
       union
select COUNT(*), 'GE'
  from Fahrzeug
 where Kennzeichen STARTING WITH 'GE-';

Für STARTING WITH gibt es Alternativen; wir haben verschiedentlich SUBSTRING mit POSITION o. a. verwendet.


Abgerufen von „https://de.wikibooks.org/w/index.php?title=Einführung_in_SQL:_Berechnete_Spalten&oldid=942630“