Loading
À propos de Salesforce Data 360
Fonctions SQL pour Insights

Fonctions SQL pour Insights

Utilisez ces fonctions agrégées, dateheure, analytiques, de mise en forme, mathématiques et statistiques, et booléennes lorsque vous créez des connaissances calculées dans Data 360. La colonne Agrégeable indique si la sortie d'une fonction peut être cumulée dans moins de dimensions lorsqu'elle est interrogée dans des segments ou des activations.

Éditions requises

Remarque
Remarque Les connaissances en continu et en temps réel prennent en charge uniquement les fonctions d'agrégation SUM et COUNT.
Disponible avec : Toutes les éditions prises en charge par Data 360. Consultez Disponibilité de Data 360 Edition.

Métriques agrégeables et non agrégeables

Certaines fonctions produisent des métriques qui peuvent être cumulées ou réduites entre les dimensions. On les appelle métriques agrégeables. Des fonctions telles que SUM, COUNT et AVG sont des exemples de métriques agrégeables. Vous pouvez interroger le résultat pour n'importe quel sous-ensemble des dimensions que vous avez définies en créant la connaissance.

D'autres fonctions produisent des métriques non agrégeables. Des fonctions telles que RANK, NTILE et CASE sont des exemples de métriques non agrégeables. Le résultat dépend de toutes les dimensions à la fois. Par conséquent, vous ne pouvez pas réduire ou réduire la métrique à un plus petit ensemble de dimensions. Lorsque vous interrogez une métrique non agrégeable, vous devez fournir des valeurs pour toutes les dimensions définies lors de la création de la connaissance.

Par exemple, si vous créez une métrique intitulée « Classer les clients par dépenses totales par catégorie de produits et emplacement » en utilisant la fonction CLASSER, vous ne pouvez plus interroger ce classement uniquement pour la catégorie de produits. Lorsque vous interrogez la métrique, vous devez toujours spécifier « Catégorie de produit » et « Emplacement ».

SELECT
  ssot__SalesOrder__dlm.ssot__SalesChannelId__c AS channel__c,
  RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ASC) AS spend_rank__c,
  UnifiedIndividual__dlm.ssot__Id__c AS unified_individual__c
FROM
  ssot__SalesOrder__dlm
  JOIN IndividualIdentityLink__dlm
    ON ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c
  JOIN UnifiedIndividual__dlm
    ON IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c
GROUP BY
  unified_individual__c,
  channel__c

Vous pouvez déterminer si une fonction produit une métrique non agrégeable dans l'écran d'accueil de l'objet Connaissances calculées.

Capture d'écran des fonctions agrégées dans les Connaissances calculées

Fonctions d'agrégation

