.. _td1asqlrst: ======================== 1A.soft - Notions de SQL ======================== .. only:: html **Links:** :download:`notebook `, :downloadlink:`html `, :download:`python `, :downloadlink:`slides `, :githublink:`GitHub|_doc/notebooks/td1a_soft/td1a_sql.ipynb|*` Premiers pas avec le langage `SQL `__. .. code:: ipython3 from jyquickhelper import add_notebook_menu add_notebook_menu() .. contents:: :local: 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 -------------------- .. code:: ipython3 from pyensae.datasource import download_data download_data("td8_velib.zip", website = 'xd') .. parsed-literal:: ['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. .. code:: ipython3 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 .. parsed-literal:: 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', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} compiling ^(?P.*)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: regex ^(?P.*)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: header True columns {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} [_guess_columns] sep=['\t'] TextFile: closing file td8_velib.txt [_guess_columns] columns_name=None guess with 1001 lines count_types {0: {: 1000}, 1: {: 1000}, 2: {: 1000}, 3: {: 1000}, 4: {: 1000}, 5: {: 1000}, 6: {: 1000}} columns {0: ('collect_date', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} guess {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} 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', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} 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', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} compiling ^(?P.*)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: regex ^(?P.*)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: header True columns {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )} 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.*)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[1-9][0-9]*?)|(0?))\t(?P([-]?[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', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )} compiling ^(?P
.*)\t(?P.*)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: regex ^(?P
.*)\t(?P.*)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )} [_guess_columns] sep=['\t'] TextFile: closing file stations.txt [_guess_columns] columns_name=None guess with 932 lines count_types {0: {: 931}, 1: {: 931}, 2: {: 931}, 3: {: 931}, 4: {: 931}, 5: {: 931}} columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )} guess {0: ('address', (, 188)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )} 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', (, 188)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )} 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', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )} compiling ^(?P
.*)\t(?P.*)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: regex ^(?P
.*)\t(?P.*)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))$ TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )} TextFile: opening file stations.txt ^(?P
.*)\t(?P.*)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\t(?P.*)\t(?P([-]?[1-9][0-9]*?)|(0?))$ error regex 0 unable to interpret line 1232 : '' TextFile: closing file stations.txt 1231 lines imported .. parsed-literal:: 'stations' Vous devriez voir un fichier .db3. .. code:: ipython3 import os [ _ for _ in os.listdir(".") if ".db3" in _] .. parsed-literal:: ['td8_velib.db3'] ## Premières requêtes SQL Dans notre cas, on va faire cela depuis le notebook. .. code:: ipython3 %load_ext pyensae .. code:: ipython3 %SQL_connect td8_velib.db3 .. parsed-literal:: On regarde les tables de la base de données. .. code:: ipython3 %SQL_tables .. parsed-literal:: ['stations', 'td8_velib'] On regarde les colonnes de chaque table. .. code:: ipython3 %SQL_schema stations .. parsed-literal:: {0: ('address', str), 1: ('contract_name', str), 2: ('lat', float), 3: ('lng', float), 4: ('name', str), 5: ('number', int)} .. code:: ipython3 %SQL_schema td8_velib .. parsed-literal:: {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. .. code:: ipython3 %%SQL SELECT * FROM td8_velib LIMIT 10 .. raw:: html
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. .. code:: ipython3 %%SQL SELECT * FROM td8_velib WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00' .. raw:: html
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. .. code:: ipython3 %%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 ; .. raw:: html
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. .. code:: ipython3 %%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 ; .. raw:: html
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. .. code:: ipython3 %%SQL --help . .. parsed-literal:: 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. .. code:: ipython3 %%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 ; .. raw:: html
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
.. code:: ipython3 df.tail() .. raw:: html
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. .. code:: ipython3 %%SQL SELECT MAX(available_bike_stands) FROM td8_velib .. raw:: html
MAX(available_bike_stands)
0 70
Et le minimum. .. code:: ipython3 %%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 .. raw:: html
label MIN(available_bike_stands)
0 min 0
1 max 70
Tous les numéros de stations de façon unique. .. code:: ipython3 %%SQL SELECT DISTINCT number FROM td8_velib .. raw:: html
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). .. code:: ipython3 %%SQL SELECT COUNT(*) FROM ( SELECT DISTINCT number FROM td8_velib ) .. raw:: html
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é). .. code:: ipython3 %%SQL --df=df SELECT last_update, SUM(available_bikes) AS velo_disponible FROM td8_velib GROUP BY last_update ORDER BY last_update .. raw:: html
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``. .. code:: ipython3 df.tail() .. raw:: html
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``) ? .. code:: ipython3 %%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 .. raw:: html
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 ? .. code:: ipython3 %%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 .. raw:: html
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 :math:`X(s)` le nombre de plages horaires de cinq minutes où il n’y a aucun vélo disponible, construire le tableau suivant : :math:`k \rightarrow card\{ s | X(s) = k \}`. 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. .. code:: ipython3 %%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 .. raw:: html
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. .. code:: ipython3 %%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" .. raw:: html
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. .. code:: ipython3 from pyquickhelper.helpgen import NbImage NbImage("images/tb8_dis_hor.png") .. image:: td1a_sql_56_0.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. .. code:: ipython3 %SQL_close Sans %%SQL ---------- La commande magique ``%%SQL`` s’appuie sur le module `sqlite3 `__. On peut faire sans. .. code:: ipython3 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() .. parsed-literal:: ('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)