Le 14 septembre 2017, nous avons publié une version actualisée de notre Politique de confidentialité. En utilisant video2brain.com vous vous engagez à respecter ces documents mis à jour. Veuillez donc prendre quelques minutes pour les consulter.

Découvrir PostgreSQL

Utiliser generate_series avec des dates

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Partez à la découverte de la fonction generate_series. Elle permet de retourner une suite de lignes générées grâce à ses paramètres.
08:55

Transcription

Nous allons voir la puissance de PostgreSQL et de son langage SQL avec un exemple, toujours en manipulation de date. J'ai récupéré ici de mes sessions le numéro de session, l'ID de la session, sa " date de début ", et puis il y un " smallint ", qui indique la durée de la session. En fait j'ai pas la date de fin de cette session. J'ai juste stocké sa " date de début " sous forme de date et puis la durée totale, " 5 jours, 3 jours ". Bien entendu donc la durée est en nombre de jour. Ce que je voudrais obtenir maintenant c'est la date de fin. Ça c'est pas très compliqué à obtenir, quelque soit le moteur SQL. Ici comment est-ce qu'on va faire ? Assez simplement, la " datedebut + duree " parce que l'unité sur " l'addition " c'est simplement les jours de la date. Donc facile, ici j'ai ma " date de fin " sans problème. [clic] Qu'en est-il de la nécessité d'ajouter d'autres types d'intervalle ? Par exemple si la durée était en heure je ferai quelque chose comme ça " datedebut + duree * " donc par exemple j'ai 5 " * interval '1 hour' ". Et la précédence des opérateurs ce sera bonne pour nous, mais pour rendre les choses plus lisibles je vais mettre entre parenthèse pour bien indiquer que je fais d'abord cette opération qui va me donner ici " 5 heures " par exemple. Et ensuite je vais additionner cette date avec un intervalle. Et donc je me retrouve à " 27, 5 heures ". Il y a eu une conversion implicite de mon type date avec ici, ce type qui m'étais retourné, un " timestamp without time zone " et il y a eu une conversion implicite du résultat en le type de donnée le plus précis de l'opération. C'est pas mal non ? Donc il vous suffit de manipuler votre intervalle avec des nombres, ou des " + ". Vous pouvez même faire une division d'ailleurs pour diviser un intervalle en une fraction. Par exemple 50% d'une heure ça donne 30 minutes. Vous pouvez faire tout ce type de calcul avec l'intervalle, c'est très puissant. Et donc ceci peut aussi s'exprimer ainsi, mais la différence c'est que ici je garde le type de donnée " date " qui est le plus précis de cette opération. Alors qu'ici je prends le type de donnée " timestamp " qui est le plus précis dans cette opération. Ceci me retournant un " timestamp ". Mais je peux bien entendu prendre tout ceci entre parenthèse et faire un " cast ", de la façon la plus simple. Comme ceci en " date " et voilà le résultat. J'ai exactement la même chose. Ceci est un peu plus compliqué que cela mais ça donne la même chose. Alors je vais vous montrer aussi quelque chose, que je vais d'abord vous montrer dans une requête distincte. Je vais faire un " SELECT * FROM ". Et dans mon " FROM " je vais utiliser une fonction. Et en l'occurrence, il s'agit d'une fonction intégrée dans Postgres, mais qui est très pratique et qui s'appelle " generate_series ". Comme ceci. Je la mets dans le " FROM ". Alors vous vous en doutez un tout petit peu. Si je la mets dans le " FROM " c'est parce qu'elle va retourner en jeu de résultat. En l'occurrence, elle va retourner plusieurs lignes. C'est intéressant. Le " generate_series " comme son nom l'indique génère une série à partir de deux types de données. Soit des types de donnée entier, par exemple je vais dire de " 1 à 10 ". Soit et on va le voir ensuite, avec des dates ou des " timestamp ". Donc j'essaye ça déjà. Il faut que je sélectionne tout, voilà. Je vais faire " F5, 1, 2, 3, 4, 5 ", etc. Pas mal non ? Donc ce " generate_series " me retourne une liste simplement dans plusieurs lignes avec une colonne. Je peux même, et pour ça je vais aller jusqu'à " 20 ", dire quel est le " step ? " " 1, 3, 5, 7, 9, 11 ", parce que j'ai dit de " 1 à 20 " avec un step de " 2 ". Et bien, je peux bien faire la même chose avec des dates. C'est très pratique, ce " generate_series " pour faire deux choses, des listes de nombre et de date qu'on peut réutiliser dans nos requêtes et dans nos jointures pour utiliser ce qu'on appelle des " tally tables ". Je vous l'écris comme ceci. C'est à dire des tables de nombre ou des tables de date de façon à pouvoir résoudre des problématiques sous forme de jointure. Donc avec des dates ou des " timestamp ". Si je fais par exemple de " '2016-02-10' " que je vais convertir en " timestamp " [clic] vers '2016-02-11' en " timestamp ". Donc une journée et ici troisième paramètre un intervalle. Je vais dire tu me fais un intervalle d'une heure. Je convertis ça en " interval ", comme ceci ou en le mettant ici. Comme je peux mettre d'ailleurs le " timestamp " devant. Je peux l'exprimer comme ceci également, c'est une autre forme de conversion qui est supportée. Donc voyons ce que ça me donne. " 1h, 2h, 3h, 4h, 5h ". Je me suis généré une liste d'intervalle, une liste de position dans le temps, à partir de cet intervalle entre " ça et ça ". C'est pas mal ! Alors, vous vous demandez peut-être, ou pas d'ailleurs mais on va y répondre quand même. Est-ce que ce " generate_series " qui me retourne des lignes peut être utilisé ailleurs que dans le " FROM ? " Et alors là c'est un des petits miracles de PostgreSQL, parce que la réponse est oui. Je vous montre. Je reviens maintenant dans mon " SELECT ", et je vais utiliser mon " generate_series " ici. Comment est-ce que je vais faire ça? Qu'est-ce que ça va me retourner ? Et qu'est-ce je vais mettre bien entendu comme paramètre? Et bien je veux ma " datedebut ". Je n'ai pas besoin forcement de la convertir en " timestamp " ici parce que je vais profiter d'une conversion implicite. Ensuite je vais mettre " datedebut + duree ", deuxième paramètre de la date de début jusqu'à la date de fin. Et ensuite je vais exprimer un intervalle " '1 day' ". Bon, je peux transtyper ou non. C'est-à-dire le convertir ou non, il va y avoir aussi ici une conversion implicite. Et je vais dire " as jour " par exemple. Alors là ça devient très très bizarre mais on va voir que ça va marcher quand même. C'est pour ça que j'ai mis le " sessionid " ici. Parce que bon regardez. On le refait. Je vais commenter ceci et exécuter. Observez bien les " sessionid " " 40, 41, 42 ", parce que c'est l'ID de la session. J'en ai donc un par ligne, et ce sont des valeurs uniques. Par contre si j'ajoute mon " generate_series ", et bien il va y avoir une sorte de croisement, une sorte de pivot qui va créer autant de ligne qui correspond à ce " sessionid ", qu'il y a ici deux jours entre la date de début et la date de fin. Je vais faire encore une chose d'ailleurs. Je vais convertir ceci en date pour enlever la partie " timestamp " , voilà. Alors c'est très pratique parce que si je veux avoir une liste de tous les jours entre date de début et date de fin je peux directement la générer ici à l'intérieur du " SELECT " avec mon " generate_series ", et avoir l'information que sur cette session il y a eu un jour de session " le 27 ", un jour le " 28, le 29, le 30, le 01 et le 02 ". Extrêmement pratique pour résoudre des problématiques avec le " SELECT ".

Découvrir PostgreSQL

Comprenez le fonctionnement de PostgreSQL ainsi que son architecture. Effectuez les tâches courantes de sécurité, de création de bases de données et d'objets, etc.

2h46 (30 vidéos)
Aucun commentaire n´est disponible actuellement
 
Logiciel :
PostgreSQL PostgreSQL 9
Spécial abonnés
Date de parution :31 mars 2016

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !