16 techniques SQL que tout débutant doit connaître

Sur une échelle de 1 à 10, quelle est la qualité de vos compétences en entreposage de données ?

Vous voulez dépasser 7/10 ? Cet article est alors pour vous.

Quelle est la qualité de votre SQL ? Vous voulez vous préparer pour un entretien d'embauche au plus vite ?

Ce billet de blog explique en détail les techniques SQL d'entrepôt de données les plus complexes. J'utiliserai le dialecte SQL standard de BigQuery pour griffonner quelques réflexions sur ce sujet.

1. Tables incrémentales et MERGE

La mise à jour du tableau est importante. C'est important en effet. La situation idéale est lorsque vous avez des transactions qui sont une clé PRIMAIRE, des entiers uniques et une incrémentation automatique. La mise à jour de la table dans ce cas est simple :

Ce n'est pas toujours le cas lorsque vous travaillez avec des ensembles de données de schéma en étoile dénormalisés dans des entrepôts de données modernes. vous pouvez être chargé de créer des sessions avec SQL et/ou de mettre à jour de manière incrémentielle des ensembles de données avec seulement une partie des données. transaction_id peut ne pas exister, mais à la place, vous devrez gérer un modèle de données où la clé unique dépend du dernier transaction_id (ou horodatage) connu. Par exemple, user_id dans l'ensemble de données last_online dépend du dernier horodatage de connexion connu. Dans ce cas, vous souhaitez update les utilisateurs existants et insert les nouveaux.

MERGE et mises à jour incrémentielles

Vous pouvez utiliser MERGE ou diviser l'opération en deux actions. Un pour mettre à jour les enregistrements existants avec de nouveaux et un pour en insérer de nouveaux qui n'existent pas (situation LEFT JOIN).

MERGE est une instruction généralement utilisée dans les bases de données relationnelles. La commande Google BigQuery MERGE est l'une des instructions DML (Data Manipulation Language). Il est souvent utilisé pour exécuter trois fonctions principales de manière atomique dans une seule instruction. Ces fonctions sont UPDATE, INSERT et DELETE.

• La clause UPDATE ou DELETE peut être utilisée lorsque deux ou plusieurs données correspondent.
• La clause INSERT peut être utilisée lorsque deux ou plusieurs données sont différentes et ne correspondent pas.
• La clause UPDATE ou DELETE peut également être utilisée lorsque les données fournies ne correspondent pas à la source.

Cela signifie que la commande Google BigQuery MERGE vous permet de fusionner des données Google BigQuery en mettant à jour, en insérant et en supprimant des données de vos tables Google BigQuery.

Considérez ce SQL :

2. Compter les mots

Faire UNNEST() et vérifier si le mot dont vous avez besoin est dans la liste dont vous avez besoin peut être utile dans de nombreuses situations, c'est-à-dire l'analyse des sentiments de l'entrepôt de données :

3. Utilisation de l'instruction IF() en dehors de l'instruction SELECT

Cela nous donne l'occasion d'économiser quelques lignes de code et d'être plus éloquent en termes de code. Normalement, vous voudriez mettre ceci dans une sous-requête et ajouter un filtre dans la clause where mais vous pouvez le faire à la place :

Un autre exemple comment NE PAS l'utiliser avec des tables partitionnées . Ne fais pas ça . C'est un mauvais exemple car, puisque les suffixes de table correspondants sont probablement déterminés dynamiquement (en fonction de quelque chose dans votre table), vous serez facturé pour une analyse complète de la table.

Vous pouvez également l'utiliser dans la clause HAVING et les fonctions AGGREGATE .

4. Utilisation de GROUP BY ROLLUP

La fonction ROLLUP est utilisée pour effectuer une agrégation à plusieurs niveaux. Ceci est utile lorsque vous devez travailler avec des graphiques de dimension.

La requête suivante renvoie le total des crédits dépensés par jour par le type de transaction (is_gift) spécifié dans la clause where , et elle affiche également le total des dépenses pour chaque jour et le total des dépenses pour toutes les dates disponibles.

5. Convertir le tableau en JSON

Imaginez que vous deviez convertir votre table en objet JSON où chaque enregistrement est un élément d'un tableau imbriqué. C'est là que la fonction to_json_string() devient utile :

Ensuite, vous pouvez l'utiliser n'importe où : dates, entonnoirs marketing, indices, histogrammes, etc.

6. Utilisation de PARTITION BY

Compte tenu des colonnes user_id , date et total_cost . Pour CHAQUE date, comment afficher la valeur totale des revenus de CHAQUE client tout en conservant toutes les lignes ? Vous pouvez y parvenir comme ceci :

7. Moyenne mobile

Très souvent, les développeurs BI sont chargés d'ajouter une moyenne mobile à leurs rapports et tableaux de bord fantastiques. Il peut s'agir d'un graphique linéaire MA de 7, 14, 30 jours/mois ou même d'un an. Alors comment le fait-on?

8. Tableaux de dates

