Data Science,
Séance 3 : SQL base de données

Etienne Côme

21 novembre 2018

Base de données relationnelles

Développer dans les années 70
Permet de stocker des volume importants
Permet d’éviter la redondance
Inspirer de l’algèbre relationnel
~ Standardiser avec le langage SQL
Difficulté de distribuer sur plusieurs machines
→ Développement des bases noSQL : Mongo, Cassandra, …

Base de données relationnelles

Différentes systèmes de gestion de base de données (SGBD) plus ou moins standardisé :

Libres : mySQL, PostGreSQL, …
Fermées : Oracle, Microsoft SQL Server, …

pour cette séance :

SQLite

Base utilisée pour les exercices, pas de serveur à configurer embarqué dans un fichier sqlite.

Algèbre Relationnelle

Relations (ou table) :

Ensemble de n-uplets

Algèbre

Définition d’opération élémentaires permettant d’extraire les informations recherchées.
Ces opérations prennent une (opérateurs unaires) ou deux relations (opérateurs binaires) et fournissent une relation en résultat.

Algèbre Relationnelle

Opérations unaire de base de l’algèbre relationnel

à partir d’un ensemble A, obtenir un ensemble B

Sélection (ou projection), \(B=\sigma_s(A)\) :
contenant uniquement les n-uplets de A qui correspondent à certains critères
→Suppression de lignes
Restriction, \(B=\pi_r(A)\) :
dont les n-uplets contiennent certaines composantes des n-uplets de A
→Suppression de colonnes

Algèbre Relationnelle

Opérations binaire de base de l’algèbre relationnel

à partir des ensembles A et B, obtenir un ensemble C

Union, \(C=A \cup B\) :
qui contient les n-uplets présents dans A ou dans B
Différence, \(C = A - B\) :
qui contient les n-uplets présents dans A mais pas dans B
Produit cartésien, \(C = A \times B\) :
qui contient des n-uplets qui sont toutes les combinaisons possibles des n-uplets de A avec les n-uplets de B.

Algèbre Relationnelle

Opérations binaire dérivées de l’algèbre relationnel

à partir des ensembles A et B, obtenir un ensemble C

Jointure, \(C=A \bowtie_q B = \sigma_q(A \times B)\) :
qui est le produit cartésien de chaque n-uplet de A pour lesquels il existe des n-uplets de B où la valeur de la composante M de A est identique à la valeur de la composante N de B
Intersection, \(C = A \cup B = A - ( A - B )\) :
qui contient les n-uplets présents à la fois dans A et dans B
Division, \(C = A \div B = \pi_r(A) - \pi_r((\pi_r(A)\times B)-A)\) :
qui contient des n-uplets qui sont toutes les combinaisons possibles des n-uplets de A avec les n-uplets de B.

SQL :

Structured

Query

Langage

SQL

Interrogation des données

SELECT * FROM table WHERE conditions

Définition de la structure des données

CREATE TABLE, ALTER TABLE, DROP TABLE

Manipulation des données (C.R.U.D)

INSERT, UPDATE, DELETE

contraintes (intégrité)
gestion des droits

SQL, Structure d’une requête simple

Sélection des attributs

SELECT attr1 as a, attr2 as b

Liste des relations concernées par la requête

FROM table1 as t1, table2 as t2

Opération de restriction (conditions)

WHERE t1.attr1=t2.attr2 AND t1.attr3> 5

Le résultat est l’ensemble de TOUS les n-uplets (pas de suppression des doublons) respectant la condition et contenant uniquement les attributs indiqués.

SQL, la clause WHERE

Les différents opérateurs

Opérateurs relationnels : <, <=, =, <>, >=, >
Opérateurs arithmétiques : +, -, *, /
Opérateurs logiques : AND, OR, NOT
Autres : LIKE, BETWEEN, IS NULL, IN, EXISTS, …

Like, matching partiel sur les chaînes de caractères :

SELECT NomF, VilF, AdrF FROM Fournisseur 
WHERE VilF LIKE 'Saint%' OR VilF LIKE '_aint%'

Between, condition sur un range :

SELECT * FROM Fournisseur WHERE NomF BETWEEN 'A%' AND 'G%'

SQL, produit cartésien :

From + plusieurs tables :

Possibilité d’utiliser des alias (as)

Exemple :

SELECT f.NomF, f.VilF, l.nomP FROM Fournisseur AS f, Livraison AS l

SQL, jointure :

Produit cartésien + where + foreign.key=primary.key :

