     
 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 :   https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql  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   avec SQL et/ou de mettre à jour de manière incrémentielle des ensembles de données avec seulement une partie des données.   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   (ou horodatage) connu. Par exemple,   dans l'ensemble de données   dépend du dernier horodatage de connexion connu. Dans ce cas, vous souhaitez   les utilisateurs existants et   les nouveaux. des sessions transaction_id transaction_id user_id last_online update insert  MERGE et mises à jour incrémentielles  Vous pouvez utiliser   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. MERGE  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 :   https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-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 :   https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql  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   mais vous pouvez   faire à la place : where le   https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql  Un autre exemple comment   l'utiliser avec des tables   .   . 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  NE PAS partitionnées Ne fais pas ça serez facturé pour une analyse complète de la table.   https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql  Vous pouvez également l'utiliser dans la clause   et les fonctions   . HAVING 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   , et elle affiche également le total des dépenses pour chaque jour et le total des dépenses pour toutes les dates disponibles. where   https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql  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   devient utile : to_json_string()   https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql  Ensuite, vous pouvez l'utiliser n'importe où : dates, entonnoirs marketing, indices, histogrammes, etc.  6. Utilisation de PARTITION BY  Compte tenu des colonnes   ,   et   . 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 : user_id date total_cost   https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql  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?   https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql  8. Tableaux de dates  Devient très pratique lorsque vous travaillez avec   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   : la rétention d'utilisateurs GENERATE_DATE_ARRAY   https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql  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 :   https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql  10. NTILE()  Une autre fonction de numérotation. Vraiment utile pour surveiller des choses comme   si vous avez une application mobile. Par exemple, mon application est connectée à Firebase et lorsque les utilisateurs   , je peux voir combien de temps cela leur a pris.  Login duration in seconds login  Cette fonction divise les lignes en compartiments   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   est évalué à NULL, 0 ou négatif, une erreur est fournie. constant_integer_expression constant_integer_expression   https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql  11. Rang / dense_rank  Elles sont aussi appelées fonctions   . J'ai tendance à utiliser     car elle ne saute pas le prochain classement disponible alors que   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.  de numérotation DENSE_RANK comme fonction de classement par défaut RANK Exemple:   https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql   Autre exemple avec les prix des produits :   https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql  12. Pivoter / dépivoter  Pivot change les lignes en colonnes. C'est tout ce qu'il fait. Unpivot fait le   . contraire   https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql  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.   https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql  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 :   https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql  De la même manière, vous pouvez créer des tables sans avoir besoin d'utiliser   . 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   +   +   dans votre éditeur. UNION ALL Alt Shift Down   https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql  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.   https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql  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 :   https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql  Travailler avec des versions d'application à l'aide de regexp  Parfois, vous souhaiterez peut-être utiliser   pour obtenir les versions   ,   ou   de votre application et créer un rapport personnalisé : regexp majeures release mod   https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql  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   par  mydataschool.com 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.

Read me on Medium.com

Read My Stories

Cet audio est produit dans la langue originale de l'histoire !

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

About Author

COMMENTAIRES

ÉTIQUETTES

CET ARTICLE A ÉTÉ PARU DANS

Related Stories

Créer des produits cryptographiques centrés sur l'utilisateur : l'importance des retours clients

Des forums aux flux : comment les algorithmes des réseaux sociaux façonnent l'interaction numérique

Nomades numériques, écoutez : ce que vous devez savoir sur le nouveau visa DTV de la Thaïlande

Le modèle Bitcoin UTXO, alimentant un écosystème unique

Créer des produits cryptographiques centrés sur l'utilisateur : l'importance des retours clients

Des forums aux flux : comment les algorithmes des réseaux sociaux façonnent l'interaction numérique

Nomades numériques, écoutez : ce que vous devez savoir sur le nouveau visa DTV de la Thaïlande

Le modèle Bitcoin UTXO, alimentant un écosystème unique

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps