Mémo SQL pour la Data Science

Analyser des données c’est grosso merdo :  

  • importer et nettoyer
  • explorer
  • analyser
  • estimer et tester les hypothèses
  • afficher les résultats

Cet article porte sur la première partie, à savoir l’importation de données en répondant à la question suivante : comment récupérer les données ?

Il existe pour ce faire plusieurs méthodes :

  • via des fichiers
  • via une API
  • via une base de donnée
  • via du scraping

En l’occurrence, je m’intéresse aux bases de données, et particulièrement au langage SQL pour communiquer avec ces dernières, m’intéressent.

Brève introduction aux bases de données

Il existe 2 types de DB : relationnelles (SQL) et non relationnelles (NoSQL).

On va se concentrer ici sur les DB relationnelles, c’est-à-dire les tables où les données sont liées.

Grande différence entre les bases de données SQL et NoSQL:

  • SQL organise le stockage de données sur le principe de tables reliées entre elles. La structure et les types des données sont rigides, c’est-à-dire fixés à l’avance.
  • NoSQL stocke et manipule des documents.

Par exemple en SQL on va créer la base pour qu’elle puisse recevoir des documents contenant un titre et un age :

{
   "title": "test",
   "age": 7
}

Alors qu’une base en NoSQL stocke toutes les données en l’état ! La base de données NoSQL ne se plaint pas si ce que vous stockez ne respecte pas un format. 

Exemple on  peut très bien stocker :

{
    "title":"title",
    "description": "description"
}

Ou

{
    "first_name":"toto",
    "last_name": "wesh"
}

Les tables SQL imposent un modèle de données strictes, donc il est difficile de faire des erreurs. NoSQL est plus flexible et pardonnable, mais la possibilité de stocker des données n’importe où peut entraîner des problèmes de cohérence.

Une base de données NoSQL peut être plus adaptée aux projets où les exigences initiales en matière de données sont difficiles à déterminer. 

Cela dit, il faut bien comprendre que 

  • noSQL ne remplacera pas SQL

SQL et NoSQL font la même chose : stocker des données, mais ils le font chacun à leur manière avec des approches bien différentes.

  • NoSQL n’est pas meilleur que SQL

Certains projets sont mieux adaptés à l’utilisation d’une base de données SQL. Certains sont mieux adaptés à NoSQL.

Les bases SQL sont plus utilisées mais ça ne veut pas dire qu’il faut négliger les NoSQL parce que les deux ne répondent pas au même besoin. 

Par exemple, dans une entreprise qui récupère des informations de capteurs comme des montres connectées : on a des données de calculs et des données méta. Des données de calculs sont en général pas compréhensible à la lecture, il s’agit de chiffres d’une séance de travail par exemple. Alors que les données méta vont décrire la séance de travail, il s’agit par exemple de la date, du type de séance (footing, longue distance etc), lieu, heure, etc.

Après avoir vu la différence entre les bases de données SQL et NoSQL, voyons le langage qui va nous permettre de communiquer avec cette base et ainsi récupérer des données.

SQL

Le langage SQL peut être utilisé pour requêter les bases de données. Par requêter j’entends stocker, insérer, mettre à jour,supprimer et lire des quantités de données afin de mener à bien les missions liées à l’analyse de données.

Comme on l’a vu, dans les bases de données relationnelles, c’est-à-dire où les données sont stockées dans des tables, des relations existent entre les données. Ce qui sous-entends qu’il sera intéressant d’aller chercher des données en fonction d’autres données. Par exemple, récupérer tous les utilisateurs d’un pays.

On va voir ça en détail et pas à pas.

Comment créer/supprimer/modifier une table ?

Commençons par voir comment créer une table.

Un base de données stocker des données qui sont elles-mêmes rangées dans des tables. La création de tables d’une base de données ne fait pas parti de la mission primaire dans l’analyse de données. Mais c’est important de comprendre les mécanismes.

