Wie man mit SQL die ersten und letzten Werte mit den Funktionen FIRST_VALUE und LAST_VALUE abruft

In der Datenanalyse und Berichtserstellung mit SQL wird häufig der erste oder letzte Wert eines Datensatzes benötigt. Um dies einfach zu erreichen, können die Funktionen FIRST_VALUE und LAST_VALUE verwendet werden. In diesem Artikel werden wir detailliert erläutern, wie Sie diese Funktionen nutzen können, um Daten effizient zu manipulieren, und dabei konkrete Beispiele verwenden.

Inhaltsverzeichnis

Grundlegende Verwendung der FIRST_VALUE-Funktion

Die FIRST_VALUE-Funktion wird verwendet, um den ersten Wert innerhalb eines bestimmten Fensters oder einer Partition zu erhalten. Dadurch können Sie den ersten Wert der ersten Zeile eines Datensatzes leicht extrahieren.

Grundlegende Syntax

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

In diesem Beispiel wird die Datenpartition nach column2 aufgeteilt und der erste Wert von column1 basierend auf der Sortierung in column3 abgerufen.

Verwendungsbeispiel

Wenn Sie beispielsweise den Namen des ersten Mitarbeiters in jeder Abteilung aus einer Mitarbeiterdatenbank abrufen möchten, können Sie die folgende Abfrage verwenden.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

Diese Abfrage gibt den Namen des zuerst eingestellten Mitarbeiters für jede Abteilung zurück.

Erweiterte Verwendung der FIRST_VALUE-Funktion

Die FIRST_VALUE-Funktion ist nicht nur für grundlegende Abfragen nützlich, sondern auch für komplexere Analysen. Hier sind einige Beispiele, die den Einsatz in realen Datensätzen zeigen.

Ermitteln des ersten Verkaufsdatums eines Produkts

Angenommen, Sie möchten für jeden Laden das erste Verkaufsdatum eines Produkts aus den Verkaufsdaten ermitteln. Die folgende Abfrage erfüllt diesen Zweck.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

Diese Abfrage gibt das erste Verkaufsdatum für jedes Produkt in jedem Laden zurück.

Ermitteln des ersten Prüfungsergebnisses eines Schülers

Wenn Sie die Ergebnisse von Schülern verwalten, können Sie das erste Prüfungsergebnis jedes Schülers abrufen.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

Diese Abfrage gibt das erste Prüfungsergebnis jedes Schülers zurück.

Ermitteln des ersten Kaufbetrags eines Kunden

Hier ist ein Beispiel, wie Sie den ersten Kaufbetrag eines jeden Kunden aus den Transaktionsdaten abrufen können.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

Diese Abfrage gibt den ersten Kaufbetrag jedes Kunden zurück.

Grundlegende Verwendung der LAST_VALUE-Funktion

Die LAST_VALUE-Funktion wird verwendet, um den letzten Wert innerhalb eines bestimmten Fensters oder einer Partition zu erhalten. Dadurch können Sie den letzten Wert der letzten Zeile eines Datensatzes leicht extrahieren.

Grundlegende Syntax

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

In diesem Beispiel wird die Datenpartition nach column2 aufgeteilt und der letzte Wert von column1 basierend auf der Sortierung in column3 abgerufen. Die Verwendung der Klausel ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ist entscheidend, um das gesamte Fenster zu spezifizieren.

Verwendungsbeispiel

Wenn Sie beispielsweise den Namen des zuletzt eingestellten Mitarbeiters in jeder Abteilung abrufen möchten, können Sie die folgende Abfrage verwenden.

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

Diese Abfrage gibt den Namen des zuletzt eingestellten Mitarbeiters für jede Abteilung zurück.

Erweiterte Verwendung der LAST_VALUE-Funktion

Die LAST_VALUE-Funktion ist ebenso wie FIRST_VALUE eine leistungsstarke Funktion zur Datenanalyse und Berichtserstellung. Hier sind einige erweiterte Beispiele zur Verwendung.

