Etude de distribution avec Oracle

L’ONU publie via le portail FAOStat une vaste collection de données relatives à l’alimentation et à l’agriculture. Cela permet par exemple d’appréhender les contributions agricoles nationales à l’échelon mondial.

Dans ce billet, j’utilise les données « Production Quantity » de l’année 2014 pour tout les types de cultures et tous les pays. L’extraction est réalisée depuis le site directement au format CSV.
A noter que j’ai utilisé la version anglaise du site car sinon le chargement des caractères accentués me posait des problèmes.

Le fichier résultant est accessible ici.

Les données sont d’abord chargées en base. Pour cela, j’utilise l’utilitaire SQLCl (extension ligne de commande SQLDeveloper) qui dispose d’une fonction LOAD très simple:

RAFA@s1401037[db121]> help LOAD

LOAD
-----

Loads a comma separated value (csv) file into a table.
The first row of the file must be a header row. The columns in the header row must match the columns defined on the table.

The columns must be delimited by a comma and may optionally be enclosed in double quotes.
Lines can be terminated with standard line terminators for windows, unix or mac.
File must be encoded UTF8.

The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.

LOAD [schema.]table_name[@db_link] file_name

RAFA@s1401037[db121]>

La table de destination est créée en respectant précisément (casse et espaces) les informations de la ligne d’entête:

SQLcl: Release 4.2.0.15.349.0706 RC on Tue Feb 02 18:53:21 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Username? (''?) rafa@localhost/STATPDB
Password? (**********?) ****
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


RAFA@s1401037[db121]>
RAFA@s1401037[db121]>
RAFA@s1401037[db121]> CREATE TABLE production_agricole
  2  (
  3     "Domain Code"   VARCHAR2 (10),
  4     "Domain"        VARCHAR2 (20),
  5     "AreaCode"      NUMBER,
  6     "AreaName"      VARCHAR2 (50),
  7     "ElementCode"   NUMBER,
  8     "ElementName"   VARCHAR2 (30),
  9     "ItemCode"      NUMBER,
 10     "ItemName"      VARCHAR2 (50),
 11     "Year"          NUMBER,
 12     "Value"         NUMBER,
 13     "Flag"          VARCHAR2 (10),
 14     "FlagD"         VARCHAR2 (1000)
 15  );


Table PRODUCTION_AGRICOLE created.

RAFA@s1401037[db121]>

J’invoque ensuite la commande LOAD:

RAFA@s1401037[db121]> load PRODUCTION_AGRICOLE C:\RTI\Stats\Distrib\7686bc51-619f-48e0-a01b-2dbf3be7e575.csv

--Number of rows processed: 4,919
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
RAFA@s1401037[db121]>
RAFA@s1401037[db121]> commit;


Commit complete.

RAFA@s1401037[db121]>

De manière à travailler avec des champs sans contraintes de casse, une vue est créée sur la table PRODUCTION_AGRICOLE de manière à ne présenter que les champs d’intérêt:

RAFA@s1401037[db121]> CREATE OR REPLACE VIEW v_production_agricole
  2  AS
  3     SELECT "AreaName" pays,
  4            "ItemName" culture,
  5            "Year" annee,
  6            "Value" quantite
  7       FROM production_agricole
  8      WHERE "Value" IS NOT NULL;


View V_PRODUCTION_AGRICOLE created.

RAFA@s1401037[db121]>

On peut ensuite analyser ces chiffres à l’aide de fonctions analytiques qui permettent de réaliser des sous-groupes (partitions) au sein du dataset par année et type de culture.
Ici, j’utilise les fonctions analytiques :

  • RATIO_TO_REPORT qui permet de connaitre au sein de chaque partition (c’est à dire pour un type de culture), pour un pays donné, la quote-part que représente sa production par rapport à la production mondiale
  • PERCENT_RANK qui permet de connaitre au sein de chaque partition, pour un pays donné, le pourcentage de pays dont la production est supérieure pour le type de culture concerné
  • RANK qui permet de connaitre au sein de chaque partition, pour un pays donné, son classement en termes de quantité produite
  • SUM (dans sa version analytique) de manière à réaliser au sein de chaque partition une somme roulante des pourcentages des contributions unitaires

Ces dernières sont embarquées dans la vue v_distribution_production:

RAFA@s1401037[db121]>
RAFA@s1401037[db121]> CREATE OR REPLACE VIEW v_distribution_production
  2  AS
  3     SELECT culture,
  4            pays,
  5            annee,
  6            quantite,
  7            pct_contrib,
  8            pct_rank,
  9            rk,
 10            SUM (pct_contrib)
 11               OVER (PARTITION BY culture, annee ORDER BY quantite DESC)
 12               roll_pct_contrib
 13       FROM (SELECT culture,
 14                    pays,
 15                    annee,
 16                    quantite,
 17                      100
 18                    * RATIO_TO_REPORT (quantite)
 19                         OVER (PARTITION BY culture, annee)
 20                       pct_contrib,
 21                      100
 22                    * CUME_DIST ()
 23                      OVER (PARTITION BY culture, annee ORDER BY quantite DESC)
 24                       pct_rank,
 25                    RANK ()
 26                    OVER (PARTITION BY culture, annee ORDER BY quantite DESC)
 27                       rk
 28               FROM v_production_agricole);


View V_DISTRIBUTION_PRODUCTION created.

RAFA@s1401037[db121]>

La requête suivante permet de connaitre l’état de la production Française pour chaque type de culture.
Pour le blé, par exemple, la France est au 5eme rang mondial. Seuls 4.03% des pays produisent davantage et la production Française correspond à 5.34% de la production mondiale:

RAFA@s1401037[db121]>   SELECT culture,
  2           quantite,
  3           TRUNC (pct_contrib, 2) pct_contrib,
  4           TRUNC (pct_rank, 2) pct_rank,
  5           rk
  6      FROM v_distribution_production
  7     WHERE pays = 'France'
  8  ORDER BY quantite DESC;


CULTURE                        QUANTITE  PCT_CONTRIB  PCT_RANK  RK
Cereals,Total                  56151227  2            4.37      8
Cereals (Rice Milled Eqv)      56123455  2.19         4.37      8
Wheat                          38966600  5.34         4.03      5
Sugar beet                     37630688  14.1         1.85      1
Coarse Grain, Total            17101227  1.28         8.33      15
Barley                         11770680  8.15         1.96      2
Roots and Tubers,Total         8054500   0.96         10        20
Potatoes                       8054500   2.09         5.06      8
Rapeseed                       5522980   7.78         7.69      5
Oilcakes Equivalent            4301403   1.17         5.64      11
Oilcrops Primary               2820272   1.42         6.06      12
Triticale                      2022500   11.84        10.52     4
Maize                          1854180   0.18         25.14     42
Sunflower seed                 1559100   3.77         12.32     9
Pulses,Total                   842259    1.08         10.91     19
Peas, dry                      512094    4.51         6.18      6
Oats                           443528    1.93         21.05     16
Sorghum                        397936    0.58         17.85     20
Broad beans, horse beans, dry  278645    6.41         6.55      4
Soybeans                       227262    0.07         22.1      21
Grain, mixed                   221700    5.92         8.33      2
Rye                            128153    0.83         25        15
Buckwheat                      111300    5.41         13.79     4
Cereals, nes                   111250    1.62         12.28     7
Rice, paddy                    83400     0.01         66.1      78
Hempseed                       57162     73.01        10        1
Millet                         40000     0.14         40.47     33
Olives                         23700     0.15         60        24
Linseed                        23319     0.9          19.6      10
Lentils                        23000     0.47         27.45     14
Oilseeds nes                   18891     0.5          36.84     21
Lupins                         15020     1.53         37.5      9
Mustard seed                   14000     2.03         34.78     8
Poppy seed                     8000      9.4          31.25     5
Beans, dry                     7500      0.02         68        85
Pulses, nes                    6000      0.11         52.72     58


 36 row selected

RAFA@s1401037[db121]>

Il est aussi possible de réaliser des analyses sur le principe du 80/20 de Pareto.
Dans la requête suivante, pour chaque type de culture, je cherche le pourcentage de pays producteurs qui correspondent à 80% de la production mondiale:

RAFA@s1401037[db121]>      SELECT culture,
  2              quantite,
  3              TRUNC (pct_rank, 2) pct_rank,
  4              TRUNC (roll_pct_contrib, 2) roll_pct_contrib
  5         FROM (SELECT a.*,
  6                      ROW_NUMBER ()
  7                      OVER (PARTITION BY culture
  8                            ORDER BY ABS (roll_pct_contrib - 80))
  9                         r
 10                 FROM v_distribution_production a)
 11        WHERE r = 1
 12     ORDER BY quantite DESC
 13  FETCH FIRST 20 ROWS ONLY;


CULTURE                    QUANTITE  PCT_RANK  ROLL_PCT_CONTRIB
Oil, palm fruit            96066760  4.65      81.22
Soybeans                   53397715  3.15      80.46
Sugar cane                 32464000  7.84      79.65
Cereals,Total              26739008  11.47     80.1
Rice, paddy                26423300  5.93      79.73
Cereals (Rice Milled Eqv)  23587258  11.47     79.74
Oil, palm                  19667016  13.33     92.78
Coarse Grain, Total        13025910  10        80.07
Wheat                      11628670  12.09     79.75
Maize                      11486800  6.58      80.51
Oilcakes Equivalent        8057933   4.1       79.44
Yams                       7119000   3.33      76.46
Roots and Tubers,Total     6615950   13        80.29
Cassava                    4910810   12.5      79.33
Palm kernels               4888756   4.65      80.99
Sugar beet                 4805559   22.22     80.45
Potatoes                   4166000   12.65     80.19
Seed cotton                3400200   6.89      79.05
Grain, mixed               2922436   4.16      78.13
Oilcrops Primary           2820272   6.06      80.49


 20 row selected

RAFA@s1401037[db121]>

On peut noter que pour la plupart des productions ci-dessus, on observe une concentration de la production sur un faible pourcentage de pays: 80% de la production mondiale de Soja émane de 3% des pays producteurs, ~6% pour le riz etc…

A l’inverse, à partir d’une distribution existante, les fonctions PERCENTILE_CONT & PERCENTILE_DISC permettent de réaliser une extrapolation de quelle devrait être la quantité produite pour être dans un percentile donné.

Dans l’exemple ci-dessous, on cherche à savoir à quel niveau de production de Maïs et d’Avoine il faut se situer pour produire plus que 85% des pays?

RAFA@s1401037[db121]> SELECT DISTINCT
  2         culture,
  3         PERCENTILE_CONT (0.85)
  4            WITHIN GROUP (ORDER BY quantite)
  5            OVER (PARTITION BY culture)
  6            Qty
  7    FROM v_distribution_production a
  8   WHERE culture IN ('Maize', 'Oats');


CULTURE  QTY
Maize    4692300
Oats     611826.5

RAFA@s1401037[db121]>

 

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

one × = two