Une table dans une base de données c’est comme une armoire.

Le langage SQL c’est utiliser des mots clés dans un ordre précis et en complétant avec nos données persos.

Pour créer une table, on a deux mots clés réservés dans le langage SQL : CREATE  TABLE 

Par exemple, si je veux créer une table, la requête est la suivante :

CREATE TABLE table_name (
    column_name type
);

(Les majuscules ne sont pas obligatoires mais conseillées. Elles permettent de différencier.les mots clés réservés au langage SQL des autres mots)

On peut vérifier le résultat avec la requête :

DESCRIBE table_name;

Si je souhaite modifier cette table en ajoutant une colonne par exemple, je vais utiliser le mot clé ALTER :

ALTER TABLE student ADD first_name DECIMAL(1);

Et pour visualiser le résultat :

DESCRIBE student;

Idem si je souhaite supprimer une colonne dans la table :

ALTER TABLE student DROP COLUMN first_name;

Et toujours pour voir le résultat :

DESCRIBE student;

Maintenant si je veux supprimer la table entièrement je vais utiliser la requête DROP :

DROP TABLE student;

Clé primaire

On l’utilise pour identifier des ressources. Par exemple dans une table où on a 2 noms identiques. L’usage courant de PRIMARY KEY peut être effectué lors de la création d’une table à l’aide de la syntaxe suivante :

CREATE TABLE 'nom_de_la_table' (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
);

Dans cet exemple, la clé primaire sera une colonne numérique de type INT, intitulée “id” (diminutif de “identifiant”) et cette colonne s’incrémentera automatiquement à chaque enregistrement.

Clé étrangère

Contrairement à la clé primaire qui peut être définie soit directement avec le champ, soit à la fin (après tous les champs), la clé étrangère est forcément définie à la fin :

CREATE TABLE nom_table (
    champ1 type,
    FOREIGN KEY (champ1) REFERENCES nom_tableX (champX)
);

La clé étrangère représente un champ qui pointe vers la clé primaire d’une autre table.

L’objectif de la clé étrangère est d’assurer que seules les valeurs devant apparaître dans la base de données sont permises. Par exemple, nous avons deux tables, l’une appelée CUSTOMER qui inclut toutes les données du client, et l’autre ORDERS qui comprend ses commandes. La contrainte ici est que toutes les commandes doivent être associées à un client qui se trouve déjà référencé dans la table CUSTOMER. Dans ce cas, une clé étrangère devra être placée sur la table ORDERS et mise en relation avec la clé primaire de la table CUSTOMER. De cette façon, il sera possible d’assurer que toutes les commandes de la table ORDERS sont mises en relation avec un client de la table CUSTOMER.

En d’autres mots, la table ORDERS ne peut contenir d’informations sur un client qui ne se trouve pas dans la table CUSTOMER.

Types de data

Il existe plusieurs types pour les données.

Les plus communs :

  • INT (pour tous les nombres). Il existe aussi FLOAT, DOUBLE, SMALLINT etc
  • DECIMAL (pour les nombres décimaux quand on sait exactement combien de chiffres après la virgule on souhaite)
  • VARCHAR(N) pour les caractères par exemple où N est variable
  • BLOB pour les larges fichiers
  • DATE au format ‘YYYY-MM-DD’
  • TIMESTAMP

Le timestamp est le nombre de secondes écoulées entre une certaine date et le 1er janvier 1970 à 00:00:00. Parce qu’une date de ce style : 15/11/2012 18:14:47 est très difficile à manipuler d’un point de vue informatique. 

Utiliser un simple entier signé (le timestamp) pour représenter une date est beaucoup plus pratique, car les langages de programmation permettent tous de faire des opérations arithmétiques sur des entiers.

Les contraintes

NOT NULL dans la création de la table sur un champ permet de spécifier que ce champ ne peut pas être nul.

CREATE TABLE student (
    student_ud INT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(student_id)
);

