1A.soft - Notions de SQL#
Links: notebook, html, python, slides, GitHub
Premiers pas avec le langage SQL.
from jyquickhelper import add_notebook_menu
add_notebook_menu()
Le langage SQL est utilisé pour manipuler des bases de données. Pour faire simple, on utilise les bases de données pour accéder rapidement à une information dans des données qui font parfois plusieurs milliards de lignes.
Le tableau dont on se sert est trop grand (comme trier 50000 lignes).
On souhaite faire des opérations sur deux feuilles Excel (associer les lignes de l’une avec celles de l’autre).
Lorsque le volume de données est important, il est impossible de les voir dans leur ensemble. On peut en voir soit une partie soit une aggrégation. Par exemple, la société qui gère les vélib a ouvert l’accès à ses données. Il est possible de télécharger aussi souvent qu’on veut (toutes les minutes par exemple) un état complet des vélos et places disponibles pour toutes les stations de Paris : c’est une table qui s’enrichit de 1300 lignes toutes les minutes.
Récupérer les donnée#
from pyensae.datasource import download_data
download_data("td8_velib.zip", website = 'xd')
['stations.txt', 'td8_velib.txt']
On crée une base de données sqlite3. On peut la consulter avec un outil tel que SqliteSpy sous Windows, sqlite_bro sur tous les OS.
from pyensae.sql import import_flatfile_into_database
dbf = "td8_velib.db3"
import_flatfile_into_database(dbf, "td8_velib.txt") # 2 secondes
import_flatfile_into_database(dbf, "stations.txt", table="stations") # 2 minutes
remove td8_velib
SQL 'DROP TABLE td8_velib'
TextFile: opening file td8_velib.txt
TextFile.guess_columns: processing file td8_velib.txt
TextFile: opening file td8_velib.txt
TextFile.guess_columns: using 101 lines
TextFile: closing file td8_velib.txt
TextFile.guess_columns: sep 't' nb cols 7 bestnb 101 more {('t', 6): 101, (' ', 2): 100}
TextFile.guess_columns: header True columns {0: ('collect_date', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
compiling ^(?P<collect_date>.*)t(?P<last_update>.*)t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))t(?P<number>([-]?[1-9][0-9]*?)|(0?))t(?P<heure>([-]?[1-9][0-9]*?)|(0?))t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: regex ^(?P<collect_date>.*)t(?P<last_update>.*)t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))t(?P<number>([-]?[1-9][0-9]*?)|(0?))t(?P<heure>([-]?[1-9][0-9]*?)|(0?))t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: header True columns {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
[_guess_columns] sep=['t']
TextFile: closing file td8_velib.txt
[_guess_columns] columns_name=None
guess with 1001 lines
count_types {0: {<class 'str'>: 1000}, 1: {<class 'str'>: 1000}, 2: {<class 'int'>: 1000}, 3: {<class 'int'>: 1000}, 4: {<class 'int'>: 1000}, 5: {<class 'int'>: 1000}, 6: {<class 'int'>: 1000}}
columns {0: ('collect_date', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
guess {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
SQL 'CREATE TABLE td8_velib(collect_date TEXT,'
' last_update TEXT,'
' available_bike_stands INTEGER,'
' available_bikes INTEGER,'
' number INTEGER,'
' heure INTEGER,'
' minute INTEGER);'
column_has_space False ['collect_date', 'last_update', 'available_bike_stands', 'available_bikes', 'number', 'heure', 'minute']
changes {}
TextFileColumns (2): regex: {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
TextFile.guess_columns: processing file td8_velib.txt
TextFile: opening file td8_velib.txt
TextFile.guess_columns: using 101 lines
TextFile: closing file td8_velib.txt
TextFile.guess_columns: sep 't' nb cols 7 bestnb 101 more {('t', 6): 101, (' ', 2): 100}
TextFile.guess_columns: header True columns {0: ('collect_date', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
compiling ^(?P<collect_date>.*)t(?P<last_update>.*)t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))t(?P<number>([-]?[1-9][0-9]*?)|(0?))t(?P<heure>([-]?[1-9][0-9]*?)|(0?))t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: regex ^(?P<collect_date>.*)t(?P<last_update>.*)t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))t(?P<number>([-]?[1-9][0-9]*?)|(0?))t(?P<heure>([-]?[1-9][0-9]*?)|(0?))t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: header True columns {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}
TextFile: opening file td8_velib.txt
adding 100000 lines into table td8_velib
adding 200000 lines into table td8_velib
adding 300000 lines into table td8_velib
adding 400000 lines into table td8_velib
adding 500000 lines into table td8_velib
adding 600000 lines into table td8_velib
adding 700000 lines into table td8_velib
adding 800000 lines into table td8_velib
adding 900000 lines into table td8_velib
adding 1000000 lines into table td8_velib
adding 1100000 lines into table td8_velib
^(?P<collect_date>.*)t(?P<last_update>.*)t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))t(?P<number>([-]?[1-9][0-9]*?)|(0?))t(?P<heure>([-]?[1-9][0-9]*?)|(0?))t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$
error regex 0 unable to interpret line 1103788 : ''
TextFile: closing file td8_velib.txt
1103787 lines imported
remove stations
SQL 'DROP TABLE stations'
TextFile: opening file stations.txt
TextFile.guess_columns: processing file stations.txt
TextFile: opening file stations.txt
TextFile.guess_columns: using 101 lines
TextFile: closing file stations.txt
TextFile.guess_columns: sep 't' nb cols 6 bestnb 101 more {('t', 5): 101, (' ', 13): 3, (' ', 21): 1, (' ', 14): 2, (' ', 12): 11, (' ', 16): 2, (' ', 9): 24, (' ', 10): 21, (' ', 8): 13, (' ', 11): 10, (' ', 7): 12, (' ', 6): 1}
TextFile.guess_columns: header True columns {0: ('address', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}
compiling ^(?P<address>.*)t(?P<contract_name>.*)t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<name>.*)t(?P<number>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: regex ^(?P<address>.*)t(?P<contract_name>.*)t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<name>.*)t(?P<number>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: header True columns {0: ('address', (<class 'str'>, 134)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}
[_guess_columns] sep=['t']
TextFile: closing file stations.txt
[_guess_columns] columns_name=None
guess with 932 lines
count_types {0: {<class 'str'>: 931}, 1: {<class 'str'>: 931}, 2: {<class 'float'>: 931}, 3: {<class 'float'>: 931}, 4: {<class 'str'>: 931}, 5: {<class 'int'>: 931}}
columns {0: ('address', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}
guess {0: ('address', (<class 'str'>, 188)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}
SQL 'CREATE TABLE stations(address TEXT,'
' contract_name TEXT,'
' lat FLOAT,'
' lng FLOAT,'
' name TEXT,'
' number INTEGER);'
column_has_space False ['address', 'contract_name', 'lat', 'lng', 'name', 'number']
changes {}
TextFileColumns (2): regex: {0: ('address', (<class 'str'>, 188)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}
TextFile.guess_columns: processing file stations.txt
TextFile: opening file stations.txt
TextFile.guess_columns: using 101 lines
TextFile: closing file stations.txt
TextFile.guess_columns: sep 't' nb cols 6 bestnb 101 more {('t', 5): 101, (' ', 13): 3, (' ', 21): 1, (' ', 14): 2, (' ', 12): 11, (' ', 16): 2, (' ', 9): 24, (' ', 10): 21, (' ', 8): 13, (' ', 11): 10, (' ', 7): 12, (' ', 6): 1}
TextFile.guess_columns: header True columns {0: ('address', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}
compiling ^(?P<address>.*)t(?P<contract_name>.*)t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<name>.*)t(?P<number>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: regex ^(?P<address>.*)t(?P<contract_name>.*)t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<name>.*)t(?P<number>([-]?[1-9][0-9]*?)|(0?))$
TextFile.guess_columns: header True columns {0: ('address', (<class 'str'>, 134)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}
TextFile: opening file stations.txt
^(?P<address>.*)t(?P<contract_name>.*)t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)t(?P<name>.*)t(?P<number>([-]?[1-9][0-9]*?)|(0?))$
error regex 0 unable to interpret line 1232 : ''
TextFile: closing file stations.txt
1231 lines imported
'stations'
Vous devriez voir un fichier .db3.
import os
[ _ for _ in os.listdir(".") if ".db3" in _]
['td8_velib.db3']
## Premières requêtes SQL
Dans notre cas, on va faire cela depuis le notebook.
%load_ext pyensae
%SQL_connect td8_velib.db3
<pyensae.sql.sql_interface_database.InterfaceSQLDatabase at 0x15ee7322d30>
On regarde les tables de la base de données.
%SQL_tables
['stations', 'td8_velib']
On regarde les colonnes de chaque table.
%SQL_schema stations
{0: ('address', str),
1: ('contract_name', str),
2: ('lat', float),
3: ('lng', float),
4: ('name', str),
5: ('number', int)}
%SQL_schema td8_velib
{0: ('collect_date', str),
1: ('last_update', str),
2: ('available_bike_stands', int),
3: ('available_bikes', int),
4: ('number', int),
5: ('heure', int),
6: ('minute', int)}
Et enfin on regarde les premières lignes.
%%SQL
SELECT * FROM td8_velib LIMIT 10
| collect_date | last_update | available_bike_stands | available_bikes | number | heure | minute | |
|---|---|---|---|---|---|---|---|
| 0 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:00:19 | 0 | 0 | 15025 | 9 | 0 |
| 1 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:05:19 | 0 | 0 | 15025 | 9 | 5 |
| 2 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:10:19 | 0 | 0 | 15025 | 9 | 10 |
| 3 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:15:19 | 0 | 0 | 15025 | 9 | 15 |
| 4 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:20:19 | 0 | 0 | 15025 | 9 | 20 |
| 5 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:25:19 | 0 | 0 | 15025 | 9 | 25 |
| 6 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:30:19 | 0 | 0 | 15025 | 9 | 30 |
| 7 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:35:19 | 0 | 0 | 15025 | 9 | 35 |
| 8 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:40:19 | 0 | 0 | 15025 | 9 | 40 |
| 9 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:45:19 | 0 | 0 | 15025 | 9 | 45 |
On sélectionne les données sur une plage horaire donnée.
%%SQL
SELECT * FROM td8_velib WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'
| collect_date | last_update | available_bike_stands | available_bikes | number | heure | minute | |
|---|---|---|---|---|---|---|---|
| 0 | 2013-09-13 10:01:38.208021 | 2013-09-13 10:00:19 | 15 | 12 | 19115 | 10 | 0 |
| 1 | 2013-09-13 10:01:38.215022 | 2013-09-13 10:00:19 | 8 | 11 | 4101 | 10 | 0 |
| 2 | 2013-09-13 10:00:39.333106 | 2013-09-13 10:00:19 | 14 | 11 | 4103 | 10 | 0 |
| 3 | 2013-09-13 10:00:39.340107 | 2013-09-13 10:00:19 | 9 | 7 | 4104 | 10 | 0 |
| 4 | 2013-09-13 09:57:39.683954 | 2013-09-13 10:00:19 | 31 | 1 | 15020 | 10 | 0 |
| 5 | 2013-09-13 09:59:38.068877 | 2013-09-13 10:00:19 | 5 | 13 | 4107 | 10 | 0 |
| 6 | 2013-09-13 10:00:39.336106 | 2013-09-13 10:00:19 | 47 | 16 | 15021 | 10 | 0 |
| 7 | 2013-09-13 09:53:38.839575 | 2013-09-13 10:00:19 | 19 | 0 | 19118 | 10 | 0 |
| 8 | 2013-09-13 10:00:39.343107 | 2013-09-13 10:00:19 | 19 | 1 | 20503 | 10 | 0 |
| 9 | 2013-09-13 10:00:39.339106 | 2013-09-13 10:00:19 | 23 | 11 | 15023 | 10 | 0 |
Sélectionner certaines colonnes et ordonner les valeurs.
%%SQL
SELECT available_bike_stands, available_bikes FROM td8_velib
WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'
ORDER BY available_bike_stands DESC ;
| available_bike_stands | available_bikes | |
|---|---|---|
| 0 | 68 | 2 |
| 1 | 68 | 2 |
| 2 | 68 | 2 |
| 3 | 68 | 2 |
| 4 | 68 | 2 |
| 5 | 68 | 2 |
| 6 | 67 | 3 |
| 7 | 67 | 3 |
| 8 | 67 | 3 |
| 9 | 67 | 3 |
Compter le nombre d’emplacements de chaque station.
%%SQL
SELECT last_update, available_bike_stands + available_bikes AS place, number FROM td8_velib
WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'
ORDER BY place DESC ;
| last_update | place | number | |
|---|---|---|---|
| 0 | 2013-09-13 10:00:19 | 70 | 16004 |
| 1 | 2013-09-13 10:05:19 | 70 | 16004 |
| 2 | 2013-09-13 10:10:19 | 70 | 16004 |
| 3 | 2013-09-13 10:15:19 | 70 | 16004 |
| 4 | 2013-09-13 10:20:19 | 70 | 16004 |
| 5 | 2013-09-13 10:25:19 | 70 | 16004 |
| 6 | 2013-09-13 10:30:19 | 70 | 16004 |
| 7 | 2013-09-13 10:35:19 | 70 | 16004 |
| 8 | 2013-09-13 10:40:19 | 70 | 16004 |
| 9 | 2013-09-13 10:45:19 | 70 | 16004 |
Par défaut la commande %%SQL n’affiche que les dix premières lignes.
%%SQL --help
.
usage: SQL [-h] [--df DF] [-n N] [-q QUERY] [-v VARIABLE]
query the database
optional arguments:
-h, --help show this help message and exit
--df DF output dataframe
-n N, --n N number of first lines to display
-q QUERY, --query QUERY
when used in a single line (no cell), query is the SQL
query, the command returns the full dataframe
-v VARIABLE, --variable VARIABLE
variable name used to store the database object
usage: SQL [-h] [--df DF] [-n N] [-q QUERY] [-v VARIABLE]
On affiche 5 lignes et on stocke le résultat dans un dataframe.
%%SQL -n 5 --df=df
SELECT last_update, available_bike_stands + available_bikes AS place, number FROM td8_velib
WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'
ORDER BY place DESC ;
| last_update | place | number | |
|---|---|---|---|
| 0 | 2013-09-13 10:00:19 | 70 | 16004 |
| 1 | 2013-09-13 10:05:19 | 70 | 16004 |
| 2 | 2013-09-13 10:10:19 | 70 | 16004 |
| 3 | 2013-09-13 10:15:19 | 70 | 16004 |
| 4 | 2013-09-13 10:20:19 | 70 | 16004 |
df.tail()
| last_update | place | number | |
|---|---|---|---|
| 14755 | 2013-09-13 10:55:19 | 0 | 7025 |
| 14756 | 2013-09-13 10:55:19 | 0 | 32004 |
| 14757 | 2013-09-13 10:55:19 | 0 | 32006 |
| 14758 | 2013-09-13 10:55:19 | 0 | 20122 |
| 14759 | 2013-09-13 10:55:19 | 0 | 16135 |
Maximum de vélos disponibles à une station.
%%SQL
SELECT MAX(available_bike_stands) FROM td8_velib
| MAX(available_bike_stands) | |
|---|---|
| 0 | 70 |
Et le minimum.
%%SQL
SELECT "min" AS label, MIN(available_bike_stands) FROM td8_velib
UNION ALL
SELECT "max" AS label, MAX(available_bike_stands) FROM td8_velib
| label | MIN(available_bike_stands) | |
|---|---|---|
| 0 | min | 0 |
| 1 | max | 70 |
Tous les numéros de stations de façon unique.
%%SQL
SELECT DISTINCT number FROM td8_velib
| number | |
|---|---|
| 0 | 15025 |
| 1 | 17014 |
| 2 | 15108 |
| 3 | 20122 |
| 4 | 34010 |
| 5 | 43003 |
| 6 | 20023 |
| 7 | 12151 |
| 8 | 18041 |
| 9 | 43008 |
Compter le nombre de stations (1230).
%%SQL
SELECT COUNT(*) FROM (
SELECT DISTINCT number FROM td8_velib
)
| COUNT(*) | |
|---|---|
| 0 | 1230 |
Exercice 1#
Déterminer le nombre de valeur distinctes pour la colonne
last_update.Déterminer la première et dernière date.
GROUP BY#
L’instruction GROUP BY permet d’aggréger des valeurs (min, max, sum)
sur un ensemble de ligne partageant le même ensemble de valeurs (ou
clé).
%%SQL --df=df
SELECT last_update, SUM(available_bikes) AS velo_disponible
FROM td8_velib
GROUP BY last_update
ORDER BY last_update
| last_update | velo_disponible | |
|---|---|---|
| 0 | 2013-07-22 09:00:19 | 0 |
| 1 | 2013-07-22 09:05:19 | 0 |
| 2 | 2013-07-22 09:10:19 | 0 |
| 3 | 2013-07-22 09:15:19 | 0 |
| 4 | 2013-07-22 09:20:19 | 0 |
| 5 | 2013-07-22 09:25:19 | 0 |
| 6 | 2013-07-22 09:30:19 | 0 |
| 7 | 2013-07-22 09:35:19 | 0 |
| 8 | 2013-07-22 09:40:19 | 0 |
| 9 | 2013-07-22 09:45:19 | 0 |
Le résultat est un tableau avec de petites valeurs au début et de
grandes vers la fin. Cela est dû au processus de création de la base de
données. Certaines stations sont hors service et la dernière arrivée ou
le dernier départ remonte à plusieurs jours. A chaque fois qu’on
récupère les données velib, on dispose pour chaque station de la
dernière arrivée ou du dernier départ de vélo. Le champ last_update
correspond à cette date. Il ne faudra considérer que les dates au-delà
de 2013-09-10 11:30:19.
df.tail()
| last_update | velo_disponible | |
|---|---|---|
| 15289 | 2013-09-13 11:05:19 | 13498 |
| 15290 | 2013-09-13 11:10:19 | 13524 |
| 15291 | 2013-09-13 11:15:19 | 13519 |
| 15292 | 2013-09-13 11:20:19 | 13546 |
| 15293 | 2013-09-13 11:25:19 | 13560 |
Exercice 1b#
Que fait la requête suivante ? Que se passe-t-il si vous enlevez les
symboles -- (on décommente la condition WHERE) ?
%%SQL --df=df
SELECT last_update, SUM(available_bikes) AS velo_disponible, COUNT(DISTINCT number) AS stations
FROM td8_velib
--WHERE last_update >= "2013-09-10 11:30:19"
GROUP BY last_update
ORDER BY last_update
| last_update | velo_disponible | stations | |
|---|---|---|---|
| 0 | 2013-07-22 09:00:19 | 0 | 1 |
| 1 | 2013-07-22 09:05:19 | 0 | 1 |
| 2 | 2013-07-22 09:10:19 | 0 | 1 |
| 3 | 2013-07-22 09:15:19 | 0 | 1 |
| 4 | 2013-07-22 09:20:19 | 0 | 1 |
| 5 | 2013-07-22 09:25:19 | 0 | 1 |
| 6 | 2013-07-22 09:30:19 | 0 | 1 |
| 7 | 2013-07-22 09:35:19 | 0 | 1 |
| 8 | 2013-07-22 09:40:19 | 0 | 1 |
| 9 | 2013-07-22 09:45:19 | 0 | 1 |
et celle-ci ?
%%SQL --df=df
SELECT last_update,
CASE WHEN available_bikes>0 THEN 1 ELSE 0 END AS vide,
COUNT(*) AS nb
FROM td8_velib
WHERE last_update >= "2013-09-10 11:30:19"
GROUP BY last_update, vide
ORDER BY last_update
| last_update | vide | nb | |
|---|---|---|---|
| 0 | 2013-09-10 11:30:19 | 0 | 233 |
| 1 | 2013-09-10 11:30:19 | 1 | 997 |
| 2 | 2013-09-10 11:35:19 | 0 | 232 |
| 3 | 2013-09-10 11:35:19 | 1 | 998 |
| 4 | 2013-09-10 11:40:19 | 0 | 241 |
| 5 | 2013-09-10 11:40:19 | 1 | 989 |
| 6 | 2013-09-10 11:45:19 | 0 | 244 |
| 7 | 2013-09-10 11:45:19 | 1 | 986 |
| 8 | 2013-09-10 11:50:19 | 0 | 246 |
| 9 | 2013-09-10 11:50:19 | 1 | 984 |
Exerice 2#
Pour chaque station, compter le nombre de plages horaires de cinq minutes où il n’y a aucun vélo disponible.
Exercice 3#
Si on note 
le nombre de plages horaires de cinq minutes où
il n’y a aucun vélo disponible, construire le tableau suivant :
.
JOIN#
L’instruction JOIN sert à associer des lignes d’une table avec les
lignes d’une autre table à partir du moment où elles partagent une
information commune.
%%SQL
SELECT A.*, B.name -- ajout du nom au bout de chaque ligne
FROM td8_velib AS A
JOIN stations AS B
ON A.number == B.number
| collect_date | last_update | available_bike_stands | available_bikes | number | heure | minute | name | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:00:19 | 0 | 0 | 15025 | 9 | 0 | 15025 - AMETTE |
| 1 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:05:19 | 0 | 0 | 15025 | 9 | 5 | 15025 - AMETTE |
| 2 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:10:19 | 0 | 0 | 15025 | 9 | 10 | 15025 - AMETTE |
| 3 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:15:19 | 0 | 0 | 15025 | 9 | 15 | 15025 - AMETTE |
| 4 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:20:19 | 0 | 0 | 15025 | 9 | 20 | 15025 - AMETTE |
| 5 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:25:19 | 0 | 0 | 15025 | 9 | 25 | 15025 - AMETTE |
| 6 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:30:19 | 0 | 0 | 15025 | 9 | 30 | 15025 - AMETTE |
| 7 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:35:19 | 0 | 0 | 15025 | 9 | 35 | 15025 - AMETTE |
| 8 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:40:19 | 0 | 0 | 15025 | 9 | 40 | 15025 - AMETTE |
| 9 | 2013-09-13 11:26:37.738913 | 2013-07-22 09:45:19 | 0 | 0 | 15025 | 9 | 45 | 15025 - AMETTE |
On peut s’en servir pour calculer un ratio en associant les deux
instructions GROUP BY et JOIN. L’instruction suivante permet
d’obtenir la distribution des vélos disponibles sur la période d’étude
pour chaque station.
%%SQL
SELECT A.*, 1.0 * A.available_bikes / B.nb_velo AS distribution_temporelle
FROM td8_velib AS A
JOIN (
SELECT number, SUM(available_bikes) AS nb_velo
FROM td8_velib
WHERE last_update >= "2013-09-10 11:30:19"
GROUP BY number
) AS B
ON A.number == B.number
WHERE A.last_update >= "2013-09-10 11:30:19"
| collect_date | last_update | available_bike_stands | available_bikes | number | heure | minute | distribution_temporelle | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2013-09-10 11:27:43.394054 | 2013-09-10 11:30:19 | 24 | 3 | 19115 | 11 | 30 | 0.000289 |
| 1 | 2013-09-10 11:28:44.115980 | 2013-09-10 11:30:19 | 16 | 2 | 4101 | 11 | 30 | 0.000288 |
| 2 | 2013-09-10 11:30:43.257442 | 2013-09-10 11:30:19 | 16 | 8 | 4103 | 11 | 30 | 0.002110 |
| 3 | 2013-09-10 11:27:43.400056 | 2013-09-10 11:30:19 | 13 | 1 | 4104 | 11 | 30 | 0.000194 |
| 4 | 2013-09-10 11:30:43.262502 | 2013-09-10 11:30:19 | 28 | 4 | 15020 | 11 | 30 | 0.000311 |
| 5 | 2013-09-10 11:27:43.391054 | 2013-09-10 11:30:19 | 12 | 6 | 4107 | 11 | 30 | 0.000798 |
| 6 | 2013-09-10 11:27:43.396054 | 2013-09-10 11:30:19 | 63 | 0 | 15021 | 11 | 30 | 0.000000 |
| 7 | 2013-09-10 11:27:43.393084 | 2013-09-10 11:30:19 | 19 | 0 | 19118 | 11 | 30 | 0.000000 |
| 8 | 2013-09-10 11:29:43.672735 | 2013-09-10 11:30:19 | 21 | 0 | 20503 | 11 | 30 | 0.000000 |
| 9 | 2013-09-10 11:30:43.264442 | 2013-09-10 11:30:19 | 31 | 2 | 15023 | 11 | 30 | 0.000555 |
Exercice 4 : distribution horaire#
Pour chaque station, déterminer la distribution du nombre de vélos disponibles pour chaque période horaire d’une journée (par station, il y aura donc 24 * 12 valeurs comprises entre 0 et 1). Le résultat que vous devriez obtenir est illustré par l’image qui suit.
from pyquickhelper.helpgen import NbImage
NbImage("images/tb8_dis_hor.png")
Exercice 5 : zones de travail#
On souhaite déterminer si une station se situe plutôt dans une zone de travail ou plutôt dans une zone de résidence. On part de l’hypothèse que, dans une zone de travail, les gens arrivent en vélib et repartent en vélib. C’est sans doute le cas de la station 8003. Les vélos seront plutôt disponibles dans la journée. A l’opposé, dans une zone de résidence, les vélos seront disponibles plutôt la nuit. Comment faire à partir de la distribution des vélos disponibles construite à la question précédente ?
On considère que la plage diurne s’étend de 10h à 16h. Vous trouverez une illustration du résultat dans cet article.
Exercice 6 : lattitude, longitude#
On repart de la requête précédente pour effectuer un JOIN avec la table stations pour récupérer les coordonnées (lat, long). Après un copier/coller dans Excel, on peut situer les zones de travail sur la région parisienne.
%SQL_close
Sans %%SQL#
La commande magique %%SQL s’appuie sur le module
sqlite3. On peut
faire sans.
import sqlite3
conn = sqlite3.connect("td8_velib.db3") # on ouvre une connexion sur la base de données
data = conn.execute("SELECT * FROM stations") # on exécute une requête SQL
for i, d in enumerate(data): # on affiche le résultat
print(d)
if i > 5:
break
conn.close()
('QUAI ANATOLE FRANCE - PONT SOLFERINO - 75007 PARIS', 'Paris', 48.86138, 2.32442, '00901 - PORT SOLFÉRINO (STATION MOBILE)', 901)
("FETE DE L'OH (BERCY) - QUAI MAURIAC ANG PONT DE BERCY - 75013 PARIS", 'Paris', 48.8371336894515, 2.37434055460561, '00903 - QUAI MAURIAC / PONT DE BERCY', 903)
('ECOLE MILITAIRE-AVENUE DE LA MOTTE PICQUET - 75007 PARIS', 'Paris', 48.85213620522547, 2.301961227213259, '00904 - PLACE JOFFRE / ECOLE MILITAIRE', 904)
("QUAI D'ORSAY - CONCORDE - 75007 PARIS", 'Paris', 48.86314, 2.31669, '00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE)', 905)
("GARDE DE L'EST-PARVIS GARE DE L'EST - 75010 PARIS", 'Paris', 48.876419813641114, 2.358630064544601, "00906 - GARE DE L'EST", 906)
("QUAI D'ORSAY - PORT DU GROS CAILLOU - 75007 PARIS", 'Paris', 48.86288, 2.30652, '00908 - PORT DU GROS CAILLOU (STATION MOBILE)', 908)
("41 QUAI DE L'HORLOGE - 75001 PARIS", 'Paris', 48.857091635218225, 2.341747995157864, '01001 - ILE DE LA CITE PONT NEUF', 1001)