mode Blog (Français)

les horodatages sont essentiels à l’analyse métier pour une raison très simple: ils vous indiquent quand les choses se passent. Imaginez essayer de détecter les tendances de vos données, comme le trafic web mensuel, les bénéfices trimestriels ou le volume de commandes quotidien sans savoir quand les événements se sont produits. Ce serait un cauchemar.

PostgreSQL offre une variété de fonctions de date pour manipuler les horodatages. Pour séparer l’utile de l’obscur, nous partageons des how-tos pour les fonctions de date Postgres les plus fréquemment utilisées et les scénarios commerciaux où ils sont utiles.,

Les fonctions de date Postgres et les scénarios commerciaux les plus fréquemment utilisés où ils sont utiles:

• arrondir les horodatages avec DATE_TRUNC
• trouver des événements par rapport à L’heure actuelle avec NOW() et CURRENT_DATE
• isoler l’heure du jour et le jour de la semaine avec EXTRACT
• calculer le temps exemple disponible dans le mode entrepôt public. Essayez chaque fonction de date en Mode que vous travaillez votre chemin à travers ces exemples. Créez un compte en mode Studio et ouvrez un nouveau rapport pour commencer.,

  arrondi des horodatages avec DATE_TRUNC

  la fonctionDATE_TRUNC arrondit une valeur d’horodatage à un intervalle spécifié, ce qui vous permet de compter les événements. Vous pouvez arrondir un horodatage à la suite des unités de temps:

  • une
  • milliseconde
  • deuxième
  • minutes
  • heure
  • jour
  • semaine
  • mois
  • trimestre
  • année
  • décennie
  • siècle
  • millenium

  Le DATE_TRUNC syntaxe ressemble à ceci: DATE_TRUNC('interval',timestamp).,

  Par exemple, SELECT DATE_TRUNC('day','2015-04-12 14:44:18') serait de retourner le résultat de 2015-04-12 00:00:00.

  pour une explication plus détaillée deDATE_TRUNC (et une référence imprimable que vous pouvez garder à votre bureau!), découvrez ce post.

  comment le trafic web a-t-il changé au fil du temps?

  essayezDATE_TRUNC par vous-même en interrogeant la table modeanalytics.web_events, qui contient des exemples d’enregistrements de visites de sites web, y compris une colonne occurred_at. Vous pouvez isoler le mois de la visite avec DATE_TRUNC.,

  Pour renvoyer un nombre de visites Web chaque mois par canal, ajoutez la colonne channel et une COUNT à l’instruction SELECT, puis groupez par month et channel. (Puisque month Et channel sont les deux premières valeurs de votre instruction SELECT, vous pouvez GROUP BY 1,2), comme ceci:

  enfin, utilisez ORDER BY 1,2 pour organiser vos résultats chronologiquement (par mois) et alphabétiquement (par canal).,

  en Mode, vous pouvez créer un graphique linéaire pour visualiser les résultats de la requête.

  analyse du Mode

  recherche d’événements relatifs à L’heure actuelle avec NOW() et CURRENT_DATE

  la fonctionNOW() DATE Renvoie l’horodatage actuel en UTC (si le fuseau horaire n’est pas spécifié). Vous pouvez soustraire des intervalles de NOW() pour extraire les événements qui se sont produits dans la dernière heure, le dernier jour, la dernière semaine, etc.

  L’exécution deSELECT NOW() à 9h00 UTC le 11 octobre 2016 entraînerait2016-10-11 09:00:00.,

  la fonction CURRENT_DATE renvoie uniquement la date actuelle, pas l’horodatage complet. Exécuter SELECT CURRENT_DATEà 9h00 UTC le 11 octobre 2016 renverrait 2016-10-11.

  quelles commandes ont été passées au cours des 12 dernières heures?

  la démo de la table.orders contient des exemples d’enregistrements de toutes les commandes, y compris une colonne d’horodatage occurred_at en UTC.,

  pour rechercher les commandes passées au cours des 12 dernières heures, utilisez une clauseWHERE pour renvoyer uniquement les commandes passées après ou exactement à (>=) l’horodatage actuel (NOW()) moins un intervalle de 12 heures.,

  SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'

  Mode Analytics

  En plus hour, vous pouvez utiliser l’un des intervalles suivants:

  • microsecondes
  • millisecondes
  • deuxième
  • minutes
  • heure
  • jour
  • semaine
  • mois
  • année
  • décennie
  • siècle
  • millénaire

  Vous pouvez également combiner différents intervalles dans la même expression comme ceci:
  interval '4 hours 3 minutes'

  Ce que les commandes ont été placées hier?,

  Vous pouvez utiliser la même table pour trouver hier commandes en combinant la balise DATE_TRUNC et CURRENT_DATE fonctions.

  commencez par utiliser une fonction DATE_TRUNC pour arrondir vos valeurs occurred_at par jour (puisque nous voulons savoir si quelque chose s’est passé hier). Ensuite, utilisez une clauseWHERE pour renvoyer uniquement les valeurs où leoccurred_at day est égal à la date actuelle (en utilisant la fonctionCURRENT_DATE) moins un intervalle d’un jour.,

  SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'

  analyse du Mode

  isoler l’heure du jour et le jour de la semaine avec EXTRACT

  la fonctionEXTRACT date vous permet d’isoler les sous-champs tels que l’année ou l’heure des horodatages.

  Voici la syntaxe: EXTRACT(subfield FROM timestamp). Exécuter EXTRACT(month FROM '2015-02-12') renverrait un résultat de 2.

  gardez à l’esprit que si l’exemple ci-dessous se concentre sur l’heure du sous-champ (Heure de la journée), vous avez beaucoup d’autres sous-champs à votre disposition allant demillennium àmicrosecond., Vous pouvez consulter la liste complète des sous-champs disponibles ici.

  Combien de commandes sont placées chaque heure de la journée?

  une entreprise qui gère un centre de traitement des commandes peut vouloir recruter plus d’employés lorsque la majeure partie des commandes arrive. Pour savoir quand les commandes sont passées tout au long de la journée, vous pouvez utiliser la fonction EXTRACT et le sous-champ hour pour isoler l’Heure de la journée (de 0 à 23) dans laquelle une commande s’est produite.,

  SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders

  Utilisation de la balise COUNT fonction de comptage des commandes, puis GROUP BY heure. (Puisque l’heure est à la première valeur de votre SELECT déclaration, vous pouvez GROUP BY 1).

  SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1

  Enfin, pour organiser vos résultats de manière séquentielle, utilisez ORDER BY 1.

  SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1

  Et voici les résultats! On dirait que cela pourrait aider à avoir des travailleurs supplémentaires sur l’horloge tôt le matin et autour de l’heure du déjeuner.,

  Mode Analytics

  Quel est le volume moyen des commandes en semaine?

  pour déterminer le volume moyen des commandes effectuées par jour de semaine, utilisez EXTRACTet le sous-champ dow pour isoler le jour de la semaine (de 0 à 6, où 0 est le dimanche) dans lequel une commande s’est produite.

  SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders

  Ensuite, tour de l’ordre des estampilles par jour avec des DATE_TRUNC., Prendre un COUNT des commandes groupées par dow et day retourne le nombre de commandes passées chaque jour avec le jour correspondant de la semaine.

  SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2

  pour trouver le volume moyen des commandes en semaine, utilisez la requête précédente comme sous-requête (alias a). Prenez la moyenne des commandes (en utilisant la fonctionAVG()), puis utilisez une clauseWHERE pour filtrer les samedis et dimanches.

  Les Grands graphiques numériques sont parfaits pour afficher des métriques agrégées., Pour garder un œil sur le volume des commandes, rassemblez des métriques comme celle-ci dans un seul tableau de bord.

  analyse du Mode

  calcul du temps écoulé avec L’âge

  la fonction AGE date calcule depuis combien de temps un événement s’est produit. La syntaxe est assez simple: appliquez AGE() à un seul horodatage, et votre requête retournera le temps écoulé depuis que cet événement a eu lieu. L’exécution de SELECT AGE( '2010-01-01' ) le 1er janvier 2011 renverrait un résultat de 1 years 0 months 0 days.,

  AGE() peut également déterminer le temps écoulé entre deux événements. Au lieu de mettre un seul horodatage entre parenthèses, insérez les deux horodatages (en commençant par l’horodatage le plus récent) et séparez-les par une virgule. Running SELECT AGE( '2012-12-01',‘2010-01-01’) serait de retour 2 years 11 months 0 days.

  notez que cette application de la fonctionAGE équivaut à soustraire les horodatages:SELECT '2012-12-01' - '2010-01-01'.

  quel est l’âge d’un compte client?,

  supposons que votre équipe commerciale souhaite personnaliser les salutations en fonction de la durée d’utilisation de votre produit par le client. Vous pouvez trouver le temps écoulé depuis la création du compte à l’aide de la fonction AGE.

  Le tableau modeanalytics.customer_accounts contient des enregistrements d’exemples de comptes clients. Sélectionnez la colonne des noms de compte (name) et d’appliquer le AGE() fonction de la colonne d’horodateurs indiquant le moment où chaque compte a été créé (created).,

  SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

  analyse du Mode

  combien de temps faut-il aux utilisateurs pour compléter leur profil chaque mois, en moyenne?

  la table modeanalytics.profilecreationevents contient des exemples de données des utilisateurs qui ont créé un profil, y compris les horodatages de début et de fin.

  pour trouver le temps moyen de remplir un profil chaque mois, commencez par trouver le temps qu’il a fallu à chaque utilisateur pour remplir un profil ainsi que le mois au cours duquel le processus de création de profil a été démarré. Tout d’abord, arrondissez l’horodatage started_at par mois, en utilisant la fonction DATE_TRUNC., Ensuite, recherchez le temps écoulé entre started_at Et ended_at pour chaque profil en utilisant la fonction AGE.

  trouvez la moyenne pour chaque mois en appliquant la fonctionAVG à la valeur du temps écoulé (votre instructionAGE) et en regroupant par mois.

  Pour renvoyer des valeurs dans une unité cohérente pour la cartographie, appliquez la fonction EXTRACT et le sous-champ epoch à vos valeurs pour renvoyer les résultats sous forme de nombre de secondes.

  Mode Analytics

  vous voulez plus de pratique?, Apprenez SQL et Python en utilisant des données réelles avec nos tutoriels gratuits.

  articles recommandés

  • comment maîtriser les anti jointures et les appliquer aux problèmes métier
  • penser en SQL vs penser en Python
  • DATE_TRUNC: une fonction D’horodatage SQL sur laquelle vous pouvez compter