La contrainte UNIQUE

Les valeurs de la ou les colonnes doivent être UNIQUE ou NULL. C’est à dire, qu’à l’exception du marqueur NULL, il ne doit jamais y avoir plus d’une fois la même valeur (pas de doublon) au sein de l’ensemble de données formé par les valeurs des différentes colonnes composant la contrainte :

CREATE TABLE student (
    student_ud INT,
    name VARCHAR(20) UNIQUE,
    PRIMARY KEY(student_id)
);

La contrainte DEFAULT

Elle permet de préciser une valeur qui sera automatiquement insérée en l’absence de précision d’une valeur explicite dans un ordre d’insertion :

CREATE TABLE student (
    student_ud INT,
    name VARCHAR(20) DEFAULT VALUE '',
    PRIMARY KEY(student_id)
):

La commande AUTO_INCREMENT

Elle est utilisée dans le langage SQL afin de spécifier qu’une colonne numérique avec une clé primaire (PRIMARY KEY) sera incrémentée automatiquement à chaque nouvelle ligne dans la table :

CREATE TABLE student (
    student_ud INT AUTO_INCREMENT,
    name VARCHAR(20),
    PRIMARY KEY(student_id)
);

Par défaut, l’auto-incrément débute à la valeur « 1 » et s’incrémentera de un pour chaque nouvel enregistrement. Il est possible de modifier la valeur initiale avec la requête SQL suivante :

ALTER TABLE `nom_de__la_table` AUTO_INCREMENT=50;

Dans l’exemple ci-dessus, la valeur initiale pour cette incrémentation sera 50

Comment insérer des données dans une table ?

Pour insérer de nouvelles data dans la table la requête est la suivante :

INSERT INTO name VALUES (value1,  value2 ...); 

Dans les parenthèses je mets les valeurs que je veux insérer. Mais attention à respecter la structure de la table. Je ne peux pas ranger une voiture dans une armoire.

C’est pourquoi il vaut mieux préciser comme suit :

INSERT INTO names(student_id, names) VALUES (3, ‘name’);

On va vérifier le résultat avec SELECT.

Le SELECT

En Data Science c’est cette commande qui est utilisée et à retenir !

L’instruction SELECT permet de sélectionner des données en bases :

SELECT * FROM table_name; --tout le contenu d’une table
SELECT * FROM table ORDER BY name DESC; -- tout le contenu d’une table triée par nom descroissant
SELECT * FROM table LIMIT 5; -- les 5 premières lignes
SELECT * FROM table ORDER BY name DESC LIMIT 5; -- triés par nom
SELECT * FROM table WHERE col_name = something; -- avec une condition

Il est aussi possible d’utiliser un autre opérateur et de compléter avec AND et/ou OR.

La magie du select est de pouvoir déduire des champs à partir ‘d’autres champs. Par exemple la moyenne :

SELECT avg('') AS

DICTINCT, IN, NOT IN

Dans une table il y a souvent des valeurs dupliquées, SELECT DISTINCT permet de de sélectionner uniquement les différentes valeurs :

SELECT DISTINCT column1, column2
FROM table_name;

Quant au IN, il permet d’introduire la notion de liste et ainsi effectuer une vérification sur la précise d’un élément dans cette dernière :

SELECT * FROM eployee WHERE DEPARTMENT IN (...)
SELECT * FROM eployee WHERE DEPARTMENT NOT IN (...)

Les opérateurs de comparaison sont disponibles pour tous les types de données pour lesquels cela a du sens. 

Les opérateurs de comparaison sont des opérateurs binaires renvoyant des boolean.

En plus des opérateurs de comparaison, on dispose du BETWEEN.

var_name BETWEEN var_min AND var_max

est équivalent à

var_name >= var_min AND var_name <= var_max

De même

var_name NOT BETWEEN var_min AND var_max

est équivalent à

