SQL-Praxis: 11 SQL-Fensterfunktionen Übungsaufgaben mit detaillierten Lösungen

In diesem Artikel stellen wir 11 Übungsaufgaben mit SQL-Fensterfunktionen vor, zusammen mit Lösungen und detaillierten Erklärungen.

SQL-Fensterfunktionen sind eine leistungsstarke Funktion, mit der wir auf einfache Weise aussagekräftige Erkenntnisse aus unseren Daten gewinnen können, doch nur wenige SQL-Kurse bieten Übungen zu SQL-Fensterfunktionen an. Das macht das Üben von Fensterfunktionen ziemlich schwierig. In diesem Artikel stellen wir Ihnen 11 Übungsaufgaben mit Fensterfunktionen vor.

Alle in diesem Artikel gezeigten Übungen stammen aus unseren interaktiven Kursen Fensterfunktionen und Fensterfunktionen Übungssatz. Der Fensterfunktionen Kurs ist ein ausführliches Tutorial mit über 200 Übungen. Wir empfehlen diesen Kurs, um das Wissen über SQL-Fensterfunktionen zu erlernen oder zu überprüfen. Fensterfunktionen Das Practice Set ist ein Übungskurs für diejenigen, die die SQL-Fensterfunktionen kennen und mehr Übung suchen.

Fensterfunktionen kurz und bündig

SQL-Fensterfunktionen sind Werkzeuge, die bei der Analyse von Daten auf verschiedene Weise helfen. Von der Berechnung laufender Summen und gleitender Durchschnitte, dem Vergleich von Daten innerhalb bestimmter Teilmengen und der Identifizierung von Top-Performern bis hin zur Erstellung komplexer Rankings und Partitionierungen - diese Funktionen ermöglichen es uns, tiefere Einblicke aus unseren Daten zu gewinnen und komplexe Aufgaben einfach zu bewältigen.

Die SQL-Fensterfunktionen bieten ein vielseitiges Toolkit für eine verbesserte Datenanalyse. Dieses Toolkit umfasst:

• ROW_NUMBER(), RANK(), DENSE_RANK(), und NTILE() für das Ranking von Daten.
• SUM() AVG(), , und für Aggregationen. COUNT() MAX() MIN()
• LEAD() und LAG() für den zeilenübergreifenden Vergleich von Daten.
• FIRST_VALUE() und LAST_VALUE() für die Extraktion von Grenzwerten.

Darüber hinaus ermöglicht die OVER() -Klausel eine präzise Datenpartitionierung und -anordnung innerhalb dieser Funktionen, so dass Benutzer komplexe Berechnungen auf definierten Teilmengen von Daten durchführen können.

Die Beherrschung der SQL-Fensterfunktionen wird für Datenexperten, Analysten und Ingenieure immer wichtiger. Sie befähigen sie nicht nur zur effizienten Lösung komplexer analytischer Aufgaben, sondern sorgen auch für ein tieferes Verständnis der Daten. Das Üben von SQL-Fensterfunktionen verbessert Ihre Fähigkeit, fortgeschrittene Abfragen zu erstellen, und hilft Ihnen, neue Erkenntnisse aus Daten zu gewinnen. Dies ist in der heutigen datenorientierten Welt eine wichtige Fähigkeit.

Bevor Sie die Übungen in Angriff nehmen, sollten Sie einen Blick auf unseren Spickzettel für Fensterfunktionen werfen, der Sie an die Liste der Fensterfunktionen und ihre Syntax erinnert.

SQL Fensterfunktionen Praxisübungen: Online-Filmladen

Bevor wir mit den Übungen beginnen, sehen wir uns das Dataset an, das wir verwenden werden.

Datensatz

Für die folgenden Übungen wird die Datenbank des Online-Filmladens verwendet, die sechs Tabellen enthält.

