Formules sur les sources de données

Les formules sont de nouvelles colonnes calculées à la demande. Il en existe 3 types :

  • formule simple
  • formule conditionnelle
  • formule valeur

Exemples

Supposons que vos données contiennent une colonne [Prix], une colonne [Coût] et une colonne [Date]. Voici quelques exemples de colonnes que vous pouvez créer.

Formules simples

  • Calcul simple
1
[Prix] * 10
  • Avec deux colonnes :
1
[Prix] / [Coût] * 100
  • Avec des parenthèses :
1
([Prix] - [Coût]) / [Prix] * 100
  • Opération sur une date :
1
extract_date_part([Date], "year")

Formules conditionnelles

  • Condition IF :
1
[Prix] == 100
  • Opérateur and:
1
[Prix] > 10 and [Pays] != "USA"
  • Opérateur or:
1
[Prix] > 10 or [Coût] < 20
  • Opérateur in :
1
[Pays] in ("USA", "Allemagne", "France")
  • Dates :
1
[Date] > "2010-10-24" and [Date] < "2010-10-30"
  • Parties de date :
1
extract_date_part([Date], "year") >= 2014
  • Comparaison de chaînes de caractères :
1
like([code_postal], "_____")

Formules valeur

  • Total simple:
1
sum([Prix])
  • Total conditionnel :
1
sum_if([Prix], [Coût] > 10)
  • Ratio :
1
sum_if([Prix], [Coût] > 10) / sum([Prix])

Formules de formules

Il est possible d'utiliser une formule dans une autre formule.

Par exemple, vous pouvez définir une formule de type Colonne simple, nommée "Produit GOLD" :

1
[Catégorie] in ["Bijouterie", "Montre", "Maroquinerie"]

et vous pouvez ensuite utiliser cette formule dans une seconde formule, par exemple pour calculer le revenu de ces "produits GOLD" dans le revenu total :

1
sum_if([Prix], [Produit GOLD]) / sum[Prix])

Date et heure

extract_date_part()

Extrait une partie de la date ou de l'heure.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • date_part: choix parmi "year", "month", "quarter", "week", "day", "weekday", "hour" ou "minute".
  • language[optional]: choix parmi "en", "fr", "num-fr", "num-en". Utiliser cette option pour récupérer une valeur littérale au lieu d'une valeur numérique. Valable que si date_part vaut "month". Ajouter le préfixe num permet de trier les données (par ex: 01-Janvier, 02-Février, ...).

Exemple :

1
extract_date_part([MaColonne], "week")

Disponibilité : tout type de source de données.


utc_to_timezone()

Convertit une colonne en UTC dans le fuseau horaire donné.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • timezone: un fuseau horaire valide (par ex. : "US/Eastern" ou "Europe/Paris"). Voir la liste complète.

Exemple :

1
utc_to_timezone([MaColonne], "Europe/Paris")

Disponibilité : tout type de source de données.


date_diff()

Calcule la différence entre 2 dates/heures dans l'unité de temps donnée. Le résultat est un entier.

Plus précisément, date_diff() détermine le nombre de frontières d'unités de temps qui sont traversées entre les deux dates/heures. Le résultat est un entier (par ex: 1 pour la différence en jours entre "2016-10-15 23:00:00" et "2016-10-16 03:00:00").

Paramètres :

  • first_date: colonne, autre formule ou résultat d'une fonction
  • second_date: colonne, autre formule ou résultat d'une fonction
  • time_unit[optional]: une unité de temps valide. Par défaut : "day". Choix parmi : "year", "month", "day", "hour", "minute", "second".

Exemple :

1
date_diff([MaColonneDate1], [MaColonneDate2], "hour")

Disponibilité : seulement MySQL, SQL Server, Redshift et le storage Serenytics. Sur MySQL, la seule unité de temps disponible est la valeur par défaut : "day".


time_delta()

Calcule la différence entre 2 dates/heures dans l'unité de temps donnée. Le résultat est un flottant (par ex: 1.083 pour la différence entre "2016-10-15 14:00:00" et "2016-10-16 16:00:00").