var_name < var_min OR var_name > var_max
= equals
<> not equals
> supérieur
< inférieur
= supérieur ou égal
<= inférieur ou égal

Comment mettre à jouer/supprimer des données dans la table ?

La commande UPDATE permet d’effectuer des modifications sur des lignes existantes. Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les modifications :

UPDATE table
SET nom_colonne_1 = 'nouvelle valeur'
WHERE condition

Cette syntaxe permet d’attribuer une nouvelle valeur à la colonne nom_colonne_1 pour les lignes qui respectent la condition stipulé avec WHERE. Il est aussi possible d’attribuer la même valeur à la colonne nom_colonne_1 pour toutes les lignes d’une table si la condition WHERE n’était pas utilisée.

A noter, pour spécifier en une seule fois plusieurs modification, il faut séparer les attributions de valeur par des virgules. Ainsi la syntaxe deviendrait la suivante :

UPDATE table
SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', colonne_3 = 'valeur 3'
WHERE condition

De cette manière on peut mettre à jour plusieurs lignes d’un coup !

Quelle table est à mettre à jour ? Quelles sont les lignes à mettre à jour ? Quels sont les colonnes à mettre à jour ? quelles sont les nouvelles valeurs ? voilà les questions auxquelles il faudra répondre pour mettre à jour une donnée.

Il existe deux manières de supprimer des données : 

  1. supprimer toutes les lignes de la table 
DELETE FROM table;
  1. supprimer uniquement quelques lignes en fonction de la condition dans la clause WHERE 
DELETE FROM table WHERE condition;

Functions 

Les fonctions SQL permettent d’effectuer des requêtes plus élaborées, par exemple adaptant les résultats pour qu’une chaîne soit affichée en majuscule ou bien pour enregistrer une chaîne avec la date actuelle.

Exemples de fonctions utiles

  • SUM() calculer la somme d’un set de résultat
  • MAX() obtenir le résultat maximum (fonctionne bien pour un entier)
  • MIN() obtenir le résultat minimum
  • COUNT() compter le nombre de lignes dans un résultat
  • ROUND() arrondir la valeur
  • UPPER() afficher une chaîne en majuscule
  • LOWER() afficher une chaîne en minuscule
  • NOW() date et heure actuelle
  • RAND() retourner un nombre aléatoire
  • CONCAT() concaténer des chaînes de caractères
  • CURRENT_DATE() date actuelle

Les fonctions d’agrégation dans le langage SQL permettent d’effectuer des opérations statistiques sur un ensemble d’enregistrement. Étant données que ces fonctions s’appliquent à plusieurs lignes en même temps, elle permettent des opérations qui servent à récupérer l’enregistrement le plus petit, le plus grand ou bien encore de déterminer la valeur moyenne sur plusieurs enregistrement.

Liste des fonctions d’agrégation statistiques

Les fonctions d’agrégation sont des fonctions idéales pour effectuer quelques statistiques de bases sur des tables. Les principales fonctions sont les suivantes :

  • AVG() pour calculer la moyenne sur un ensemble d’enregistrement
  • COUNT() pour compter le nombre d’enregistrement sur une table ou une colonne distincte
  • MAX() pour récupérer la valeur maximum d’une colonne sur un ensemble de ligne. Cela s’applique à la fois pour des données numériques ou alphanumérique
  • MIN() pour récupérer la valeur minimum de la même manière que MAX()
  • SUM() pour calculer la somme sur un ensemble d’enregistrement

Utilisation simple

L’utilisation la plus générale consiste à utiliser la syntaxe suivante :

SELECT fonction(colonne) FROM table

La fonction COUNT() possède une subtilité. Pour compter le nombre total de ligne d’une table, il convient d’utiliser l’étoile « * » qui signifie que l’ont cherche à compter le nombre d’enregistrement sur toutes les colonnes. La syntaxe serait alors la suivante :

SELECT COUNT(*) FROM table