• Die Tabelle customer Tabelle speichert Informationen über alle registrierten Kunden. Die Spalten sind id, first_name, last_name, join_date und country.
• Die Tabelle movie enthält Datensätze über alle im Shop verfügbaren Filme. Die Spalten lauten id, title, release_year, genre, und editor_ranking.
• Die Tabelle review Tabelle speichert die Kundenbewertungen der Filme. Die Spalten sind id, rating, customer_id (verweist auf die customer Tabelle), und movie _id (verweist auf die movie Tabelle).
• Die Tabelle single_rental speichert Informationen über Filme, die von Kunden für einen bestimmten Zeitraum ausgeliehen wurden. Die Spalten sind id, rental_date, rental_period, platform, customer_id (verweist auf die customer Tabelle), movie _id (verweist auf die movie Tabelle), payment_date, und payment_amount.
• Die Tabelle subscription Tabelle speichert Datensätze für alle Kunden, die den Shop abonniert haben. Die Spalten sind id, length (in Tagen), start_date, platform, payment_date, payment_amount und customer_id (verweist auf die customer Tabelle).
• Die Tabelle giftcard enthält Informationen über gekaufte Geschenkkarten. Die Spalten lauten id, amount_worth, customer_id (verweist auf die customer Tabelle), payment_date, und payment_amount.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren.

Übung 1: Rangfolge der Vermietungen nach Preis

Übung:

Zeigen Sie für jeden einzelnen Verleih die rental_date, den Titel des ausgeliehenen Films, sein Genre, den Zahlungsbetrag und den Rang des Verleihs in Bezug auf den gezahlten Preis (der teuerste Verleih sollte den Rang = 1 haben). Die Rangliste sollte für jedes Filmgenre getrennt erstellt werden. Lassen Sie denselben Rang für mehrere Zeilen zu und erlauben Sie Lücken in der Nummerierung.

Lösung:

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Erklärung der Lösung:

Die Anweisung sagt uns, dass wir bestimmte Informationen über einzelne Ausleihen und Filme anzeigen sollen. Wir verbinden also die Tabelle single_rental Tabelle mit der movie über ihre gemeinsame Spalte (d. h. die Spalte movie_id ).

Als Nächstes müssen wir alle Verleihvorgänge nach dem gezahlten Preis pro Verleihvorgang ordnen. Dazu verwenden wir RANK(). In der Klausel OVER() ordnen wir dann die Daten nach der Spalte payment_amount in absteigender Reihenfolge, so dass die teuerste Vermietung den Rang 1 hat.

Da die Rangliste für jedes Filmgenre getrennt erstellt werden soll, werden die Daten in der OVER() -Klausel nach der Genre-Spalte aufgeteilt.

Warum haben wir RANK() anstelle von DENSE_RANK() oder ROW_NUMBER() gewählt? Die Anweisung besagt, dass derselbe Rang für mehrere Zeilen zulässig ist; daher reduzieren wir die Optionen auf RANK() und DENSE_RANK(). Die Funktion ROW_NUMBER() weist aufeinanderfolgenden Zeilen fortlaufende Nummern als Ränge zu; sie lässt nicht zu, dass mehrere Zeilen denselben Rang haben.

Lücken in der Zeilennummerierung sind erlaubt, daher benötigen wir die Funktion RANK(). DENSE_RANK() überspringt keine Nummer in einer Folge, auch wenn mehrere Zeilen den gleichen Rang haben. Die folgende Tabelle zeigt diese Rangfolge-Funktionen und wie sie bei einer Liste von Datenwerten funktionieren:

Lesen Sie diesen Artikel, um mehr über die verschiedenen Rangfunktionen zu erfahren.

Übung 2: Finden Sie den^zweiten Kunden, der eine Geschenkkarte kauft

Übung:

Geben Sie den Vor- und Nachnamen des Kunden an, der die zweitjüngste Geschenkkarte gekauft hat, sowie das Datum, an dem die Zahlung erfolgt ist. Gehen Sie davon aus, dass für jeden Geschenkkartenkauf ein eindeutiger Rang vergeben wird.

Lösung:

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Erklärung der Lösung:

Wir wollen Informationen über Kunden und deren Geschenkkartenkäufe anzeigen, also müssen wir die Tabelle customer Tabelle mit der giftcard Tabelle über ihre gemeinsame Spalte (customer_id) verbinden.

Die Anweisung besagt, dass der Kunde gefunden werden soll, der den zweitneuesten Geschenkgutschein gekauft hat. Dazu ordnen wir zunächst die Geschenkkartenkäufe mit der Funktion ROW_NUMBER(); wir gehen davon aus, dass jedem Geschenkkartenkauf ein eindeutiger Rang zugewiesen wird.

