Comment trouver la prochaine valeur non NULL en SQL

Vous devez trouver la prochaine valeur nonNULL dans une série chronologique, mais vous ne savez pas comment faire. Pouvez-vous le faire en SQL ? Oui, vous le pouvez ! Cet article vous montrera ce qu'il faut faire.

Si vous travaillez avec SQL, vous serez tôt ou tard confronté aux valeurs NULL. Avoir NULLs dans une base de données est presque inévitable. Cependant, vous souhaitez parfois les éviter dans vos rapports. C'est souvent le cas lorsque vous analysez des données de séries chronologiques ; les valeurs NULL signifient qu'il n'y a pas de données disponibles. L'absence de données disponibles dans la série de données signifie généralement qu'un événement s'est produit ou non.

Alors, comment trouver la prochaine valeur nonNULL dans une série chronologique ? Avant de répondre à la question "comment", concentrons-nous sur "quand", c'est-à-dire "quand aurais-je besoin de trouver la prochaine valeur autre queNULL ". Voici un scénario qui fournira une réponse.

Scénario

Supposons que vous analysiez des données pour une plateforme de freelance appelée NoBoss, qui met en relation des entreprises et des freelances. Les entreprises recherchent de bons freelances ; les freelances recherchent de bons emplois (ou gigs, comme on dit dans le secteur). Vous travaillez avec la table log qui contient les données des freelances, leurs données de connexion et certaines activités. Voici les colonnes de la table :

• id - L'ID de l'enregistrement du journal et la clé primaire (PK) de la table.
• user_id - L'ID de l'utilisateur.
• first_name - Le prénom de l'utilisateur.
• last_name - Le nom de famille de l'utilisateur.
• login_start - L'heure de début de connexion de l'utilisateur.
• login_end - L'heure de fin de connexion de l'utilisateur.
• job_id - L'ID de l'emploi auquel l'utilisateur a postulé.
• job_name - Le nom de la fonction à laquelle l'utilisateur a postulé.
• category_id - L'ID de la catégorie de la fonction.
• category_name - Le nom de la catégorie de l'offre.

Votre tâche ici est de trouver les ID et les noms des utilisateurs. Vous devez également obtenir les heures de début et de fin de connexion des utilisateurs, ainsi que la fonction à laquelle l'utilisateur a postulé. Enfin, vous avez également besoin d'une nouvelle colonne nommée profile_category. La catégorie de profil d'un utilisateur est déterminée par la catégorie de la première offre à laquelle il a postulé (par exemple, si le freelance postule à une offre d'"assistant virtuel", sa valeur category_name est "assistant virtuel"). De cette façon, la plateforme NoBoss peut fournir à ses utilisateurs des rapports et des statistiques qui les comparent aux autres utilisateurs de la même catégorie.

Cela vous semble-t-il compliqué ? Peut-être pas au début. Mais jetez un coup d'oeil aux données de la table log et vous verrez pourquoi votre tâche n'est pas simple. Portez une attention particulière à la colonne surlignée.

Vous voyez ce qui rend la tâche si difficile ? Ce tableau enregistre chaque connexion. En d'autres termes, cette table contient une série chronologique, ce qui signifie qu'il peut y avoir des enregistrements de journal où l'utilisateur n'a postulé à aucun emploi. Vous devez exclure tous les enregistrements avec les valeurs NULL dans la colonne job_id. C'est la partie la plus facile.

Vous devez également afficher uniquement la catégorie du premier emploi. L'utilisateur peut être connecté et ne pas postuler à son premier emploi pendant des mois. Cela signifie que vous devrez sauter qui sait combien d'enregistrements avec des valeurs NULL avant d'atteindre l'enregistrement nonNULL qui correspond au premier emploi.

N'oubliez pas que pour mener à bien cette tâche, vous devez trouver toutes les valeurs autres queNULL dans la colonne job_id et seulement la première valeur autre queNULL dans la même colonne. Pour résoudre ce problème, vous aurez besoin des fonctions de fenêtre SQL ; si vous ne les connaissez pas, consultez notre cours Fonctions de fenêtrage .

Exemple de solution : SQL Fonctions de fenêtrage

L'utilisation des fonctions de fenêtre n'est pas la seule façon de résoudre ce problème en SQL. C'est pourquoi le code suivant n'est qu'un exemple. Toutefois, l'utilisation des fonctions de fenêtre est la manière la plus élégante de trouver des valeurs autres queNULL. Voici donc le 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;

Que fait ce code ? Il sélectionne d'abord plusieurs colonnes dans le tableau logrien d'extraordinaire. La partie passionnante vient de l'utilisation de la fonction fenêtre FIRST_VALUE(). Cette fonction renvoie la première valeur d'un ensemble défini de données. Dans ce cas, elle renvoie la première valeur de la colonne category_name.

Cependant, je ne veux pas que la première catégorie d'emploi choisie soit la catégorie de chaque utilisateur ; la première catégorie d'emploi des utilisateurs n'est utilisée que pour cet utilisateur particulier. Pour y parvenir, j'ai utilisé la fonction PARTITION BY. Celle-ci définit la fenêtre sur laquelle la fonction FIRST_VALUE() va travailler. Cela signifie qu'elle trouvera le premier emploi du premier utilisateur ; puis elle passera à l'utilisateur suivant et trouvera son premier emploi, et ainsi de suite.

Remarquez que cette opération est effectuée sur la colonne job_id dans l'ordre croissant. Comment cela se fait-il ? Les ID des tâches sont attribués séquentiellement aux tâches. Si vous ordonnez les ID de travail par ordre croissant, cela signifie que l'ID le plus bas sera en haut et que les valeurs NULL seront en bas. De cette façon, vous évitez d'obtenir un NULL comme première valeur dans le résultat.

