Premières requêtes SQL

Sélectionner, Filtrer, Agréger
Author

Ludovic Deneuville

Introduction

Vous allez réaliser ce TP su un Datalab.

Le Datalab en quelques mots

Un TP dédié vous expliquera plus en détail ce qu’est le Datalab.

Le Datalab permet aux utilisateurs de lancer facilement des services pour travailler avec des langages comme R ou Python, ou encore gérer des bases de données.

Il offre une grande puissance de calcul, ce qui permet de traiter de gros volumes de données et de réaliser des analyses complexes sans se soucier des limites techniques.

Avec cette plateforme :

  • les utilisateurs accèdent aux ressources nécessaires en quelques clics
  • sans devoir installer eux-mêmes les logiciels
  • ni configurer les serveurs

Datalab du GENES ou SSPCloud

Connectez-vous à l’une des 2 instances suivantes (elles sont quasiment identiques) :

Tip

Pour éviter les problèmes de charges :

  • Les élèves fonctionnaires feront le TP sur le SSPCloud
  • Les élèves ingénieurs ➡️ Datalab du GENES

VM ENSAI

En cas d’indisponibilité des Datalabs

Uniquement en cas d’indisponibilité des Datalabs, vous pouvez réaliser le TP directement sur la VM. Le logiciel DBeaver est installé.

    • Type de connexion : DuckDB
    • Suivant
    • Path : :memory:
    • Terminer
    • Editeur SQL ➡️ Script SQL (raccourci : F3)

Cela ouvre une fenêtre dans laquelle vous pouvez saisir du SQL.

1 Lancement du service

Note

Uniquement sur le Datalab.

    • Allez dans Catalogue de services
    • Onglet Databases
CloudBeaver

CloudBeaver est une application web légère dédiée à la gestion de bases de données. Elle permet de se connecter à divers types de bases de données, qu’elles soient SQL, NoSQL ou hébergées dans le cloud, à partir d’un seul point d’accès via un navigateur. CloudBeaver facilite l’exploration, la modification et la visualisation des données sans nécessiter l’installation de logiciels locaux.

Elle offre également des fonctionnalités de sécurité pour protéger l’accès aux données. Son interface conviviale en fait un outil accessible aussi bien aux développeurs qu’aux analystes de données.

Pour ce premier TP, nous n’allons pas utiliser de base de données PostgreSQL. Nous allons simplement utiliser DuckDB pour lire des fichiers de données.

DuckDB

DuckDB est un moteur de base de données relationnelle conçu pour des analyses rapides et efficaces.

DuckDB est idéal pour les charges de travail analytiques en raison de sa simplicité, de sa rapidité et de son extensibilité, surtout pour traiter des fichiers volumineux localement.

Caractéristique Description
Simplicité Fonctionne sans serveur, intégré dans le processus hôte. Aucune dépendance externe pour la compilation ou l’exécution, simplifiant son déploiement.
Rapidité Optimisé pour l’analytique (OLAP) avec un moteur d’exécution vectorisé en colonnes, réduisant le temps de traitement pour les requêtes complexes.
Richesse fonctionnelle Supporte des requêtes SQL complexes, fonctions de fenêtre, index secondaires, et assure des garanties ACID grâce au contrôle de concurrence (MVCC).
Extensibilité Permet l’ajout de types de données, fonctions et formats de fichiers via des extensions (supporte Parquet, JSON, S3, HTTP(S)).
Gratuit et open-source Licence MIT, code source disponible et contributions ouvertes à tous.
    • En haut à gauche, cliquez sur le +, puis New connection
    • Sélectionner DuckDB
    • Cliquez sur le bouton CREATE
    • Dans l’explorer à gauche, clic droit sur votre connection DuckDB ➡️ SQL Editor
    • vous pouvez maintenant saisir du code SQL

2 Les prénoms

Le fichier des prénoms contient des données sur les prénoms attribués aux enfants nés en France depuis 1900. Ces données sont disponibles au niveau France et par département.