Ermitteln des letzten Verkaufsdatums eines Produkts

Angenommen, Sie möchten für jeden Laden das letzte Verkaufsdatum eines Produkts aus den Verkaufsdaten ermitteln. Die folgende Abfrage erfüllt diesen Zweck.

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Diese Abfrage gibt das letzte Verkaufsdatum für jedes Produkt in jedem Laden zurück.

Ermitteln des letzten Prüfungsergebnisses eines Schülers

Wenn Sie die Ergebnisse von Schülern verwalten, können Sie das letzte Prüfungsergebnis jedes Schülers abrufen.

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Diese Abfrage gibt das letzte Prüfungsergebnis jedes Schülers zurück.

Ermitteln des letzten Kaufbetrags eines Kunden

Hier ist ein Beispiel, wie Sie den letzten Kaufbetrag jedes Kunden aus den Transaktionsdaten abrufen können.

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Diese Abfrage gibt den letzten Kaufbetrag jedes Kunden zurück.

Verwendung der FIRST_VALUE- und LAST_VALUE-Funktion in Kombination

Durch die Kombination der Funktionen FIRST_VALUE und LAST_VALUE können Sie sowohl den ersten als auch den letzten Wert eines Datensatzes abrufen. Dies ermöglicht beispielsweise den Vergleich der ersten und letzten Werte innerhalb eines bestimmten Zeitraums und bietet so eine erweiterte Analyse.

Ermitteln des ersten und letzten Verkaufsdatums eines Produkts

Die folgende Abfrage zeigt, wie Sie das erste und letzte Verkaufsdatum eines Produkts für jeden Laden abrufen können.

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Diese Abfrage gibt das erste und letzte Verkaufsdatum für jedes Produkt in jedem Laden zurück.

Ermitteln des ersten und letzten Eintrittsdatums eines Mitarbeiters

Um das erste und letzte Eintrittsdatum eines Mitarbeiters für jede Abteilung aus einer Mitarbeiterdatenbank abzurufen, verwenden Sie die folgende Abfrage.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Diese Abfrage gibt das erste und letzte Eintrittsdatum für jeden Mitarbeiter in jeder Abteilung zurück.

Ermitteln des ersten und letzten Kaufbetrags eines Kunden

Um den ersten und letzten Kaufbetrag eines jeden Kunden aus den Transaktionsdaten zu ermitteln, verwenden Sie die folgende Abfrage.

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Diese Abfrage gibt den ersten und letzten Kaufbetrag jedes Kunden zurück.

Verwendung als Fensterfunktion

Die Funktionen FIRST_VALUE und LAST_VALUE können als Fensterfunktionen verwendet werden, wodurch die Datenanalyse und -manipulation flexibler wird. Durch die Verwendung dieser Funktionen als Fensterfunktionen können die ersten und letzten Werte jeder Zeile berechnet und als Ergebnis zurückgegeben werden.

Grundlegendes Konzept von Fensterfunktionen

Fensterfunktionen führen Berechnungen über ein Ergebnisset aus und wenden diese auf die Zeilen innerhalb eines bestimmten Fensters an. Das Fenster wird durch die Klauseln PARTITION BY und ORDER BY definiert.

Grundlegende Syntax

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

Diese Syntax teilt die Daten nach column2 auf und gibt die ersten und letzten Werte von column1 basierend auf der Sortierung in column3 zurück.

Verwendungsbeispiel: Fensterfunktionen in Verkaufsdaten

Angenommen, Sie möchten das erste und letzte Verkaufsdatum für jeden Laden in den Verkaufsdaten abrufen. Die folgende Abfrage zeigt, wie Sie dies mit Fensterfunktionen erreichen können.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Diese Abfrage gibt das erste und letzte Verkaufsdatum für jeden Laden in den Verkaufsdaten zurück.

Verwendungsbeispiel: Schülerleistung

