Wie man den nächsten Nicht-NULL-Wert in SQL findet

Sie müssen den nächsten Nicht-NULL-WertNULL Wert in einer Zeitreihe finden, aber Sie wissen nicht, wie. Kann man das überhaupt in SQL machen? Ja, das können Sie! Dieser Artikel zeigt Ihnen, was zu tun ist.

Wenn Sie mit SQL arbeiten, werden Sie früher oder später mit NULL Werten konfrontiert. NULLs in einer Datenbank zu haben, ist fast unvermeidlich. Manchmal möchten Sie sie jedoch in Ihren Berichten vermeiden. Dies ist häufig der Fall, wenn Sie Zeitreihendaten analysieren; NULL Werte bedeuten, dass keine Daten verfügbar sind. Keine Daten in der Datenreihe bedeuten in der Regel, dass ein Ereignis stattgefunden hat oder nicht stattgefunden hat.

Wie findet man also den nächsten Wert, der nichtNULL ist, in einer Zeitreihe? Bevor wir die Frage nach dem "Wie" beantworten, sollten wir uns auf das "Wann" konzentrieren, d. h. auf die Frage "Wann muss ich den nächsten Wert finden, der nicht vonNULL stammt?". Hier ist ein Szenario, das eine Antwort liefert.

Szenario

Angenommen, Sie analysieren Daten für eine Freiberuflerplattform namens NoBoss, die Unternehmen mit Freiberuflern zusammenbringt. Die Unternehmen suchen nach guten Freiberuflern, die Freiberufler suchen nach guten Jobs (oder Gigs, wie man in der Branche sagt). Sie arbeiten mit der log Tabelle, die die Daten der Freiberufler, ihre Anmeldedaten und einige Aktivitäten enthält. Hier sind die Spalten der Tabelle:

• id - Die ID des Protokolldatensatzes und der Primärschlüssel (PK) der Tabelle.
• user_id - Die ID des Benutzers.
• first_name - Der Vorname des Benutzers.
• last_name - Der Nachname des Benutzers.
• login_start - Die Startzeit der Anmeldung des Benutzers.
• login_end - Die Endzeit der Anmeldung des Benutzers.
• job_id - Die ID der Stelle, auf die sich der Benutzer beworben hat.
• job_name - Der Name der Stelle, auf die sich der Benutzer beworben hat.
• category_id - Die Kategorie-ID der Stelle.
• category_name - Der Name der Stellenkategorie.

Ihre Aufgabe ist es, die IDs und Namen der Benutzer zu finden. Außerdem müssen Sie die Start- und Endzeiten der Benutzeranmeldung sowie die Stelle, auf die sich der Benutzer beworben hat, ermitteln. Schließlich benötigen Sie auch eine neue Spalte namens profile_category. Die Profilkategorie eines Nutzers wird durch die Kategorie des ersten Jobs bestimmt, auf den sich der Nutzer bewirbt (d.h. wenn sich der Freiberufler auf einen "Virtual Assistant"-Job bewirbt, ist sein category_name Wert "Virtual Assistant"). Auf diese Weise kann die NoBoss-Plattform ihren Nutzern Berichte und Statistiken zur Verfügung stellen, die sie mit anderen Nutzern innerhalb der gleichen Kategorie vergleichen.

Hört sich das kompliziert an? Auf den ersten Blick vielleicht nicht. Aber werfen Sie einen Blick auf die Daten in der log Tabelle an, und Sie werden sehen, warum Ihre Aufgabe nicht ganz einfach ist. Achten Sie besonders auf die hervorgehobene Spalte.

Verstehen Sie, warum das so schwierig ist? Diese Tabelle zeichnet jede Anmeldung auf. Mit anderen Worten, diese Tabelle enthält eine Zeitreihe, was bedeutet, dass es Protokolleinträge geben kann, bei denen sich der Benutzer auf keine Stelle beworben hat. Sie müssen alle Datensätze mit NULL Werten in der Spalte job_id ausschließen. Dies ist der einfachere Teil.

Sie müssen auch nur die Kategorie der ersten Stelle anzeigen. Der Benutzer kann angemeldet sein und sich monatelang nicht auf seine erste Stelle bewerben. Das bedeutet, dass Sie beliebig viele Datensätze mit NULL -Werten überspringen müssen, bevor Sie den Datensatz ohneNULL erreichen, der der ersten Stelle entspricht.

