Nützliche SQL-Muster: Bedingte Zusammenfassungen mit CASE

Wenn Sie mit dem Programmieren in SQL beginnen, werden Sie einige Anweisungen und Techniken immer wieder verwenden. Wir nennen diese "SQL-Muster". In dieser Serie werden wir uns die häufigsten SQL-Muster ansehen und überlegen, wie man sie verwendet.

Zuvor haben wir uns angesehen das SQL-Muster der passenden NULLs. Dies ist wichtig, wenn Sie Spalten mit NULL-Werten vergleichen. Heute wollen wir uns eine andere SQL-Praxis ansehen: die bedingte Zusammenfassung mit dem CASE-Operator.

Was ist die bedingte Verdichtung?

Wenn Sie Aggregatfunktionen zur Erstellung von Berichtsabfragen verwenden, werden Sie häufig eine bedingte Zusammenfassung mit dem Operator CASE verwenden. Denken Sie daran, dass CASE einen Wert zurückgibt, der auf definierten Kriterien basiert. (Weitere Informationen über den Ausdruck CASE finden Sie unter dieser Beitrag und dieser Beitrag.) Wenn Sie eine Verdichtung mit CASE durchführen, addieren (verdichten) Sie einfach die Werte, die dem Ausdruck CASE entsprechen.

Natürlich werden Sie den SUM Teil der Abfrage verwenden, um diese Werte zu aggregieren. Ich weiß, das klingt kompliziert, ist es aber nicht. Lassen Sie es uns anhand eines einfachen Beispiels erklären.

Wir beginnen mit der Betrachtung einer einfachen transactions Tabelle, die Transaktionsdaten eines kleinen Unternehmens enthält.

Die Tabelle transactions Tabelle hat diese Spalten:

• id - Ein eindeutiger Bezeichner für jede Transaktion
• datetime - Der Zeitstempel für die Transaktion
• customer - Die ID des Kunden
• creditcard - Die ID der verwendeten Kreditkarte
• amount - Der Transaktionsbetrag in Dollar
• account - die Kontonummer des Kunden
• type - Die Transaktionsart

Die folgenden Daten sind in einer solchen Tabelle zu finden:

Wir möchten die Summe der Transaktionsbeträge und die Anzahl der vor dem 1. April abgeschlossenen Transaktionen ermitteln. Außerdem möchten wir diese nach einzelnen Kunden auflisten. Dies könnten wir mit der folgenden Abfrage erreichen:

	SELECT
 customer,
 SUM(
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END
 )        AS sum_amount_after,
 SUM(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_after,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END) AS sum_amount_prior,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_prior
 FROM transactions
 WHERE datetime BETWEEN TIMESTAMP '2017-01-01'  AND '2017-05-01'
 GROUP BY customer

Das Ergebnis dieser Abfrage ist:

Moment! Wie hat das funktioniert?

Diese lange Abfrage kann verwirrend sein, daher wollen wir sie ein wenig aufschlüsseln. Wir konzentrieren uns zunächst auf den Teil, der sich mit dem Stichtag 1. April (2017-04-01) befasst.

👾 [NEW] Was passiert, wenn man #CASE mit den #SQL-Anweisungen zur #Datenänderung kombiniert? Finde es heraus!!! ➽ https://t.co/7IuBoDT85z... pic.twitter.com/wNiDiv5hSa

- Vertabelo (@Vertabelo) June 6, 2017

Im Folgenden sehen wir uns den Transaktionsbetrag für den Kunden "1" an. Alle Transaktionsbeträge, die vor dem 01.04.2017 gebucht wurden, werden auf "0" gesetzt. Wir werden diese Spalte amount_after nennen.

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE 0
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Alternativ können wir die in den Ergebnissen angezeigten Nullen in der Anweisung ELSE durch ein NULL ersetzen:

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE null
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Wenn wir nun diese Spalten zusammenfassen, erhalten wir:

1. Die Summe aller Transaktionen für sum(amount).
2. Die Summe aller Transaktionen, die nach dem 01.04. gebucht wurden. Alle Transaktionen, die vor dem 01.04. gebucht wurden, werden für sum(amount_after) auf Null (oder NULL) gesetzt.

Wenn wir zählen wollen, wie viele Transaktionen nach dem 01.04. gebucht wurden, können wir die Abfrage ändern und eine COUNT -Anweisung erstellen, die dieselbe CASE mit NULL in ELSE verwendet.

SELECT
  customer,
  count(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
    THEN amount
        ELSE NULL
        END)
    AS count_after
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
GROUP BY customer
ORDER BY customer;

Hinweis: Diese Abfrage ist sehr schnell, da das RDBMS nur auf eine Tabelle zugreifen muss. Das Erstellen von Aggregationsabfragen, die nur eine Tabelle verwenden, ist ein guter Weg, um schnell Ergebnisse zu erhalten.

Versuchen Sie, CASE mit einer Null in der Anweisung COUNT zu verwenden. Was ist das Ergebnis und warum? Sagen Sie es uns in den Kommentaren unten.