6 Beispiele für die Funktion NTILE() in SQL

Die SQL-Funktion NTILE() kann Ihre Datenanalyse und Berichterstattung erheblich vereinfachen. In den folgenden sechs Beispielen erfahren Sie, was sie ist und wann Sie sie verwenden sollten.

Fensterfunktionen sind sehr nützlich und bieten SQL eine große Ausdruckskraft. In diesem Artikel geht es um die Funktion NTILE(), die es ermöglicht, eine Menge von Datensätzen in Teilmengen von annähernd gleicher Größe zu unterteilen. Diese Funktion wird häufig bei finanziellen oder wirtschaftlichen Berechnungen verwendet.

Bevor wir uns mit der Funktion NTILE() befassen, wollen wir uns kurz mit den Fensterfunktionen in SQL beschäftigen.

Was sind SQL Fensterfunktionen?

Fensterfunktionen sind Funktionen, die mit einer Gruppe von Zeilen arbeiten, die als Fenster bezeichnet werden, und die einen Wert auf der Grundlage dieser Gruppe zurückgeben. Die Funktion NTILE(N) Funktion erhält einen ganzzahligen Parameter(N) und unterteilt den kompletten Satz von Zeilen in N Teilmengen. Jede Untergruppe hat ungefähr die gleiche Anzahl von Zeilen und wird durch eine Nummer zwischen 1 und N identifiziert. Diese ID-Nummer ist das, was NTILE() zurückgibt.

Wenn Sie die SQL-Fensterfunktionen lernen wollen, empfehle ich Ihnen unseren interaktiven Kurs. Er enthält 218 praktische Übungen, mit denen Sie verschiedene Fensterfunktionen üben können. Am Ende des Kurses werden Sie mit dieser fortgeschrittenen SQL-Technik vertraut sein.

Wenn Sie die Fensterfunktionen in Aktion sehen möchten, lesen Sie unseren Artikel SQL-Fensterfunktionen - Beispiel mit Erklärungen. Und wenn Sie die Unterschiede zwischen GROUP BY und Fensterfunktionen erkunden möchten, lesen Sie SQL Fensterfunktionen vs. GROUP BY.

Beispiel-Datensatz: Ein Fußballgeschäft

Im weiteren Verlauf des Artikels werden wir unsere Beispiele auf die Datenbank eines fiktiven Fußballgeschäfts stützen. Wir werden uns auf die sales Tabelle, die die folgende Struktur und die folgenden Daten hat:

Ich denke, alles in dieser Tabelle ist selbsterklärend, also gehen wir zu den Beispielen über.

Beispiel 1: Aufteilung der Zeilen in 2 Gruppen

Wir beginnen mit einem sehr einfachen Beispiel. Wir wollen die Zeilen in der Tabelle sales Tabelle in zwei Gruppen aufteilen: Gruppe #1 und Gruppe #2. Dies geschieht mit der folgenden Abfrage:

SELECT NTILE(2) OVER() AS group, 
       sale_date, 
       product_id,
 soccer_team 
FROM sales;

In der Abfrage gibt der Ausdruck NTILE(2) OVER() für die ersten 50 % der Zeilen in der Ergebnismenge 1 und für die zweiten 50 % der Zeilen 2 zurück. Die Zeilen werden jeder Gruppe auf nicht-deterministische Weise zugewiesen, d. h. es gibt keine Kriterien für die Zuordnung von Zeilen zu einer bestimmten Gruppe. Hier ist ein Teilergebnis der Abfrage, das jede Gruppe in einer anderen Farbe zeigt:

Beispiel #2: Verteilung der Verkäufe 2023

Die Tabelle sales speichert Datensätze für jeden abgeschlossenen Verkauf. Die Marketingabteilung ist daran interessiert, die Umsatzverteilung auf der Grundlage des ausgegebenen Betrags zu analysieren. Sie bittet um einen Bericht, der alle Verkäufe des Jahres 2023 in vier gleich große Gruppen einteilt (die Anzahl der Verkäufe in jeder Gruppe muss gleich sein). Jeder Verkauf muss auf der Grundlage des Verkaufsbetrags zugeordnet werden.

