{"cells": [{"cell_type": "markdown", "metadata": {}, "source": ["# 1A.soft - Notions de SQL - correction\n", "\n", "Correction des exercices du premier notebooks reli\u00e9 au SQL."]}, {"cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [{"data": {"text/html": ["
run previous cell, wait for 2 seconds
\n", ""], "text/plain": [""]}, "execution_count": 2, "metadata": {}, "output_type": "execute_result"}], "source": ["from jyquickhelper import add_notebook_menu\n", "add_notebook_menu()"]}, {"cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": ["%matplotlib inline"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Recup\u00e9rer les donn\u00e9es"]}, {"cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": [" TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: processing file td8_velib.txt\n", " TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file td8_velib.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 7 bestnb 101 more {('\\t', 6): 101, (' ', 2): 100}\n", " 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', )}\n", " 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?))$\n", " 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?))$\n", " 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', )}\n", " [_guess_columns] sep=['\\t']\n", " TextFile: closing file td8_velib.txt\n", " [_guess_columns] columns_name=None\n", " guess with 1001 lines\n", " count_types {0: {: 1000}, 1: {: 1000}, 2: {: 1000}, 3: {: 1000}, 4: {: 1000}, 5: {: 1000}, 6: {: 1000}}\n", " columns {0: ('collect_date', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " guess {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", "SQL 'CREATE TABLE td8_velib(collect_date TEXT,'\n", "' last_update TEXT,'\n", "' available_bike_stands INTEGER,'\n", "' available_bikes INTEGER,'\n", "' number INTEGER,'\n", "' heure INTEGER,'\n", "' minute INTEGER);'\n", " column_has_space False ['collect_date', 'last_update', 'available_bike_stands', 'available_bikes', 'number', 'heure', 'minute']\n", " changes {}\n", " TextFileColumns (2): regex: {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " TextFile.guess_columns: processing file td8_velib.txt\n", " TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file td8_velib.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 7 bestnb 101 more {('\\t', 6): 101, (' ', 2): 100}\n", " 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', )}\n", " 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?))$\n", " 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?))$\n", " 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', )}\n", " TextFile: opening file td8_velib.txt\n", "adding 100000 lines into table td8_velib\n", "adding 200000 lines into table td8_velib\n", "adding 300000 lines into table td8_velib\n", "adding 400000 lines into table td8_velib\n", "adding 500000 lines into table td8_velib\n", "adding 600000 lines into table td8_velib\n", "adding 700000 lines into table td8_velib\n", "adding 800000 lines into table td8_velib\n", "adding 900000 lines into table td8_velib\n", "adding 1000000 lines into table td8_velib\n", "adding 1100000 lines into table td8_velib\n", "^(?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?))$\n", "error regex 0 unable to interpret line 1103788 : ''\n", " TextFile: closing file td8_velib.txt\n", "1103787 lines imported\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: processing file stations.txt\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file stations.txt\n", " 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}\n", " TextFile.guess_columns: header True columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " 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?))$\n", " 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?))$\n", " TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " [_guess_columns] sep=['\\t']\n", " TextFile: closing file stations.txt\n", " [_guess_columns] columns_name=None\n", " guess with 935 lines\n", " count_types {0: {: 934}, 1: {: 934}, 2: {: 934}, 3: {: 934}, 4: {: 934}, 5: {: 934}}\n", " columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " guess {0: ('address', (, 152)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", "SQL 'CREATE TABLE stations(address TEXT,'\n", "' contract_name TEXT,'\n", "' lat FLOAT,'\n", "' lng FLOAT,'\n", "' name TEXT,'\n", "' number INTEGER);'\n", " column_has_space False ['address', 'contract_name', 'lat', 'lng', 'name', 'number']\n", " changes {}\n", " TextFileColumns (2): regex: {0: ('address', (, 152)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " TextFile.guess_columns: processing file stations.txt\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file stations.txt\n", " 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}\n", " TextFile.guess_columns: header True columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " 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?))$\n", " 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?))$\n", " TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " TextFile: opening file stations.txt\n", "^(?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?))$\n", "error regex 0 unable to interpret line 1232 : ''\n", " TextFile: closing file stations.txt\n", "1231 lines imported\n"]}], "source": ["import os\n", "if not os.path.exists(\"td8_velib.db3\"):\n", " from pyensae.datasource import download_data\n", " download_data(\"td8_velib.zip\", website = 'xd')\n", " from pyensae.sql import import_flatfile_into_database\n", " dbf = \"td8_velib.db3\"\n", " import_flatfile_into_database(dbf, \"td8_velib.txt\") # 2 secondes \n", " import_flatfile_into_database(dbf, \"stations.txt\", table=\"stations\") # 2 minutes"]}, {"cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": ["%load_ext pyensae"]}, {"cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [{"data": {"text/plain": [""]}, "execution_count": 6, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_connect td8_velib.db3"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 1"]}, {"cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(*)
015294
\n", "
"], "text/plain": [" COUNT(*)\n", "0 15294"]}, "execution_count": 7, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT COUNT(*) FROM (\n", " SELECT DISTINCT last_update FROM td8_velib\n", ") ;"]}, {"cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MIN(last_update)MAX(last_update)
02013-07-22 09:00:192013-09-13 11:25:19
\n", "
"], "text/plain": [" MIN(last_update) MAX(last_update)\n", "0 2013-07-22 09:00:19 2013-09-13 11:25:19"]}, "execution_count": 8, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT MIN(last_update), MAX(last_update) FROM td8_velib ;"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 2"]}, {"cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numbernb
042704864
141101864
235008864
335005864
435003864
533012864
633011864
733006864
833005864
932603864
\n", "
"], "text/plain": [" number nb\n", "0 42704 864\n", "1 41101 864\n", "2 35008 864\n", "3 35005 864\n", "4 35003 864\n", "5 33012 864\n", "6 33011 864\n", "7 33006 864\n", "8 33005 864\n", "9 32603 864"]}, "execution_count": 9, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT number, COUNT(*) AS nb \n", "FROM td8_velib\n", "WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'\n", "GROUP BY number\n", "ORDER BY nb DESC"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 3 : plage horaires de cinq minutes o\u00f9 il n'y a aucun v\u00e9lo disponible"]}, {"cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nbnb_station
017
127
235
346
456
565
678
789
891
9107
\n", "
"], "text/plain": [" nb nb_station\n", "0 1 7\n", "1 2 7\n", "2 3 5\n", "3 4 6\n", "4 5 6\n", "5 6 5\n", "6 7 8\n", "7 8 9\n", "8 9 1\n", "9 10 7"]}, "execution_count": 10, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT nb, COUNT(*) AS nb_station\n", "FROM (\n", " -- requ\u00eate de l'exercice pr\u00e9c\u00e9dent\n", " SELECT number, COUNT(*) AS nb \n", " FROM td8_velib\n", " WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", ")\n", "GROUP BY nb"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 4 : distribution horaire par station et par tranche de 5 minutes"]}, {"cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numberheureminutedistribution_temporelle
0901000.001104
1901050.001104
29010100.001104
39010150.001104
49010200.001104
59010250.000946
69010300.000946
79010350.000946
89010400.000946
99010450.000946
\n", "
"], "text/plain": [" number heure minute distribution_temporelle\n", "0 901 0 0 0.001104\n", "1 901 0 5 0.001104\n", "2 901 0 10 0.001104\n", "3 901 0 15 0.001104\n", "4 901 0 20 0.001104\n", "5 901 0 25 0.000946\n", "6 901 0 30 0.000946\n", "7 901 0 35 0.000946\n", "8 901 0 40 0.000946\n", "9 901 0 45 0.000946"]}, "execution_count": 11, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", "FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", ") AS A\n", "JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", ") AS B\n", "ON A.number == B.number\n", "--WHERE A.number in (8001, 8003, 15024, 15031) -- pour n'afficher que quelques stations\n", "ORDER BY A.number, A.heure, A.minute"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Zones de travail et zones de r\u00e9sidence"]}, {"cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numbervelo_jour
09010.555188
19030.473137
29040.357640
39050.323395
49060.117566
59080.501727
610010.486796
710020.355434
810030.364749
910040.440549
\n", "
"], "text/plain": [" number velo_jour\n", "0 901 0.555188\n", "1 903 0.473137\n", "2 904 0.357640\n", "3 905 0.323395\n", "4 906 0.117566\n", "5 908 0.501727\n", "6 1001 0.486796\n", "7 1002 0.355434\n", "8 1003 0.364749\n", "9 1004 0.440549"]}, "execution_count": 12, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL --df=df\n", "SELECT number, SUM(distribution_temporelle) AS velo_jour\n", "FROM (\n", " -- requ\u00eate de l'exercice 4\n", " SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", " FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", " ) AS A\n", " JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", " ) AS B\n", " ON A.number == B.number\n", ")\n", "WHERE heure >= 10 AND heure <= 16 \n", "GROUP BY number"]}, {"cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
indexnumbervelo_jour
00181130.000000
11350130.000000
22100100.000000
33200370.007382
44201190.010623
\n", "
"], "text/plain": [" index number velo_jour\n", "0 0 18113 0.000000\n", "1 1 35013 0.000000\n", "2 2 10010 0.000000\n", "3 3 20037 0.007382\n", "4 4 20119 0.010623"]}, "execution_count": 13, "metadata": {}, "output_type": "execute_result"}], "source": ["df = df.sort_values(\"velo_jour\").reset_index()\n", "df[\"index\"] = range(0, df.shape[0])\n", "df.head()"]}, {"cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [{"data": {"text/plain": [""]}, "execution_count": 14, "metadata": {}, "output_type": "execute_result"}, {"data": {"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEGCAYAAAB1iW6ZAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/Il7ecAAAACXBIWXMAAAsTAAALEwEAmpwYAAAp0klEQVR4nO3deXxU9b3/8deHBBLZtwCBEAj7IggYQUVU3BeUWrWCXqutiteti0uvXr22tb33Vtuft/YW91K7uFGKFpWKS3HBBRKUfQ1rAgFCwhrI/vn9MSN3TKMZwkwmM3k/H488mHPOd+Z8Tk7yZvKdc75fc3dERCT+tYh1ASIiEhkKdBGRBKFAFxFJEAp0EZEEoUAXEUkQybHacdeuXb1v376x2r2ISFxavHjxbndPq2tbzAK9b9++5Obmxmr3IiJxycy2fNU2dbmIiCQIBbqISIJQoIuIJIiY9aHXpbKykoKCAsrKymJdSlxKTU0lIyODli1bxroUEYmBsALdzC4AHgOSgGfd/Re1tmcCfwA6Btvc6+5zj7aYgoIC2rVrR9++fTGzo316s+buFBcXU1BQQFZWVqzLEZEYqLfLxcySgOnAhcAwYKqZDavV7AFgpruPBqYAjzekmLKyMrp06aIwbwAzo0uXLvrrRqQZC6cPfSyQ5+4b3b0CeAmYXKuNA+2DjzsA2xtakMK84fS9E2newgn0XkB+yHJBcF2onwD/YmYFwFzgjrpeyMymmVmumeUWFRU1oFwRkfh1uKKaX85bw9L8vVF5/Uhd5TIVeM7dM4CLgD+Z2T+9trs/7e7Z7p6dllbnjU4iIglr7+EKps/fwKrC/VF5/XACfRvQO2Q5I7gu1A3ATAB3/wRIBbpGosCmrG3btkf9nO3bt3PFFVdEoRoRaepKy6sBaJMSnQsMwwn0HGCgmWWZWSsCH3rOqdVmK3A2gJkNJRDo6lOpQ8+ePZk1a1ZEXqu6ujoiryMijaO0vAqANq2SovL69f434e5VZnY7MI/AJYkz3H2lmT0E5Lr7HOAu4Bkz+yGBD0iv92Oc2+6nr61k1fbI/lkyrGd7fnzJ8K/cfu+999K7d29uu+02AH7yk5/Qtm1b3J2ZM2dSXl7OZZddxk9/+tMvPc/d+dGPfsTf//53zIwHHniAq666qs59bN68mUmTJrFixQrKysq45ZZbyM3NJTk5mUcffZSJEyfy3HPPkZuby29/+1sAJk2axN13382ZZ55J27Ztufnmm3nnnXeYPn06p512WoS+OyISbaUVgUBv3Sp279Bx97nuPsjd+7v7fwbXPRgMc9x9lbuPd/cT3H2Uu78VlWqj7KqrrmLmzJlHlmfOnElaWhrr169n0aJFLFmyhMWLF/PBBx986XmzZ89myZIlLF26lHfeeYd77rmHwsLCevc3ffp0zIzly5fz4osvct1119V72WFpaSnjxo1j6dKlCnOROHMo2OXSNkpdLk3qTtFQX/dOOlpGjx7Nrl272L59O0VFRXTq1Inly5fz1ltvMXr0aAAOHjzI+vXrOf300488b8GCBUydOpWkpCS6d+/OGWecQU5ODpdeeunX7m/BggXccUfggqAhQ4bQp08f1q1b97XPSUpK4vLLLz/GIxWRWDhQXglAm5QYdbk0N1deeSWzZs1ix44dXHXVVWzZsoX77ruPm2++udFqSE5Opqam5shy6Lv21NRUkpKi88MgItG1c385AN3ap0bl9TU4Vy1XXXUVL730ErNmzeLKK6/k/PPPZ8aMGRw8eBCAbdu2sWvXri89Z8KECbz88stUV1dTVFTEBx98wNixY+vd14QJE3j++ecBWLduHVu3bmXw4MH07duXJUuWUFNTQ35+PosWLYr8gYpIo9uxr4y2KcnNr8slVoYPH86BAwfo1asX6enppKens3r1ak455RQgcKnin//8Z7p163bkOZdddhmffPIJJ5xwAmbGI488Qo8ePb5yH1/c0Xnrrbdyyy23MGLECJKTk3nuuedISUlh/PjxZGVlMWzYMIYOHcqYMWOie9Ai0ii2FJfSu3PrqL2+HePFKA2WnZ3ttWcsWr16NUOHDo1JPY1l8eLF3Hnnnbz//vtRef3m8D0UiVcTf/UeQ9Pb8fg1Jzb4Ncxssbtn17VNXS6NKDc3l6lTp/L9738/1qWISCOrrK5ha8khsrq2ido+1OUSJcuXL+faa6/90rqUlJR6r2IRkcT0/toiqmucoent62/cQE0u0N09IUYNHDFiBEuWLGnUfcaq+0xE6rd46x6SWxjnDusetX00qS6X1NRUiouLFUwN8MUEF6mp0bkcSkSOzbwVOxiR0YGU5Ohddtyk3qFnZGRQUFCAhtZtmC+moBORpqWquoYtJYe4dWT/qO6nSQV6y5YtNX2aiCScwn1lVNc4PTseF9X9NKkuFxGRRLQyONDgoO5HP+T20VCgi4hEUUVVDY++vZbu7VMY3rNDVPelQBcRiaLf/mM963Ye5MFJw0ltGd1xmBToIiJRsjR/L7/5Rx4Du7XlnGHd6n/CMVKgi4hEyfy1gYH8Xpx2clQvV/xCWIFuZheY2VozyzOze+vY/j9mtiT4tc7M9ka8UhGROPPBuiJO6N2Rrm1TGmV/9V62aGZJwHTgXKAAyDGzOe6+6os27v7DkPZ3AKOjUKuISNz49Tvr+GzrXu45f3Cj7TOcd+hjgTx33+juFcBLwOSvaT8VeDESxYmIxKOZufn8+p31TBqZzg2nNd69NeEEei8gP2S5ILjun5hZHyAL+MdXbJ9mZrlmlqu7QUUkES3N38t/zV3NsPT2/L9vnRD1K1tCRfpD0SnALHevrmujuz/t7tnunp2WlhbhXYuIxNaKbfu47PGPSDLjV1ee0CgfhIYK59b/bUDvkOWM4Lq6TAFuO9aiRETi0azFBZgZ8354eqN9EBoqnHfoOcBAM8sys1YEQntO7UZmNgToBHwS2RJFRJo+d+ftVTs5Z2i3mIQ5hBHo7l4F3A7MA1YDM919pZk9ZGaXhjSdArzkGvtWRJqZyuoaHnp9Fdv2HubsIdEb77w+YY226O5zgbm11j1Ya/knkStLRCQ+HCir5NbnP+PD9bu5/tS+XJkduyGsm9TwuSIi8aS6xrlz5lI+2VDMI1eM5FvZvet/UhQp0EVEGuC1pdv51Vtr2VJ8iNsm9o95mIMCXUTkqO0preCuvywlrW0KD00ezjXj+sS6JECBLiJyVMoqq7n5T4upqKrhmW9nM6xn+1iXdIQCXUQkTHtKK7j+uRyW5u/lx5cMa1JhDho+V0QkbP/999VHwvw745ve/McKdBGRMKzYto+ZuQV8c3SvJhnmoEAXEalX4b7D/GjWMpJbGPddNDTW5Xwl9aGLiHyNN1cU8r2XllBZXcODk4aR1i42t/WHQ4EuIlKH3M0lzFu5g2c+3ERauxR+f/1JHN+rQ6zL+loKdBGRWv66uIC7/rIUMxiT2ZGnrs1u0u/Mv6BAFxEJMX/NLv7tr8s4tX8XnrjmRDq0bhnrksKmQBcRCfHMhxvp3j6Vp649kXap8RPmoKtcRESO+ChvNws3lTB5VM+4C3NQoIuIAPD2qp1c+7uFDEhry00T+sW6nAZRoItIs/fioq3c9MdcurVL5YWbxtGpTatYl9QgYQW6mV1gZmvNLM/M7v2KNt8ys1VmttLMXohsmSIi0fHa0u3cN3s5w3u255dXjqRLjKaPi4R6PxQ1syRgOnAuUADkmNkcd18V0mYgcB8w3t33mFm3aBUsIhIJ5VXVPD5/A9Pn5zG4ezteuXU8rZLju9MinKtcxgJ57r4RwMxeAiYDq0La3ARMd/c9AO6+K9KFiohESk2Nc+Mfcvlw/W4uOaEnP598fNyHOYTX5dILyA9ZLgiuCzUIGGRmH5nZp2Z2QV0vZGbTzCzXzHKLiooaVrGIyDF6dck2Ply/m9sm9ud/p46Oq2vNv06k/ktKBgYCZwJTgWfMrGPtRu7+tLtnu3t2WlpahHYtIhK+iqoannx/A61bJXH3eYNjXU5EhRPo24DQyfIygutCFQBz3L3S3TcB6wgEvIhIk1FVXcOVT37Mup0HmTikG2YW65IiKpxAzwEGmlmWmbUCpgBzarV5lcC7c8ysK4EumI2RK1NE5Ni4O798ay1LC/bx4KRhTL96TKxLirh6A93dq4DbgXnAamCmu680s4fM7NJgs3lAsZmtAuYD97h7cbSKFhE5Wi8s2spT72/k4hHpfGd831iXExXm7jHZcXZ2tufm5sZk3yLSvLg7Zz/6Pm1TkvnbbePjuqvFzBa7e3Zd2+L/Oh0RkXo8/OZaNhaVMuWkzLgO8/oo0EUkoW3aXcqzH27kihMzmDq2d/1PiGMKdBFJWK8t3c4VT3xMSnIL7jl/cEK/OwcFuogkqE27S7lv9nLM4MFLhtG9fWqsS4o6TXAhIgln4cZivvtcDocrq/nfq09i4uDmMbyUAl1EEsrPXl/F7xZsom+X1jz3nbH07dom1iU1GgW6iCSMBet387sFm7hsdC9+fMkwOraOz3HNG0qBLiIJ4ZE31/D4exvo3fk4/vOy42ndqvnFW/M7YhFJOM9+uJHH39vA5WMy+I9JQ5tlmIMCXUTi3N+WbOPnb6xmwsCu/Pc3RyTEuOYNpUAXkbg1Y8EmHnp9FZ1at+R/p45u1mEOCnQRiUNV1TXM+GgT/zV3DWcP6cbDV4xsdh+A1kWBLiJxpWDPIX40axkfbyjmrCHd+J8po2ifmhgzDh0rBbqIxI3qGucb0z9m76EKHrh4KDeclpXwt/Mfjebd4SQicaX4YDm7D5Zz/al9uXFCP4V5LQp0EYkbO/aXATA2q3OMK2mawgp0M7vAzNaaWZ6Z3VvH9uvNrMjMlgS/box8qSLSXLk7W4pLeXz+BgD6d2sb44qapnr70M0sCZgOnEtgMugcM5vj7qtqNX3Z3W+PQo0i0ozNWlzAb95dz9aSQwBce3If+qcp0OsSzoeiY4E8d98IYGYvAZOB2oEuIhIxhyuquf+V5cz+fBtDerTj3guHcM7Q7gzQu/OvFE6g9wLyQ5YLgHF1tLvczE4H1gE/dPf82g3MbBowDSAzM/PoqxWRZsHduW7GInK2lHDHWQO4beIAUlsmxbqsJi9SH4q+BvR195HA28Af6mrk7k+7e7a7Z6elpUVo1yKSSIoOlHPlk5+waHMJP710OHedN1hhHqZwAn0bEDoRX0Zw3RHuXuzu5cHFZ4ETI1OeiDQnn23dw+TfLmBpwV5+cM5Arj25T6xLiivhdLnkAAPNLItAkE8Brg5tYGbp7l4YXLwUWB3RKkUk4VXXON/5fQ7JLYzZt4xnREaHWJcUd+oNdHevMrPbgXlAEjDD3Vea2UNArrvPAb5nZpcCVUAJcH0UaxaRBPTXxQXsO1zJI1eMVJg3kLl7THacnZ3tubm5Mdm3iDQt7k72z9+hdUoS835werMdzzwcZrbY3bPr2qY7RUUkptydb89YRHFpBd8Y1UthfgwU6CISU899vJkP1+/mlH5d+ME5g2JdTlxToItIzMxdXshPX1vFmMyOPHtdNkktNNjWsdDfNiISE4crqvm3WcsY1L0tL998Ci2T9P7yWOk7KCIx8cdPNnOgvIrvnz1IYR4h+i6KSKP7bOse/vvvawA4e2i3GFeTONTlIiKNprS8is+27uGBV1fQMsl4+PKRuq0/ghToIhJ1RQfKefjNNcxbsYMD5VUktTCmXz2GC47vEevSEooCXUSiqrS8ihv/mMvS/L1ceHwPpozNZFRGRzq01sTOkaZAF5GocXe+9dQnrNy+n0tO6Mn/Th0d65ISmgJdRKKisrqGJ9/bwMrt+7n7vEFMO71/rEtKeAp0EYk4d+fa3y3k040ljM7syA2n9aNVsi6qizYFuohEVGV1DbM/K+DTjSXccmZ/7jp3EMm6zrxRKNBFJGIOV1RzzbOf8tnWvYzJ7MhtEwcozBuRAl1Ejll1jTP7swJmfLSZ1YX7uffCIdx4WpbCvJGFFehmdgHwGIEJLp519198RbvLgVnASe6uwc5FElzRgXLmr93F8wu3sjR/L+1Tk/mvy0Zw9ThNAh8L9Qa6mSUB04FzgQIgx8zmuPuqWu3aAd8HFkajUBFpWqqqazj/1x9QUlpBWrsUHrl8JFdmZ2CmERNjJZx36GOBPHffCGBmLwGTgVW12v0MeBi4J6IVikiTk7frALf8+TNKSiv44TmDuHVifw2w1QSEcwZ6AfkhywXBdUeY2Rigt7u/EcHaRKQJyi85xLee+pTCfWU8NmUUt581QGHeRBzzh6Jm1gJ4lDAmhjazacA0gMxM9bGJxJO1Ow7wxHt5vL6skOQk45lvZzNhYFqsy5IQ4QT6NqB3yHJGcN0X2gHHA+8F+856AHPM7NLaH4y6+9PA0xCYJPoY6haRRlRd40x95lP2H67kstG9uPmM/gzo1jbWZUkt4QR6DjDQzLIIBPkU4OovNrr7PqDrF8tm9h5wt65yEUkMZZXV/PhvKykpreCxKaOYPKpX/U+SmKg30N29ysxuB+YRuGxxhruvNLOHgFx3nxPtIkWk8bk7CzeV8KNZy9hacojbJw7gkpE9Y12WfI2w+tDdfS4wt9a6B7+i7ZnHXpaIxNLK7ft46LVVLNxUQrd2Kcy4PpuzhnSPdVlSD90pKiJHVNc4f8nN597ZyzmuZRJ3nDWAW88cwHGtNKtQPFCgiwgAqwv38/Cba3hvbRGZnVvzx++OpW/XNrEuS46CAl2kmaupcV5fXshdM5dQ4+hGoTimQBdppnbuL+OJ9zYw+7MC9pdVkd2nE09deyJd2qbEujRpIAW6SDO071AlFz32IcWlFVw2uhen9u/CpJE91Vce5xToIs3I9r2Huf+V5by/rogah99dl83ZQ3X1SqJQoIs0E4u37OHW5xdzqLyab5/Sl3OHdWf8gK71P1HihgJdpBlYsH433/1DDl3atOL5m8YxMqNjrEuSKFCgiySw6hrn043F3PbCZxzXMokZ15/E0PT2sS5LokSBLpKAyquqef7TrTzz4UYK95WR1i6F6VePUZgnOAW6SIL5/UebmD5/A7sPlnNyv878+0VDOXtoN1q30q97otMZFkkAZZXV/M8763j1823s3F/Oqf278NiUUZzav4umhGtGFOgicWx/WSWLN+/hyfc3sHBTCecP786Zg7vxrezeJLVQkDc3CnSROFNVXcOCvN28trSQ15Ztp6KqhvapyfzkkmFcPz4r1uVJDCnQReLI4i17+I9XV7CqcD+tWyXxrewMLjo+ndGZnXSXpyjQReLBu6t3MuOjTXyUV0yn1i35+TeO54oTM0htqRCX/xNWoJvZBcBjBGYsetbdf1Fr+78CtwHVwEFgmruvinCtIs3KgbJK3l9XxOtLC3lz5Q46t2nF7RMHcMuZ/WmTovdi8s/q/akwsyRgOnAuUADkmNmcWoH9grs/GWx/KfAocEEU6hVJaDv2lfHG8kI+ytvNpxuLOVRRTetWSdw0IYu7zhusd+TytcL5b34skOfuGwHM7CVgMnAk0N19f0j7NoBHskiRRFZT47y1agdvLN/B68u24w790toweVRPzh/eg1P6dyElWUEu9Qsn0HsB+SHLBcC42o3M7DbgTqAVcFZdL2Rm04BpAJmZmUdbq0hCKS2v4t01u3h8fh5rdhygXWoyk0b25HtnDWBg93axLk/iUMQ64tx9OjDdzK4GHgCuq6PN08DTANnZ2XoXL81OVXUNb6/aSe6WPczMyedAeRX90trw66tGceGIHnonLscknEDfBvQOWc4IrvsqLwFPHEtRIonog3VFPDJvDSu27ScluQUjenXg+vF9ufD4dN0EJBERTqDnAAPNLItAkE8Brg5tYGYD3X19cPFiYD0iwuGKapYV7OXl3Hxmf7aN9qnJ/PKKkXxzTIZCXCKu3kB39yozux2YR+CyxRnuvtLMHgJy3X0OcLuZnQNUAnuoo7tFpDk5VFHFz15fzaufb+NwZTUA14zL5MeXDKdVsiZflugIqw/d3ecCc2utezDk8fcjXJdI3HF38nYd5OWcfN5ds4stxaVceWJvxvXrzOmD0uiqyZclynR3gkgE5O06yEOvr+KDdUUAnNKvC/92wRAuOL5HjCuT5kSBLtIAhyuq+Tx/D59sKGbW4gIK95WR2rIFd507iG+emEGvjsfFukRphhToIkfho7zd/Ocbq1m78wDVNYErb3t2SOUnlwzjohHpdGufGuMKpTlToIvUo6bGmb92F698vo25ywvp3j6VW87oz4l9OjEyowOd27TSJBLSJCjQRb5CSWkFsz8r4NUl21ixbT+dWrfkihMzuPmM/vRPaxvr8kT+iQJdJERldQ1zlxfy1sqdLMjbzb7DlQzp0Y77LxrKdaf21SWH0qQp0KXZK6us5v11RcxZup0P1hZxoLyKHu1TGZfVmRsn9GNsVudYlygSFgW6NEuV1TUsK9jLCwvz+ceanew5VEnXtilcOKIH5w3rwVlDutFCd3JKnFGgS7NSXeN8lLebn7y2ko1FpbQwOHtod64Zl8lpA7qSnKQuFYlfCnRpFrYUl/JyTj5vrdpJ3q6DtEsJTKp88ciepLXTHZySGBToktD2lFZw7YyFrNgWmIPlhIwOPDZlFGcN6Ua71JYxrk4kshTokpDW7jjAb/6xnvfXFnGoooobTsvi6nGZutxQEpoCXRLKzv1l3DNrGR+sK6JNqyTOP74HV5yYwan9u8a6NJGoU6BL3CurrOaNZYXMWlzAJxuLMYMbT8vi2lP60KdLm1iXJ9JoFOgSt7bvPcxDr63iH2t3UVFVQ+c2rfj2KX245ISenNRX145L86NAl7iy73Alf/pkM/NW7mT5tn0AXDa6FxePSOeMwWm01GWH0oyFFehmdgHwGIEZi55191/U2n4ncCNQBRQB33X3LRGuVZq5ZQV7mfL0pxyqqGZ0ZkfuvXAI5wztxoBu7WJdmkiTUG+gm1kSMB04FygAcsxsjruvCmn2OZDt7ofM7BbgEeCqaBQszcuOfWW8t3YXry8r5JONxSS3MF64cRynDtCHnCK1hfMOfSyQ5+4bAczsJWAycCTQ3X1+SPtPgX+JZJHSPL27eid3vPg5hyqqSWuXwk0T+nH12Ewyu7SOdWkiTVI4gd4LyA9ZLgDGfU37G4C/17XBzKYB0wAyMzPDLFGam/ySQzzx/gZm5uTTo0Mqf70lm8Hd22lsFZF6RPRDUTP7FyAbOKOu7e7+NPA0QHZ2tkdy3xL/Kqtr+MXf1/CHjzfTooVx8ch0Hrh4mG7NFwlTOIG+DegdspwRXPclZnYOcD9whruXR6Y8aQ72HqrguY83MzMnn+37yrh8TAb3nD+YHh00nZvI0Qgn0HOAgWaWRSDIpwBXhzYws9HAU8AF7r4r4lVKQvp4w25mLNjExxuKOVRRzan9u/Cfl43gzMFpmtJNpAHqDXR3rzKz24F5BC5bnOHuK83sISDX3ecAvwTaAn8J/iJudfdLo1i3xLG8XQf4+Rur+WBdEa1bJTN5VE+mnJTJCb07xro0kbgWVh+6u88F5tZa92DI43MiXJckoE83FrOsYC/T52/ADG6bOIAbJ/Sjw3Ea9VAkEnSnqETV9r2Hee7jzSzN38vCTSUADO/ZnieuOVGXH4pEmAJdIi6/5BB//GQz768rYt3Og7QwGNyjPdee3IdbzuxPeodU9ZGLRIECXSKi+GA5f/xkC/NW7mDNjgO0MDi1f1cuH5PBecN7kNVVox6KRJsCXY5JWWU1v5q3lj8v3EJZZQ2n9OvCPecP5qIR6QpxkUamQJcGKSmt4LF31jH7820cKKti8qie3HHWAA2UJRJDCnQJW1llNX/Jzef1ZYUs2lyCO3xjVE++ld1bg2WJNAEKdKlXeVU1uZv38PM3VrO6cD9ZXdtw25kDOH1QGmOzNJGESFOhQJevVFldw+PzN/C7BRvZX1ZF61ZJPH3tiZw3vEesSxOROijQ5Z+UVVazYP1unl2wkU83lnDO0O58Y3RPJgxIo0Nr3QQk0lQp0OVLlubv5eY/LWbH/jLapSZz93mDuP2sgbEuS0TCoECXI363YBM/e30VrVsl8ey3szl9UBqtkjVHp0i8UKAL7s6/v7KcFxflM6h7W/7w3bGkdzgu1mWJyFFSoDdze0orePjNNbyUk8/FI9P55RUjad1KPxYi8Ui/uc1Q0YFyPlhXxPy1u/h4QzElpRXcNCGLf79oqMZYEYljCvRmYse+Mt5ds5Pff7SZvF0HAejUuiVjMjtxw2lZujFIJAEo0BPcu6t38qu31rG6cD8AWV3bcP9FQxmR0YExmZ30oadIAgkr0M3sAuAxAjMWPevuv6i1/XTg18BIYIq7z4pwnXIUyquqeXPFDp7/dCuLNpcwqHtb7r1wCGcN6cbAbm3VrSKSoOoNdDNLAqYD5wIFQI6ZzXH3VSHNtgLXA3dHo0gJX/HBcm78Yy6fb91Lr47Hcfd5g7jhtH4c1yop1qWJSJSF8w59LJDn7hsBzOwlYDJwJNDdfXNwW00UapR65Jcc4rVl21m74wCfbiym+GAFt57Znx+cM0hdKiLNSDiB3gvID1kuAMY1ZGdmNg2YBpCZmdmQl5AQebsO8MR7G3lt6XYqqmvo2SGVYentuePsgYzJ7BTr8kSkkTXqh6Lu/jTwNEB2drY35r4TRVllNZ9v3cvbq3bywqItVFTVcNVJvblxQj/6p7WNdXkiEkPhBPo2oHfIckZwnTSS6hrnuY83M2/FDj7P30NltdPC4Oyh3Xng4qH06aKZgUQkvEDPAQaaWRaBIJ8CXB3VqgSA+Wt38dmWPcxfu4sV2/YzpEc7vjs+i7FZncnu01kjH4rIl9Qb6O5eZWa3A/MIXLY4w91XmtlDQK67zzGzk4BXgE7AJWb2U3cfHtXKE5C7U3SwnHdX72L2ZwXkbN4DQL+0Nnzv7IHcee6gGFcoIk1ZWH3o7j4XmFtr3YMhj3MIdMXIUdpYdJAP1+/m/XVFLNxYTGlFNQCZnVvzvbMH8q9n9NPYKiISFiVFjBwoq+T5hVt59K11R65QmTSyJwO7t2X8gK4M7t6OFi10A5CIhE+B3siKD5bzycZiHvzbSkpKKzilXxfuv3gow3u21x2cInJMFOhRtvtgOcsK9rJ59yHeXLGDRZtLAGifmqz5OUUkohToEVZSWsGH64t4Z3XgCpVtew8f2da783Hcee4gxg/owoheHXUXp4hElAI9AtydVz7fxoyPNrFy+37coVu7FE7K6sz1p/blhN4dyerahq5tW6lbRUSiRoHeQIX7DvP+2sAkETmb91BSWsGw9Pbcec4gxg/syqiMjvpQU0QalQI9DHtKK1hduJ9VhftZu+MAa3YcYPm2fQD06ngcEwd344zBaUwaka4QF5GYUaDXYd/hSmYtLiB3cwlL8vdSuK/syLaubVPo26U1t08cwKQT0hncvZ26UUSkSVCgA5XVNby/tojff7yJ5QX72F9WBUBGp+M4qW9nhvdsz9D0wFdau5QYVysiUrdmGehV1TXk7zlMzqYSPszbzbyVO6ioqqFtSjKXnJBORqfWjMzowISBabEuVUQkbM0i0N2ddTsPsn7XAT7ZUMzc5YXsOVQJQNe2rbj0hJ6cO6w7ZwxKI7WlZvYRkfiUsIFeVV3Dh3m7eWNZIe+t3cXugxUAtG6VxMn9ujBxcBon9unM0HT1gYtIYkiYQN+0u5S3V+1g1fb9LN66h+17y6iucTq2bkl2n85MHJLGqN4dGdCtLSnJehcuIoknrgN936FKXvm8gDeWFx4ZarZbuxSy+3biG6N60bdLGyadkK4AF5FmIW4DfWZOPg+9voqD5VUMTW/Pzaf34xujezE0vX2sSxMRiYm4DPQ1O/bzb7OXcXJWYKTC43t1iHVJIiIxF9boUGZ2gZmtNbM8M7u3ju0pZvZycPtCM+sb8UpDvPr5dpLMmH7NGIW5iEhQvYFuZknAdOBCYBgw1cyG1Wp2A7DH3QcA/wM8HOlCQ322ZQ+jMzvSuU2raO5GRCSuhPMOfSyQ5+4b3b0CeAmYXKvNZOAPwcezgLMtStcCzszJZ9HmEvpqpnsRkS8JJ9B7AfkhywXBdXW2cfcqYB/QpfYLmdk0M8s1s9yioqIGFdyxdUsuHpHOlLGZDXq+iEiiatQPRd39aeBpgOzsbG/Ia5w3vIdm+RERqUM479C3Ab1DljOC6+psY2bJQAegOBIFiohIeMIJ9BxgoJllmVkrYAowp1abOcB1wcdXAP9w9wa9AxcRkYapt8vF3avM7HZgHpAEzHD3lWb2EJDr7nOA3wF/MrM8oIRA6IuISCMKqw/d3ecCc2utezDkcRlwZWRLExGRo6Fp50VEEoQCXUQkQSjQRUQShAJdRCRBWKyuLjSzImBLA5/eFdgdwXJiScfSNOlYmiYdC/Rx9zonPI5ZoB8LM8t19+xY1xEJOpamScfSNOlYvp66XEREEoQCXUQkQcRroD8d6wIiSMfSNOlYmiYdy9eIyz50ERH5Z/H6Dl1ERGpRoIuIJIi4C/T6Jqxuasyst5nNN7NVZrbSzL4fXN/ZzN42s/XBfzsF15uZ/SZ4fMvMbExsj+DLzCzJzD43s9eDy1nBicHzghOFtwqub9SJwxvCzDqa2SwzW2Nmq83slHg8L2b2w+DP1goze9HMUuPpvJjZDDPbZWYrQtYd9Xkws+uC7deb2XV17StGx/LL4M/YMjN7xcw6hmy7L3gsa83s/JD1Dcs5d4+bLwLD924A+gGtgKXAsFjXVU/N6cCY4ON2wDoCk20/AtwbXH8v8HDw8UXA3wEDTgYWxvoYah3PncALwOvB5ZnAlODjJ4Fbgo9vBZ4MPp4CvBzr2us4lj8ANwYftwI6xtt5ITD94ybguJDzcX08nRfgdGAMsCJk3VGdB6AzsDH4b6fg405N5FjOA5KDjx8OOZZhwQxLAbKC2ZZ0LDkX8x/Io/xmnQLMC1m+D7gv1nUd5TH8DTgXWAukB9elA2uDj58Cpoa0P9Iu1l8EZqt6FzgLeD34S7U75If1yPkhMH7+KcHHycF2FutjCDmWDsEgtFrr4+q88H/z+XYOfp9fB86Pt/MC9K0Vgkd1HoCpwFMh67/ULpbHUmvbZcDzwcdfyq8vzs2x5Fy8dbmEM2F1kxX883Y0sBDo7u6FwU07gO7Bx035GH8N/AioCS53AfZ6YGJw+HKtYU0cHkNZQBHw+2AX0rNm1oY4Oy/uvg34FbAVKCTwfV5M/J6XLxzteWiS56cO3yXwFwZE4VjiLdDjlpm1Bf4K/MDd94du88B/w036+lEzmwTscvfFsa4lQpIJ/Gn8hLuPBkoJ/Gl/RJycl07AZAL/QfUE2gAXxLSoCIuH8xAOM7sfqAKej9Y+4i3Qw5mwuskxs5YEwvx5d58dXL3TzNKD29OBXcH1TfUYxwOXmtlm4CUC3S6PAR0tMDE4fLnWpj5xeAFQ4O4Lg8uzCAR8vJ2Xc4BN7l7k7pXAbALnKl7PyxeO9jw01fMDgJldD0wCrgn+BwVROJZ4C/RwJqxuUszMCMy5utrdHw3ZFDqx9nUE+ta/WP/t4Kf5JwP7Qv70jBl3v8/dM9y9L4Hv+z/c/RpgPoGJweGfj6PJThzu7juAfDMbHFx1NrCKODsvBLpaTjaz1sGftS+OIy7PS4ijPQ/zgPPMrFPwr5bzgutizswuINBVeam7HwrZNAeYErzyKAsYCCziWHIu1h+GNOADh4sIXCmyAbg/1vWEUe9pBP5cXAYsCX5dRKDf8l1gPfAO0DnY3oDpweNbDmTH+hjqOKYz+b+rXPoFfwjzgL8AKcH1qcHlvOD2frGuu47jGAXkBs/NqwSujoi78wL8FFgDrAD+ROCqibg5L8CLBPr/Kwn85XRDQ84Dgf7pvODXd5rQseQR6BP/4vf/yZD29wePZS1wYcj6BuWcbv0XEUkQ8dblIiIiX0GBLiKSIBToIiIJQoEuIpIgFOgiIglCgS4Jz8w+Psr2Z1pwNEmReKJAl4Tn7qfGugaRxqBAl4RnZgeD/55pZu/Z/42B/nzw7sovxp9eY2afAd8MeW6b4BjXi4KDeE0Orn/MzB4MPj7fzD4wM/0+SUwl199EJKGMBoYD24GPgPFmlgs8Q2B8mjzg5ZD29xO4Pf67wYkJFpnZOwSGNM0xsw+B3wAXuXsNIjGkdxTS3Cxy94Jg+C4hMHb1EAIDXK33wK3Tfw5pfx5wr5ktAd4jcOt8pgfG5LgJeBv4rbtvaLQjEPkKeocuzU15yONq6v8dMOByd19bx7YRBEYq7Bmh2kSOid6hiwQGtuprZv2Dy1NDts0D7gjpax8d/LcPcBeBLpwLzWxcI9YrUicFujR77l4GTAPeCH4ouitk88+AlsAyM1sJ/CxkSOS73X07gRH1njWz1EYuXeRLNNqiiEiC0Dt0EZEEoUAXEUkQCnQRkQShQBcRSRAKdBGRBKFAFxFJEAp0EZEE8f8BXvXgC44u6iQAAAAASUVORK5CYII=\n", "text/plain": ["
"]}, "metadata": {"needs_background": "light"}, "output_type": "display_data"}], "source": ["df.plot(x=\"index\", y=\"velo_jour\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## JOIN avec la table stations et les stations \"travail\"\n", "\n", "On trouve les arrondissements o\u00f9 les stations de v\u00e9lib sont les plus remplies en journ\u00e9e au centre de Paris."]}, {"cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numbernamelatlngvelo_jour
090100901 - PORT SOLF\u00c9RINO (STATION MOBILE)48.8613802.3244200.555188
190300903 - QUAI MAURIAC / PONT DE BERCY48.8371342.3743410.473137
290400904 - PLACE JOFFRE / ECOLE MILITAIRE48.8521362.3019610.357640
390500905 - CONCORDE/BERGES DE SEINE (STATION MOBILE)48.8631402.3166900.323395
490600906 - GARE DE L'EST48.8764202.3586300.117566
590800908 - PORT DU GROS CAILLOU (STATION MOBILE)48.8628802.3065200.501727
6100101001 - ILE DE LA CITE PONT NEUF48.8570922.3417480.486796
7100201002 - PLACE DU CHATELET48.8579402.3470100.355434
8100301003 - RIVOLI SAINT DENIS48.8591502.3476200.364749
9100401004 - MARGUERITE DE NAVARRE48.8598962.3467570.440549
\n", "
"], "text/plain": [" number name lat \\\n", "0 901 00901 - PORT SOLF\u00c9RINO (STATION MOBILE) 48.861380 \n", "1 903 00903 - QUAI MAURIAC / PONT DE BERCY 48.837134 \n", "2 904 00904 - PLACE JOFFRE / ECOLE MILITAIRE 48.852136 \n", "3 905 00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE) 48.863140 \n", "4 906 00906 - GARE DE L'EST 48.876420 \n", "5 908 00908 - PORT DU GROS CAILLOU (STATION MOBILE) 48.862880 \n", "6 1001 01001 - ILE DE LA CITE PONT NEUF 48.857092 \n", "7 1002 01002 - PLACE DU CHATELET 48.857940 \n", "8 1003 01003 - RIVOLI SAINT DENIS 48.859150 \n", "9 1004 01004 - MARGUERITE DE NAVARRE 48.859896 \n", "\n", " lng velo_jour \n", "0 2.324420 0.555188 \n", "1 2.374341 0.473137 \n", "2 2.301961 0.357640 \n", "3 2.316690 0.323395 \n", "4 2.358630 0.117566 \n", "5 2.306520 0.501727 \n", "6 2.341748 0.486796 \n", "7 2.347010 0.355434 \n", "8 2.347620 0.364749 \n", "9 2.346757 0.440549 "]}, "execution_count": 15, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT C.number, name, lat, lng, velo_jour FROM \n", "(\n", " -- requ\u00eate de la partie pr\u00e9c\u00e9dente\n", " SELECT number, SUM(distribution_temporelle) AS velo_jour\n", " FROM (\n", " -- requ\u00eate de l'exercice 4\n", " SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", " FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", " ) AS A\n", " JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", " ) AS B\n", " ON A.number == B.number\n", " )\n", " WHERE heure >= 10 AND heure <= 16 \n", " GROUP BY number\n", ") AS C\n", "INNER JOIN stations\n", "ON C.number == stations.number "]}, {"cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": []}], "metadata": {"kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}, "language_info": {"codemirror_mode": {"name": "ipython", "version": 3}, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.7"}}, "nbformat": 4, "nbformat_minor": 2}