Die innere Anweisung SELECT wählt die Kundeninformationen und die Daten der Geschenkkartenkäufe aus. Anschließend werden die Zeilen mit der Funktion ROW_NUMBER() in eine Rangfolge gebracht, um den zweitjüngsten Geschenkkartenkauf (d. h. den Rangwert 2) zu markieren.

Diese innere SELECT -Anweisung ist eine Common Table Expression (CTE). Sie ist in die WITH Klausel eingeschlossen und heißt ranking. Wir wählen relevante Daten aus dieser CTE aus und geben eine Bedingung in der WHERE -Klausel an, um nur die Zeile mit dem Rangwert 2 auszugeben.

Warum müssen wir eine CTE definieren und sie dann abfragen? Weil wir die Rangspalte nicht in der WHERE -Klausel der inneren SELECT verwenden können. Der Grund dafür ist die Reihenfolge der Ausführung, die wie folgt lautet FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, und LIMIT. Die Rangspalte ist also zu dem Zeitpunkt, zu dem die WHERE Klausel der inneren SELECT ausgeführt wird, noch nicht definiert.

Übung 3: Berechnen der laufenden Summe für Zahlungen

Übung:

Zeigen Sie für jede einzelne Miete die id, rental_date, payment_amount und die laufende Summe von payment_amounts aller Mieten von der ältesten (im Sinne von rental_date) bis zur aktuellen Zeile.

Lösung:

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Erklärung der Lösung:

Zuerst wählen wir Informationen über jede einzelne Vermietung aus der single_rental Tabelle.

Anschließend ermitteln wir die laufende Summe der Zahlungsbeträge aller Vermietungen mit Hilfe der Funktion SUM() (die die Spalte payment_amount als Argument verwendet) mit der Klausel OVER(). Hier ist ein Artikel, der die Details über die laufende Summe und ihre Berechnung in SQL erklärt.

Die Anweisung besagt, dass die laufende Summe vom ältesten Mietdatum bis zum Datum der aktuellen Zeile ermittelt werden soll. In der OVER() Klausel müssen wir also die Daten nach der Spalte rental_date ordnen und dann definieren, dass ROWS in der laufenden Summe vom ältesten Datum (BETWEEN UNBOUNDED PRECEDING) bis zum aktuellen Datum (AND CURRENT ROW) gezählt werden soll.

SQL Fensterfunktionen Praxisübungen: Gesundheitsklinik

Datensatz

Die folgenden Übungen verwenden eine Klinikdatenbank, die zwei Tabellen enthält.

• Die Tabelle doctor Tabelle speichert Informationen über Ärzte. Die Spalten sind id, first_name, last_name und age.
• Die Tabelle procedure enthält Informationen über Verfahren, die von Ärzten an Patienten durchgeführt wurden. Die Spalten sind id, procedure_date, doctor_id (verweist auf die doctor Tabelle), patient_id, category, name, price, und score.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren.

Übung 4: Berechnen Sie den gleitenden Durchschnitt für Werte

Übung:

Geben Sie für jede Prozedur die folgenden Informationen an: procedure_date, doctor_id, category, name, score und den Durchschnittswert der Prozeduren derselben Kategorie, die im folgenden Fensterrahmen enthalten sind: die beiden vorherigen Zeilen, die aktuelle Zeile und die drei folgenden Zeilen in Bezug auf das Prozedurdatum.

Lösung:

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Erklärung der Lösung:

Wir zeigen die Informationen für jede Prozedur an, indem wir sie aus der procedure Tabelle.

Anschließend ermitteln wir die durchschnittliche Punktzahl der Verfahren in derselben Kategorie. Dazu verwenden wir die Funktion AVG() mit der Spalte score als Argument. Es folgt die Klausel OVER(), mit der wir den Datensatz in Kategorien unterteilen.

Außerdem müssen wir nur die Zeilen berücksichtigen, die im folgenden Fensterrahmen enthalten sind: die beiden vorherigen Zeilen, die aktuelle Zeile und die drei folgenden Zeilen in Bezug auf das Verfahrensdatum. Wir definieren diesen Datenrahmen in der OVER() Klausel. Zunächst ordnen wir den Datensatz nach der Spalte procedure_date, damit die Verfahren chronologisch aufgelistet werden. Dann definieren wir die Zeilen, die für die Berechnung des Durchschnittswerts berücksichtigt werden: zwei vorhergehende Zeilen (BETWEEN 2 PRECEDING) und drei folgende Zeilen (AND 3 FOLLOWING), einschließlich der aktuellen Zeile.

