.. _td1asqlcorrectionrst:

=====================================
1A.soft - Notions de SQL - correction
=====================================


.. only:: html

    **Links:** :download:`notebook <td1a_sql_correction.ipynb>`, :downloadlink:`html <td1a_sql_correction2html.html>`, :download:`python <td1a_sql_correction.py>`, :downloadlink:`slides <td1a_sql_correction.slides.html>`, :githublink:`GitHub|_doc/notebooks/td1a_soft/td1a_sql_correction.ipynb|*`


Correction des exercices du premier notebooks relié au SQL.

.. code:: ipython3

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





.. code:: ipython3

    %matplotlib inline

Recupérer les données
---------------------

.. code:: ipython3

    import os
    if not os.path.exists("td8_velib.db3"):
        from pyensae.datasource import download_data
        download_data("td8_velib.zip", website = 'xd')
        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::
      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
      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  935 lines
       count_types  {0: {<class 'str'>: 934}, 1: {<class 'str'>: 934}, 2: {<class 'float'>: 934}, 3: {<class 'float'>: 934}, 4: {<class 'str'>: 934}, 5: {<class 'int'>: 934}}
       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'>, 152)), 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'>, 152)), 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


.. code:: ipython3

    %load_ext pyensae

.. code:: ipython3

    %SQL_connect td8_velib.db3




.. parsed-literal::
    <pyensae.sql.sql_interface_database.InterfaceSQLDatabase at 0x206823f4100>



Exercice 1
----------

.. code:: ipython3

    %%SQL
    SELECT COUNT(*) FROM (
        SELECT DISTINCT last_update FROM td8_velib
    ) ;






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>COUNT(*)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>15294</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    %%SQL
    SELECT MIN(last_update), MAX(last_update) FROM td8_velib ;






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>MIN(last_update)</th>
          <th>MAX(last_update)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2013-07-22 09:00:19</td>
          <td>2013-09-13 11:25:19</td>
        </tr>
      </tbody>
    </table>
    </div>



Exercice 2
----------

.. code:: ipython3

    %%SQL
    SELECT number, COUNT(*) AS nb 
    FROM td8_velib
    WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'
    GROUP BY number
    ORDER BY nb DESC






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>number</th>
          <th>nb</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>42704</td>
          <td>864</td>
        </tr>
        <tr>
          <th>1</th>
          <td>41101</td>
          <td>864</td>
        </tr>
        <tr>
          <th>2</th>
          <td>35008</td>
          <td>864</td>
        </tr>
        <tr>
          <th>3</th>
          <td>35005</td>
          <td>864</td>
        </tr>
        <tr>
          <th>4</th>
          <td>35003</td>
          <td>864</td>
        </tr>
        <tr>
          <th>5</th>
          <td>33012</td>
          <td>864</td>
        </tr>
        <tr>
          <th>6</th>
          <td>33011</td>
          <td>864</td>
        </tr>
        <tr>
          <th>7</th>
          <td>33006</td>
          <td>864</td>
        </tr>
        <tr>
          <th>8</th>
          <td>33005</td>
          <td>864</td>
        </tr>
        <tr>
          <th>9</th>
          <td>32603</td>
          <td>864</td>
        </tr>
      </tbody>
    </table>
    </div>



Exercice 3 : plage horaires de cinq minutes où il n’y a aucun vélo disponible
-----------------------------------------------------------------------------

.. code:: ipython3

    %%SQL
    SELECT nb, COUNT(*) AS nb_station
    FROM (
      -- requête de l'exercice précédent
      SELECT number, COUNT(*) AS nb 
      FROM td8_velib
      WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'
      GROUP BY number
    )
    GROUP BY nb






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>nb</th>
          <th>nb_station</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1</td>
          <td>7</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2</td>
          <td>7</td>
        </tr>
        <tr>
          <th>2</th>
          <td>3</td>
          <td>5</td>
        </tr>
        <tr>
          <th>3</th>
          <td>4</td>
          <td>6</td>
        </tr>
        <tr>
          <th>4</th>
          <td>5</td>
          <td>6</td>
        </tr>
        <tr>
          <th>5</th>
          <td>6</td>
          <td>5</td>
        </tr>
        <tr>
          <th>6</th>
          <td>7</td>
          <td>8</td>
        </tr>
        <tr>
          <th>7</th>
          <td>8</td>
          <td>9</td>
        </tr>
        <tr>
          <th>8</th>
          <td>9</td>
          <td>1</td>
        </tr>
        <tr>
          <th>9</th>
          <td>10</td>
          <td>7</td>
        </tr>
      </tbody>
    </table>
    </div>