Devient très pratique lorsque vous travaillez avec la rétention d'utilisateurs ou que vous souhaitez vérifier certains ensembles de données pour les valeurs manquantes, c'est-à-dire les dates. BigQuery a une fonction appelée GENERATE_DATE_ARRAY :

9. Numéro_ligne()

Ceci est utile pour obtenir quelque chose de plus récent à partir de vos données, c'est-à-dire le dernier enregistrement mis à jour, etc. ou même pour supprimer les doublons :

10. NTILE()

Une autre fonction de numérotation. Vraiment utile pour surveiller des choses comme Login duration in seconds si vous avez une application mobile. Par exemple, mon application est connectée à Firebase et lorsque les utilisateurs login , je peux voir combien de temps cela leur a pris.

Cette fonction divise les lignes en compartiments constant_integer_expression en fonction de l'ordre des lignes et renvoie le numéro de compartiment de base 1 attribué à chaque ligne. Le nombre de lignes dans les compartiments peut différer d'au plus 1. Les valeurs restantes (le reste du nombre de lignes divisé par compartiments) sont distribuées une pour chaque compartiment, en commençant par le compartiment 1. Si constant_integer_expression est évalué à NULL, 0 ou négatif, une erreur est fournie.

11. Rang / dense_rank

Elles sont aussi appelées fonctions de numérotation . J'ai tendance à utiliser DENSE_RANK comme fonction de classement par défaut car elle ne saute pas le prochain classement disponible alors que RANK le ferait. Il renvoie des valeurs de rang consécutives. Vous pouvez l'utiliser avec une partition qui divise les résultats en compartiments distincts. Les lignes de chaque partition reçoivent les mêmes classements si elles ont les mêmes valeurs. Exemple:

Autre exemple avec les prix des produits :

12. Pivoter / dépivoter

Pivot change les lignes en colonnes. C'est tout ce qu'il fait. Unpivot fait le contraire .

13. Première_valeur / dernière_valeur

C'est une autre fonction utile qui aide à obtenir un delta pour chaque ligne par rapport à la première/dernière valeur de cette partition particulière.

14. Convertissez une table en tableau de structures et passez-les à UDF

Ceci est utile lorsque vous devez appliquer une fonction définie par l'utilisateur (UDF) avec une logique complexe à chaque ligne ou à une table. Vous pouvez toujours considérer votre table comme un tableau d'objets TYPE STRUCT, puis passer chacun d'eux à UDF. Cela dépend de votre logique. Par exemple, je l'utilise pour calculer les délais d'expiration des achats :

De la même manière, vous pouvez créer des tables sans avoir besoin d'utiliser UNION ALL . Par exemple, je l'utilise pour simuler certaines données de test pour les tests unitaires. De cette façon, vous pouvez le faire très rapidement en utilisant simplement Alt + Shift + Down dans votre éditeur.

15. Création d'entonnoirs d'événements à l'aide de FOLLOWING AND UNBOUNDED FOLLOWING

Un bon exemple pourrait être les entonnoirs marketing. Votre ensemble de données peut contenir des événements du même type qui se répètent en continu, mais idéalement, vous voudriez enchaîner chaque événement avec le suivant d'un type différent. Cela peut être utile lorsque vous avez besoin d'obtenir une liste de quelque chose, c'est-à-dire des événements, des achats, etc. afin de créer un ensemble de données d'entonnoirs. Travailler avec PARTITION BY vous donne la possibilité de regrouper tous les événements suivants, quel que soit leur nombre dans chaque partition.

16. Expression régulière

Vous devriez l'utiliser si vous avez besoin d'extraire quelque chose de données non structurées, c'est-à-dire des taux de change, des groupements personnalisés, etc.

Travailler avec les taux de change en utilisant regexp

Prenons cet exemple avec des données sur les taux de change :

Travailler avec des versions d'application à l'aide de regexp

Parfois, vous souhaiterez peut-être utiliser regexp pour obtenir les versions majeures , release ou mod de votre application et créer un rapport personnalisé :

Conclusion

SQL est un outil puissant qui aide à manipuler les données. J'espère que ces cas d'utilisation SQL du marketing numérique vous seront utiles. C'est une compétence pratique en effet et peut vous aider dans de nombreux projets. Ces extraits SQL m'ont rendu la vie beaucoup plus facile et je les utilise presque tous les jours au travail. De plus, SQL et les entrepôts de données modernes sont des outils essentiels pour la science des données. Ses fonctionnalités de dialecte robustes permettent de modéliser et de visualiser facilement les données. Étant donné que SQL est le langage utilisé par les entrepôts de données et les professionnels de l'informatique décisionnelle, il s'agit d'un excellent choix si vous souhaitez partager des données avec eux. C'est le moyen le plus courant de communiquer avec presque toutes les solutions d'entrepôt de données/lac du marché.

Publié à l'origine sur mydataschool.com par datamike

Mike est une personne passionnée et axée sur le numérique avec une abondance de dynamisme et d'enthousiasme, aimant les défis que le mélange complet de marketing numérique lance. Vit au Royaume-Uni, a obtenu un MBA à l'Université de Newcastle en 2015.