Dies wird als gleitender Durchschnitt bezeichnet. Weitere Informationen finden Sie unter Was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet.

Übung 5: Die Differenz zwischen Prozedurpreisen ermitteln

Übung:

Zeigen Sie für jede Prozedur die folgenden Informationen an: id, procedure_date, name, price, price der vorherigen Prozedur (in Bezug auf id) und die Differenz zwischen diesen beiden Werten. Benennen Sie die beiden letzten Spalten previous_price und difference.

Lösung:

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Erklärung der Lösung:

Auch hier beginnen wir mit der Auswahl von Informationen über jedes Verfahren aus der procedure Tabelle.

Die Anweisung besagt, dass der Preis der vorherigen Prozedur angezeigt werden soll. Dazu verwenden wir die Funktion LAG(), die den Wert der vorherigen Zeile für ihr Argument zurückgibt (hier für die Spalte price ). Um sicherzustellen, dass wir den Preis des vorherigen Verfahrens in Bezug auf id auswählen, ordnen wir den Datensatz nach der Spalte id in der Klausel OVER(). Wir geben ihr den Alias previous_price.

Da wir nun den Preiswert und den vorherigen Preiswert haben, können wir die Differenz zwischen diesen beiden Werten auswählen. Wir subtrahieren einfach die Funktion LAG() von der Spalte price und geben ihr den Alias difference.

Weitere Informationen finden Sie in How to Calculate the Difference Between Two Rows in SQL.

Übung 6: Die Differenz zwischen dem aktuellen und dem besten Preis ermitteln

Übung:

Zeigen Sie für jede Prozedur den:

• procedure_date
• name
• price
• category
• score
• Preis der besten Prozedur (in Bezug auf die Punktzahl) aus derselben Kategorie (Spalte best_procedure).
• Die Differenz zwischen diesem price und dem best_procedure (Spalte difference).

Lösung:

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Erklärung der Lösung:

Wir beginnen mit der Auswahl von Informationen über jedes Verfahren aus der Verfahrenstabelle.

Der nächste Schritt besteht darin, den Preis für das beste Verfahren zu ermitteln. Wir verwenden die Funktion FIRST_VALUE(), die den ersten Wert in einer geordneten Partition einer Ergebnismenge zurückgibt. Um den Preis der besten Prozedur aus derselben Kategorie zu erhalten, müssen wir den Datensatz nach der Spalte category partitionieren. Um den Preis der besten Prozedur in Bezug auf die Punktzahl zu erhalten, müssen wir den Datensatz nach der Punktzahlspalte in absteigender Reihenfolge ordnen. Dieser Ausdruck wird als best_procedure bezeichnet.

Schließlich ermitteln wir die Differenz zwischen price und best_procedure, indem wir die Funktion FIRST_VALUE() von der Preisspalte subtrahieren.

Übung 7: Den besten Arzt pro Verfahren finden

Übung:

Finden Sie heraus, welcher Arzt für jede Prozedur der beste ist. Wählen Sie für jede Prozedur den Namen der Prozedur und den Vor- und Nachnamen aller Ärzte, die eine hohe Punktzahl erreicht haben (höher oder gleich der Durchschnittspunktzahl für diese Prozedur). Ordnen Sie die Ärzte pro Verfahren danach, wie oft sie dieses Verfahren durchgeführt haben. Zeigen Sie dann die besten Ärzte für jedes Verfahren an, d. h. diejenigen, die einen Rang von 1 haben.

Lösung:

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Erklärung der Lösung:

Zunächst wählen wir den Namen des Verfahrens und die Informationen über die Ärzte aus, also verbinden wir die procedure Tabelle mit der doctor Tabelle über ihre gemeinsame Spalte (doctor_id).

Wir wollen alle Ärzte auswählen, die eine hohe Punktzahl erreicht haben (höher oder gleich der durchschnittlichen Punktzahl für dieses Verfahren). Zu diesem Zweck definieren wir die Bedingung der WHERE Klausel für die Spalte score. Die Spalte score muss einen Wert enthalten, der gleich oder größer ist als die durchschnittliche Punktzahl für die Prozedur der aktuellen Zeile.