Exercice 4 : distribution horaire par station et par tranche de 5 minutes
-------------------------------------------------------------------------

.. code:: ipython3

    %%SQL
    SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot  AS distribution_temporelle
    FROM (
      SELECT number, heure, minute, SUM(available_bikes) AS nb_velo
      FROM td8_velib
      WHERE last_update >= '2013-09-10 11:30:19'
      GROUP BY heure, minute, number
    ) AS A
    JOIN (
      SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot
      FROM td8_velib
      WHERE last_update >= '2013-09-10 11:30:19'
      GROUP BY number
    ) AS B
    ON A.number == B.number
    --WHERE A.number in (8001, 8003, 15024, 15031)  -- pour n'afficher que quelques stations
    ORDER BY A.number, A.heure, A.minute






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>number</th>
          <th>heure</th>
          <th>minute</th>
          <th>distribution_temporelle</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>901</td>
          <td>0</td>
          <td>0</td>
          <td>0.001104</td>
        </tr>
        <tr>
          <th>1</th>
          <td>901</td>
          <td>0</td>
          <td>5</td>
          <td>0.001104</td>
        </tr>
        <tr>
          <th>2</th>
          <td>901</td>
          <td>0</td>
          <td>10</td>
          <td>0.001104</td>
        </tr>
        <tr>
          <th>3</th>
          <td>901</td>
          <td>0</td>
          <td>15</td>
          <td>0.001104</td>
        </tr>
        <tr>
          <th>4</th>
          <td>901</td>
          <td>0</td>
          <td>20</td>
          <td>0.001104</td>
        </tr>
        <tr>
          <th>5</th>
          <td>901</td>
          <td>0</td>
          <td>25</td>
          <td>0.000946</td>
        </tr>
        <tr>
          <th>6</th>
          <td>901</td>
          <td>0</td>
          <td>30</td>
          <td>0.000946</td>
        </tr>
        <tr>
          <th>7</th>
          <td>901</td>
          <td>0</td>
          <td>35</td>
          <td>0.000946</td>
        </tr>
        <tr>
          <th>8</th>
          <td>901</td>
          <td>0</td>
          <td>40</td>
          <td>0.000946</td>
        </tr>
        <tr>
          <th>9</th>
          <td>901</td>
          <td>0</td>
          <td>45</td>
          <td>0.000946</td>
        </tr>
      </tbody>
    </table>
    </div>



Zones de travail et zones de résidence
--------------------------------------

.. code:: ipython3

    %%SQL --df=df
    SELECT number, SUM(distribution_temporelle) AS velo_jour
    FROM (
      -- requête de l'exercice 4
        SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot  AS distribution_temporelle
        FROM (
            SELECT number, heure, minute, SUM(available_bikes) AS nb_velo
            FROM td8_velib
            WHERE last_update >= '2013-09-10 11:30:19'
            GROUP BY heure, minute, number
        ) AS A
        JOIN (
            SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot
            FROM td8_velib
            WHERE last_update >= '2013-09-10 11:30:19'
            GROUP BY number
        ) AS B
        ON A.number == B.number
    )
    WHERE heure >= 10 AND heure <= 16 
    GROUP BY number






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>number</th>
          <th>velo_jour</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>901</td>
          <td>0.555188</td>
        </tr>
        <tr>
          <th>1</th>
          <td>903</td>
          <td>0.473137</td>
        </tr>
        <tr>
          <th>2</th>
          <td>904</td>
          <td>0.357640</td>
        </tr>
        <tr>
          <th>3</th>
          <td>905</td>
          <td>0.323395</td>
        </tr>
        <tr>
          <th>4</th>
          <td>906</td>
          <td>0.117566</td>
        </tr>
        <tr>
          <th>5</th>
          <td>908</td>
          <td>0.501727</td>
        </tr>
        <tr>
          <th>6</th>
          <td>1001</td>
          <td>0.486796</td>
        </tr>
        <tr>
          <th>7</th>
          <td>1002</td>
          <td>0.355434</td>
        </tr>
        <tr>
          <th>8</th>
          <td>1003</td>
          <td>0.364749</td>
        </tr>
        <tr>
          <th>9</th>
          <td>1004</td>
          <td>0.440549</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df = df.sort_values("velo_jour").reset_index()
    df["index"] = range(0, df.shape[0])
    df.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>index</th>
          <th>number</th>
          <th>velo_jour</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>0</td>
          <td>18113</td>
          <td>0.000000</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>35013</td>
          <td>0.000000</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2</td>
          <td>10010</td>
          <td>0.000000</td>
        </tr>
        <tr>
          <th>3</th>
          <td>3</td>
          <td>20037</td>
          <td>0.007382</td>
        </tr>
        <tr>
          <th>4</th>
          <td>4</td>
          <td>20119</td>
          <td>0.010623</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df.plot(x="index", y="velo_jour")




