Cours
Manipulation de données en SQL
4 h
335.2K
Parmi les fonctions les plus utiles dans les implémentations SQL de Postgres (comme Amazon Redshift) figurent DATE_DIFF et DATE_TRUNC :
DATE_DIFFrenvoie le temps écoulé entre deux dates. Par exemple, le code suivant donne le nombre de jours entredate1etdate2:
DATE_DIFF('day', date1, date2)
DATE_DIFF est idéal pour calculer le nombre de jours entre l’inscription et la résiliation, ou le nombre d’heures entre la connexion et la déconnexion.
DATE_TRUNCtronque une date au jour, à la semaine, au mois ou à l’année la plus proche. Par exemple, le code suivant renvoie le lundi de la semaine du timestampmy_timestamp:
DATE_TRUNC('week', my_timestamp)
DATE_TRUNC est parfait pour agréger les données. Par exemple, on peut l’utiliser pour calculer le nombre d’utilisateurs actifs mensuels (MAU) en tronquant à la date de début de mois.
Mais toutes les implémentations SQL ne proposent pas ces excellentes fonctions. Pour notre code Learn SQL from Scratch, nous utilisons SQLite, une implémentation légère de SQL qui tourne sur une seule instance Docker. SQLite est très bien pour les backends de sites web et les petits projets, mais il manque mes deux fonctions préférées. Heureusement, il existe des contournements.
Pour émuler DATE_DIFF, nous pouvons utiliser une fonction peu connue appelée juliandate. Selon Wikipédia, « le Julian Day Number (JDN) est l’entier attribué à un jour solaire complet dans le compte julien à partir de midi en temps universel, avec le numéro julien 0 attribué au jour commençant à midi le lundi 1er janvier 4713 av. J.-C. ». En convertissant une date en nombre à virgule flottante, on peut utiliser la soustraction pour obtenir la différence entre deux horodatages.
On peut même convertir le résultat en heures en multipliant par 24, ou en minutes en multipliant par 24 * 60.
Nous pouvons reproduire une partie du comportement de DATE_TRUNC à l’aide de strftime. Cette fonction convertit un timestamp en chaîne selon un format donné.
%djour du mois : 00 %fsecondes fractionnaires : SS.SSS %Hheure : 00-24 %jjour de l’année : 001-366 %JJour julien %mmois : 01-12 %Mminute : 00-59 %ssecondes depuis 1970-01-01 %Ssecondes : 00-59 %wjour de la semaine 0-6 avec dimanche==0 %Wsemaine de l’année : 00-53 %Yannée : 0000-9999
Habituellement, on l’utilise pour convertir entre différents formats de timestamp, par exemple de YYYY-MM-DD vers MM-DD-YYYY :
strftime('%M-%D-%Y', mydate)
Mais en choisissant astucieusement le format, on peut tronquer au bon niveau. Par exemple, pour tronquer au mois, on peut faire :
strftime('%M/%Y', mydate)
Ou tronquer à la semaine avec :
strftime('%Y-%w', mydate)
Avec ces deux astuces, vous pouvez utiliser SQLite pour mener des analyses comparables à celles d’Amazon Redshift !
Pour en savoir plus sur les bases de SQL, suivez le cours Intro to SQL for Data Science de DataCamp et consultez notre SQL Tutorial for Beginners.
Sujets