Die erste Gruppe (sale_group #1) sollte die Verkäufe mit dem niedrigsten Betrag enthalten, und die letzte Gruppe (sale_group #4) sollte die Verkäufe mit dem höchsten Betrag enthalten. Für jeden Verkauf sollte der Bericht die Verkaufsgruppennummer, die customer_id, die product_id und die soccer_team enthalten. Die Abfrage, um dieses Ergebnis zu erhalten, ist die folgende:

  SELECT
      NTILE(4) OVER ( ORDER BY amount ) AS sale_group,
      product_id,
      product_category,
      soccer_team,
      amount as sales_amount
  FROM sales
  WHERE sale_date >= '2023-12-01' AND sale_date <= '2023-12-31';

Diese Abfrage verwendet eine WHERE Klausel, um nach Verkäufen zu filtern, die im Jahr 2023 stattgefunden haben. Dann versucht die Funktion NTILE(4), vier Gruppen von Zeilen mit jeweils der gleichen Anzahl von Zeilen zu erstellen. Wir verwenden "versucht", weil es nicht immer möglich ist, Gruppen mit der gleichen Größe zu erstellen; einige Gruppen können eine Zeile weniger als die anderen haben.

Wie wird festgelegt, zu welcher Gruppe jede Zeile gehört? Die Klausel OVER (ORDER BY amount) gibt an, dass alle Zeilen nach dem Verkaufsbetrag sortiert werden müssen, bevor sie den Gruppen zugeordnet werden. Nach der Sortierung nimmt die Funktion NTILE(4) das erste Viertel der Verkäufe und weist ihnen den Wert 1 zu, dann das nächste Viertel der Verkäufe und weist ihnen den Wert 2 zu und so weiter. Nachfolgend sehen Sie ein Teilergebnis, das die Datensätze in jedem sale_group zeigt:

Wir sehen, dass in der billigsten Gruppe (1) nur Verkäufe aus der Kategorie Zubehör enthalten sind. Das liegt daran, dass Zubehörprodukte in der Regel am billigsten sind, während Hemden in der Regel teurer sind. Wir können auch sehen, dass Produkte für Barcelona in allen vier Verkaufsgruppen zu finden sind, was darauf hindeutet, dass dieses Team ein Angebot zu allen Preispunkten hat.

Beispiel #3: Monatliche Verkäufe für jede Kategorie und Mannschaft

In der nächsten Abfrage erstellen wir einen Bericht über die monatlichen Verkäufe. Die Marketingabteilung möchte die monatlichen Verkäufe für jede Produktkategorie und jedes Team in vier Gruppen unterteilen. Die erste Gruppe enthält die Produktkategorien, Fußballmannschaften und Monate mit dem niedrigsten Gesamtumsatz. Die nächste Gruppe enthält die nächsthöhere Ebene total_sales und so weiter. Auf diese Weise können die Vermarkter die Verteilung der Verkäufe in den verschiedenen Monaten und Kategorien analysieren. Nachfolgend ist die Abfrage dargestellt:

WITH monthly_sales_stats AS (
   SELECT
        EXTRACT(MONTH FROM sale_date) as month,
        product_category,
        soccer_team,
        SUM(amount) AS total_sales
   FROM sales
   WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
   GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
SELECT
     NTILE(4) OVER ( ORDER BY total_sales ) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
FROM monthly_sales_stats
ORDER BY group DESC, total_sales DESC;

Die Abfrage hat einen gemeinsamen Tabellenausdruck (CTE) mit der Bezeichnung monthly_sales_stats, der den Gesamtumsatz für jede Kombination von Monat, product_category und soccer_team berechnet.

Beachten Sie, dass wir die Zeilen des Jahres 2023 in der WHERE Klausel filtern. Der Ausdruck EXTRACT(MONTH FROM sale_date) wird verwendet, um den Monatswert im Feld sale_date zu erhalten.

In der Hauptabfrage rufen wir NTILE(4) OVER (ORDER BY total_sales) auf, um jeder Zeile des CTE eine Gruppennummer zuzuweisen. Nachstehend sehen Sie ein Teilergebnis der Abfrage, das die ersten drei Zeilen jeder Gruppe zeigt:

Im Ergebnis sehen Sie, dass die höchsten Umsätze für jede Fußballmannschaft im Dezember erzielt werden, wahrscheinlich wegen des Weihnachtsgeschäfts. Die Kategorie "Zubehör" befindet sich am Ende der Ergebnistabelle, da Zubehör in der Regel preisgünstiger ist.

Beispiel Nr. 4: Niedrigster und höchster Umsatz der Mannschaften

Wie andere Fensterfunktionen auch, können Sie NTILE() mit der Klausel PARTITION BY verwenden. Hier ist ein Beispiel.

Das Marketingteam möchte untersuchen, wie sich die Verkäufe innerhalb der Artikel für jede Fußballmannschaft verteilen. Die Idee ist, die Verkäufe jeder Mannschaft in Gruppen auf der Grundlage des Betrags aufzuteilen. Auch hier werden wir die niedrigsten Umsätze in den ersten Satz aufnehmen und uns dann bis zu den höchsten Umsätzen im vierten Satz vorarbeiten.

Dies ist die Abfrage:

SELECT	soccer_team,
      	NTILE(4) OVER (PARTITION BY soccer_team ORDER BY amount) AS group_number,
product_id, 
product_category,
amount 
FROM sales ; 

Die Abfrage ist sehr einfach; sie hat eine SELECT mit einer Liste von Spalten und eine FROM mit einem Tabellennamen. Die Funktion NTILE() verwendet PARTITION BY und ORDER BY. Die Funktion PARTITION BY soccer_team ordnet alle Zeilen mit demselben Wert in soccer_team in dieselbe Partition ein. Der Betrag von ORDER BY ordnet die Datensätze in der Menge jeder Mannschaft, wobei die mit den niedrigsten Werten zuerst eingefügt werden. Dann gibt NTILE(4) für die ersten 25 % der Zeilen in der Gruppe von Zeilen 1 zurück, für die zweiten 25 % der Zeilen in der Gruppe 2 und so weiter.

Nachstehend finden Sie einige Teilergebnisse:

Beispiel Nr. 5: Weihnachtsverkaufsverhalten nach Fußballmannschaft

Dieses Beispiel ist dem Beispiel 3 sehr ähnlich, mit dem Unterschied, dass die Funktion NTILE() eine Unterklausel PARTITION BY soccer_team verwendet. Das bedeutet, dass NTILE() Gruppen von Zeilen für jede soccer_team erstellt, anstatt Gruppen aus der gesamten Ergebnismenge zu erstellen (wie in Beispiel 3). Das Ergebnis ist, dass jede Fußballmannschaft vier Gruppen hat.

Die Abfrage lautet:

WITH monthly_sales_stats AS (
       SELECT
            EXTRACT(MONTH FROM sale_date) as month,
            product_category,
            soccer_team,
            SUM(amount) AS total_sales
      FROM sales
      WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
      GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
  SELECT
     NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
  FROM monthly_sales_stats
  ORDER BY total_sales DESC;

Die CTE monthly_sales_stats ist genau dieselbe wie im vorherigen Beispiel. Sie enthält eine GROUP BY Klausel, mit deren Hilfe der Gesamtbetrag der Verkäufe für jede Kombination von Monat, product_category und soccer_team berechnet wird.

Nach der Erstellung der CTE schreiben wir eine SELECT mit dem folgenden Ausdruck NTILE():

NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group

Dieser Ausdruck teilt die CTE-Zeilen in Sätze auf, die denselben Wert im Feld soccer_team haben. Für jede Fußballmannschaft versucht NTILE(4), vier gleiche Teilmengen zu erstellen, wobei jeder Zeile ein Wert von 1 bis 4 zugewiesen wird.

Die restlichen Spalten im Ergebnis stammen aus der CTE. Nachfolgend sehen Sie Teilergebnisse mit zwei Zeilen für jede Gruppe für die Mannschaften von Barcelona und Manchester.

Beispiel Nr. 6: Gleichmäßig verteilte Gruppen von Kunden erhalten

Angenommen, die Marketingabteilung möchte drei gleichmäßig verteilte Kundengruppen bilden, um drei verschiedene Marketingkampagnen durchzuführen; jede Kampagne ist auf eine Gruppe ausgerichtet. Die Kunden in jeder Gruppe werden nach dem Zufallsprinzip ausgewählt. Anschließend vergleicht die Marketingabteilung die Ergebnisse der Kampagnen und bewertet, welche Kampagne besser ist.

Um eine zufällige Auswahl von Kunden zu simulieren, könnte man die Sekunden des letzten Kaufs jedes Kunden verwenden. Geordnet nach den Sekunden werden wir drei Gruppen von Kunden erstellen. Schauen wir uns die Abfrage an:

WITH customer_last_transaction_timestamp AS (
  SELECT customer_id, 
       max(sales_date) AS last_ts
  FROM   sales
  GROUP BY customer_id
)
SELECT NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts)) AS group_number, 
       customer_id 
FROM   customer_last_transaction_timestamp 
ORDER BY group_number;

Die vorherige Abfrage gibt alle Kunden mit einer group_number von 1 bis 3 zurück; dies stellt die Marketinggruppe dar, der der Kunde zugeordnet wurde. Die CTE customer_last_transaction_timestamp speichert jeden Kunden mit dem Zeitstempel seiner letzten Transaktion (erhalten mit MAX(sales_date)).

Die Hauptabfrage verwendet die Funktion NTILE(3), um drei Kundengruppen von ungefähr gleicher Größe zu erstellen:

NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts))