Sortieren wir die Ärzte nach Verfahren. Dazu verwenden wir die Funktion RANK() mit der Klausel OVER(), in der wir den Datensatz nach dem Namen der Prozedur partitionieren. Außerdem müssen wir die Rangfolge danach erstellen, wie oft der Arzt diese Prozedur durchgeführt hat. Um die Anzahl der vom Arzt durchgeführten Eingriffe zu ermitteln, müssen wir COUNT(*) verwenden, wobei wir nach dem Namen des Eingriffs und dem Vor- und Nachnamen des Arztes gruppieren (d. h. wir gruppieren nach allen in der Anweisung SELECT aufgeführten Spalten).

Alles, was wir bisher getan haben, ist die Definition eines Common Table Expression (CTE), d. h. der inneren SELECT -Anweisung, die von der WITH -Klausel eingeschlossen ist und cte heißt.

Nun wählen wir die relevanten Spalten aus dieser CTE aus. Um die besten Ärzte für jede Prozedur zu erhalten (diejenigen mit einem Rang von 1), definieren wir die WHERE Klausel mit der Bedingung für die rank Spalte.

Warum müssen wir eine CTE definieren und sie dann abfragen? Weil wir die Spalte rank nicht in der Klausel WHERE des inneren SELECT verwenden können. Der Grund dafür ist die Reihenfolge der Ausführung, die wie folgt lautet: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, und LIMIT. Die Spalte rank ist zum Zeitpunkt der Ausführung der WHERE Klausel noch nicht definiert.

SQL Fensterfunktionen Praxisübungen: Sportliche Meisterschaften

Datensatz

Die folgenden Übungen verwenden die Datenbank für Leichtathletikmeisterschaften, die acht Tabellen enthält.

• Die Tabelle competition speichert Informationen über Wettbewerbe. Die Spalten sind id, name, start_date, end_date, year und location.
• In der Disziplinentabelle werden Informationen zu allen Laufdisziplinen (von den Kurzstreckenläufen (z.B. 100 Meter) bis zu den Langstreckenläufen (z.B. Marathon)) gespeichert. Die Spalten sind id, name, is_men und distance.
• Die Tabelle event speichert Informationen über den Wettbewerb und die Disziplin für jede Veranstaltung. Die Spalten sind id, competition_id (verweist auf die competition Tabelle), und discipline_id (verweist auf die discipline Tabelle).
• Die Tabelle round Tabelle speichert die Runden der einzelnen Disziplinen. Die Spalten lauten id, event_id (verweist auf die event Tabelle), round_name, round_number, und is_final.
• Die Tabelle race Tabelle speichert die Daten für jedes Rennen jeder Runde. Die Spalten lauten id, round_id (verweist auf die Tabelle round Tabelle), round_name (wie in der round Tabelle), race_number, race_date, is_final (wie in der Tabelle round Tabelle), und wind.
• Die Tabelle athlete speichert Informationen über die am Wettbewerb teilnehmenden Athleten. Die Spalten sind id, first_name, last_name, nationality_id (verweist auf die nationality Tabelle), und birth_date.
• Die Tabelle nationality speichert Informationen über die Herkunftsländer der Athleten. Die Spalten lauten id, country_name und country_abbr.
• Die Tabelle result speichert Informationen über alle Teilnehmer einer bestimmten Veranstaltung. Die Spalten sind race_id (verweist auf die race Tabelle), athlete_id (verweist auf die athlete Tabelle), result, place, is_dsq, is_dns, und is_dnf.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren.

Übung 8: Berechnen Sie die Differenz zwischen den täglichen Durchschnittswerten der Windgeschwindigkeit

Übung:

Geben Sie für jedes Datum, an dem ein Rennen stattgefunden hat, die race_date, die durchschnittliche Windgeschwindigkeit an diesem Datum, gerundet auf drei Dezimalstellen, und die Differenz zwischen der durchschnittlichen Windgeschwindigkeit an diesem Datum und der durchschnittlichen Windgeschwindigkeit am Tag davor, ebenfalls gerundet auf drei Dezimalstellen, an. Die Spalten sollten die Namen race_date, avg_wind und avg_wind_delta tragen.

Lösung:

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Erklärung der Lösung:

Wir wollen die Informationen zu den einzelnen Wettkampftagen anzeigen, also wählen wir Daten aus der race Tabelle.