Um die ersten und letzten Prüfungsergebnisse jedes Schülers in den Leistungsdaten zu ermitteln, können Sie die folgende Abfrage verwenden.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Diese Abfrage gibt das erste und letzte Prüfungsergebnis jedes Schülers zurück.

Verwendung von Partitionierung und ORDER BY

Eine der leistungsstarken Funktionen der FIRST_VALUE- und LAST_VALUE-Funktionen ist die Möglichkeit, die Daten nach bestimmten Kriterien zu partitionieren und zu sortieren. Dadurch können Sie die ersten und letzten Werte innerhalb einer bestimmten Gruppe ermitteln.

Grundkonzept der Partitionierung

Unter Partitionierung versteht man die Gruppierung von Daten nach bestimmten Kriterien. Dadurch können Berechnungen unabhängig innerhalb jeder Gruppe durchgeführt werden. Die Partitionierung erfolgt mit der Klausel PARTITION BY.

Grundkonzept von ORDER BY

Die ORDER BY-Klausel wird verwendet, um die Zeilen innerhalb jeder Partition in einer bestimmten Reihenfolge zu sortieren. Dadurch können die ersten und letzten Werte korrekt ermittelt werden.

Verwendungsbeispiel von Partitionierung und ORDER BY

Die folgende Abfrage zeigt, wie Sie für jede Filiale das erste und letzte Verkaufsdatum jedes Produkts aus den Verkaufsdaten abrufen können.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Diese Abfrage gibt das erste und letzte Verkaufsdatum für jedes Produkt in jeder Filiale zurück.

Verwendungsbeispiel: Schülerdaten

Um das erste und letzte Prüfungsergebnis jedes Schülers in den Schülerdaten zu ermitteln, können Sie die folgende Abfrage verwenden.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Diese Abfrage gibt das erste und letzte Prüfungsergebnis jedes Schülers zurück.

Verwendungsbeispiel: Transaktionsdaten

Um den ersten und letzten Kaufbetrag jedes Kunden in den Transaktionsdaten zu ermitteln, verwenden Sie die folgende Abfrage.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Diese Abfrage gibt den ersten und letzten Kaufbetrag jedes Kunden zurück.

Übungen und Lösungen

Um Ihr Verständnis für die Funktionen FIRST_VALUE und LAST_VALUE zu vertiefen, haben wir einige Übungsfragen zusammengestellt. Zu jeder Frage gibt es auch eine Lösung, die Sie ausprobieren können.

Frage 1: Ermitteln Sie das erste und letzte Eintrittsdatum für jede Abteilung

Erstellen Sie eine Abfrage, um für jede Abteilung das erste und letzte Eintrittsdatum aus der Tabelle employees zu ermitteln.

-- employees-Tabelle
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

Lösung

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Frage 2: Ermitteln Sie das erste und letzte Verkaufsdatum jedes Produkts

Erstellen Sie eine Abfrage, um für jedes Produkt das erste und letzte Verkaufsdatum aus der Tabelle sales zu ermitteln.

-- sales-Tabelle
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

Lösung

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Frage 3: Ermitteln Sie den ersten und letzten Kaufbetrag jedes Kunden

Erstellen Sie eine Abfrage, um den ersten und letzten Kaufbetrag jedes Kunden aus der Tabelle transactions zu ermitteln.

-- transactions-Tabelle
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

Lösung

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Zusammenfassung

In diesem Artikel haben wir gelernt, wie Sie mit den SQL-Funktionen FIRST_VALUE und LAST_VALUE die ersten und letzten Werte eines Datensatzes abrufen können. Wir haben die grundlegende Verwendung, erweiterte Beispiele, die Verwendung als Fensterfunktion sowie die Kombination von Partitionierung und ORDER BY im Detail erläutert. Durch den Einsatz dieser Funktionen können Sie Ihre Datenanalyse und Berichtserstellung effizienter gestalten. Probieren Sie diese Techniken in Ihren eigenen Datensätzen aus!