SELECT f.NomF, f.VilF, l.nomP 
FROM Fournisseur AS f, Livraison AS l 
WHERE f.NoF = l.NoF

Possibilité de rajouter des conditions

SELECT f.NomF, f.VilF, l.nomP 
FROM Fournisseur AS f, Livraison AS l 
WHERE f.NoF = l.NoF and f.VilF LIKE 'P%'

SQL, l’opérateur GROUP BY :

Possibilités

Comptage : COUNT, COUNT DISTINCT
Arithmètique : AVG, SUM, …
Conditions sur le groupe : HAVING

Exemples :

Comptage des fournisseurs par villes :

SELECT COUNT(DISTINCT NomF), VilF FROM Fournisseur GROUP BY VilF

Comptage des fournisseurs pour les villes ayant + de 5 fournisseurs :

SELECT COUNT(DISTINCT NomF), VilF FROM Fournisseur 
GROUP BY VilF HAVING COUNT(DISTINCT NomF)>5

SQL, l’opérateur ORDER BY

Gestion du tri de la table

DESC : descending, ASC : ascending
LIMIT : pour limiter le nombre de résultats retourner

Exemples :

SELECT NomF, VilF, AdrF 
FROM Fournisseur 
WHERE VilF LIKE 'Saint%' 
ORDER BY NomF DESC LIMIT 10

SQL, chaine de charactères et dates

Pas forcément uniformisé entre les différents système de bdd

Concatenation :

|| : sqlite
CONCAT() : mySQL, PostgreSQL,…

Dates :

STRFTIME(‘%s’,attr), DATE(attr) : sqlite
DATE_FORMAT(attr,‘%H’), DATE_SUB(attr1,attr2) : mySQL
…

Exemples (sqlite):

SELECT MIN(strftime('%s',l.Date)), VilF, AdrF, NomF 
FROM Livraison AS l, fournisseur AS f GROUP BY NomF

SQL, définition d’un schéma

Définition des variables (type)
Contraintes d’unicité
Primary key, Foreign key
Index

Exemples :

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE UNIQUE INDEX artistname ON artist(artistname);
CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

SQL, définition d’un schéma

Schéma ?

Liste des tables, des variables
- types
- Contraintes d’unicité
- Primary key, Foreign key
- Index

Normalisation

Éliminer les redondances
Décomposer les relations
1FN, 3FN,…

Mettre sous forme normalisé une base stockant des informations sur des films (producteur, réalisateur, acteurs, budget, recette)

Schéma et normalisation

Correction

CREATE TABLE realisateur(
  realid    INTEGER PRIMARY KEY,
  name      TEXT,
  adresse   TEXT
};
CREATE TABLE producteur(
  prodid    INTEGER PRIMARY KEY,
  name      TEXT,
  adresse   TEXT
};
CREATE TABLE acteurs(
  acteurid  INTEGER PRIMARY KEY,
  name      TEXT,
  adresse   TEXT
};

Schéma et normalisation

Correction

CREATE TABLE film(
  filmid    INTEGER PRIMARY KEY, 
  budget    INTEGER,
  recette   INTEGER,
  realid    INTEGER,
  FOREIGN KEY(realid) REFERENCES realisateur(realid),
  prodid    INTEGER,
  FOREIGN KEY(prodid) REFERENCES producteur(prodid)
);
CREATE TABLE role(
  filmid    INTEGER NOT NULL,
  FOREIGN KEY(filmid) REFERENCES film(filmid),
  acteurid  INTEGER NOT NULL,
  FOREIGN KEY(acteurid) REFERENCES acteur(acteurid),
  PRIMARY KEY(filmid,acteurid)
);

SQL, opérations C.R.U.D

Create : CREATE
Read : SELECT
Update : UPDATE
Delete : DELETE

Exemples

/* mise à jour */
UPDATE artist SET artistname = 'unknown' WHERE artistname = ''
/* suppression */
DELETE FROM artist WHERE artistname = ''

Package sqldf

Permet de se connecter à une base
\(\neq\) drivers
Retourne une data.frame

Exemples :

library(sqldf)

## Loading required package: gsubfn

## Loading required package: proto

## Loading required package: RSQLite

# importer a partir d'un fichier
routes = read.csv("./data/exo4.gtfs/routes.txt")
sqldf("select route_id, route_desc from routes limit 5")

##   route_id route_desc
## 1        1    Majeure
## 2        2    Majeure
## 3        3    Majeure
## 4        4 Chronostar
## 5        5    Majeure

Importer les données du dossier ./data/exo4.gtfs