Um diese Aufgabe zu lösen, müssen Sie alle Werte in der Spalte job_id finden, die nichtNULL sind, und nur den ersten Wert in derselben Spalte, der nichtNULL ist. Um dieses Problem zu lösen, benötigen Sie SQL-Fensterfunktionen; wenn Sie damit nicht vertraut sind, besuchen Sie unseren Kurs Fensterfunktionen .

Beispiellösung: SQL Fensterfunktionen

Die Verwendung von Fensterfunktionen ist nicht die einzige Möglichkeit, dieses Problem in SQL zu lösen. Deshalb ist der folgende Code auch nur ein Beispiel. Die Verwendung von Fensterfunktionen ist jedoch der eleganteste Weg, um Werte zu finden, die nichtNULL sind. Also, hier ist der Code:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		FIRST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id ASC) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Was macht dieser Code? Er wählt zunächst mehrere Spalten aus der Tabelle aus logalso nichts Ausgefallenes. Der spannende Teil entsteht durch die Verwendung der Funktion FIRST_VALUE() window. Diese Funktion gibt den ersten Wert in einer bestimmten Datenmenge zurück. In diesem Fall gibt sie den ersten Wert aus der Spalte category_name zurück.

Ich möchte jedoch nicht, dass die erste jemals gewählte Jobkategorie die Kategorie für jeden Benutzer ist; der erste Job des Benutzers wird nur für diesen bestimmten Benutzer verwendet. Um dies zu erreichen, habe ich PARTITION BY verwendet. Damit wird das Fenster definiert, in dem die Funktion FIRST_VALUE() arbeitet. Das bedeutet, dass sie den ersten Job des ersten Benutzers findet; dann geht sie zum nächsten Benutzer über und findet dessen ersten Job, und so weiter.

Beachten Sie, dass diese Operation über die Spalte job_id in aufsteigender Reihenfolge ausgeführt wird. Warum ist das so? Die Auftragskennungen werden den Aufträgen sequentiell zugewiesen. Wenn Sie die Auftragskennungen aufsteigend ordnen, bedeutet dies, dass die niedrigste Kennung ganz oben und die Werte von NULL ganz unten stehen. Auf diese Weise wird vermieden, dass NULL als erster Wert im Ergebnis erscheint.

Schließlich schließt die Klausel WHERE alle Anmeldungen aus, bei denen sich der Benutzer nicht auf eine Stelle beworben hat.

Überprüfen wir den Code, um zu sehen, ob er das gewünschte Ergebnis liefert:

Der Code scheint perfekt zu funktionieren! Dies ist nur ein Aspekt der Analyse von Zeitreihen in SQL. Sie können mehr erfahren, wenn Sie diesen Artikel über Zeitreihenanalyse lesen . Sie müssen nicht alle Fensterfunktionen und ihre Syntax auswendig kennen, vor allem nicht, wenn Sie sie nicht sehr oft verwenden. In solchen Situationen kann unser SQL Fensterfunktionen Cheat Sheet hilfreich sein. Dort finden Sie alle Fensterfunktionen, ihre Syntax und Beispiele, die zeigen, wie sie funktionieren.

Beachten Sie bei der Anpassung dieser Abfrage

Die obige Abfrage gibt Ihnen den allgemeinen Rahmen für die Lösung und findet erfolgreich den nächsten Wert, der nichtNULL ist. Sie können diesen Code leicht an die Zeitreihe anpassen, die Sie analysieren. Seien Sie jedoch vorsichtig und passen Sie auf, wenn Sie dies tun!

Das Wichtigste ist, dass Sie Ihre Daten kennen. Verstehen Sie, ob Ihr Datensatz die NULL Werte enthält und was sie bedeuten. Im obigen Beispiel war es notwendig zu wissen, dass der Benutzer angemeldet sein kann und sich nie auf eine Stelle bewirbt. Bei diesen Daten sind die Werte von NULL kein Fehler, sondern eine Information, die Sie zu bestimmten Schlussfolgerungen führt.

Es gibt ein weiteres Beispiel dafür, wie wichtig es ist, seine Daten zu kennen. Ich wusste, dass die job_id Werte nicht zufällig, sondern sequentiell vergeben werden. Ich habe dieses Wissen genutzt, um die Daten zu ordnen und so NULLs aus dem Ergebnis zu eliminieren. Es ist auch hilfreich zu wissen, wie die Werte von NULL bei der Anordnung der Daten behandelt werden. Je nach Reihenfolge erscheinen sie entweder als erste oder als letzte Werte in der Tabelle.

