Wie man CASE WHEN in GROUP BY verwendet

Erfahren Sie, wie Sie SQL CASE WHEN und GROUP BY kombinieren können, um benutzerdefinierte Kategorien in Ihren SQL-Abfragen zu erstellen.

Rohdaten sind von Natur aus nicht immer für den Menschen lesbar. Oftmals liegen die Daten, die Sie abfragen, in ihrer unformatierten Form vor. Beispiele hierfür sind Codes für verschiedene Geschäftsabteilungen oder Produkt-SKUs, die für bestimmte Produkte stehen. Für das bloße Auge haben diese Codes keine Bedeutung, so dass es für die Person, die sie liest, nicht hilfreich ist, sie in einen Bericht zu ziehen.

Es gibt auch Situationen, in denen Rohdaten auf höhere Ebenen aufgerollt werden sollten, um sie leichter verdaulich zu machen. Eine Liste mit 50 Produkten könnte zum Beispiel auf 5 Produktkategorien aufgerollt werden, wodurch Ihr Bericht viel leichter zu lesen und zu verstehen wäre.

In solchen Situationen können wir die Anweisungen CASE WHEN und GROUP BY verwenden, um Daten zu formatieren und Metadaten zu unserem Originaldatensatz hinzuzufügen. Die Funktionsweise dieser Anweisungen wird in diesem Artikel näher erläutert. Wenn Sie einen tieferen Einblick in diese Konzepte erhalten möchten, besuchen Sie unsere Erstellen einfacher SQL-Berichte der Sie durch alle Nuancen von CASE und GROUP BY führt. Er ist eine hervorragende Ergänzung zu diesem Artikel.

Lassen Sie uns zunächst die Anweisung CASE WHEN mit ein paar Beispielen durchgehen. Wenn Sie weitere Informationen über die Anweisung CASE wünschen, lesen Sie bitte unseren Artikel Wie man CASE in SQL verwendet.

Ein kurzer Überblick über CASE WHEN

Sie können sich die Anweisung CASE WHEN als wenn...dann-Logik für Ihre Abfrage vorstellen. Sie wertet Bedingungen aus, und wenn die Bedingung erfüllt ist, gibt sie ein bestimmtes Ergebnis zurück.

Es gibt drei wichtige Teile von CASE Anweisungen in SQL: CASE WHEN, THEN, und END. Jedes dieser Elemente wird benötigt, sonst gibt Ihre Abfrage einen Fehler zurück.

Sie beginnen die Anweisung mit CASE WHEN, um Ihre logische Bedingung zu definieren. Danach verwenden Sie THEN, um den Wert zu definieren, wenn die Bedingung erfüllt ist. Nach der letzten Anweisung THEN verwenden Sie END, um die Klausel zu schließen.

Eine weitere optionale Klausel ist die ELSE -Klausel. Wenn alle logischen Bedingungen in der CASE WHEN -Anweisung fehlschlagen, können Sie ELSE verwenden, um diesen Daten einen Wert zuzuweisen. Sie ist quasi ein Auffangtatbestand. Wenn Ihre Daten alle Bedingungen nicht erfüllen und Sie ELSE nicht verwenden, geben die Daten einen NULL-Wert zurück.

So sieht die Syntax von CASE WHEN aus:

CASE WHEN product = ‘Shirt’ THEN ‘Clothing’ 
     WHEN product = ‘Hat’ THEN ‘Accessories’ 
     ELSE ‘Other’
END 

Wenn es sich bei dem Produkt um ein Hemd handelt, ordnet CASE WHEN es der Kategorie Clothing zu. Handelt es sich bei dem Produkt um einen Hut, ordnet CASE WHEN das Produkt der Kategorie Accessories zu. Andernfalls ordnet CASE WHEN das Produkt der Kategorie Other zu.

Zur weiteren Veranschaulichung betrachten Sie die folgenden Daten aus der cities Tabelle:

Wenn wir die Bevölkerung jeder Stadt in die Kategorien niedrig, mittel oder hoch einteilen wollten, würden wir diese Abfrage ausführen:

SELECT 
  city,
  population, 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level
FROM cities

In dieser Abfrage fügen wir eine bedingte Logik hinzu, die auf dem Feld population basiert. Wenn die Einwohnerzahl unter 500.000 liegt, weisen wir den Wert Low zu. Wenn die Einwohnerzahl zwischen 500.000 und 1.500.000 liegt, weisen wir den Wert Medium zu. Ist die Einwohnerzahl größer als 1 500 000, wird der Wert High zugewiesen. Die Spalte wird dann als population_level aliasiert. Das zurückgegebene Ergebnis sieht wie folgt aus:

Was wäre, wenn wir die durchschnittliche Bevölkerungszahl für jede population_level herausfinden wollten? In diesem Szenario können wir dies erreichen, indem wir eine Aggregation und eine GROUP BY Klausel einfügen. Wir werden dies im nächsten Abschnitt demonstrieren.

Verwendung von CASE WHEN mit GROUP BY

Beispiel 1: Aggregationen von benutzerdefinierten Kategorien

Jetzt, da wir unsere benutzerdefinierte Kategorie population_level haben, können wir verschiedene Metriken für sie berechnen. In diesem Beispiel berechnen wir die durchschnittliche Bevölkerung für jede population_level.

Wir verwenden die gleiche Anweisung CASE WHEN wie oben, fügen eine Aggregation für den Durchschnitt hinzu und verwenden dann GROUP BY mit der gleichen Syntax.

Führen wir diese Abfrage aus:

SELECT  
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Wir haben dieselbe Bevölkerungskategorie wie in der obigen Abfrage verwendet. Wir haben eine Berechnung für die durchschnittliche Bevölkerung hinzugefügt. Beachten Sie, dass wir denselben Ausdruck CASE WHEN in SELECT und in GROUP BY wiederholt haben.

Wenn diese Abfrage ausgeführt wird, werden die Daten zunächst in den logischen Bedingungen der Anweisung CASE WHEN ausgewertet und ein Wert für population_level zugewiesen. Dann wird der Durchschnitt über jede dieser Ebenen mit der GROUP BY berechnet.

Die zurückgegebenen Ergebnisse sehen wie folgt aus:

Wenn Sie eine Auffrischung der GROUP BY Klausel benötigen, lesen Sie diesen Artikel über GROUP BY in SQL.

Beispiel 2: CASE WHEN mit ELSE in GROUP BY

Eine andere Möglichkeit, diese Abfrage zu schreiben, wäre die Verwendung der ELSE Klausel. Sie würden die ersten beiden Bevölkerungsebenen definieren und dann ELSE verwenden, um jede andere Stadt in high einzuordnen. Dies würde wie die folgende Abfrage aussehen:

SELECT  
  CASE 
    WHEN population < 500000 THEN ‘Low’ 
    WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
    ELSE ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Die Logik für bevölkerungsreiche Städte muss nicht unbedingt definiert werden; wenn die Stadt die ersten beiden logischen Anweisungen nicht erfüllt, ist die Einwohnerzahl größer als 1.500.000.

Beispiel 3: CASE WHEN-Anweisungen innerhalb von Aggregationen

Wir können auch CASE WHEN Anweisungen innerhalb von Aggregatfunktionen platzieren, um die Anzahl der Datensätze zu zählen, die bestimmte Bedingungen erfüllen. Fahren wir mit demselben Beispiel fort und überlegen wir, wie wir die Anzahl der Städte in jedem population_level zählen würden.

Für dieses Beispiel benötigen wir drei separate CASE WHEN Anweisungen, eine für jede Bedingung, die wir in unserer CASE WHEN Anweisung definiert haben: low, medium, und high.

