Traduire les requêtes SQL dans Commerce Intelligence
Vous êtes-vous déjà demandé comment les requêtes SQL sont converties dans les colonnes calculées, mesures et les rapports que vous utilisez dans Commerce Intelligence ? Si vous êtes un utilisateur SQL lourd, comprendre comment SQL est traduit dans Commerce Intelligence vous permet de travailler plus intelligemment dans le Gestionnaire de Data Warehouse et de tirer le meilleur parti de la plateforme Commerce Intelligence.
À la fin de cette rubrique, vous trouverez une matrice de traduction pour les clauses de requête SQL et des éléments Commerce Intelligence .
Commencez par examiner une requête générale :
SELECT
a,
group by
SUM(b)
Aggregate function
(colonne)FROM c
Source
tableWHERE
d IS NOT NULL
Filter
AND time < X
AND time >= Y
time frame
GROUP BY a
group by
Cet exemple couvre la plupart des cas de traduction, mais il existe certaines exceptions. Explorez, en commençant par la manière dont la fonction aggregate
est traduite.
Fonctions d’agrégation
Les fonctions d’agrégation (par exemple, count
, sum
, average
, max
, min
) dans les requêtes prennent la forme d’ agrégations de mesures ou d’ agrégations de colonnes dans Commerce Intelligence. Le facteur de différenciation est de savoir si une jointure est nécessaire pour effectuer l’agrégation.
Consultez un exemple pour chacun des cas ci-dessus.
Agrégation des mesures aggregate
Une mesure est requise lors de l'agrégation de within a single table
. Par exemple, la fonction d’agrégat SUM(b)
de la requête ci-dessus serait probablement représentée par une mesure qui additionne la colonne B
.
Examinez un exemple spécifique de la manière dont une mesure Total Revenue
peut être définie dans Commerce Intelligence. Examinez la requête ci-dessous que vous tentez de traduire :
SELECT
SUM(order_total) as "Total Revenue"
Metric operation
(colonne)FROM orders
Metric source
tableWHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < X
AND created_at >= Y
timestamp
(et rapport time range
)Accédez au créateur de mesures en cliquant sur Manage Data > Mesures > Créer une mesure, vous devez d’abord sélectionner la table source
appropriée, qui dans ce cas est la table orders
. La mesure est ensuite configurée comme illustré ci-dessous :
Agrégation des colonnes
Une colonne calculée est requise lors de l’agrégation d’une colonne qui est associée à partir d’un autre tableau. Par exemple, une colonne peut être créée dans votre table customer
appelée Customer LTV
, qui additionne la valeur totale de toutes les commandes associées à ce client dans la table orders
.
La requête de cette agrégation peut ressembler à ce qui suit :
Select
c.customer_id
SUM(o.order_total) as "Customer LTV"
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.status = 'success'
La configuration de cette fonctionnalité dans Commerce Intelligence nécessite l’utilisation de votre gestionnaire de Data Warehouse, où vous créez un chemin entre votre table orders
et customers
, puis créez une colonne appelée Customer LTV
dans la table de votre client.
Découvrez comment établir un nouveau chemin entre customers
et orders
. L’objectif final est de créer une nouvelle colonne agrégée dans la table customers
. Vous devez donc d’abord accéder à la table customers
de votre Data Warehouse, puis cliquer sur Create a Column > Sélectionner une définition > SUM.
Vous devez ensuite sélectionner la table source. S’il existe un chemin d’accès à votre table orders
, sélectionnez-le simplement dans la liste déroulante. Cependant, si vous créez un chemin d’accès, cliquez sur Create new path et l’écran ci-dessous s’affiche :
Ici, vous devez examiner attentivement la relation entre les deux tables auxquelles vous essayez de vous joindre. Dans ce cas, il existe potentiellement Many
commandes associées au client One
. Par conséquent, la table orders
est répertoriée du côté Many
, tandis que la table customers
sélectionnée du côté One
.
path
équivaut à un Join
dans SQL.Une fois le chemin enregistré, vous pouvez créer la colonne Customer LTV
! Voir ci-dessous :
Maintenant que vous avez créé la nouvelle colonne Customer LTV
dans votre table customers
, vous êtes prêt à créer une agrégation de mesures à l’aide de cette colonne (par exemple, pour trouver la moyenne LTV par client). Vous pouvez également group by
ou filter
en fonction de la colonne calculée dans un rapport à l’aide de mesures existantes créées sur la table customers
.
filter
ou group by
.Voir création de colonnes calculées avec votre Gestionnaire de Data Warehouse.
Group By
clauses
Les fonctions Group By
des requêtes sont souvent représentées dans Commerce Intelligence sous la forme d’une colonne utilisée pour segmenter ou filtrer un rapport visuel. À titre d’exemple, nous allons revoir la requête Total Revenue
que vous avez explorée précédemment, mais segmentons cette fois les recettes par le coupon\_code
pour mieux comprendre quels coupons génèrent le plus de recettes.
Commencez par la requête ci-dessous :
SELECT coupon_code,
group by
SUM(order_total) as "Total Revenue"
Metric operation
(column)FROM orders
Metric source
tableWHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < '2016-12-01'
AND created_at >= '2016-09-01'
timestamp
(et rapport time range
)GROUP BY coupon_code
group by
En utilisant la même mesure Total Revenue
que celle que vous avez créée précédemment, vous êtes maintenant prêt à créer votre rapport de recettes segmenté par code de coupon ! Regardez l’gif ci-dessous qui indique comment configurer ce rapport visuel qui examine les données de septembre à novembre :
Formules
Parfois, une requête peut impliquer plusieurs agrégations afin de calculer la relation entre des colonnes distinctes. Par exemple, vous pouvez calculer la valeur de commande moyenne dans une requête de l’une des deux façons suivantes :
AVG('order\_total')
OUSUM('order\_total')/COUNT('order\_id')
La première méthode impliquerait la création d’une nouvelle mesure qui effectue une moyenne sur la colonne order\_total
. Toutefois, la dernière méthode peut être créée directement dans le créateur de rapports en supposant que des mesures soient déjà configurées pour calculer les Total Revenue
et Number of orders
.
Revenez en arrière et examinez la requête globale pour Average order value
:
SELECT
SUM(order_total) as "Total Revenue"
operation
(colonne)COUNT(order_id) as "Number of orders"
operation
(colonne)SUM(order_total)/COUNT(order_id) as "Average order value"
operation
(colonne) / Opération de mesure (colonne)FROM orders
source
WHERE
email NOT LIKE '%@magento.com'
filter
AND created_at < '2016-12-01'
AND created_at >= '2016-09-01'
Supposons maintenant que des mesures soient déjà configurées pour calculer les Total Revenue
et Number of orders
. Comme ces mesures existent, vous pouvez simplement ouvrir le Report Builder
et créer un calcul à la demande à l’aide de la fonction Formula
:
Remplissage
Si vous êtes un utilisateur SQL lourd, penser à la façon dont les requêtes se traduisent dans Commerce Intelligence vous permet de créer des colonnes, des mesures et des rapports calculés.
Pour une référence rapide, consultez le tableau ci-dessous. Cela affiche l’élément Commerce Intelligence équivalent d’une clause SQL et la manière dont elle peut mapper à plusieurs éléments, selon la manière dont elle est utilisée dans la requête.
Éléments Commerce Intelligence
SQL Clause
Metric
Filter
Report group by
Report time frame
Path
Calculated column inputs
Source table
SELECT
FROM
WHERE
WHERE
(avec éléments de temps)JOIN...ON
GROUP BY