Dans ce TP, nous allons utiliser la version au format parquet, mise à disposition sur le site data.gouv par Icem7.

Fichier parquet

Le format Parquet est un format de fichier de stockage de données optimisé pour les systèmes de traitement analytique de grande échelle. Voici ses principales caractéristiques :

  1. Stockage en colonnes : Parquet stocke les données par colonnes plutôt que par lignes, ce qui améliore l’efficacité de l’accès aux données dans les charges de travail analytiques.

  2. Compression efficace : La compression par colonne permet un taux de compression moyen de 5 à 10 fois par rapport aux formats CSV, voire plus pour de gros fichiers. Cela réduit significativement la taille des données stockées et le coût de stockage.

  3. Optimisé pour l’analytique : Parquet est conçu pour les requêtes en lecture intensive, car il permet de charger uniquement les colonnes nécessaires pour une analyse, ce qui améliore les performances en particulier sur les données volumineuses.

  4. Métadonnées Riches et Auto-descriptives : Parquet inclut des métadonnées détaillées (schéma, types de données, statistiques min/max), permettant une lecture rapide et sans risque d’erreur. Ces métadonnées facilitent également le traitement des données par différents outils.

  5. Langage Indépendant et Open Source : Le format Parquet est indépendant du langage et peut être utilisé avec divers langages de programmation (Python, R, C++, Java). Il est aussi open source et compatible avec la plupart des frameworks de big data.

Pour en savoir plus sur le format Parquet :

  • CREATE OR REPLACE VIEW prenom AS
FROM 'https://static.data.gouv.fr/resources/base-prenoms-insee-format-parquet/20231121-161435/prenoms-nat2022.parquet'
    • Vous pourrez ensuite requêter sur cette vue comme si c’était une table
Éxécuter une requête

Cliquez sur la requête pour y positionner le pointeur de la souris, puis au choix :

  • Cliquez sur le petit triangle orange
  • CTRL + ENTREE

2.1 Premières requêtes

Important

Prenez le temps et l’habitude d’écrire de jolies requêtes bien alignées !!!

bien.sql
SELECT c.nom AS club, 
       AVG(j.elo) AS moyenne_elo
  FROM joueuse j 
 INNER JOIN club c USING (id_club) 
 WHERE j.mail IS NOT NULL 
 GROUP BY c.nom 
 ORDER BY 2 DESC;


pasbien.sql
SELECT c.nom AS club, AVG(j.elo) AS moyenne_elo FROM joueuse j INNER JOIN club c USING (id_club) WHERE mail IS NOT NULL GROUP BY c.nom ORDER BY 2 DESC;
Secret statistique

Les prénoms ayant un nombre insuffisant d’occurrences sont regroupés sous une appellation générique. Cela permet de protéger le secret statistique et de garantir l’anonymat.

Sans cette précaution, par exemple :

  • si vous vous appeliez Yann-Adam LEFGRIGO
  • et que vous êtes le seul à porter ce prénom
  • n’importe qui pourrait connaître votre année de naissance

De même, si vous êtiez 2 à porter ce prénom, l’autre Yann-Adam connaitrait aussi votre année de naissance. Ce n’est toujours pas satisfaisant. Il faut donc à minima 3 individus pour garantir l’anonymat.

    • Classez-ces années en décroissant
    • Que remarquez-vous ?

2.2 Année 2022

Filtrez sur l’année 2022 et excluez les prénoms rares.

    • Classez-les en décroissant par le nombre de fois où ils sont été donnés
    • Quels sont les prénoms masculins et féminins les plus donnés ?
    • Pour extraite la première lettre d’un prénom, vous pouvez par exemple utiliser la fonction SUBSTRING
    • Bonus : différenciez filles et garçons

2.3 Statistiques descriptives

