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.
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.
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']
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 |
last_update
.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 |
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 |
Pour chaque station, compter le nombre de plages horaires de cinq minutes où il n'y a aucun vélo disponible.
Si on note $X(s)$ le nombre de plages horaires de cinq minutes où il n'y a aucun vélo disponible, construire le tableau suivant : $k \rightarrow card\{ s | X(s) = k \}$.
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 |
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")
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.
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
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)