Apprendre SQL en 100 requêtes pour les data scientists

SQL for the Weary

• Public visé : Rachel, titulaire d’un master en biologie cellulaire, travaille sur l’analyse cellulaire dans un hôpital de recherche et souhaite devenir data scientist.
• Prérequis : connaissances de base des commandes Unix et capacité à analyser des données tabulaires.
• Résultats d’apprentissage : expliquer la différence entre une base de données et un administrateur de base de données, écrire du SQL, définir des tables et manipuler des données, expliquer les types de jointures, utiliser des fonctions de fenêtre pour travailler sur des lignes adjacentes, comprendre les transactions et les triggers, manipuler des données JSON avec SQL, interagir avec une base de données via Python.

Configuration

• Télécharger la dernière version : décompresser dans un répertoire temporaire les fichiers contenant un exemple de base de données SQLite, des requêtes SQL, des scripts Python, etc.

Concepts de base

• Base de données : ensemble de données stockées et consultables.
• Système de gestion de base de données (DBMS) : programme qui gère un type spécifique de base de données.
• SQLite : stocke la base de données dans un seul fichier ; PostgreSQL répartit les informations sur plusieurs fichiers pour de meilleures performances.
• Système de gestion de base de données relationnelle (RDBMS) : stocke les données dans des tables et les interroge avec SQL.
• Base de données NoSQL : il existe aussi des bases de données comme MongoDB qui n’utilisent pas de tables.

Connexion à la base de données

• Connexion à la base de données : ce n’est pas une requête à proprement parler, mais une étape nécessaire avant d’effectuer d’autres opérations.

Commandes d’administration

• Commandes d’administration SQLite : elles ne font pas partie du SQL standard, et les commandes spéciales de PostgreSQL commencent par \.
• Format de sortie : utiliser .headers on et .mode markdown pour afficher les résultats de manière lisible.

Manipulation des tables

• Création de table : définir les tables et les colonnes avec la commande create table.
• Insertion de données : ajouter des données dans une table avec la commande insert into.
• Mise à jour de lignes : modifier les données des lignes qui satisfont une condition précise avec la commande update.
• Suppression de lignes : supprimer les lignes qui satisfont une condition précise avec la commande delete from.

Jointures

• Jointure : opération qui combine les informations de deux tables.
• Jointure interne : utiliser inner join pour combiner uniquement les lignes correspondantes des deux tables.
• Jointure gauche : utiliser left join pour conserver toutes les lignes de la table de gauche et remplir les valeurs manquantes de la table de droite avec null.

Fonctions d’agrégation

• Agrégation : opération qui combine plusieurs valeurs en une seule.
• Fonctions d’agrégation courantes : utiliser sum, max, min, avg, etc. pour agréger les données.
• Regroupement : utiliser group by pour regrouper les lignes selon les combinaisons uniques de certaines colonnes et effectuer des agrégations pour chaque groupe.

L’avis de GN⁺

• Cet article est une ressource utile pour aider à acquérir une compréhension de base des bases de données et de SQL, particulièrement pour les ingénieurs logiciels débutants ou les personnes intéressées par la data science.
• Il explique les différentes fonctionnalités de SQL à l’aide d’exemples concrets, et fournit des connaissances pratiques sur la gestion et la manipulation des bases de données.
• En particulier, les jointures, les fonctions d’agrégation et la manipulation des tables sont essentielles dans les tâches liées à l’analyse de données, et cet article aide à comprendre clairement ces concepts.

[GN⁺]

Avis Hacker News

• Il est possible d’utiliser ChatGPT pour rédiger des requêtes complexes comme le ferait un data scientist. Cela permet d’obtenir des métriques utiles, comme le taux de conversion ou le taux de clic sur les messages.
• Le sens du terme data scientist a évolué depuis l’époque où c’était « le métier le plus sexy ». L’article présente Rachel, titulaire d’un master en biologie, qui analyse des cellules dans un hôpital de recherche et souhaite devenir data scientist. Autrefois, les data scientists avaient à la fois un bagage quantitatif et une formation en software engineering, mais aujourd’hui ils sont parfois considérés comme des « analystes de données surévalués ».
• Il pourrait être préférable de réécrire ce guide avec DuckDB. DuckDB est aujourd’hui plus souvent utilisé que SQLite par les data scientists.
• Un guide SQL très concis, avec un excellent apprentissage par l’exemple. Dans les universités américaines, ce contenu est enseigné sur tout un semestre, mais ce guide suffit pour la plupart des postes d’ingénierie logicielle en entreprise, à l’exception de l’optimisation de bases de données.
• Il est possible de pratiquer SQL avec une application Mac appelée TextQuery. On peut importer rapidement des jeux de données CSV et exécuter des requêtes SQL, et la version bêta est actuellement gratuite.
• Il est utile de télécharger un long tutoriel en une seule page pour un vol avec une connexion Wi-Fi instable. On se demande s’il existe des tutoriels du même type pour d’autres langages ou technologies que SQL.
• Beaucoup de requêtes ne sont valides que pour SQLite, et certaines incluent des fonctionnalités non prises en charge par certains SGBD. Par exemple, le filtrage sur agrégats n’est pas pris en charge par MySQL, MS SQL, Oracle, etc.
• Cela semble moins être un tutoriel pour data scientists qu’un bon résumé général de SQL.
• L’explication de « left outer join » ne s’applique qu’aux conditions d’égalité et peut donner une mauvaise compréhension de left join. Ce type d’explication est très courant, mais trompeur.