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]>