Les fonctions SQL sur les chaînes de caractères

Elles permettent d’ajouter de nombreuses fonctionnalités aux requêtes SQL. La fonction LENGTH() permet de calculer la longueur d’une chaîne de caractères :

SELECT LENGTH('exemple');

La fonction LOWER() permet de transformer tous les caractères d’une chaîne de caractère en minuscules.

SELECT LOWER('BONJOUR tout le monde');

Dans le langage SQL, la fonction TRIM() permet de supprimer des caractères au début et à la fin d’une chaîne de caractères. L’utilisation la plus commune de LTRIM() consiste à supprimer les caractères invisibles tel que l’espace, la tabulation ou le retour à la ligne.

Une telle fonction peut se révéler utile pour économiser de l’espace dans une base de données ou pour afficher proprement des données.

SELECT TRIM('   Exemple   ');

La fonction REPLACE dans le langage SQL permet de remplacer des caractères alphanumérique dans une chaîne de caractère.

La fonction comporte 3 paramètres :

  1. Chaîne d’entré
  2. Texte à remplacer
  3. Texte qui sert de remplacement
SELECT REPLACE('Hello tout le monde', 'Hello', 'Bonjour');

Il existe une multitude de fonction qui concerne les éléments temporels pour pouvoir lire ou écrire plus facilement des données à une date précise ou à un intervalle de date.

La fonction NOW() permet de retourner la date et l’heure du système. Cette fonction est très pratique pour enregistrer la date et l’heure d’ajout ou de modification d’une donnée, dans un DATETIME ou un DATE(). Cela permet de savoir exactement le moment où a été ajouté une donnée contenu dans une table :

SELECT NOW();

La fonction DATEDIFF() permet de déterminer l’intervalle entre 2 dates spécifiées :

SELECT DATEDIFF( date1, date2 );

Les dates doivent être au format DATE (cf. AAAA-MM-JJ) ou DATETIME (cf. AAAA-MM-JJ HH:MM:SS). Pour que le résultat soit positif il faut que la date1 soit plus récente que la date2.

Il existe une multitude de fonctions pour retourner les résultats de la façon souhaités. Il existe également de nombreuses fonctions mathématiques pour effectuer des calculs ou des statistiques concernant les données contenus dans une base de données.

TRUNC() tronquer à un nombre à virgule en un nombre entier ou en un nombre avec le nombre de décimal souhaité.

La fonction RAND() permet de sélectionner un nombre aléatoire à virgule, compris entre 0 et 1. Le résultat de cette fonction sera différent à chaque fois que la fonction est exécutée dans une requête SQL.

Cette fonction est pratique avec un ORDER BY pour classer des résultats aléatoirement. Toutefois, même si c’est possible il faut essayer d’éviter cette méthode qui n’est pas particulièrement efficace en terme de performances.

La fonction peut aussi être utilisée dans une clause WHERE. Cela peut être pratique pour sélectionner un résultat aléatoirement.

La syntaxe pour utiliser la fonction RAND() est la suivante :

SELECT RAND();

FLOOR() obtenir la valeur entière inférieure d’un nombre.

GROUP BY

L’instruction GROUP BY instruction permet de grouper des lignes de valeurs identiques et opérer des agrégats sur d’autres colonnes avec des fonctions comme MIN, MAX, COUNT et SUM.

Par exemple, si on cherche le nombre d’utilisateurs et le plus jeune pour chaque longueur du nom possible : 

SELECT LENGTG(name) as name_length, MIN(age) as min_user, COUNT(*) as num_users 
FROM users 
GROUP BY LENGTH(name);

Chaque champ mentionné dans le SELECT doit être soit dans la clause GROUP BY soit agrégé (comme min_user et num_users).

SQL accepte également une clause HAVING sui se comporte comme une clause WHERE sauf que le filtre est appliqué aux agrégats (alors qu’un filtre WHERE filtre les lignes avant agrégation).