La structures des tables correpond à celle du format gtfs défini ici https://developers.google.com/transit/gtfs/

Importation des données ?

importer a partir d’un fichier

routes = read.csv("./data/exo4.gtfs/routes.txt")
agency = read.csv("./data/exo4.gtfs/agency.txt")
calendar = read.csv("./data/exo4.gtfs/calendar.txt")
stop_times = read.csv("./data/exo4.gtfs/stop_times.txt")
stops = read.csv("./data/exo4.gtfs/stops.txt")
trips = read.csv("./data/exo4.gtfs/trips.txt")

Combien de trips, routes et stops ?

Correction

Un simple count(*) :

SELECT count(*) FROM trips

en R, en utilisant sqldf :

sqldf("select count(*) from trips")

##   count(*)
## 1    29130

sqldf("select count(*) from routes")

##   count(*)
## 1      104

sqldf("select count(*) from stops")

##   count(*)
## 1     1442

Afficher les 5 premières lignes de la table stop_time ?

Correction

Un simple select * avec un limit :

SELECT * FROM stop_times LIMIT 5

en R, en utilisant sqldf :

sqldf("select trip_id,arrival_time from stop_times limit 5")

##   trip_id arrival_time
## 1       1     05:23:00
## 2       1     05:25:00
## 3       1     05:26:00
## 4       1     05:26:00
## 5       1     05:27:00

Combien d’arrêts dans chaque trips ?

Correction

Un count avec un group by :

SELECT COUNT(*) AS nbstops, * FROM stop_times GROUP BY trip_id

en R, en utilisant sqldf :

query="select count(*) as nbstops, trip_id from stop_times 
  group by trip_id limit 5"
sqldf(query)

##   nbstops trip_id
## 1      30       1
## 2      30       2
## 3      30       3
## 4      30       4
## 5      30       5

Le trip avec le + de stops ?

Correction

Un count avec un group by :

SELECT COUNT(*) AS nbstops, * 
FROM stop_times 
GROUP BY trip_id 
ORDER BY nbstops 
DESC LIMIT 1

en R, en utilisant sqldf :

query="select count(*) as nbstops, * from stop_times 
group by trip_id order by nbstops desc limit 1"
sqldf(query)

##   nbstops trip_id arrival_time departure_time stop_id stop_sequence
## 1      42   25963     08:20:00       08:20:00    2846            40
##   stop_headsign pickup_type drop_off_type shape_dist_traveled
## 1            NA           0             0                  NA

Les routes avec + de 500 trips ?

Correction

Un count avec un group by + distinct + jointure + having :

SELECT COUNT(DISTINCT t.trip_id), t.route_id, r.route_long_name 
FROM trips AS t, routes AS r 
WHERE t.route_id=r.route_id 
GROUP BY r.route_id 
HAVING COUNT(DISTINCT t.trip_id)>500

en R, en utilisant sqldf :

query="select count(distinct t.trip_id), t.route_id, r.route_long_name 
from trips as t, routes as r 
where t.route_id=r.route_id 
group by r.route_id 
having count(distinct t.trip_id)>500 limit 5"
sqldf(query)

##   count(distinct t.trip_id) route_id
## 1                      1475        1
## 2                      1215        2
## 3                      1308        3
## 4                      1932        4
## 5                      1661        5
##                                            route_long_name
## 1 Cesson-Sévigné (Champs Blancs) <> Chantepie (Rosa Parks)
## 2                Grand Quartier <> La Poterie / Haut Sancé
## 3                          Saint-Laurent <> Henri Fréville
## 4                           Beauregard <> ZA Saint-Sulpice
## 5                                Patton <> Lycée Bréquigny

Le stop avec le + de trips ?

Correction

Un count avec un group by + order by + limit :

SELECT COUNT(*) AS nbtrips, stop_id
FROM stop_times 
GROUP BY stop_id 
ORDER BY nbtrips DESC
LIMIT 1

en R, en utilisant sqldf :

query="select count(*) as nbtrips, stop_id
from stop_times 
group by stop_id 
order by nbtrips desc
limit 1"
sqldf(query)

##   nbtrips stop_id
## 1    3236    1027

Le nombre de routes par stop ?

Correction

Un count distinct avec un group by + jointures :

SELECT COUNT(DISTINCT  ro.route_id) AS nbroutes, 
st.stop_id, tr.trip_id 
FROM stop_times AS st,trips AS tr 
WHERE st.trip_id=tr.trip_id 
GROUP BY stop_id

en R, en utilisant sqldf :