Um die durchschnittliche Windgeschwindigkeit an diesem Tag auf drei Dezimalstellen gerundet zu ermitteln, verwenden wir die Funktion AVG() mit der Spalte wind als Argument. Dann schließen wir sie in die Funktion ROUND() ein und runden sie auf drei Dezimalstellen. Beachten Sie, dass wir nach der Spalte race_date gruppieren müssen, da wir die Aggregatfunktion AVG() verwenden.

Wir können den durchschnittlichen Wind am Vortag ermitteln, indem wir die Funktion LAG() mit dem Wert AVG(wind) als Argument verwenden. Die Klausel OVER() legt fest, dass der gesamte Datensatz nach der Spalte race_date geordnet wird, damit die Datenzeilen chronologisch aufgelistet werden.

Da wir die Differenz zwischen der durchschnittlichen Windgeschwindigkeit an diesem Tag und der durchschnittlichen Windgeschwindigkeit am Tag davor sehen wollen, subtrahieren wir LAG(AVG(wind)) von AVG(wind). Und um auf drei Dezimalstellen zu runden, verwenden wir wieder die Funktion ROUND().

Übung 9: Vergleichen Sie die besten und vorherigen Ergebnisse

Übung:

Zeigen Sie für jede Frau, die in der Endrunde des Frauenmarathons in Rio gelaufen ist, die folgenden Informationen an:

• Den Platz, den sie im Rennen erreicht hat.
• Ihren Vornamen.
• Ihr Nachname.
• comparison_to_best - Die Differenz zwischen ihrer Zeit und der besten Zeit in diesem Finale.
• comparison_to_previous - Die Differenz zwischen ihrer Zeit und dem Ergebnis des Athleten, der die nächsthöhere Zeit erzielt hat

Sortieren Sie die Zeilen nach der Spalte place.

Lösung:

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Erklärung der Lösung:

Wir werden Informationen über Wettbewerbe, Disziplinen, Runden, Athleten und Ergebnisse verwenden. Daher müssen wir alle diese Tabellen über ihre gemeinsamen Spalten verbinden, wie in der Datensatzeinführung erwähnt.

Die Anweisung besagt, dass Informationen für jede Frau angezeigt werden sollen, die an der Endrunde des Frauen-Marathons in Rio teilgenommen hat. Dies wird in der Klausel WHERE abgedeckt, die die folgenden Bedingungen enthält:

• Der Wettkampfname muss Rio de Janeiro Olympic Games lauten.
• Der Name der Disziplin muss Women's Marathon lauten.
• Die Runde muss die Endrunde sein.

Als nächstes wählen wir die Spalte place aus der Tabelle result und die Spalten first_name und last_name aus der Tabelle athlete Tabelle.

Um die Differenz zwischen ihrer Zeit und der besten Zeit in diesem Finale zu ermitteln, verwenden wir die Funktion FIRST_VALUE() mit der Spalte result als Argument. Danach folgt die Klausel OVER(), die den Datensatz nach der Spalte result ordnet. Dann subtrahieren wir diese Funktion FIRST_VALUE() von der aktuellen Zeile result. Diese Funktion wird als comparison_to_best bezeichnet.

Um die Differenz zwischen ihrer Zeit und dem Ergebnis des Athleten zu ermitteln, der die nächstbessere Platzierung erreicht hat, verwenden wir die Funktion LAG() mit der Spalte result als Argument, um das vorherige Ergebnis zu erhalten. Auch hier folgt die Klausel OVER(), um den Datensatz nach der Spalte result zu ordnen (um sicherzustellen, dass wir das nächstbessere Ergebnis erhalten). Dann subtrahieren wir diese Funktion LAG() von der aktuellen Zeile result. Wir geben ihr den Alias comparison_to_previous.

Schließlich sortieren wir die Zeilen nach der Spalte place mit der Klausel ORDER BY.

SQL Fensterfunktionen Praxisübungen: Website-Statistiken

Datensatz

Die folgenden Übungen verwenden die Website-Statistikdatenbank, die zwei Tabellen enthält.

• Die Tabelle website Tabelle speichert Informationen über Websites. Die Spalten sind id, name, budget und opened.
• Die Tabelle statistics Die Spalten sind website_id (verweist auf die Tabelle website Tabelle), day, users, impressions, clicks, und revenue.

Da wir nun mit dem Datensatz vertraut sind, können wir mit den SQL-Übungen fortfahren.

Übung 10: Vorausschauend mit der Funktion LEAD()