SELECT  
  SUM(CASE 
      WHEN population < 500000 
      THEN 1 
      ELSE 0 END) as low_pop_ct,
  SUM(CASE 
      WHEN population >= 500000 and population < 1500000 
      THEN 1   
      ELSE 0 END) as medium_pop_ct, 
  SUM(CASE 
      WHEN population >= 1500000 
      THEN 1 
      ELSE 0 END) as high_pop_ct
FROM cities

Gehen wir dies Zeile für Zeile durch. In der ersten Zeile prüfen wir, ob die Stadt weniger als 500.000 Einwohner hat. Wenn ja, wird der Stadt der Wert 1 zugewiesen. Ist dies nicht der Fall, wird ihr durch die Klausel ELSE der Wert 0 zugewiesen. Dann haben wir die gesamte Anweisung CASE WHEN in eine SUM() eingeschlossen. Dadurch werden die Zeilen für Städte mit geringer Einwohnerzahl effektiv gezählt.

Wir wiederholen dieses Muster in den nächsten beiden Zeilen und verwenden dieselben logischen Bedingungen, die wir zuvor verwendet haben, um die Werte von medium und high den Städten auf der Grundlage ihrer Einwohnerzahl zuzuweisen.

Diese Abfrage wird zurückgegeben:

Jetzt haben wir die Anzahl der Städte, die in jede Kategorie fallen.

Möchten Sie mehr erfahren? Weitere Beispiele für die Verwendung von CASE WHEN mit SUM() finden Sie hier.

Beispiel 4: CASE WHEN in GROUP BY

Sehen wir uns nun einige weitere Beispiele für die Verwendung von CASE WHEN in GROUP BY an. Die folgenden Daten sind in der Tabelle enthalten products.

Erweitern wir diese Daten um eine neue Spalte, die product_category enthält. Dadurch können wir die einzelnen Produkte in einer übergeordneten Kategorie gruppieren, so dass wir anschließend Aggregationen berechnen können.

Um eine Spalte für product_category hinzuzufügen, führen wir aus:

SELECT *, 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END as product_category 
FROM products

Wir verwenden den Operator LIKE, um das Beschreibungsfeld nach jeder Zeichenfolge in Anführungszeichen zu durchsuchen. Der Operator % bedeutet lediglich, dass vor oder nach der Beschreibung etwas stehen kann. Wir suchen also jede Beschreibung nach einem der Schlüsselwörter, die in den WHEN Anweisungen aufgeführt sind. Die Ergebnisse werden unten angezeigt:

Jetzt, da wir unser neues Feld product_category haben, können wir die Anzahl der Produkte in jeder Kategorie zählen, indem wir GROUP BY verwenden:

SELECT
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
   END as product_category, 
   COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

In diesem Beispiel haben wir CASE WHEN in der SELECT Klausel verwendet, aber das ist nicht immer erforderlich. Sie können die obige Abfrage auch einfach ohne diese Klausel ausführen:

SELECT COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

Was kommt als Nächstes mit CASE WHEN und GROUP BY?

Wie Sie sehen, gibt es viele verschiedene Szenarien, in denen die Kombination der Anweisungen CASE WHEN und GROUP BY äußerst nützlich ist. Sie ermöglichen es Ihnen, Ihren Daten Geschäftslogik hinzuzufügen und dann Metriken auf der Grundlage der neu definierten Datenfelder zu berechnen. Sie können CASE WHEN Anweisungen sowohl außerhalb als auch innerhalb von Aggregationen verwenden; sie folgen der gleichen Syntax. Beginnen Sie Ihre Anweisung mit CASE WHEN, um Ihre bedingte Logik zu definieren, und weisen Sie dann Werte mit den Anweisungen THEN/ELSE zu. Schließen Sie die Anweisung schließlich mit END ab.

Schauen Sie sich unbedingt unseren Kurs über Erstellen einfacher SQL-Berichte. Dort lernen Sie alle Feinheiten von CASE und GROUP BY kennen und erhalten echte Übungsaufgaben zum Ausfüllen!