Es ist leicht vorstellbar, dass Sie die Funktion LAST_VALUE() anstelle von FIRST_VALUE() verwenden und die gleichen Ergebnisse erhalten können, indem Sie die Daten einfach anders anordnen. Schauen wir mal, ob es funktioniert!

Die Logik sagt, dass ich einfach unsere vorherige Abfrage nehmen und sie so schreiben muss:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Der einzige Unterschied liegt in dieser Zeile: LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category. Anstelle von FIRST_VALUE() habe ich die Funktion LAST_VALUE() verwendet. Der andere Unterschied ist, dass die Funktion über job_id in absteigender Reihenfolge ausgeführt wird.

Führen wir den Code aus und analysieren wir das Ergebnis für den Benutzer Suzy Cinque:

Auf der Seite profile_category sollte nur "Datenanalyse" stehen. Warum ist das so? Das liegt daran, dass Suzy Cinque sich zuerst auf die Stelle "Datenanalytikerin zur Datenanalyse" beworben hat. In dieser Spalte wird nun aber auch die Kategorie "Schreiben" angezeigt. Schauen Sie genauer hin. Die beiden anderen Stellen, auf die sich Suzy Cinque beworben hat, sind "Korrekturleser" und "Ghostwriter für Biografie". Sie gehören beide zur Jobkategorie "Schreiben".

Dieser Code gibt Ihnen nur die Kategorie der aktuellen Stelle an. Ein Korrekturleser ist ein schriftstellerischer Beruf. Ein Ghostwriter ist ebenfalls ein schreibender Beruf. Datenanalytiker ist, nun ja, ein Job in der Datenanalyse. Wie kam es dazu? Es liegt daran, dass der Standard-Fensterrahmen RANGE UNBOUNDED PRECEDING ist, wenn ORDER BY verwendet wird. Das bedeutet, dass LAST_VALUE() nur die Werte zwischen der ersten Zeile und der aktuellen Zeile berücksichtigt.

Verzweifeln Sie nicht - es gibt einen Weg, dies zu beheben! Der Trick besteht darin, den Fensterrahmen richtig zu definieren:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

In diesem geänderten Code werden nun alle Werte zwischen der ersten und der letzten Zeile berücksichtigt. Dies wird durch die folgende Klausel erreicht: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Sehen Sie sich das neue Ergebnis für Suzy Cinque an:

Jetzt ist das Ergebnis wieder korrekt. Die erste Stelle, auf die sich Suzy Cinque beworben hat, ist "Datenanalytikerin zur Datenbereinigung"; deshalb wird ihre Profilkategorie immer "Datenanalyse" sein.

Die Suche nach dem nächsten Wert, der nichtNULL ist, ist nur ein Aspekt der Analyse einer Zeitreihe. Um sich mit Zeitreihen und Fensterfunktionen besser vertraut zu machen, sollten Sie wie in diesem Artikel an tatsächlichen COVID-19-Daten üben.

Apropos Zeitdaten: Es kann sein, dass Sie die Länge einer Zeitreihe berechnen müssen. Keine Sorge, in diesem Artikel erfahren Sie, wie Sie mit Hilfe von Fensterfunktionen die Länge einer Reihe berechnen können.

Möchten Sie mehr Hilfe bei der Arbeit mit Nicht-NULL-Werten?

Dieses Szenario mit der NoBoss-Plattform ist nur ein Beispiel dafür, wie Sie die nächsten Nicht-NULL-Werte finden können:NULL. Verwenden Sie dieses Szenario und den von mir erläuterten Code als Grundlage für weiteres Lernen und Üben. Unser Kurs Fensterfunktionen gibt Ihnen mehr Struktur und hilft Ihnen, alle anderen Fensterfunktionen kennen zu lernen. Wenn Sie daran interessiert sind, finden Sie hier alle Informationen über den Kurs Fensterfunktionen , die Sie benötigen, zur Verfügung gestellt von unserem Chief Content Officer.

Üben Sie, was Sie hier gelernt haben, und viel Erfolg bei der Suche nach einem Weg durch die nichtNULL Werte in Zeitreihendaten!