Übung:

Nehmen Sie die Statistiken für die Website mit id = 2 zwischen dem 1. und 14. Mai 2016 und zeigen Sie den Tag, die Anzahl der Nutzer und die Anzahl der Nutzer 7 Tage später.

Beachten Sie, dass die letzten 7 Zeilen keinen Wert in der letzten Spalte haben. Das liegt daran, dass für sie keine Zeilen "7 Tage ab jetzt" gefunden werden können. Zeigen Sie in diesen Fällen -1 anstelle von NULL an, wenn kein LEAD() Wert gefunden wird.

Lösung:

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Erklärung der Lösung:

Wir werden den Tag, die Anzahl der Nutzer und die Anzahl der Nutzer 7 Tage später anzeigen. Die ersten beiden Werte stammen aus der Tabelle statistics Tabelle - das sind die Spalten Tag und Benutzer. Der letzte Wert muss mit der Funktion LEAD() berechnet werden.

Wir wollen den Wert der Spalte users nach sieben Tagen sehen; daher übergeben wir der Funktion LEAD() die Spalte users als erstes Argument und den Wert 7 als zweites Argument. Und um sicherzustellen, dass wir -1 anstelle von NULL anzeigen, wenn kein LEAD() Wert gefunden wird, übergeben wir das dritte Argument als -1.

Auf die Funktion LEAD() folgt die Klausel OVER(). Diese Klausel enthält die Bedingung, den Datensatz nach der Tagesspalte zu ordnen, da die Statistiken chronologisch geordnet sein sollen.

Um die Statistiken für die Website mit id = 2 zwischen dem 1. und 14. Mai 2016 anzuzeigen, müssen wir die entsprechenden Bedingungen in der Klausel WHERE definieren.

Übung 11: Rückblick mit der Funktion LAG()

Übung:

Zeigen Sie die Statistiken für die Website mit id = 3 an, die Tag, Umsatz und den Umsatz 3 Tage zuvor enthalten. Zeigen Sie -1.00 für Zeilen an, die 3 Tage zuvor keinen Umsatzwert hatten.

Lösung:

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Erklärung der Lösung:

Wir werden den Tag, den Umsatz und den Umsatz 3 Tage zuvor anzeigen. Die ersten beiden Werte stammen aus der Tabelle statistics - das sind die Spalten Tag und Umsatz. Der letzte Wert muss mit der Funktion LAG() berechnet werden.

Wir möchten den Wert der Umsatzspalte von drei Tagen vor der aktuellen Zeile sehen; daher übergeben wir die Umsatzspalte als erstes Argument und den Wert 3 als zweites Argument an die Funktion LAG(). Und um sicherzustellen, dass wir -1,00 für Zeilen anzeigen, in denen 3 Tage zuvor kein Umsatzwert vorhanden war, übergeben wir als drittes Argument den Wert -1,00.

Auf die Funktion LAG() folgt die Klausel OVER(). Sie enthält die Bedingung, den Datensatz nach der Spalte day zu ordnen, da die Statistiken chronologisch geordnet sein sollen.

Um die Statistiken für die Website mit id = 3 anzuzeigen, müssen wir eine Bedingung in der WHERE Klausel definieren.

Weitere SQL-Übungen Fensterfunktionen

Die in diesem Artikel vorgestellten Übungen zu den SQL-Fensterfunktionen bieten eine umfassende Plattform, um Ihre SQL- und Datenanalysefähigkeiten abfrageweise zu verbessern. Diese Übungen stammen aus unseren Kursen; weitere Übungsaufgaben finden Sie in den unten verlinkten Kursen.

1. Fensterfunktionen
2. Fensterfunktionen Übungssatz

Wenn Sie die Fensterfunktionen kennenlernen oder Ihre Kenntnisse auffrischen möchten, empfehlen wir Ihnen, mit dem Kurs Fensterfunktionen zu beginnen, der eine gründliche Auseinandersetzung mit diesem Thema bietet. Wenn Sie Ihre Kenntnisse über Fensterfunktionen vertiefen möchten, sollten Sie sich unser Fensterfunktionen Practice Set ansehen. Es enthält 100 Übungen, die in drei verschiedene Teile gegliedert sind, wobei jeder Teil einen anderen Datensatz verwendet.

Melden Sie sich jetzt an und fangen Sie kostenlos an. Viel Erfolg!