.. parsed-literal::
    <AxesSubplot:xlabel='index'>




.. image:: td1a_sql_correction_19_1.png


JOIN avec la table stations et les stations “travail”
-----------------------------------------------------

On trouve les arrondissements où les stations de vélib sont les plus
remplies en journée au centre de Paris.

.. code:: ipython3

    %%SQL
    SELECT C.number, name, lat, lng, velo_jour FROM 
    (
      -- requête de la partie précédente
        SELECT number, SUM(distribution_temporelle) AS velo_jour
        FROM (
            -- requête de l'exercice 4
            SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot  AS distribution_temporelle
            FROM (
                SELECT number, heure, minute, SUM(available_bikes) AS nb_velo
                FROM td8_velib
                WHERE last_update >= '2013-09-10 11:30:19'
                GROUP BY heure, minute, number
            ) AS A
            JOIN (
                SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot
                FROM td8_velib
                WHERE last_update >= '2013-09-10 11:30:19'
                GROUP BY number
            ) AS B
            ON A.number == B.number
        )
        WHERE heure >= 10 AND heure <= 16 
        GROUP BY number
    ) AS C
    INNER JOIN stations
    ON C.number == stations.number 






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>number</th>
          <th>name</th>
          <th>lat</th>
          <th>lng</th>
          <th>velo_jour</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>901</td>
          <td>00901 - PORT SOLFÉRINO (STATION MOBILE)</td>
          <td>48.861380</td>
          <td>2.324420</td>
          <td>0.555188</td>
        </tr>
        <tr>
          <th>1</th>
          <td>903</td>
          <td>00903 - QUAI MAURIAC  / PONT DE BERCY</td>
          <td>48.837134</td>
          <td>2.374341</td>
          <td>0.473137</td>
        </tr>
        <tr>
          <th>2</th>
          <td>904</td>
          <td>00904 - PLACE JOFFRE / ECOLE MILITAIRE</td>
          <td>48.852136</td>
          <td>2.301961</td>
          <td>0.357640</td>
        </tr>
        <tr>
          <th>3</th>
          <td>905</td>
          <td>00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE)</td>
          <td>48.863140</td>
          <td>2.316690</td>
          <td>0.323395</td>
        </tr>
        <tr>
          <th>4</th>
          <td>906</td>
          <td>00906 - GARE DE L'EST</td>
          <td>48.876420</td>
          <td>2.358630</td>
          <td>0.117566</td>
        </tr>
        <tr>
          <th>5</th>
          <td>908</td>
          <td>00908 - PORT DU GROS CAILLOU (STATION MOBILE)</td>
          <td>48.862880</td>
          <td>2.306520</td>
          <td>0.501727</td>
        </tr>
        <tr>
          <th>6</th>
          <td>1001</td>
          <td>01001 - ILE DE LA CITE PONT NEUF</td>
          <td>48.857092</td>
          <td>2.341748</td>
          <td>0.486796</td>
        </tr>
        <tr>
          <th>7</th>
          <td>1002</td>
          <td>01002 - PLACE DU CHATELET</td>
          <td>48.857940</td>
          <td>2.347010</td>
          <td>0.355434</td>
        </tr>
        <tr>
          <th>8</th>
          <td>1003</td>
          <td>01003 - RIVOLI SAINT DENIS</td>
          <td>48.859150</td>
          <td>2.347620</td>
          <td>0.364749</td>
        </tr>
        <tr>
          <th>9</th>
          <td>1004</td>
          <td>01004 - MARGUERITE DE NAVARRE</td>
          <td>48.859896</td>
          <td>2.346757</td>
          <td>0.440549</td>
        </tr>
      </tbody>
    </table>
    </div>