Important
Important Pour comparer les résultats des fonctions d'agrégation, utilisez une instruction CASE WHEN. La comparaison booléenne ne prend pas en charge les fonctions d'agrégation.
Fonction Agrégeable Description Exemple
SUM Y Calcule le total en ajoutant toutes les valeurs du champ sélectionné. Prend en charge les valeurs numériques.
SELECT ProductID, SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID
COUNT Y Compte toutes les valeurs du champ sélectionné. Prend en charge les mesures de date, de texte et booléennes. COUNT(*) n'est pas pris en charge.
SELECT
COUNT(EmailEngagement__dlm.Id__c) as email_engagement_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM EmailEngagement__dlm
AVG Y Calcule la moyenne de toutes les valeurs du champ sélectionné. Prend en charge les valeurs numériques.
SELECT avg(SalesOrder__dlm.TotalAmount__c) as avg__c,
SubQuery1.Id as customer_id__c
FROM SalesOrder__dlm
MIN Y Calcule la valeur minimale du champ sélectionné. Prend en charge les valeurs numériques.
SELECT MIN(Price) FROM SalesOrder__dlm;
MAX Y Calcule la valeur maximale du champ sélectionné. Prend en charge les valeurs numériques.
SELECT MAX(Price) FROM SalesOrder__dlm;
MEAN Y Calcule la moyenne du jeu de données donné en divisant la somme totale par le nombre de valeurs du jeu de données.
SELECT AVG(sale_amount) FROM sales;
FIRST Y Renvoie la première valeur de l'expression d'un groupe de lignes.
SELECT first(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 10
LAST Y Renvoie la dernière valeur de l'expression d'un groupe de lignes.
SELECT last(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 20
APPROX_COUNT_DISTINCT N Renvoie la cardinalité estimée en utilisant l'algorithme HyperLogLog++, similaire à COUNT DISTINCT. Utilisez cette fonction pour compter les occurrences uniques de dimensions dans une connaissance calculée.
SELECT approx_count_distinct(col1)
FROM VALUES (1), (1), (2), (2), (3) tab(col1); -- 3
PERCENTILE N Renvoie le percentile exact de la colonne numérique au pourcentage donné. Le pourcentage doit être de 0,0 à 1.0.
SELECT percentile(col, array(0.25, 0.75))
FROM VALUES (0), (10) AS tab(col); -- [2.5, 7.5]
STDDEV N Renvoie l'écart type statistique de toutes les valeurs de l'expression spécifiée.
SELECT stddev(col)

Fonctions de dateheure

Fonction Agrégeable Description Exemple
HOUR(TIMESTAMP) N Renvoie le composant heure de la chaîne ou l'horodatage. Type de renvoi : entier 0 à 23.
SELECT hour('2009-07-30 12:58:59'); -- 12
DAY(DATE) N Renvoie le jour du mois de la date ou de l'horodatage. Type de renvoi : entier 1 à 31.
SELECT day('2009-07-30'); -- 30
MONTH(DATE) N Renvoie le composant mois de la date ou de l'horodatage. Type de renvoi : entier 1 à 12.
SELECT month('2016-07-30'); -- 7
QUARTER(DATE) N Renvoie le trimestre de l'année de la date. Type de renvoi : entier 1 à 4.
SELECT quarter('2016-08-31'); -- 3
YEAR(DATE) N Renvoie le composant année de la date ou de l'horodatage. Type de renvoi : entier.
SELECT year('2016-07-30'); -- 2016
CDPHOUR(TIMESTAMP) N Renvoie un horodatage représentant l'heure de l'horodatage. Type de renvoi : timestamp.
SELECT CdpHour('2009-07-30 01:10:05');
-- '2009-07-30 01:00:00'
CDPDAY(DATE) N Renvoie un horodatage représentant l'heure 0 du jour. Type de renvoi : timestamp.
SELECT CdpDay('2009-07-30 01:10:05');
-- '2009-07-30 00:00:00'
CDPMONTH(DATE) N Renvoie un horodatage représentant le mois. Type de renvoi : timestamp.
SELECT CdpMonth('2009-07-30 01:10:05');
-- '2009-07-01 00:00:00'
CDPQUARTER(DATE) N Renvoie un horodatage représentant le premier jour du trimestre. Type de renvoi : timestamp.
SELECT CdpQuarter('2009-07-30 01:10:05');
-- '2009-07-01 00:00:00'
CDPYEAR(DATE) N Renvoie un horodatage représentant le premier jour de l'année. Type de renvoi : timestamp.
SELECT CdpYear('2009-07-30 01:10:05');
-- '2009-01-01 00:00:00'
CURRENT_DATE N Renvoie la date actuelle au début de l'évaluation de la requête. Tous les appels CURRENT_DATE dans la même requête renvoient la même valeur.
SELECT current_date(); -- 2020-04-25
DATE_SUB N Soustrait un nombre de jours de la valeur de date et renvoie la date antérieure.
SELECT date_sub('2016-07-30', 1); -- 2016-07-29
DATE_ADD N Ajoute un nombre de jours à la valeur de date et renvoie la date ultérieure.
SELECT date_add('2016-07-30', 1); -- 2016-07-31
DATEDIFF N Renvoie le nombre de jours entre la date de début et la date de fin.
SELECT datediff('2009-07-31', '2009-07-30'); -- 1
MONTHS_BETWEEN N Renvoie le nombre de mois entre deux dates.
SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
-- 3.94959677
DAYOFWEEK N Renvoie le jour de la semaine d'une date ou d'un horodatage (1 = dimanche, 2 = lundi... 7 = samedi).
SELECT dayofweek('2009-07-30'); -- 5
DAYOFMONTH N Renvoie le jour du mois de la date ou de l'horodatage.
SELECT dayofmonth('2009-07-30'); -- 30
DAYOFYEAR N Renvoie le jour de l'année de la date ou de l'horodatage.
SELECT dayofyear('2009-07-30'); -- 211
TO_DATE N Analyse la chaîne de date avec l'expression de format en date. Renvoie null si l'entrée n'est pas valide.
SELECT to_date('2016-12-31', 'yyyy-MM-dd'); -- 2016-12-31
TO_TIMESTAMP N Analyse la chaîne d'horodatage avec l'expression de format en horodatage. Renvoie null si l'entrée n'est pas valide.
SELECT to_timestamp('2016-12-31 00:12:00');
-- 2016-12-31 00:12:00
DATE_TRUNC N Renvoie l'horodatage tronqué à l'unité spécifiée par le modèle de format.
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
-- 2015-01-01 00:00:00
HOUR_ADD N Ajoute un nombre d'heures à la valeur dateheure et renvoie la dateheure ultérieure.
SELECT COUNT(SalesOrder__dlm.orderid__c) as orderid__c,
HOUR_ADD(SalesOrder__dlm.checkout_date__c, 2) as newDateHour__c
FROM SalesOrder__dlm GROUP BY newDateHour__c
HOUR_SUB N Soustrait un nombre d'heures de la valeur dateheure et renvoie la dateheure antérieure.
SELECT COUNT(SalesOrder__dlm.orderid__c) as orderid__c,
HOUR_SUB(SalesOrder__dlm.checkout_date__c, 2) as newDateHour__c
FROM SalesOrder__dlm GROUP BY newDateHour__c
NOW N Renvoie la date et l'heure actuelles alignées sur le traitement des connaissances. Tous les appels NOW dans la même requête renvoient la même valeur. Le temps est calculé avec une précision en millisecondes UTC pour la région de votre instance.
SELECT NOW(); -- 2020-04-25T13:00:00.000Z
SECOND_ADD N Ajoute un nombre de secondes à une valeur dateheure et renvoie la dateheure ultérieure.
SELECT SECOND_ADD('2020-04-25T13:00:00.000Z', 305);
-- 2020-04-25T13:05:05.000Z
SECOND_SUB N Soustrait un nombre de secondes d'une valeur de dateheure et renvoie la dateheure antérieure. Utilisez par exemple cette fonction pour inclure les enregistrements des 5 dernières minutes au moment du traitement par lot.
SELECT SECOND_SUB('2020-04-25T13:00:00.000Z', 305);
-- 2020-04-25T12:54:55.000Z

Fonctions analytiques

Fonction Agrégeable Description Exemple
LAG N Renvoie la valeur d'entrée de la ligne décalée avant la ligne actuelle dans la fenêtre. Le décalage par défaut est 1. Renvoie null si la valeur d'entrée à la ligne de décalage est null.
SELECT a, b, lag(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 NULL  A1 1 1  A1 2 1  A2 3 NULL
LEAD N Renvoie la valeur de la ligne décalée après la ligne actuelle dans la fenêtre. Le décalage par défaut est 1. Renvoie null si aucune ligne de décalage n'existe.
SELECT a, b, lead(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 2  A1 2 NULL  A2 3 NULL
NTILE N Divise les lignes de chaque partition de fenêtre en n compartiments allant de 1 à n maximum.
SELECT a, b, ntile(2)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 2  A2 3 1
RANK N Calcule le classement d'une valeur dans un groupe de valeurs. Le résultat est 1 plus le nombre de lignes précédant la ligne actuelle dans l'ordre de la partition. Produit des écarts dans la séquence.
SELECT a, b, rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 3  A2 3 1
PERCENT_RANK N Calcul le classement en pourcentage d'une valeur dans un groupe de valeurs.
SELECT a, b, percent_rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 0.0  A1 1 0.0  A1 2 1.0  A2 3 0.0
DENSE_RANK N Calcule le classement d'une valeur dans un groupe de valeurs. Contrairement à RANK, ne produit pas d'écart dans la séquence de classement.
SELECT a, b, dense_rank(b)
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 1  A1 2 2  A2 3 1
ROW_NUMBER N Attribue un numéro séquentiel unique à chaque ligne commençant par 1, en fonction de l'ordre des lignes dans la partition de la fenêtre.
SELECT a, b, row_number()
OVER (PARTITION BY a ORDER BY b)
FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 1 1  A1 1 2  A1 2 3  A2 3 1
FIRST_VALUE N Renvoie la première valeur de l'expression pour chaque ligne de la fenêtre.
SELECT first_value(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 10
LAST_VALUE N Renvoie la dernière valeur de l'expression pour chaque ligne de la fenêtre.
SELECT last_value(col)
FROM VALUES (10), (5), (20) AS tab(col); -- 20
REGEXP N Recherche des modèles dans le texte dans les chaînes.
SELECT * FROM Customers
WHERE name REGEXP '^[A-Z][a-z]+'
REGEXP_EXTRACT N Crée des valeurs de dimension en les extrayant d'une dimension source à l'aide d'expressions régulières Google RE2.
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1); -- 100
REGEXP_REPLACE N Étend REPLACE en vous permettant de rechercher un modèle d'expression régulière dans une chaîne.
SELECT regexp_replace('100-200', '(\\d+)', 'num'); -- num-num
ISNULL N Renvoie true si l'expression est nulle, sinon renvoie false.
SELECT isnull(1); -- false
ISNOTNULL N Renvoie true si l'expression n'est pas nulle, sinon renvoie false.
SELECT isnotnull(1); -- true
IFNULL N Renvoie le deuxième argument si le premier est nul, sinon renvoie le premier argument.
SELECT ifnull(null, 'default'); -- default
NULLIF N Renvoie null si les deux expressions sont égales, sinon renvoie la première expression.
SELECT nullif(2, 2); -- NULL
CONTAINS N Recherche un mot ou une phrase dans une ou plusieurs colonnes de texte en utilisant une correspondance précise ou partielle.
SELECT * FROM COMPANY
WHERE CONTAINS(products, "product1")
LIKE N Recherche un modèle spécifique dans une valeur de chaîne.
SELECT * FROM Customers
WHERE last_name LIKE 'R%';
RLIKE N Recherche un modèle d'expression régulière dans une chaîne.
SELECT id, text_field, text_field RLIKE '..number'
FROM SalesOrder__dlm ORDER BY id;
NOTLIKE N Ignore les valeurs qui correspondent au modèle spécifié dans la colonne.
SELECT * FROM Customers
WHERE last_name NOTLIKE 'R%'
NOTRLIKE N Ignore les valeurs qui correspondent à un modèle d'expression régulière dans la colonne.
SELECT * FROM SalesOrder__dlm
WHERE description__c NOTRLIKE '[0-9]+'
CASE N Évalue les conditions et renvoie une valeur lorsque la première condition est remplie. Si aucune condition n'est vraie, la valeur ELSE est renvoyée (ou null en l'absence de clause ELSE).
SELECT
  CASE
    WHEN SUM(ssot__Order__dlm.ssot__TotalAmount__c) > 500
    THEN 'high spender'
    ELSE 'low spender'
  END AS spend_label__c,
  Individual__dlm.ssot__Id__c AS customer_id__c
FROM ssot__Order__dlm
  JOIN IdLink__dlm ON ssot__Order__dlm.ssot__SoldId__c = IdLink__dlm.RecId__c
  JOIN UnifiedIndividual__dlm ON IdLink__dlm.UnifiedRecId__c = Individual__dlm.ssot__Id__c
GROUP BY customer_id__c

Fonctions de mise en forme

Fonction Agrégeable Description Exemple
SUBSTRING N Extrait une sous-chaîne d'une chaîne. Accepte un index de début et une longueur.
SELECT COUNT(SUBSTRING(ssot__Acct__dlm.ssot__AcctTypeId__c, 1, 9))
AS count_of_acct_type__c,
ssot__Acct__dlm.ssot__Id__c AS account_id__c
FROM ssot__Acct__dlm
GROUP BY account_id__c
SUBSTR N Extrait une sous-chaîne d'une chaîne. Accepte une position de départ et un nombre de caractères.
substr(Account_Id, 13, 3)
RTRIM N Retire les caractères d'espace de fin d'une chaîne.
rtrim('2 Spaces After  ')
UPPER N Convertit les caractères minuscules en majuscules.
upper("Account.Industry")
LOWER N Convertit les caractères majuscules en minuscules.
lower("Account.Industry")
CONCAT N Renvoie une chaîne en fusionnant les valeurs des champs spécifiés et des chaînes d'entrée.
concat("OwnerId.FirstName", ' ', "OwnerId.LastName")

Fonctions mathématiques et statistiques

Fonction Agrégeable Description Exemple
MOD N Renvoie le reste après expr1 / expr2.
SELECT MOD(3, 1.8);
ABS N Calcule la valeur absolue d'un nombre (la valeur sans son signe).
abs(number)
RAND N Renvoie un nombre décimal aléatoire dans [0, 1). Non déterministe dans le cas général. Spécifiez une amorce pour renvoyer une séquence répétitive.
SELECT rand();   -- random
SELECT rand(0);  -- repeatable sequence
ROUND N Arrondi au nombre de décimales spécifié.
SELECT round(2.5, 0)
GREATEST N Renvoie la plus grande valeur de la liste d'arguments, en ignorant les valeurs nulles.
SELECT greatest(10, 9, 2, 4, 3);
LEAST N Renvoie la plus petite valeur de la liste des arguments, en ignorant les valeurs nulles.
SELECT least(10, 9, 2, 4, 3);
LOG N Renvoie le logarithme d'un nombre dans une base spécifiée. Arguments : base (la base du logarithme) et number (la valeur dont le logarithme doit être extrait).
log(base, number)
EXP N Renvoie e élevé à la puissance du nombre spécifié.
SELECT EXP(2);
BETWEEN N Sélectionne des valeurs dans une plage donnée (inclusive). Les valeurs peuvent être des chiffres, un texte ou des dates.
SELECT * FROM table
WHERE price BETWEEN 50 AND 100;

Fonctions booléennes

Fonction Agrégeable Description Exemple
IS_TRUE N Renvoie true si la valeur est true.
SELECT count(Id) FROM Table
WHERE Is_True(Boolean_Column);
IS_FALSE N Renvoie true si la valeur est false.
SELECT count(Id) FROM Table
WHERE Is_False(Boolean_Column);
HAS_BOOLEAN_VALUE N Renvoie true si le champ contient une valeur ; renvoie false si elle est nulle ou vide.
SELECT count(Id) FROM Table
WHERE Has_Boolean_Value(Boolean_Column);
HAS_NO_BOOLEAN_VALUE N Renvoie true si la valeur du champ est nulle ou vide ; renvoie false s'il contient une valeur.
SELECT count(Id) FROM Table
WHERE Has_No_Boolean_Value(Boolean_Column);
 
Chargement
Salesforce Help | Article