Cherchons le nombre moyen d’amis des individus dont le nom commence par une lettre donnée tout en demandant les résultats des lettres dont la moyenne est supérieure à 1.

SELECT SUBSTR(name, 1, 1) as first_letter, AVG(num_friends) AS avg_num_friends
FROM users
GROUP BY SUBSTR(name, 1, 1)
HAVING AVG(num_friends) > 1;


ORDER BY

Le plus souvent, vous souhaitez trier les résultats de vos calculs. Par exemple, on veut connaître les deux premiers noms de nos utilisateurs : 

SELECt * FROM users
ORDER BY name ASC
LIMIT 2;

L’instruction ORDER BY permet de spécifier un ordre ASC ou DESC pour chaque champ. 

Union 

La commande UNION de SQL permet de mettre bout-à-bout les résultats de plusieurs requêtes utilisant elles-même la commande SELECT. C’est donc une commande qui permet de concaténer les résultats de 2 requêtes ou plus. Pour l’utiliser il est nécessaire que chacune des requêtes à concaténer retournes le même nombre de colonnes, avec les mêmes types de données et dans le même ordre.

SELECT * FROM table1
UNION
SELECT * FROM table2

Join

Par définition une base de données est normalisées, c’est à dire pensée pour éviter la redondance.

Par exemple, quand nous travaillons sur les centres d’intérêts des users, nous pouvons donner une liste de sujets à chaque users. Les tables ne peuvent pas contenir de listes.

La solution consiste à créer une 2eme table user_interests décrivant les relations 1-n entre les user_id et les centres d’intérêt et donc faire une 3eme table avec les centres d’intérêts.

Pour analyser les données lorsqu’elles sont éparpillées sur plusieurs tables on utilise les jointures. JOIN combine les lignes de la table de gauche avec les lignes correspondantes de la table de droite. La manière de faire correspondre les données est spécifiée dans la jointure.Par exemple pour trouver les intérêts des utilisateurs : 

SELECT users.name
FROM users
JOIN user_interests
ON users.user_id = user_interest.user_id
JOIN interests
ON interests.interests_id = user_interests.interests_id
WHERE user_interest.interest = 'SQL'

Dans cet exemple JOIN dit que, pour chaque ligne de la table users, nous devons regarder le user_id et associer cette ligne avec chaque ligne de user_interest contenant le même user_id.

Notez que nous devons spécifier les tables à joindre et aussi les colonnes concernées.

Il s’agit d’une instruction INNER JOIN, qui retourne les combinaisons de lignes (et seulement les combinaisons de lignes) qui correspondent suivant les critères de jointure spécifiés.

Il existe par ailleurs une instruction LEFT JOIN qui, en plus des combinaisons de lignes correspondantes, retourne une ligne pour chaque ligne de la table de gauche sans correspondance des lignes. 

Avec un LEFT JOIN, on peut aisément compter le nombre de centres d’intérêt de chaque user : 

SELECT user_id, COUNT(user_interests.interests) AS num_interests
FROM users
LEFT JOIN user_interests
ON users.user_id = user_interests.user_id

Le LEFT JOIN garantit que tous les users sans centre d’intérêt auront encore des lignes dans l’ensemble de donneés jointes (avec des valeurs NULL pour les champs provenant de user_interests). Et COUNT ne compte que les valeurs non nulles. 

SQL propose aussi un RIGHT JOIN, qui conserve les lignes de la table de droite sans correspondance, et un FULL OUTER JOIN, qui conserve les lignes de deux tables sans correspondance. Mais pour l’analyse de données, ce n’est pas utile.

Les sous-requêtes

Si vous cherchez le plus petit user_id de tous ceux qui s’intéressent au sujet SQL, vous pouvez faire une sous-requête :

SELECT Min(user_id) as min_user_id 
FROM (
 SELECT user_id 
 FROM user_interests 
 WHERE interest = 'SQL'
) sql_interests;