Umgang mit SQL-NULLs: COALESCE-Funktion

Es ist unvermeidlich, dass einige Daten in der Datenbank keinen Wert haben, was in SQL durch das Schlüsselwort NULL dargestellt wird. "Kein Wert" unterscheidet sich hier von Null, False oder einer leeren Zeichenkette (aber mit Ausnahmen! In Oracle Datenbank ist NULL dasselbe wie ein String der Länge Null). Beim Spielen mit der Datenbank wird die Behandlung von NULLimmer lästiger, deshalb MySQL Standard Hilfe mit einigen der Funktionen, wie COALESCE.

Lassen Sie uns die COALESCE() Funktion vor:

Definition

COALESCE Die Funktion COALESCE gibt das erste Argument, das nicht NULL ist, aus der übergebenen Liste der Argumente zurück. Ihre Syntax lautet wie folgt:

COALESCE(x, y, … , n)

Was zusätzlich wichtig ist, dass:

• Sie benötigt mindestens zwei Argumente.
• Die Ausdrücke in der Funktion COALESCE müssen auf denselben Datentyp evaluiert werden (die SQL-Anweisung SELECT COALESCE (1, 'aa', NULL, 23); wird beispielsweise einen Fehler erzeugen).
• Wenn alle aufgelisteten Argumente zu NULL ausgewertet werden, gibt die Funktion auch NULL zurück.

COALESCE ist im Wesentlichen eine verkürzte CASE Anweisung, die im Folgenden vorgestellt wird:

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN ... IS NOT NULL THEN …
ELSE n
END

COALESCE - Anwendungsbeispiele

Werfen Sie nun einen Blick auf den unten dargestellten Prototyp des Universitätsdatenbankmodells. Ich werde einige Beispiele zeigen, die die Funktion COALESCE benötigen, die sich auf Tabellen aus diesem Modell bezieht.

Verwendung von COALESCE mit Wertkonkatenation

Werfen Sie einen Blick auf die Tabelle student aus dem vorgestellten Universitäts-Datenbankmodell:

Es kann vorkommen, dass eine bestimmte Person nicht den mittleren Namen hat, daher können einige Datensätze in der Spalte middle_name NULL sein, wie die Beispieldaten unten zeigen.

Versuchen wir, den Vornamen, den mittleren Namen und den Nachnamen des Studenten zu verknüpfen. Dies funktioniert in PostgreSQL und Oracle.

SELECT
  first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM student;

Die Ergebnistabelle enthält eine Spalte full_name mit den verketteten Zeichenfolgen.

Beachten Sie, dass nur ein Datensatz einen richtigen vollständigen Namen hat. Die restlichen Datensätze sind NULL, da der mittlere Name auch NULL war (bei der Verkettung von Strings mit dem Wert NULL ist das Ergebnis auch NULL).

Ein solches Ergebnis sieht nicht sehr schön aus. Das Ergebnis zeigt nicht den vollständigen Namen von Schülern ohne zweiten Vornamen an. Hier kommt die COALESCE() Funktion die Rettung. Mit ihr können wir die Felder von NULL zum Beispiel durch einen leeren String ersetzen. Die neue Abfrage sieht nun wie folgt aus:

SELECT
  first_name || COALESCE(' ' || middle_name || ' ', ' ') || last_name AS full_name
FROM student;

Das Ergebnis ist nun vollständig 🙂 .

MySQL, MS SQL Server verwendet für die Verkettung CONCAT() Funktion oder '+' Operator.

In ähnlicher Weise sieht die Abfrage in MySQL oder MS SQL Server wie folgt aus:

SELECT
  CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM student;

... und die Datensätze, die NULLs haben, haben auch :

Mit der Funktion COALESCE werden die NULLs, die in der Spalte middle_name erscheinen, ersetzt.

SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM student;

Verwendung von PostgreSQL COALESCE mit Aggregatfunktionen

Nun, NULLs kann in der Tat sehr problematisch erscheinen. Lassen Sie uns eine andere Situation annehmen. Ich möchte wissen, wie die Durchschnittsnote für den Kurs ist. Das scheint sehr einfach zu sein, oder? Aber, Moment, Moment... auch wenn es sich um eine einfache Abfrage handelt, können wir auf einige Nachteile stoßen. Das Problem betrifft einige der SQL-Aggregatfunktionen wie SUM(), MAX() oder MIN().

BEISPIEL: Verwendung von COALESCE mit der Funktion AVG

Lassen Sie uns ein Beispiel mit der Tabelle student_courses ausprobieren.

Wie Sie unten sehen, haben die Studenten, die für den Kurs mit der ID 1 registriert sind, noch keine Note.

Die Abfrage, die die Durchschnittsnote für den Kurs liefert, sieht wie folgt aus:

SELECT
  course_instance_id,
  AVG(grade) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Für die Funktion AVG() werden die Felder, die nichtNULL sind, summiert, und die Summe wird durch die Anzahl der Felder, die nichtNULL sind, geteilt. Wenn also alle Datensätze NULL in der Spalte "Note" haben, ist die Durchschnittsnote auch NULL.

Die Ergebnistabelle unten:

In einem solchen Fall möchten wir einen anderen Wert einfügen, z. B. 0 (die Note kann ein Wert von 2 bis 5 sein, so dass die Durchschnittsnote 0 für uns bedeuten kann, dass die Schüler keine Noten haben)

COALESCE() Die Funktion hilft bei der Ersetzung eines Wertes. Die gleiche Abfrage mit COALESCE wird sein:

SELECT
  course_instance_id,
  COALESCE(AVG(grade), 0) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Und jetzt können wir die Ergebnistabelle wie folgt erhalten:

BEISPIEL: Verwendung von COALESCE mit der Funktion SUMME()

Dies ist ein ähnliches Beispiel. Lassen Sie uns eine andere Situation annehmen. Wir wollen die Gesamtzahl der Abwesenheitsstunden für jeden Schüler zählen. Werfen Sie einen Blick auf den ausgewählten Teil des Modells:

Die Daten in der Tabelle student_course_attendance sehen wie folgt aus.

Während die Daten student_courses sind:

Schauen Sie sich die Daten in diesen Tabellen genau an. Wie Sie sehen, sind zwei Schüler für den betreffenden Kurs eingeschrieben, während nur ein Schüler den Unterricht verpasst hat.

Die Abfrage, die die Summe der Abwesenheitsstunden für eine course_id = 1 berechnet, lautet für jeden Schüler:

SELECT
  student_id,
  SUM(absence_hours) AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

... und wertet in einer Ergebnistabelle aus:

Die Gesamtabwesenheitsstunde wurde auf NULL ausgewertet, da dieser Schüler in dieser Kursinstanz tatsächlich keinen Unterricht versäumt hat. In der Tabelle student_course_attendance gab es keinen Datensatz für diesen Schüler, und die Funktion SUM() gab NULL zurück. Wir können diese Situation natürlich mit der COALESCE Funktion.

Die neue Abfrage lautet wie folgt:

SELECT
  student_id,
  COALESCE(SUM(absence_hours), 0)  AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

Und das Ergebnis ist jetzt:

Verlassen wir nun das Universitätsmodell und sehen uns das andere Beispiel an.

Verwendung von COALESCE bei der Erstellung von PIVOT-Tabellen

Dieser Anwendungsfall wird für die PostgreSQL-Datenbank vorgestellt. Beachten Sie, dass andere Datenbanken wie Oracle und MS SQL Server über entsprechende Funktionen zur Erstellung von Pivot-Tabellen verfügen.

Sehen Sie sich die folgende Tabelle an. Dies ist das Ergebnis einer Abfrage, die zeigt, wie hoch der Gesamtverkauf für jede Marke in einem bestimmten Monat ist.

Ich möchte die Zeilen in Spalten umwandeln (eine Pivot-Tabelle erstellen). Die Ergebnistabelle soll Spalten enthalten: brand_id, jan, feb, ..., dec. Beachten Sie, dass nicht in jedem Monat Produkte der jeweiligen Marke verkauft wurden. Werfen wir einen Blick auf das Postgres-Beispiel.

SELECT brand_id, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

FROM CROSSTAB (
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Nach der Ausführung einer solchen Abfrage, erhalten wir:

Leider wird der Verkauf nur in einigen der Monate aufgezeichnet und nur in diesen Feldern wird die Anzahl der Gesamtverkäufe eingefügt. Wie Sie wahrscheinlich erwartet haben, enthält der Rest der Felder NULLs.

Um die unerwünschten NULLs loszuwerden, können wir die Funktion COALESCE verwenden, die bei Bedarf eine 0 anstelle von NULL einfügt. Die reparierte Abfrage sieht wie folgt aus:

SELECT 
  brand_id,
  COALESCE(jan, 0),
  COALESCE(feb, 0),
  COALESCE(mar, 0),
  COALESCE(apr, 0),
  COALESCE(may, 0),
  COALESCE(jun, 0),
  COALESCE(jul, 0),
  COALESCE(aug, 0),
  COALESCE(sep, 0),
  COALESCE(oct, 0),
  COALESCE(nov, 0),
  COALESCE(dec, 0)
FROM CROSSTAB(
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Zusammenfassung

NULLs können das Leben problematisch machen. Wenn Sie die schlechte Seite der fehlenden Werte noch nicht erlebt haben, werden Sie sie mit Sicherheit kennen lernen. Es ist nur eine Frage der Zeit. Erinnern Sie sich an die FunktionCOALESCE , die Ihnen helfen wird, die unerwünschten NULLs zu beseitigen.