query="select count(distinct  tr.route_id) as nbroutes, 
st.stop_id, tr.trip_id 
from stop_times as st,trips as tr 
where st.trip_id=tr.trip_id 
group by stop_id limit 5"
sqldf(query)

##   nbroutes stop_id trip_id
## 1        2    1001    5576
## 2        2    1002    5576
## 3        2    1003    5576
## 4        3    1004     999
## 5        3    1005     999

Les stops avec le + de routes ?
(avec leur noms)

Correction

Un count distinct avec un group by + jointures :

SELECT COUNT(DISTINCT  tr.route_id) AS nbroutes, 
st.stop_id, stn.stop_name 
FROM stops AS stn, stop_times AS st,trips AS tr 
WHERE st.stop_id=stn.stop_id AND st.trip_id=tr.trip_id 
GROUP BY st.stop_id 
ORDER BY nbroutes DESC LIMIT 5"

en R, en utilisant sqldf :

query="select count(distinct  tr.route_id) as nbroutes, 
st.stop_id, stn.stop_name 
from stops as stn, stop_times as st,trips as tr 
where st.stop_id=stn.stop_id and st.trip_id=tr.trip_id 
group by st.stop_id 
order by nbroutes desc limit 5"
sqldf(query)

##   nbroutes stop_id    stop_name
## 1       12    1610  Tournebride
## 2       10    1160  Tournebride
## 3        8    1007   Assomption
## 4        8    1011 Fac de Droit
## 5        8    1033   Assomption

L’heure du premier départ à chaque arrêt ?

Correction

Une jointure + group by + min :

SELECT st.stop_id, s.stop_name, MIN(st.departure_time) 
FROM stop_times AS st, stops AS s 
WHERE st.stop_id=s.stop_id 
GROUP BY st.stop_id

en R, en utilisant sqldf :

query="select st.stop_id, s.stop_name, min(st.departure_time) 
from stop_times as st, stops as s 
where st.stop_id=s.stop_id 
group by st.stop_id limit 5"
sqldf(query)

##   stop_id    stop_name min(st.departure_time)
## 1    1001 Longs Champs               04:25:00
## 2    1002       Bouzat               04:26:00
## 3    1003      Gallets               04:26:00
## 4    1004     Donzelot               04:26:00
## 5    1005     Mirabeau               04:27:00

Les arrêts avec des bus la nuit entre 2h et 5h du matin ?

Correction

Un jointure + conditions ou between + distinct :

SELECT DISTINCT st.stop_id, s.stop_name, st.departure_time 
FROM stop_times AS st, stops AS s 
WHERE st.departure_time BETWEEN '02:00:00' AND '05:00:00' 
AND s.stop_id=st.stop_id

en R, en utilisant sqldf :

query="select distinct st.stop_id, s.stop_name, st.departure_time 
from stop_times as st, stops as s 
where st.departure_time between '02:00:00' and '05:00:00' 
and s.stop_id=st.stop_id limit 5"
sqldf(query)

##   stop_id         stop_name departure_time
## 1    1051 Place de Bretagne       03:56:00
## 2    1052        République       03:55:00
## 3    1192          La Salle       04:03:00
## 4    1216           Le Mail       03:58:00
## 5    1217   Chèques Postaux       03:59:00

Lister les arrêts non accessibles aux handicapés ?

Correction

Un requête simple sur la table stops :

SELECT * FROM stops 
WHERE wheelchair_boarding=2

en R, en utilisant sqldf :

query="select stop_id, stop_name from stops 
where wheelchair_boarding=2 limit 5"
sqldf(query)

##   stop_id      stop_name
## 1    1015  Champ Jacquet
## 2    1017     Les Halles
## 3    1040      Donelière
## 4    1041    Trois Croix
## 5    1042 Cimetière Nord

Calculer la durée des trips ?

La durée des trips ?

Correction

strftime + min, max et group by : :

SELECT trip_id, MIN(arrival_time), MAX(arrival_time), 
MAX(STRFTIME('%s','2014-01-01 ' || arrival_time)) 
- MIN(STRFTIME('%s','2014-01-01 ' || arrival_time)) as time 
FROM stop_times GROUP BY trip_id

en R, en utilisant sqldf :

query="select trip_id, min(arrival_time), max(arrival_time), 
max(strftime('%s',arrival_time))
- min(strftime('%s', arrival_time)) as time 
from stop_times group by trip_id 
order by time desc limit 5"
sqldf(query)