Die obige Anweisung gibt 1 für die 33 % der Kunden mit einem Zeitstempelwert im unteren Drittel zurück. Sie gibt 2 für die nächsten 33 Prozent zurück usw. Der Unterausdruck EXTRACT(SECOND FROM last_ts) entnimmt den Sekundenanteil (d. h. 22) aus einem Zeitstempel (z. B. '2023-03-30 10:30:22'). Unten sehen Sie ein Teilergebnis:

Lernen Sie weiter über NTILE() und anderes SQL Fensterfunktionen

In diesem Artikel haben wir mehrere Beispiele für die Verwendung der Funktion NTILE() window gezeigt. Außerdem haben wir verschiedene OVER -Klauseln vorgestellt. Die OVER Klausel ist allen SQL-Fensterfunktionen gemeinsam. Wenn Sie praktische Erfahrungen mit diesen Befehlen sammeln möchten, empfehle ich Ihnen unseren interaktiven Fensterfunktionen Kurs.

Wenn Sie häufig Fensterfunktionen verwenden, ist unser kostenloses SQL Fensterfunktionen Cheat Sheet eine sehr nützliche Ressource. Ich habe es sogar in meinem Büro an der Wand hängen, um es bei Zweifeln an der Syntax zu benutzen. Ich kann es nur empfehlen.

Und schließlich ist der Artikel Die 10 besten SQL-Fensterfunktionen Interviewfragen ideal, wenn Sie ein Vorstellungsgespräch haben und auf SQL-Themen vorbereitet sein wollen.

Viel Spaß beim Lernen, und machen Sie weiter mit den SQL-Fensterfunktionen!