Paramètres :

  • first_date: colonne, autre formule ou résultat d'une fonction
  • second_date: colonne, autre formule ou résultat d'une fonction
  • time_unit[optional]: une unité de temps valide. Par défaut : "day". Choix parmi : "day", "hour", "minute", "second".

Exemple :

1
time_delta([MyDateColumn1], [MyDateColumn2], "hour")

Disponibilité : tout type de source de données.


now()

Renvoie la date et l'heure actuelles.

Disponibilité : tout type de source de données.


date()

Crée une date à partir de l'année, du mois et du jour.

Paramètres :

  • year: colonne, autre formule ou résultat d'une fonction.
  • month[optional]: colonne, autre formule ou résultat d'une fonction qui renvoie le mois de la date à créer. Par défaut vaut 1 (janvier).
  • day[optional]: colonne, autre formule ou résultat d'une fonction qui renvoie le jour de la date à créer. Par défaut vaut 1 (premier jour du mois).

Exemple :

1
date([ColonneAnnée], [ColonneMois])

Disponibilité : Tout type de source de données sauf Microsoft SQL Server.


date_add()

Ajoute un interval de temps à une date.

Paramètres :

  • myDate: colonne, autre formule ou résultat d'une fonction.
  • interval_type: Unité de l'interval: "day", "hour", "minute" ou "second".
  • nb_intervals: Nombre d'unités à ajouter à la date. Retranche les unités si négatif.

Exemple :

1
2
# to add 10 days to a given date
date_add([myDate], "day", 10)

Availability: Tout type de sources de données sauf fichiers et APIs.


Filtrage sur les dates/heures

Pour les mesures de type date/heure, vous pouvez utiliser des fonctions dédiées pour calculer les conditions.

Exemples

Si vos données contiennent une colonne [Timestamp], vous pouvez la filtrer sur :

  • aujourd'hui
1
[Timestamp] in day(0)
  • hier
1
[Timestamp] in day(-1)
  • les dernières 24 heures
1
[Timestamp] >= hour(-24)
  • le mois courant
1
[Timestamp] In month(0)
  • les 2 derniers mois en fenêtre glissante (= derniers 62 jours)
1
Timestamp] >= days(-62)
  • le mois courant jusqu'à hier mais sans inclure aujourd'hui
1
[Timestamp] >= month(0) and [Timestamp] < day(0)
  • l'année en cours jusqu'à hier inclus
1
[Timestamp] In year_to_date(0)
  • l'année précédente jusqu'au même jour qu'aujourd'hui du mois précédent
1
[Timestamp] In month_to_date(-1)

