Formation sélection de données en SQL

Cas d'usage : sauvetage en mer

Transcript

1. Formation à la sélection de données en SQL 1
2. Généralités sur les bases de données relationnelles 2
3. Qu'est-ce qu'une base de données ? Une base de données (BDD) est un logiciel qui permet d'organiser des données sur un disque dur de manière organisée. • Répond au besoin de stocker de l'information dans un système informatique de manière optimisée • Le modèle de données est à définir en fonction de la finalité de la base de données • La taille d'une base de données dépend de la complexité de ce que l'on modélise et de la popularité du système informatique (quelques Mo à plusieurs To) • Permet de stocker de l'information de manière structurée la plupart du temps • Il existe plusieurs acteurs de système de gestion de bases de données 3
4. Dans les bases de données : les tables Les tables dans une base de données permettent de stocker de l'information. Dans chaque table, on stocke un concept (un utilisateur, un moyen de secours, un message etc.). On ne stocke pas des concepts différents dans une même table. Les tables peuvent avoir des liaisons entre elles ("un utilisateur possède 0..n comptes bancaires"). Si le domaine métier est complexe, la base de données aura un schéma complexe et ainsi un grand nombre de tables. Les tables ressemblent à des feuilles de tableur : ce sont des tableaux à structure fixe qui comportent plusieurs champs / colonnes et plusieurs lignes. 4
5. Dans les bases de données : les tables Les colonnes des tables peuvent être de différents types : • texte : adresse e-mail, nom d'un établissement • entiers : nombre de portes d'une maison, identifiant numérique • nombres décimaux : prix, température en degrés • booléens : vrai / faux • dates : date de création, date d'expédition Le type d'une colonne ne peut pas être changé. Ce type définit ce qu'il est possible de stocker et quelles seront les utilisations possibles des données. 5
6. Exemple d'une table 6
7. Récupérer des données depuis une BDD 7
8. Le langage SQL • Pour interagir avec une base de données, on utilise le langage SQL • Le langage SQL est apparu dans les années 70-80 et a très peu évolué depuis • Une immense majorité des acteurs de bases de données relationnelles respectent le standard du langage, avec quelques modifications mineures • On utilise le langage SQL pour créer des tables, insérer des données, récupérer des données, mettre à jour des données, supprimer des données 8
9. Structure d'une requête SQL de sélection Une requête de sélection classique comporte 3 parties : • SELECT : spécifie quels champs des tables nous intéressent • FROM : spécifie depuis quelle table nous récupérons les données • WHERE : spécifie les contraintes à appliquer lorsque nous récupérons les données Exemple : sélection de tous les noms des CROSS et des numéros d'opération depuis 2017 SELECT "operation_id", "cross" FROM "operations" WHERE "date_heure_reception_alerte" >= '2017-01-01' 9
10. Applications de contraintes avancées Exemple de contraintes plus avancées SELECT "operation_id", "cross", "cross_sitrep" FROM "operations" WHERE "date_heure_reception_alerte" >= '2017-01-01' AND "evenement" IN ('Abordage', 'Baignade') AND ("mer_force" BETWEEN 2 AND 5 OR "vent_force" BETWEEN 5 AND 8) AND "cross" <> 'La Garde' 10
11. Sélection sans les doublons L'opérateur DISTINCT permet de retirer des doublons en effectuant une comparaison stricte (Bob est différent de BOB). SELECT DISTINCT "evenement" FROM "operations" SELECT COUNT(DISTINCT "cross") FROM "operations" 11
12. Exercices Trouver tous les numéros de SITREP du CROSS La Garde en 2018 avec un vent supérieur à 2. Trouver la date d'alerte, le CROSS et la position GPS pour les opérations concernant une activité de plongée entre 2010 et 2015 qui ne se déroulent pas à La Garde. 12
13. Corrections SELECT "cross_sitrep" FROM "operations" WHERE "cross" = 'La Garde' AND "vent_force" > 2 SELECT "date_heure_reception_alerte", "cross", "latitude", "longitude" FROM "operations" WHERE "evenement" = 'Plongée à bouteille' AND "date_heure_reception_alerte" BETWEEN '2010-01-01' AND '2015-12-31' AND "cross" <> 'La Garde' 13
14. Agrégats dans une requête SQL Il est souvent nécessaire de produire des agrégats quand on ne s'intéresse pas au détail de chaque ligne. Pour ceci, on utilise l'opérateur GROUP BY et des fonctions d'agrégats. Fonctions d'agrégats classiques : • AVG : calcule une moyenne dans un groupe • COUNT : compte le nombre de lignes dans un groupe • MIN : trouve la valeur minimale dans un groupe • MAX : trouve la valeur maximale dans un groupe • SUM : calcule une somme de valeurs dans un groupe 14
15. Agrégats dans une requête SQL : exemple Comptons le nombre d'opérations par CROSS et par moyen d'alerte sur l'année 2017. SELECT "cross", "moyen_alerte", count("operation_id") as "nombre_operations" FROM "operations" WHERE "date_heure_reception_alerte" BETWEEN '2017-01-01' AND '2018-01-01' GROUP BY "cross", "moyen_alerte" 15
16. L'absence de valeur : NULL La valeur NULL permet de spécifier qu'une valeur est absente (et non pas vide). Elle peut être utilisée pour tous les types de données. Lors de la création d'une table, il est possible de spécifier qu'aucune valeur ne peut valoir la valeur NULL au niveau de chaque colonne. SELECT "departement", count("operation_id") FROM "operations" GROUP BY "departement" SELECT "cross_sitrep", "latitude", "longitude" FROM "operations" WHERE "latitude" IS NULL AND "longitude" IS NOT NULL 16
17. Sous-requêtes Les sous-requêtes prennent en entrée le résultat d'une première requête. Trouvons les journées avec plus de 100 opérations CROSS. select * from ( select "annee", "mois", "jour", count("operation_id") as "nb_operations" from "operations_stats" group by "annee", "mois", "jour" ) as "t" where "nb_operations" > 100 17 Nouveautés : ◦ Alias d'une colonne ◦ Création d'une table virtuelle aliassée ◦ Sélection de toutes les colonnes avec *
18. IN / NOT IN En utilisant une sous-requête et en la combinant avec un IN ou un NOT IN, on peut combiner les résultats de plusieurs sources. select "cross", count("operation_id") as "nb_operations" from "operations" where "operation_id" in ( select "operation_id" from "operations_stats" where "nombre_moyens_aeriens_engages" > 0 and "nombre_personnes_tous_deces_ou_disparues_sans_clandestins" > 0 and "annee" = 2017 ) group by "cross" 18
19. Jointures : lier des tables entre elles Comment lier des données provenant de plusieurs tables en même temps ? Avec des jointures. Trouvons tous les numéros de SITREP qui rentrent dans le cadre du SNOSAN en 2017. select "op"."cross_sitrep" from "operations" as "op" join "operations_stats" as "stats" on "stats"."operation_id" = "op"."operation_id" where "stats"."concerne_snosan" = true and "stats"."annee" = 2017 19
20. Exercices Trouver le nombre d'opérations par événement, CROSS et année depuis 2010, qui rentrent dans le cadre d'étude du SNOSAN. Trouver le nombre de personnes décédées ou disparues en omettant les clandestins par année et par CROSS, lors de la saison estivale (d'avril à octobre inclus). Trouver le nombre de moyens nautiques et aériens engagés suite à un isolement par la marée par année depuis 2000. 20
21. Corrections SELECT op."evenement", op."cross", stats."annee", count(op."operation_id") as nb_operations FROM "operations" op JOIN "operations_stats" stats ON stats."operation_id" = op."operation_id" WHERE stats."concerne_snosan" = true AND stats."annee" >= 2010 GROUP BY op."evenement", op."cross", stats."annee" SELECT op."cross", stats."annee", sum(stats."nombre_personnes_tous_deces_ou_disparues_sans_clande stins") total_decedes_disparus FROM "operations" op JOIN "operations_stats" stats ON stats."operation_id" = op."operation_id" WHERE stats."mois" BETWEEN 4 AND 10 GROUP BY op."cross", stats."annee" 21
22. Corrections SELECT stats."annee", sum(stats."nombre_moyens_nautiques_engages") nb_moyens_nautiques, sum(stats."nombre_moyens_aeriens_engages") nb_moyens_aeriens FROM "operations" op JOIN "operations_stats" stats ON stats."operation_id" = op."operation_id" WHERE stats."annee" >= 2000 AND op."evenement" = 'Isolé par la marée / Envasé' GROUP BY stats."annee" 22
23. Exercices Trouver les moyens d'alerte utilisés et le nombre d'opérations par année pour les opérations impliquant au moins un kitesurf. Trouvez le nombre d'opérations et le nombre de personnes décédées ou disparues suite à une chute à la mer par phase de la journée entre 2010 et 2017 pour les opérations dans le cadre d'étude du SNOSAN 23
24. Corrections SELECT stats."annee", op."moyen_alerte", count(op."operation_id") as nb_operations FROM "operations" op JOIN "operations_stats" stats ON stats."operation_id" = op."operation_id" WHERE stats."nombre_flotteurs_kitesurf_impliques" >= 1 GROUP BY stats."annee", op."moyen_alerte" SELECT stats."phase_journee", sum(stats."nombre_personnes_tous_deces_ou_disparues_sans_clande stins") as total_decedes_disparus, count(op."operation_id") as nb_operations FROM "operations" op JOIN "operations_stats" stats ON stats."operation_id" = op."operation_id" WHERE stats."concerne_snosan" = true AND op."evenement" = 'Homme à la mer' AND stats."annee" BETWEEN 2010 AND 2017 GROUP BY stats."phase_journee" 24
25. Lorsqu'on effectue une agrégation, on peut utiliser le nom des colonnes ou la position des colonnes dans le GROUP BY. Les requêtes suivantes sont équivalentes. Ceci est très utile quand on applique des fonctions à des colonnes et qu'elles n'ont pas de nom. select marque, nom_serie, count(1) from flotteurs group by marque, nom_serie select marque, nom_serie, count(1) from flotteurs group by 1, 2 Raccourcis dans les requêtes d'agrégation 25
26. Il est possible de trier automatiquement le résultat d'une requête SQL pour simplifier la lecture. select stats.annee, o."cross", count(1) from operations o join operations_stats stats on stats.operation_id = o.operation_id group by 1, 2 order by 1 asc, 3 desc Ordonner le résultat d'une requête 26
27. Les différents types de jointures 27
28. Jointures à gauche Découvrons quelle différence il y a entre une jointure interne et une jointure à gauche. Il n'y a pas de différence entre une jointure à gauche et une jointure à droite. Il suffit d'inverser les ensembles. select count(op.operation_id) from operations op left join flotteurs f on f.operation_id = op.operation_id select count(op.operation_id) from operations op join flotteurs f on f.operation_id = op.operation_id 28
29. Jointures à gauche : impact sur les contraintes Voyons comment applique-t-on des contraintes avec des jointures. On peut se retrouver à écrire malgré soi une jointure interne alors qu'on pense écrire une jointure à gauche. select count(op.operation_id) from operations op left join flotteurs f on f.operation_id = op.operation_id where f.resultat_flotteur = 'Remorqué' select count(op.operation_id) from operations op left join flotteurs f on f.operation_id = op.operation_id and f.resultat_flotteur = 'Remorqué' 29
30. Exercices Trouver le nombre de moyens aériens engagés par la Marine Nationale par année et par CROSS pour les opérations dans le cadre d'étude du SNOSAN depuis 2005. Trouver le nombre d'opérations sans bilan humain par année depuis 2010, sans utiliser la table operations_stats. 30
31. Il existe des fonctions que l'on peut appliquer à des colonnes pour transformer les valeurs de celles-ci. • LOWER / UPPER : transformer en minuscules / majuscules • ROUND : arrondir des chiffres • || : concaténer du texte • at time zone : convertir des dates à un fuseau horaire select o.date_heure_reception_alerte at time zone 'utc', o.date_heure_reception_alerte at time zone o.fuseau_horaire, round(o.latitude), UPPER(o."cross") || ' ' || o.numero_sitrep from operations o Fonctions SQL 31
32. Il est possible d'effectuer une recherche par motif dans les champs textes. Ceci est par exemple utile si l'on s'intéresse aux marques et modèles de navires. On utilise l'opérateur LIKE dans ce cas. Les pourcentages indique que l'on peut avoir n'importe quoi avant ou après le mot first dans la colonne nom_serie. select marque, nom_serie, count(1) from flotteurs where lower(nom_serie) like '%first%' group by marque, nom_serie Recherche par motif 32