##   trip_id min(arrival_time) max(arrival_time) time
## 1    8265          17:09:00          18:18:00 4140
## 2    8266          17:09:00          18:18:00 4140
## 3    8267          17:09:00          18:18:00 4140
## 4    8268          17:09:00          18:18:00 4140
## 5    8269          17:09:00          18:18:00 4140

La fréquence moyenne
à la station ‘Gayeulles Piscine’
le 23 Septembre 2014

Vous pourrez utiliser un second traitement effectué en R

La fréquence moyenne à la station ‘Gayeulles Piscine’ le 23 Septembre 2014

Correction

Recherche de tous les passages à la station le 23 Septembre, jointures multiples et conditions sur la table calendar :

SELECT departure_time, 
strftime('%s','2014-09-23' || arrival_time) AS depsec 
FROM stop_times AS st, stops AS s, trips AS tr, calendar AS cal 
WHERE s.stop_id=st.stop_id AND s.stop_name = 'Gayeulles Piscine' 
AND tr.trip_id=st.trip_id AND tr.service_id=cal.service_id 
AND cal.tuesday=1 
AND cal.start_date<'20140923' AND cal.end_date>'20140923' 
ORDER BY st.departure_time

La fréquence moyenne à la station ‘Gayeulles Piscine’ le 23 Septembre 2014

Correction

en R, en utilisant sqldf + moyenne des différences :

query="select departure_time, 
strftime('%s','2014-09-23' || arrival_time) as depsec 
from stop_times as st, stops as s, trips as tr, calendar as cal 
where s.stop_id=st.stop_id and s.stop_name = 'Gayeulles Piscine' 
and tr.trip_id=st.trip_id and tr.service_id=cal.service_id 
and cal.tuesday=1 and cal.start_date<'20140923' 
and cal.end_date>'20140923' order by st.departure_time"
# stockage de la requête dans la data.frame horaires
horaires=sqldf(query)
# calcul de la fréquence moyenne
mean(diff(as.numeric(sort(horaires$depsec))))

## [1] 392.6966

Data Science, Séance 3 : SQL base de données

Etienne Côme

21 novembre 2018

Base de données relationnelles

Base de données relationnelles

SQLite

Algèbre Relationnelle

Relations (ou table) :

Algèbre

Algèbre Relationnelle

Opérations unaire de base de l’algèbre relationnel

Algèbre Relationnelle

Opérations binaire de base de l’algèbre relationnel

Algèbre Relationnelle

Opérations binaire dérivées de l’algèbre relationnel

SQL :

Structured

Query

Langage

SQL

Interrogation des données

Définition de la structure des données

Manipulation des données (C.R.U.D)

SQL, Structure d’une requête simple

Sélection des attributs

Liste des relations concernées par la requête

Opération de restriction (conditions)

SQL, la clause WHERE

Les différents opérateurs

Like, matching partiel sur les chaînes de caractères :

Between, condition sur un range :

SQL, produit cartésien :

From + plusieurs tables :

Exemple :

SQL, jointure :

Produit cartésien + where + foreign.key=primary.key :

Possibilité de rajouter des conditions

SQL, l’opérateur GROUP BY :

Possibilités

Exemples :

SQL, l’opérateur ORDER BY

Gestion du tri de la table

Exemples :

SQL, chaine de charactères et dates

Concatenation :

Dates :

Exemples (sqlite):

SQL, définition d’un schéma

Exemples :

SQL, définition d’un schéma

Schéma ?

Normalisation

Mettre sous forme normalisé une base stockant des informations sur des films (producteur, réalisateur, acteurs, budget, recette)

Schéma et normalisation

Correction

Schéma et normalisation

Correction

SQL, opérations C.R.U.D

Exemples

Package sqldf

Exemples :

Importer les données du dossier ./data/exo4.gtfs

Importation des données ?

importer a partir d’un fichier

Combien de trips, routes et stops ?

Combien de trips, routes et stops ?

Correction

Afficher les 5 premières lignes de la table stop_time ?

Afficher les 5 premières lignes de la table stop_time ?

Correction

Combien d’arrêts dans chaque trips ?

Combien d’arrêts dans chaque trips ?

Correction

Le trip avec le + de stops ?

Le trip avec le + de stops ?

Correction

Les routes avec + de 500 trips ?

Les routes avec + de 500 trips ?

Correction

Le stop avec le + de trips ?

Data Science,
Séance 3 : SQL base de données

Les stops avec le + de routes ?
(avec leur noms)

Les stops avec le + de routes ?
(avec leur noms)

La fréquence moyenne
à la station ‘Gayeulles Piscine’
le 23 Septembre 2014