Filtrez pour exclure les prénoms rares.

    • Trouvez une fonction adéquate
    • classez par année
    • Difficulty : ⭐ ⭐ ⭐
    • classez par année
    • utilisez la fonction ROUND(<value>, 2) pour arrondir les moyennes
    • Que remarquez-vous ?

Dans cette partie vous allez avoir besoin d’utiliser des sous-requêtes

2.4 Jérôme, c’est moi

    • classez par année décroissante
    • Que remarquez-vous ?
    • recherchez une fonction DuckDB qui permet de répondre à ce besoin
  • voir Fontion DuckDB strip_accents()
  • Regroupez par strip_accents(preusuel) et annais
  • Affichez strip_accents(preusuel), annais et la somme de la colonne nombre

2.5 Suivi temporel

Filtrez pour exclure l’année XXXX.

    • Classez par année décroissante
    • Remarque : vous n’aurez pas le nombre exact car certains sont dans les prénoms rares
Note

Même si le champ annais n’est pas de type numérique, il est possible de comparer des chaines de caractères.

Le code ASCII est utilisé pour comparer le premier caractère de la chaine, puis en cas d’égalité, le 2e caractère…

2.6 Prénoms composés

Nous allons maintenant nous interesser aux prénoms composés entre 2000 et 2009

    • Triez en décroissant par ce nombre
    • Que remarquez-vous ? N’y a-t-il pas des intrus ?
    • Par exemple avec la fonction DuckDB split_part()

2.7 Cette année-là

Difficulty : ⭐ ⭐ ⭐

Filtrez sur l’année 1962 et excluez les prénoms rares.

    • exemple : ici Dominique (H) et Dominique (F) comptent pour 2 prénoms
    • affichez le nombre de fois où ils ont été donnés à chaque sexe
    • True si le prénom a été donné à plus de filles que de garçons
    • False sinon

3 Fichier des individus

  • CREATE OR REPLACE VIEW individus AS
FROM 'https://static.data.gouv.fr/resources/recensement-de-la-population-fichiers-detail-individus-localises-au-canton-ou-ville-2020-1/20231023-122841/fd-indcvi-2020.parquet';

    • Pourquoi ce nombre d’individus vivant en France paraît faible ?
Poids de l’individu

Dans de nombreuses bases de données de l’INSEE, la variable ipondi représente le poids de l’individu.

i.e. Si une ligne a un IPONDI égal à 2, cela signifie qu’elle représente 2 individus.

    • Vous pouvez suffixer votre somme par ::INT pour convertir ce nombre en entier
    • Que remarquez-vous ?
Codification

Pourquoi utiliser une codification pour le fichier national individus localisés au canton-ou-ville du recensement de la population 2020 ?

  • Espace de stockage réduit : Les codes courts remplacent des chaînes de texte plus longues, ce qui diminue la taille du fichier et rend le stockage et le transfert de données plus efficaces. Même après codification, le fichier pèse plus de 500 Mo
  • Standardisation et uniformité : Utiliser des codes standardisés permet de garantir une structure homogène et facilite l’intégration et la comparaison des données entre différents fichiers ou études.
  • Performances de traitement : Les codes sont plus rapides à traiter que des textes longs, notamment pour des opérations de recherche, de tri, et de filtrage, ce qui est crucial pour les grands ensembles de données.

  • CREATE OR REPLACE VIEW variables_individus AS
FROM 'https://static.data.gouv.fr/resources/recensement-de-la-population-fichiers-detail-individus-localises-au-canton-ou-ville-2020-1/20231025-082910/dictionnaire-variables-indcvi-2020.csv'

    • ordonnez par numéro de département

    • restreignant sur les individus entre 25 et 29 ans
    • différenciant les hommes et les femmes

Arrêtez votre service

C’est la fin du TP, vous pouvez maintenant sauvegarder votre travail et libérer les ressources réservées :

    • par exemple dans P:/Cours1A/UE3-Bases-de-donnees-relationnelles/TP1/tp1.sql

Pour aller plus loin