Présentation des principales fonctionnalités de Google BigQuery : entraînement à la rédaction de demandes d'analyse marketing
Publié: 2022-04-12Plus une entreprise accumule d'informations, plus la question de savoir où les stocker se pose avec acuité. Si vous n'avez pas la capacité ou le désir de gérer vos propres serveurs, Google BigQuery (GBQ) peut vous aider. BigQuery fournit un stockage rapide, économique et évolutif pour travailler avec le Big Data, et il vous permet d'écrire des requêtes en utilisant une syntaxe de type SQL ainsi que des fonctions standard et définies par l'utilisateur.
Dans cet article, nous examinons les principales fonctions de BigQuery et montrons leurs possibilités à l'aide d'exemples spécifiques. Vous apprendrez à écrire des requêtes de base et à les tester sur des données de démonstration.
Créez des rapports sur les données GBQ sans formation technique ni connaissance de SQL.
Vous avez régulièrement besoin de rapports sur des campagnes publicitaires mais n'avez pas le temps d'étudier SQL ou d'attendre une réponse de vos analystes ? Avec OWOX BI, vous pouvez créer des rapports sans avoir besoin de comprendre comment vos données sont structurées. Sélectionnez simplement les paramètres et les mesures que vous souhaitez voir dans votre rapport Smart Data. OWOX BI Smart Data visualisera instantanément vos données d'une manière que vous pouvez comprendre.
Table des matières
- Qu'est-ce que SQL et quels dialectes sont compatibles avec BigQuery ?
- Où commencer
- Fonctionnalités de Google BigQuery
- Fonctions d'agrégation
- Fonctions de date
- Fonctions de chaîne
- Fonctions de la fenêtre
- conclusion
Qu'est-ce que SQL et quels dialectes sont compatibles avec BigQuery ?
Le langage de requête structuré (SQL) vous permet de récupérer des données, d'ajouter des données et de modifier des données dans de grands tableaux. Google BigQuery prend en charge deux dialectes SQL : le SQL standard et l'ancien SQL hérité.
Le dialecte à choisir dépend de vos préférences, mais Google recommande d'utiliser le SQL standard pour ces avantages :
- Flexibilité et fonctionnalité pour les champs imbriqués et répétitifs
- Prise en charge des langages DML et DDL, vous permettant de modifier les données dans les tables ainsi que de gérer les tables et les vues dans GBQ
- Traitement plus rapide de grandes quantités de données par rapport à Legacy SQL
- Prise en charge de toutes les futures mises à jour de BigQuery
Pour en savoir plus sur les différences de dialecte, consultez la documentation BigQuery.
Voir aussi : Quels sont les avantages du nouveau dialecte SQL standard de Google BigQuery par rapport à l'ancien SQL, et quelles tâches métier pouvez-vous résoudre avec ?
Par défaut, les requêtes Google BigQuery s'exécutent sur Legacy SQL.
Vous pouvez passer au SQL standard de plusieurs manières :
- Dans l'interface BigQuery, dans la fenêtre d'édition de la requête, sélectionnez Afficher les options et décochez la case à côté de Use Legacy SQL :
- Avant d'interroger, ajoutez la ligne #standardSQL et commencez votre requête avec une nouvelle ligne :
Où commencer
Pour que vous puissiez vous entraîner et exécuter des requêtes avec nous, nous avons préparé un tableau avec des données de démonstration. Remplissez le formulaire ci-dessous et nous vous l'enverrons par e-mail.
Données de démonstration pour la pratique des requêtes SQL
Télécharger Pour commencer, téléchargez votre tableau de données de démonstration et importez-le dans votre projet Google BigQuery. Pour ce faire, le moyen le plus simple consiste à utiliser le module complémentaire OWOX BI BigQuery Reports.
- Ouvrez Google Sheets et installez le module complémentaire OWOX BI BigQuery Reports.
- Ouvrez le tableau que vous avez téléchargé et qui contient des données de démonstration et sélectionnez Rapports OWOX BI BigQuery –> Importer des données dans BigQuery :
- Dans la fenêtre qui s'ouvre, choisissez votre projet Google BigQuery, un ensemble de données, et imaginez un nom pour la table dans laquelle les données chargées seront stockées.
- Spécifiez un format pour les données chargées (comme indiqué dans la capture d'écran) :
Si vous n'avez pas de projet dans Google BigQuery, créez-en un. Pour ce faire, vous aurez besoin d'un compte de facturation actif dans Google Cloud Platform. N'ayez pas peur d'avoir besoin de lier une carte bancaire : rien ne vous sera débité à votre insu. De plus, lors de votre inscription, vous recevrez 300 $ pendant 12 mois que vous pourrez dépenser pour le stockage et le traitement des données.
OWOX BI vous aide à combiner les données de différents systèmes dans BigQuery : données sur les actions des utilisateurs sur votre site Web, les appels, les commandes de votre CRM, les e-mails, les frais de publicité. Vous pouvez utiliser OWOX BI pour personnaliser les analyses avancées et automatiser les rapports de toute complexité.
Avant de parler des fonctionnalités de Google BigQuery, rappelons-nous à quoi ressemblent les requêtes de base dans les dialectes Legacy SQL et Standard SQL :
| Requête | SQL hérité | SQL standard |
|---|---|---|
| Sélectionnez des champs dans le tableau | SÉLECTIONNER champ 1, champ2 | SÉLECTIONNER champ 1, champ2 |
| Sélectionner une table à partir de laquelle choisir des champs | DE [projectID:dataSet.tableName] | DE `projectID.dataSet.tableName` |
| Sélectionnez le paramètre par lequel filtrer les valeurs | WHERE champ1=valeur | WHERE champ1=valeur |
| Sélectionner les champs selon lesquels regrouper les résultats | GROUPER PAR champ1, champ2 | GROUPER PAR champ1, champ2 |
| Sélectionnez comment classer les résultats | ORDER BY field1 ASC (croissant) ou DESC (décroissant) | ORDER BY field1 ASC (croissant) ou DESC (décroissant) |
Fonctionnalités de Google BigQuery
Lors de la création de requêtes, vous utiliserez le plus souvent les fonctions d'agrégation, de date, de chaîne et de fenêtre. Examinons de plus près chacun de ces groupes de fonctions.
Voir aussi : Comment commencer à travailler avec le stockage dans le cloud — créer un ensemble de données et des tables et configurer l'importation des données vers Google BigQuery.
Fonctions d'agrégation
Les fonctions d'agrégation fournissent des valeurs récapitulatives pour une table entière. Par exemple, vous pouvez les utiliser pour calculer la taille moyenne des chèques ou le revenu total par mois, ou vous pouvez les utiliser pour sélectionner le segment d'utilisateurs qui a effectué le nombre maximum d'achats.
Voici les fonctions d'agrégation les plus populaires :
| SQL hérité | SQL standard | Que fait la fonction |
|---|---|---|
| MOY(champ) | AVG([DISTINCT] (champ)) | Renvoie la valeur moyenne de la colonne de champ. En SQL standard, lorsque vous ajoutez une condition DISTINCT, la moyenne n'est prise en compte que pour les lignes avec des valeurs uniques (non répétitives) dans la colonne de champ. |
| MAX(champ) | MAX(champ) | Renvoie la valeur maximale de la colonne de champ. |
| MIN(champ) | MIN(champ) | Renvoie la valeur minimale de la colonne de champ. |
| SOMME(champ) | SOMME(champ) | Renvoie la somme des valeurs de la colonne de champ. |
| COUNT(champ) | COUNT(champ) | Renvoie le nombre de lignes dans la colonne de champ. |
| EXACT_COUNT_DISTINCT(champ) | COUNT([DISTINCT] (champ)) | Renvoie le nombre de lignes uniques dans la colonne de champ. |
Pour obtenir une liste de toutes les fonctions d'agrégation, consultez la documentation Legacy SQL et Standard SQL.
Regardons les données de démonstration pour voir comment ces fonctions fonctionnent. Nous pouvons calculer le revenu moyen des transactions, les achats pour les montants les plus élevés et les plus bas, le revenu total, le total des transactions et le nombre de transactions uniques (pour vérifier si les achats ont été dupliqués). Pour ce faire, nous allons écrire une requête dans laquelle nous spécifions le nom de notre projet Google BigQuery, l'ensemble de données et la table.
#SQL hérité
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]#SQL standard
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`En conséquence, nous obtiendrons ce qui suit :
Vous pouvez vérifier les résultats de ces calculs dans le tableau d'origine avec des données de démonstration à l'aide des fonctions standard de Google Sheets (SUM, AVG et autres) ou à l'aide de tableaux croisés dynamiques.
Comme vous pouvez le voir sur la capture d'écran ci-dessus, le nombre de transactions et de transactions uniques est différent. Cela suggère qu'il y a deux transactions dans notre table avec le même transactionId :
Si vous êtes intéressé par des transactions uniques, utilisez une fonction qui compte les chaînes uniques. Vous pouvez également regrouper les données à l'aide de la fonction GROUP BY pour éliminer les doublons avant d'appliquer la fonction d'agrégation.
Données de démonstration pour la pratique des requêtes SQL
TéléchargerFonctions de date
Ces fonctions permettent de traiter les dates : changer leur format, sélectionner le champ nécessaire (jour, mois ou année), ou décaler la date d'un certain intervalle.
Ils peuvent être utiles lorsque :
- convertir les dates et les heures de différentes sources en un seul format pour configurer des analyses avancées
- créer des rapports automatiquement mis à jour ou déclencher des envois (par exemple, lorsque vous avez besoin de données pour les deux dernières heures, la semaine ou le mois)
- créer des rapports de cohorte dans lesquels il est nécessaire d'obtenir des données pour une période de jours, de semaines ou de mois
Voici les fonctions de date les plus couramment utilisées :
| SQL hérité | SQL standard | Description de la fonction |
|---|---|---|
| DATE ACTUELLE() | DATE ACTUELLE() | Renvoie la date actuelle au format % AAAA -% MM-% JJ. |
| DATE(horodatage) | DATE(horodatage) | Convertit la date du format % AAAA -% MM-% JJ% H:% M:% C. au format % AAAA -% MM-% JJ. |
| DATE_ADD(horodatage, intervalle, unités_intervalle) | DATE_ADD(timestamp, INTERVAL interval interval_units) | Renvoie la date de l'horodatage, en l'augmentant de l'intervalle spécifié interval.interval_units.En SQL hérité, il peut prendre les valeurs YEAR, MONTH, DAY, HOUR, MINUTE et SECOND, et en SQL standard, il peut prendre YEAR, QUARTER, MONTH, SEMAINE et JOUR. |
| DATE_ADD(horodatage, - intervalle, unités_intervalle) | DATE_SUB(timestamp, INTERVAL interval interval_units) | Renvoie la date de l'horodatage, en la diminuant de l'intervalle spécifié. |
| DATEDIFF(horodatage1, horodatage2) | DATE_DIFF(horodatage1, horodatage2, partie_date) | Renvoie la différence entre les dates timestamp1 et timestamp2. En SQL hérité, renvoie la différence en jours et en SQL standard, renvoie la différence en fonction de la valeur date_part spécifiée (jour, semaine, mois, trimestre, année). |
| JOUR(horodatage) | EXTRAIT (horodatage DU JOUR) | Renvoie le jour à partir de la date d'horodatage. Prend des valeurs de 1 à 31 inclus. |
| MOIS(horodatage) | EXTRAIT(MOIS DE L'horodatage) | Renvoie le numéro de séquence du mois à partir de la date de l'horodatage. Prend des valeurs de 1 à 12 inclus. |
| ANNEE(horodatage) | EXTRAIT(ANNÉE DE l'horodatage) | Renvoie l'année à partir de la date d'horodatage. |
Pour obtenir une liste de toutes les fonctions de date, consultez la documentation Legacy SQL et Standard SQL.
Jetons un coup d'œil à nos données de démonstration pour voir comment chacune de ces fonctions fonctionne. Par exemple, nous obtiendrons la date actuelle, transformerons la date de la table d'origine au format % AAAA -% MM-% JJ, enlevez-la et ajoutez-y un jour. Ensuite, nous calculerons la différence entre la date actuelle et la date de la table source et décomposerons la date actuelle en champs d'année, de mois et de jour distincts. Pour ce faire, vous pouvez copier les exemples de requêtes ci-dessous et remplacer le nom du projet, l'ensemble de données et la table de données par les vôtres.
#SQL hérité
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]#SQL standard
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)Après avoir exécuté la requête, vous recevrez ce rapport :
Voir aussi : Exemples de rapports pouvant être créés à l'aide de requêtes SQL sur des données dans Google BigQuery et quelles mesures uniques vous pouvez compléter les données de Google Analytics avec OWOX BI.
Fonctions de chaîne
Les fonctions de chaîne vous permettent de générer une chaîne, de sélectionner et de remplacer des sous-chaînes et de calculer la longueur d'une chaîne et la séquence d'index de la sous-chaîne dans la chaîne d'origine. Par exemple, avec les fonctions de chaîne, vous pouvez :
- filtrer un rapport avec des balises UTM transmises à l'URL de la page
- mettre les données dans un format unique si les noms de source et de campagne sont écrits dans des registres différents
- remplacer les données incorrectes dans un rapport (par exemple, si le nom de la campagne est mal imprimé)
Voici les fonctions les plus populaires pour travailler avec des chaînes :
| SQL hérité | SQL standard | Description de la fonction |
|---|---|---|
| CONCAT('str1', 'str2') ou 'str1'+ 'str2' | CONCAT('str1', 'str2') | Concatène 'str1' et 'str2' en une seule chaîne. |
| 'str1' CONTIENT 'str2' | REGEXP_CONTAINS('str1', 'str2') ou 'str1' LIKE '%str2%' | Renvoie true si la chaîne 'str1' contient la chaîne 'str2.'En SQL standard, la chaîne 'str2' peut être écrite sous la forme d'une expression régulière à l'aide de la bibliothèque re2 . |
| LONGUEUR('chaîne' ) | CHAR_LENGTH('str' ) ou CHARACTER_LENGTH('str' ) | Renvoie la longueur de la chaîne 'str' (nombre de caractères). |
| SUBSTR('chaîne', index [, longueur_max]) | SUBSTR('chaîne', index [, longueur_max]) | Renvoie une sous-chaîne de longueur max_len commençant par un caractère d'index de la chaîne 'str'. |
| INFÉRIEUR('str') | INFÉRIEUR('str') | Convertit tous les caractères de la chaîne 'str en minuscules. |
| MAJUSCULE(str) | MAJUSCULE(str) | Convertit tous les caractères de la chaîne 'str' en majuscules. |
| INSTR('str1', 'str2') | STRPOS('ch1', 'ch2') | Renvoie l'index de la première occurrence de la chaîne 'str2' à la chaîne 'str1' ; sinon, renvoie 0. |
| REMPLACER('str1', 'str2', 'str3') | REMPLACER('str1', 'str2', 'str3') | Remplace 'str1' par 'str2' par 'str3'. |
Vous pouvez en savoir plus sur toutes les fonctions de chaîne dans la documentation Legacy SQL et Standard SQL.
Examinons les données de démonstration pour voir comment utiliser les fonctions décrites. Supposons que nous ayons trois colonnes distinctes contenant des valeurs de jour, de mois et d'année :
Travailler avec une date dans ce format n'est pas très pratique, nous pouvons donc combiner les valeurs dans une colonne. Pour ce faire, utilisez les requêtes SQL ci-dessous et n'oubliez pas de remplacer le nom de votre projet, de votre ensemble de données et de votre table dans Google BigQuery.
#SQL hérité
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1#SQL standard
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1Après avoir exécuté la requête, nous recevons la date dans une colonne :
Souvent, lorsque vous téléchargez une page sur un site Web, l'URL enregistre les valeurs des variables que l'utilisateur a choisies. Il peut s'agir d'un mode de paiement ou de livraison, d'un numéro de transaction, de l'index du magasin physique dans lequel l'acheteur souhaite retirer l'article, etc. A l'aide d'une requête SQL, vous pouvez sélectionner ces paramètres à partir de l'adresse de la page. Considérez deux exemples de comment et pourquoi vous pourriez le faire.
Exemple 1 . Supposons que nous voulions connaître le nombre d'achats au cours desquels les utilisateurs récupèrent des marchandises dans des magasins physiques. Pour ce faire, nous devons calculer le nombre de transactions envoyées à partir des pages de l'URL qui contiennent une sous-chaîne shop_id (un index pour un magasin physique). Nous pouvons le faire avec les requêtes suivantes :
#SQL hérité
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check#SQL standard
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2D'après le tableau résultant, nous voyons que 5502 transactions (check = true) ont été envoyées à partir de pages contenant shop_id :
Exemple 2 . Vous avez attribué un delivery_id à chaque méthode de livraison et vous spécifiez la valeur de ce paramètre dans l'URL de la page. Pour savoir quelle méthode de livraison l'utilisateur a choisie, vous devez sélectionner le delivery_id dans une colonne séparée.
Nous pouvons utiliser les requêtes suivantes pour cela :
#SQL hérité
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC#SQL standard
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASCEn conséquence, nous obtenons une table comme celle-ci dans Google BigQuery :
Données de démonstration pour la pratique des requêtes SQL
TéléchargerFonctions de la fenêtre
Ces fonctions sont similaires aux fonctions d'agrégation dont nous avons parlé ci-dessus. La principale différence est que les fonctions de fenêtre n'effectuent pas de calculs sur l'ensemble des données sélectionnées à l'aide de la requête, mais uniquement sur une partie de ces données — un sous-ensemble ou une fenêtre .
À l'aide des fonctions de fenêtre, vous pouvez agréger des données dans une section de groupe sans utiliser la fonction JOIN pour combiner plusieurs requêtes. Par exemple, vous pouvez calculer le revenu moyen par campagne publicitaire ou le nombre de transactions par appareil. En ajoutant un autre champ au rapport, vous pouvez facilement connaître, par exemple, la part des revenus d'une campagne publicitaire lors du Black Friday ou la part des transactions effectuées depuis une application mobile.
Avec chaque fonction de la requête, vous devez épeler l'expression OVER qui définit les limites de la fenêtre. OVER contient trois composants avec lesquels vous pouvez travailler :
- PARTITION BY — Définit la caractéristique par laquelle vous divisez les données d'origine en sous-ensembles, tels que clientId ou DayTime
- ORDER BY — Définit l'ordre des lignes dans un sous-ensemble, comme l'heure DESC
- WINDOW FRAME — Vous permet de traiter des lignes dans un sous-ensemble d'une entité spécifique (par exemple, uniquement les cinq lignes avant la ligne actuelle)
Dans ce tableau, nous avons rassemblé les fonctions de fenêtre les plus fréquemment utilisées :
| SQL hérité | SQL standard | Description de la fonction |
|---|---|---|
| MOY(champ) COUNT(champ) COUNT(champ DISTINCT) MAX() MIN() SOMME() | AVG([DISTINCT] (champ)) COUNT(champ) COUNT([DISTINCT] (champ)) MAX(champ) MIN(champ) SOMME(champ) | Renvoie la valeur moyenne, numérique, maximale, minimale et totale de la colonne de champ dans le sous-ensemble sélectionné. DISTINCT est utilisé pour calculer uniquement des valeurs uniques (non répétitives). |
| DENSE_RANK() | DENSE_RANK() | Renvoie le numéro de ligne dans un sous-ensemble. |
| FIRST_VALUE(champ) | FIRST_VALUE (champ[{RESPECT | IGNORE} NULLS]) | Renvoie la valeur de la première ligne de la colonne de champ dans un sous-ensemble. Par défaut, les lignes avec des valeurs vides de la colonne de champ sont incluses dans le calcul. RESPECT ou IGNORE NULLS spécifie s'il faut inclure ou ignorer les chaînes NULL. |
| LAST_VALUE(champ) | LAST_VALUE (champ [{RESPECT | IGNORE} NULLS]) | Renvoie la valeur de la dernière ligne d'un sous-ensemble à partir de la colonne de champ. Par défaut, les lignes avec des valeurs vides dans la colonne de champ sont incluses dans le calcul. RESPECT ou IGNORE NULLS spécifie s'il faut inclure ou ignorer les chaînes NULL. |
| LAG(champ) | LAG (champ[, offset [, default_expression]]) | Renvoie la valeur de la ligne précédente par rapport à la colonne de champ actuelle dans le sous-ensemble. Offset est un entier qui spécifie le nombre de lignes à décaler vers le bas à partir de la ligne actuelle. Default_expression est la valeur que la fonction renverra s'il n'y a pas d'exigence chaîne dans le sous-ensemble. |
| PLOMB(champ) | LEAD (champ[, offset [, default_expression]]) | Renvoie la valeur de la ligne suivante par rapport à la colonne de champ actuelle dans le sous-ensemble. Offset est un entier qui définit le nombre de lignes que vous souhaitez déplacer vers le haut par rapport à la ligne actuelle. Default_expression est la valeur que la fonction renverra s'il n'y a pas de chaîne requise dans le sous-ensemble actuel. |
Vous pouvez voir une liste de toutes les fonctions analytiques d'agrégation et fonctions de navigation dans la documentation pour Legacy SQL et Standard SQL.
Exemple 1 . Disons que nous voulons analyser l'activité des clients pendant les heures de travail et les heures non travaillées. Pour ce faire, nous devons diviser les transactions en deux groupes et calculer les métriques d'intérêt :
- Groupe 1 — Achats pendant les heures de travail de 9h00 à 18h00
- Groupe 2 — Achats après les heures de 00h00 à 9h00 et de 18h00 à 23h59
En plus des heures de travail et des heures non travaillées, une autre variable pour former une fenêtre est clientId. Autrement dit, pour chaque utilisateur, nous aurons deux fenêtres :
| la fenêtre | identité du client | Jour |
|---|---|---|
| fenêtre 1 | ID client 1 | heures d'ouverture |
| fenêtre 2 | ID client 2 | heures non travaillées |
| fenêtre 3 | ID client 3 | heures d'ouverture |
| fenêtre 4 | ID client 4 | heures non travaillées |
| fenêtre N | ID client N | heures d'ouverture |
| fenêtre N+1 | identifiant client N+1 | heures non travaillées |
Utilisons les données de démonstration pour calculer les revenus moyens, maximaux, minimaux et totaux, le nombre total de transactions et le nombre de transactions uniques par utilisateur pendant les heures ouvrables et non ouvrables. Les demandes ci-dessous nous aideront à le faire.
#SQL hérité
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC#SQL standard
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESCVoyons ce qui se passe en conséquence en utilisant l'exemple de l'utilisateur avec le clientId 102041117.1428132012. Dans le tableau d'origine pour cet utilisateur, nous avons les données suivantes :
En exécutant la requête, nous recevons un rapport qui contient les revenus moyens, minimum, maximum et totaux de cet utilisateur ainsi que le nombre total de transactions de l'utilisateur. Comme vous pouvez le voir dans la capture d'écran ci-dessous, les deux transactions ont été effectuées par l'utilisateur pendant les heures de travail :
Exemple 2 . Passons maintenant à une tâche plus compliquée :
- Mettez des numéros de séquence pour toutes les transactions dans la fenêtre en fonction de l'heure de leur exécution. Rappelons que nous définissons la fenêtre par utilisateur et plages horaires travaillées/non travaillées.
- Indiquez les revenus de la transaction suivante/précédente (par rapport à la transaction actuelle) dans la fenêtre.
- Affichez le chiffre d'affaires de la première et de la dernière transaction dans la fenêtre.
Pour ce faire, nous utiliserons les requêtes suivantes :
#SQL hérité
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour#SQL standard
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hourNous pouvons vérifier les résultats des calculs en utilisant l'exemple d'un utilisateur que nous connaissons déjà : clientId 102041117.1428132012 :
Sur la capture d'écran ci-dessus, nous pouvons voir que :
- la première transaction était à 15h00 et la deuxième transaction était à 16h00
- après la transaction à 15h00, il y a eu une transaction à 16h00 avec un revenu de 25066 (colonne lead_revenue)
- avant la transaction à 16h00, il y a eu une transaction à 15h00 avec un revenu de 3699 (colonne lag_revenue)
- la première transaction dans la fenêtre était à 15h00 et le revenu de cette transaction était de 3699 (colonne first_revenue_by_hour)
- la requête traite les données ligne par ligne, donc pour la transaction en question, la dernière transaction dans la fenêtre sera elle-même et les valeurs dans les colonnes last_revenue_by_hour et revenue seront les mêmes
Articles utiles sur Google BigQuery :
- Les 6 meilleurs outils de visualisation BigQuery
- Comment télécharger des données sur Google BigQuery
- Comment télécharger des données brutes de Google Ads vers Google BigQuery
- Connecteur Google BigQuery Google Sheets
- Automatisez les rapports dans Google Sheets à l'aide des données de Google BigQuery
- Automatisez les rapports dans Google Data Studio à partir des données de Google BigQuery
Si vous souhaitez collecter des données non échantillonnées à partir de votre site Web dans Google BigQuery, mais que vous ne savez pas par où commencer, réservez une démo. Nous vous parlerons de toutes les possibilités que vous offrent BigQuery et OWOX BI.
Nos clients
croître 22 % plus rapide
Développez-vous plus rapidement en mesurant ce qui fonctionne le mieux dans votre marketing
Analysez votre efficacité marketing, trouvez les zones de croissance, augmentez le ROI
Obtenir une démoconclusion
Dans cet article, nous avons examiné les groupes de fonctions les plus populaires : agrégat, date, chaîne et fenêtre. Cependant, Google BigQuery possède de nombreuses autres fonctions utiles, notamment :
- fonctions de casting qui vous permettent de convertir des données dans un format spécifique
- fonctions génériques de table qui vous permettent d'accéder à plusieurs tables dans un ensemble de données
- des fonctions d'expressions régulières qui permettent de décrire le modèle d'une requête de recherche et non sa valeur exacte
Nous allons certainement écrire sur ces fonctions sur notre blog. En attendant, vous pouvez essayer toutes les fonctions décrites dans cet article à l'aide de nos données de démonstration.
Données de démonstration pour la pratique des requêtes SQL
Télécharger