Note sur les limites de périodes

  • timestamp <= period(arg) signifie tous les instants jusqu'à la fin de la période incluse
  • timestamp < period(arg) signifie tous les instants jusqu'à avant le début de la période
  • timestamp >= period(arg) signifie tous les instants depuis le début de la période inclus
  • timestamp > period(arg) signifie tous les instants depuis après la fin de la période

  • period(0) signifie la période actuelle (ex. : aujourd'hui)

  • period(-1) signifie la période pérécédente (ex. : hier), et ainsi de suite

  • period_to_date(0) signifie la période actuelle jusqu'à hier inclus. Par exemple, pour year_to_date(0), si la date du jour est 2018-06-20, la période concernée démarre à 2018-01-01 00:00:00 et se termine à 2018-06-19 23:59:59.

  • period_to_date(-1) signifie la période précédente jusqu'au même jour qu'aujourd'hui l'année dernière. Par exemple, pour year_to_date(-1), si la date du jour est 2018-06-20, la période concernée démarre à 2017-01-01 00:00:00 et se termine à 2017-06-19 23:59:59.

Fonctions disponibles

  • minute(arg)
  • hour(arg)
  • day(arg)
  • week(arg)
  • week_to_date(arg)
  • month(arg)
  • month_to_date(arg)
  • quarter(arg)
  • quarter_to_date(arg)
  • year(arg)
  • year_to_date(arg)

Chaînes de caractères

like()

Compare les valeurs d'une colonne avec un pattern.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • pattern: Pattern à valider. Peut contenir des caractères génériques (% valide zéro ou plusieurs caractères, _ valide un seul caractère).

Exemple :

1
2
3
4
# retourne true si zip_code a une taille de 5 et commence par "75"
like([zip_code], "75___")
# retourne true si url contient la chaîne de caractères "/product/"
like([url], "%/product/%")

Disponibilité : tout type de source de données.


regexp_match()

Renvoie True si une chaîne de caractères match une expression régulière, False sinon.

Paramètres :

  • column : colonne, autre formule ou résultat d'une fonction
  • pattern : Expression regulière à matcher.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# returns true if zip_code length is five and first two digits are "75" and last 3 digits are numbers
regexp_match([zip_code], "^75[0-9]{3}$")
# details:
# ^ matches the begining of the string
# 75 matches the string "75"
# [0-9] matches a decimal between 0 and 9
# {3} indicates that [0-9] must be matched exactly three times
# $ matches the end of the string

# returns true if a string is a date with format YYYY-MM-DD
regexp_match([date_str], "^[0-9]{4}-[0-9]{2}-[0-9]{2}$")

Disponibilité : tout type de source de données, sauf MS SQLServer.


left()

Retourne les n premiers caractères d'une chaîne de caractères.

Paramètres:

  • column: colonne, autre formule ou résultat d'une fonction
  • n: nombre de caractères à extraire

Exemple :

1
left([MyColumn], 5)

Disponibilité: tout type de source de données.


Retourne les n derniers caractères d'une chaîne de caractères.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • n: nombre de caractères à extraire

Exemple :

1
right([MyColumn], 5)

Disponibilité : tout type de source de données.


str_len()

Retourne la taille d'une chaîne de caractères.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
str_len([MyColumn])

Disponibilité : tout type de source de données.


lower_case()

Renvoie column en minuscules.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
lower_case([MaColonne])

upper_case()

Renvoie column en majuscules.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
upper_case([MaColonne])

lpad()

Renvoie column en la complétant, du coté gauche, avec le caractère padding_char, pour atteindre la taille nb_chars.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • nb_chars: taille de la chaîne de caractères renvoyée
  • padding_char: caractère à utiliser pour compléter la chaîne

Exemple :

1
lpad([MaColonne], 4, "0")

rpad()

Renvoie column en la complétant, du coté droit, avec le caractère padding_char, pour atteindre la taille nb_chars.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • nb_chars: taille de la chaîne de caractères renvoyée
  • padding_char: caractère à utiliser pour compléter la chaîne

Exemple :

1
rpad([MaColonne], 4, " ")

split()

Découpe une chaîne sur le délimiteur donné et renvoie la partie à la position donnée.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • delimiter: délimiteur
  • part: position de la partie à renvoyer (en comptant à partir de 1).

Exemple :

1
split([MaColonne], "/", 2)

Disponibilité : tout type de source de données sauf Microsoft SQL Server.


substring()

Extrait une sous-chaîne de caractères.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • first_char: Index du premier caractère extrait. Commence à 1.
  • nb_chars: Longueur de la sous-chaîne extraite

Exemple :

1
substring([MaColonneChaïneDeCaractères], 1, 5)

Disponibilité : tout type de source de données.


Conversions

numeric()

Convertit une colonne de chaînes de caractères en valeurs numériques. Si la chaîne de caractères ne peut pas être convertie en valeur numérique, le résultat est 0.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
numeric([MaColonneChaïneDeCaractères])

Disponibilité : seulement les fichiers.


str()

Convertit les valeurs d'une colonne en chaînes de caractères.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
str([MaColonne])

Disponibilité : tout type de source de données.


round()

Retourne l'entier le plus proche. Par exemple round(3.1) est 3 et round(3.9) est 4.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
round([MaColonne])

Disponibilité: tout type de source de données.


to_int()

Convertit les valeurs d'une colonne en entier. Les nombres flottants sont convertis en entier. Les valeurs non définies sont converties en zéro.

to_int retourne le plus grand entier inférieur à la valeur en entrée. Utilisez la fonction round pour arrondir à l'entier le plus proche.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
to_int([MaColonne])

Disponibilité: tout type de source de données.


datetime_from_string()

Convertit une chaîne de caractères en date.

Cette fonction va retourner une erreur si une seule des lignes n'a pas le format passé en paramètre. Si vous n'êtes pas sûr de la qualité de vos données, utilisez la fonction regexp_match pour vérifier le format de chaque ligne. Si une ligne n'est pas au bon format, retournez null() au lieu de datetime_from_string.

Paramètres:

  • string : colonne, autre formule ou résultat d'une fonction.
  • format: format de la chaîne de caractères à convertir. Si votre datasource est un fichier, utilisez le format de date Python (voir la documentation strftime.org). Si votre datasource est une table PostGreSQL ou un storage Serenytics, utilisez le format PostGreSQL (voir la documentation postgresql.org. Si votre datasource est une table MySQL, utilisez la syntax MYSQL (voir la documentation dev.mysql.com).

Exemple:

Dans cet exemple, la colonne à convertir contient des chaînes de caractères du type: 2013--01--23 21:17

1
2
3
4
5
6
7
8
# pour un fichier
datetime_from_string([date_str], "%Y--%m--%d %H:%M")

# pour une table PostGreSQL ou un storage Serenytics
datetime_from_string([date_str], "YYYY--MM--DD HH24:MI")

# pour une table MySQL
datetime_from_string([date_str], "%Y--%m--%d %H:%i")

Availability: Tout type de source de données sauf Microsoft SQL Server.


json_extract_path_text()

Extrait une valeur dans une chaîne de caractères JSON en spécifiant son chemin dans le JSON. Renvoie la chaîne de texte vide si le chemin n'existe pas dans le JSON.

Paramètres :

  • string : colonne, autre formule ou résultat d'une fonction.
  • path1: première clé du chemin pour accéder à la valeur cherchée dans le JSON
  • [path2]: seconde clé du chemin pour accéder à la valeur cherchée dans le JSON
  • jusqu'à 5 clés

Availability : Uniquement les sources de type Storage, Redshift et PostGreSQL.

Exemple :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# si la valeur dans la colonne est '{"first_name":"john", "last_name": "doe"}', renvoie 'john'.
json_extract_path_text([json_str], "fist_name")

# si la valeur dans la colonne est '{"first_name":"john", "last_name": "doe", "address": {"city" : "paris"}}'
# renvoie 'paris'.
json_extract_path_text([date_str], "address", "city")

# si la valeur dans la colonne est '{"first_name":"john", "last_name": "doe", "address": {"city" : "paris"}}'
# renvoie la chaine de texte vide.
json_extract_path_text([date_str], "address", "zipcode")

Rang

is_first()

Renvoie 1 si la valeur courante apparaît pour la première fois dans la colonne, 0 sinon. Peut être utilisé pour travailler avec des valeurs uniques ou supprimer des doublons.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
is_first([MaColonne])

Disponibilité : seulement les fichiers.


ntile()

Trie les lignes en fonction de la colonne passée en argument puis les range dans nb_buckets groupes qui ont tous le même nombre de lignes. Renvoie l'index du groupe dans lequel est rangée la ligne. Le premier groupe qui contient les plus petites valeurs a l'index 1.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • nb_buckets: nombre de groupes à utiliser

Exemple :

1
ntile([MaColonne], 100)

Disponibilité : tout type de source de données.


rank()

Trie les lignes en fonction de la colonne passée en argument puis renvoie le rang de chaque ligne. Si plusieurs lignes ont la même valeur, elles ont le même rang et il y aura une discontinuité dans les résultats. Si partition_by est défini, le rang est indépendant pour chaque valeur de cette colonne.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • order: soit "asc" (croissant) soit "desc" (décroissant)
  • partition_by[optional]: colonne, autre formule ou résultat d'une fonction

Exemple :

1
rank([MaColonne], "asc")
1
2
# calcule le rang des commandes pour chaque client
rank([commande.date], "asc", [commande.id_client])

Disponibilité : tout type de source de données.


dense_rank()

Trie les lignes en fonction de la colonne passée en argument dans l'ordre croissant ou décroissant, et renvoie le rang de chaque ligne. Si plusieurs lignes ont la même valeur, elles ont le même rang mais contrairement à la fonction rank, il n'y aura pas de discontinuité dans les résultats. Si partition_by est défini, le rang est indépendant pour chaque valeur de cette colonne.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • order: soit "asc" (croissant) soit "desc" (décroissant)
  • partition_by[optional]: colonne, autre formule ou résultat d'une fonction

Exemple :

1
dense_rank([MaColonne], "desc")
1
2
# calcule le rang des commandes pour chaque client
rank([commande.date], "asc", [commande.id_client])

Disponibilité : tout type de source de données.


quantile()

Renvoie la valeur de la colonne à la valeur du quantile fournie en argument (fraction).

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • fraction: valeur du quantile désirée (par ex. 0.5 pour la médiane)
  • interpolation: méthode d'interpolation à utiliser quand le quantile désiré se situe entre 2 points. Choix parmi : "linear" (linéaire) ou "nearest" (plus proche valeur dans les données).

Exemple :

1
2
# calcule la médiane :
quantile([MaColonne], 0.5, "linear")

Disponibilité : tout type de source de données sauf MySQL.


Valeurs

sum()

Calcule la somme.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
sum([MaColonne])

sum_if()

Calcule la somme pour les lignes qui satisfont une condition.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction
  • condition: expression booléenne

Exemple :

1
sum_if([MaColonne], [MonAutreColonne] == 3)

avg(), avg_if()

Renvoie la moyenne. Voir sum et sum_if pour plus de détails.

min(), min_if()

Renvoie le minimum. Voir sum et sum_if pour plus de détails.

max(), max_if()

Renvoie le maximum. Voir sum et sum_if pour plus de détails.

count(), count_if()

Renvoie le nombre de lignes des données. Voir sum et sum_if pour plus de détails.

dcount(), dcount_if()

Renvoie le nombre de lignes distinctes. Voir sum et sum_if pour plus de détails.


last_value()

Renvoie la dernière valeur de la colonne value_column en triant de manière croissante par la colonne order_column.

Paramètres :

  • value_column: colonne, autre formule ou résultat d'une fonction
  • order_column: colonne, autre formule ou résultat d'une fonction utilisée pour trier les valeurs de value_column

Exemple :

1
2
# obtiens la dernière valeur dans une série temporelle
last_value([valeur], [date])

last_value_if()

Renvoie la dernière valeur de la colonne value_column pour les lignes qui satisfont la condition, en triant de manière croissante par la colonne order_column.

Paramètres :

  • value_column: colonne, autre formule ou résultat d'une fonction
  • order_column: colonne, autre formule ou résultat d'une fonction utilisée pour trier les valeurs de value_column
  • condition: expression booléenne

Exemple :

1
2
# obtiens la valeur à la fin du mois précédent
last_value([valeur], [date], [date] in month(-1))

Fonctions de qualité de données

is_defined()

Renvoie False si la valeur de la colonne est NULL ou une chaîne de caractères vide, sinon renvoie True. Utilisez not is_defined([myCol]) pour tester si une colonne est non NULL.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
is_defined([MaColonne])

is_empty()

Renvoie True si la colonne est une chaîne de caractères vide, sinon renvoie False. La colonne doit être de type String. Utilisez not is_empty([myCol]) pour tester si une colonne est non vide.

Paramètres :

  • column: colonne, autre formule ou résultat d'une fonction

Exemple :

1
is_empty([MaColonne])

Autres formules

null()

Renvoie NULL.

Cette fonction peut être utilisée quand vous souhaitez compter les valeurs distinctes (dcount) d'une colonne en excluant certaines lignes. Pour cela, vous pouvez créer une formule qui renvoie null() pour les lignes à exclure. Cela fonctionne car les lignes à NULL ne sont pas comptées par un dcount.

Pour tester si une colonne est NULL ou est une chaine de texte vide, utilisez les fonctions is_defined ou is_empty.

Exemple :

A partir d'une table contenant une liste d'achats de clients, vous pouvez compter le nombre de clients qui ont fait des achats avec un prix supérieur à 100 (en utilisant la formule ci-dessous). Cela fonctionnera aussi si vous groupez selon une ou plusieurs dimensions (par exemple pour créer une table avec le nombre de clients qui ont fait des achats, ceux qui ont fait des achats de plus de 100€, groupés par pays et par catégorie de produits).

1
2
IF [price]>100 THEN [client_id]
ELSE null()