Enfin, la clause WHERE exclut toutes les connexions pour lesquelles l'utilisateur n'a postulé à aucun emploi.

Vérifions le code pour voir s'il renvoie le résultat souhaité :

Le code semble fonctionner parfaitement ! Ceci n'est qu'un aspect de l'analyse des séries chronologiques en SQL. Vous pouvez en apprendre davantage en lisant cet article sur l'analyse des séries temporelles. Il n'est pas nécessaire de connaître par cœur toutes les fonctions de fenêtre et leur syntaxe, surtout si vous ne les utilisez pas très souvent. Dans ce cas, notre aide-mémoire SQL Fonctions de fenêtrage peut vous être utile. Vous y trouverez toutes les fonctions de fenêtre, leur syntaxe et des exemples montrant comment elles fonctionnent.

Faites attention lorsque vous adaptez cette requête

La requête ci-dessus vous donne le cadre général de la solution et réussit à trouver la prochaine valeur nonNULL. Vous pouvez facilement adapter ce code à la série chronologique que vous analysez. Cependant, soyez prudent et faites attention lorsque vous le faites !

La chose la plus importante est de connaître vos données. Comprenez si votre ensemble de données contient les valeurs NULL et ce qu'elles signifient. Dans l'exemple ci-dessus, il était nécessaire de savoir que l'utilisateur peut être connecté et ne jamais postuler à aucun emploi. Pour ces données, avoir les valeurs NULL n'est pas une erreur ; c'est une information qui vous conduit à certaines conclusions.

Il existe un autre exemple de l'importance de connaître ses données. Je savais que les valeurs job_id sont attribuées de manière séquentielle et non aléatoire. J'ai utilisé cette connaissance pour ordonner les données et ainsi éliminer NULLs du résultat. Il est également utile de savoir comment les valeurs NULL sont traitées lorsqu'on ordonne les données. En fonction de l'ordre, elles apparaîtront soit comme les premières, soit comme les dernières valeurs du tableau.

Il est facile de penser que vous pouvez utiliser la fonction LAST_VALUE() au lieu de FIRST_VALUE() et obtenir les mêmes résultats en ordonnant simplement les données différemment. Voyons si cela fonctionne !

La logique veut que je prenne notre requête précédente et que je l'écrive de la façon suivante :

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;

La seule différence réside dans cette ligne : LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category. Au lieu de FIRST_VALUE(), j'ai utilisé la fonction LAST_VALUE(). L'autre différence est que la fonction sera exécutée sur job_id par ordre décroissant.

Exécutons le code et analysons le résultat pour l'utilisateur Suzy Cinque :

Il ne devrait y avoir que "Data Analysis" sur profile_category. Pourquoi ? C'est parce que Suzy Cinque a d'abord postulé au poste d'"Analyste de données pour scruter les données". Cependant, cette colonne montre maintenant aussi la catégorie "Rédaction". Regardez de plus près. Les deux autres emplois auxquels Suzy Cinque a postulé sont "Correcteur d'épreuves" et "Rédacteur fantôme de biographies". Elles appartiennent toutes deux à la catégorie "Écriture".

Ce que ce code vous donne, c'est uniquement la catégorie de l'emploi en question. Un correcteur d'épreuves est un travail d'écriture. Un rédacteur fantôme est également un travail d'écriture. Un analyste de données est, eh bien, un emploi d'analyse de données. Pourquoi cela s'est-il produit ? C'est parce que le cadre de fenêtre par défaut est RANGE UNBOUNDED PRECEDING lorsque ORDER BY est utilisé. Cela signifie que le site LAST_VALUE() ne prendra en compte que les valeurs comprises entre la première ligne et la ligne actuelle.

Ne désespérez pas - il y a un moyen de faire fonctionner cela ! L'astuce consiste à définir correctement le cadre de la fenêtre :

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;

Ce code modifié prendra désormais en compte toutes les valeurs comprises entre la première et la dernière ligne. Il y parvient grâce à la clause suivante : ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Regardez le nouveau résultat pour Suzy Cinque :

Le résultat est à nouveau correct. Le premier emploi auquel Suzy Cinque a postulé est celui d'"analyste de données" ; c'est pourquoi la catégorie de son profil sera toujours "Analyse de données".

Trouver la prochaine valeur nonNULL n'est qu'un aspect de l'analyse d'une série chronologique. Pour vous familiariser avec les séries temporelles et les fonctions de fenêtre, essayez de vous exercer sur des données COVID-19 réelles, comme dans cet article.

En parlant des données temporelles, vous serez peut-être amené à calculer la longueur d'une série temporelle. Ne vous inquiétez pas, voici un article qui vous apprend à utiliser les fonctions de fenêtre pour calculer la longueur d'une série.

Vous voulez plus d'aide pour travailler avec des valeurs non nulles ?

Ce scénario avec la plate-forme NoBoss n'est qu'un exemple de la façon de trouver les prochaines valeurs nonNULL. Utilisez ce scénario et le code que j'ai expliqué comme base pour poursuivre votre apprentissage et votre pratique. Notre cours Fonctions de fenêtrage vous donnera plus de structure et vous aidera à découvrir toutes les autres fonctions de la fenêtre. Si vous êtes intéressé, voici toutes les informations sur le cours Fonctions de fenêtrage dont vous aurez besoin, fournies par notre Chief Content Officer.

Mettez en pratique ce que vous avez appris ici, et bonne chance pour trouver votre chemin parmi les valeurs nonNULL dans les données de séries chronologiques !