« Pattern matching » avec Oracle
Depuis le portail internet OECD.Stat, l’OCDE met à la disposition du grand public une multitude d’indicateurs économiques et sociétaux. Les informations sont présentées via de nombreux tableaux de bords facilement paramétrables et dont les données peuvent être extraites.
Pour ce billet, je me suis intéressé à l’évolution du PIB des pays de la zone Euro. Plus précisément, je me suis interrogé sur la fréquence de survenue de phénomènes de récession.
Selon la page Wikipedia consacrée, une récession économique correspond à 2 ou 3 trimestres consécutifs de contraction du PIB.
C’est un bon cas d’utilisation de la fonction MATCH_RECOGNIZE (introduite par Oracle 12c) qui permet de réaliser simplement la détection de séquences au sein de séries temporelles. Il s’agit d’une fonction analytique opérant sur un dataset par une approche fenêtrée pour repérer des successions de lignes correspondant à un « profil » spécifié via des expressions régulières.
Les données utilisées ci-dessous proviennent du tableau de bord « Comptes nationaux trimestriels : PIB historique – approche par les dépenses » dont j’ai limité le périmètre aux pays de l’Eurozone (hors Malte et Chypre) et à la période 2000 à 2015: http://stats.oecd.org//Index.aspx?QueryId=69551
Chargement des données
Une table externe est utilisée pour accéder les données récupérées depuis OECD.Stat dans un fichier csv:
SQL>
SQL> CREATE TABLE exttab_pib_evol
2 (
3 pays VARCHAR2 (50),
4 sujet VARCHAR2 (200),
5 mesure VARCHAR2 (200),
6 frequence VARCHAR2 (40),
7 periode VARCHAR2 (20),
8 unit VARCHAR2 (50),
9 powercode VARCHAR2 (20),
10 reference_period VARCHAR2(20),
11 valeur NUMBER,
12 flags VARCHAR2 (30)
13 )
14 ORGANIZATION EXTERNAL
15 (
16 TYPE oracle_loader
17 DEFAULT DIRECTORY datadir
18 ACCESS PARAMETERS
19 (
20 RECORDS DELIMITED BY NEWLINE
21 SKIP 1
22 FIELDS TERMINATED BY '|'
23 OPTIONALLY ENCLOSED BY '"' AND '"'
24 MISSING FIELD VALUES ARE NULL
25 )
26 LOCATION ('QNA_01122015114635142.csv')
27 )
28 REJECT LIMIT 0
29 NOPARALLEL;
Table created.
SQL>
Le « décodage » des périodes d’intérêt (« T3 – 2014 » en « 3 » et « 2014 ») est réalisé par l’intermédiaire d’une vue:
SQL> SQL> CREATE OR REPLACE VIEW pib_evol 2 AS 3 SELECT pays, 4 TO_NUMBER (SUBSTR (periode, INSTR (periode, '-') + 1)) annee, 5 TO_NUMBER (SUBSTR (periode, 2, 1)) trimestre, 6 TO_NUMBER ( 7 valeur * CASE powercode WHEN 'milliards' THEN 1000 ELSE 1 END) 8 pib 9 FROM exttab_pib_evol; View created. SQL>
On peut jeter un œil sur les informations ainsi transformées:
SQL> SQL> column PAYS format a20 SQL> set pages 100 SQL> SQL> SELECT * 2 FROM pib_evol 3 FETCH FIRST 10 ROWS ONLY; PAYS ANNEE TRIMESTRE PIB -------------------- ---------- ---------- ---------- Autriche 2000 1 297275.421 Autriche 2000 2 301052.975 Autriche 2000 3 302616.903 Autriche 2000 4 307202.832 Autriche 2001 1 306744.543 Autriche 2001 2 305478.39 Autriche 2001 3 304888.539 Autriche 2001 4 306391.908 Autriche 2002 1 309353.755 Autriche 2002 2 310525.205 10 rows selected. SQL>
Détection de séquences
Ici, on va donc chercher les séquences de 3 (ou plus) baisses successives du PIB d’un pays. On considère qu’une baisse correspond à une diminution de plus de 0.1% du PIB. Entre 0 et 0.1%, il s’agit plutôt d’une stagnation.
La spécification du mécanisme de fenêtrage est tout à fait standard (PARTITION BY … ORDER BY…). La clause MEASURES permet d’indiquer les informations à restituer – ici, on renvoie l’année et le trimestre de la première survenue (opérateur FIRST) de chaque séquence, le nombre d’éléments de la séquence, le numéro d’ordre de la séquence (fonction MATCH_NUMBER) ainsi que l’évolution du PIB sur l’ensemble de la séquence (en s’appuyant sur les opérateurs FIRST et LAST).
On indique aussi comment la restitution doit être opérée (une ligne par séquence) et ou reprendre la recherche de nouvelle séquence (après la dernière ligne de la séquence courante).
La clause DEFINE permet de définir une ou plusieurs mesures sur lesquelles on va préciser le PATTERN que l’on recherche. Ici, la mesure BAISSE est définie comme une diminution de plus de 0.1% entre une ligne et sa prédécesseur « prev () ». Les séquences recherchées contiennent 3 ou plus occurrences consécutives de la mesure BAISSE:
SQL>
SQL> SELECT *
2 FROM pib_evol MATCH_RECOGNIZE (
3 PARTITION BY pays
4 ORDER BY annee, trimestre
5 MEASURES FIRST (annee) annee,
6 FIRST (trimestre) trimestre,
7 COUNT (*) nb_trim,
8 match_number () recess#,
9 ROUND (
10 100
11 * (FIRST (baisse.pib) - LAST (baisse.pib))
12 / FIRST (baisse.pib),
13 1) pib_reduc
14 ONE ROW PER MATCH
15 AFTER MATCH SKIP PAST LAST ROW
16 PATTERN (baisse{3, })
17 DEFINE baisse AS ROUND (
18 100
19 * (baisse.pib - prev (baisse.pib))
20 / prev (baisse.pib),
21 1) <= -0.1)
22 ORDER BY 1 ASC;
PAYS ANNEE TRIMESTRE NB_TRIM RECESS# PIB_REDUC
-------------------- ---------- ---------- ---------- ---------- ----------
Allemagne 2008 2 4 1 6.7
Autriche 2001 1 3 1 .6
Autriche 2008 2 5 2 5.1
Belgique 2001 2 3 1 .3
Belgique 2008 3 4 2 3.3
Espagne 2008 3 6 1 3.9
Espagne 2011 1 10 2 5
Estonie 2008 3 5 1 17.9
Finlande 2008 4 3 1 7
Finlande 2012 2 3 2 1.3
France 2008 2 5 1 3.5
Grèce 2008 2 4 1 6.1
Grèce 2010 1 13 2 21.9
Irlande 2008 1 8 1 9.3
Italie 2001 2 3 1 .5
Italie 2008 2 5 2 6.9
Italie 2011 3 8 3 4.7
Italie 2014 1 3 4 .3
Lettonie 2007 4 8 1 21.8
Lettonie 2010 1 3 2 .8
Lituanie 2008 3 4 1 14.5
Luxembourg 2002 3 3 1 1.8
Luxembourg 2008 2 5 2 8.3
Pays-Bas 2008 3 4 1 4.2
Portugal 2002 2 3 1 1.2
Portugal 2008 2 4 2 3.8
Portugal 2010 4 9 3 7.8
Slovénie 2008 3 4 1 9.2
Slovénie 2011 3 7 2 4.3
29 rows selected.
SQL>
On voit donc que la France n’a connu qu’un seul épisode récessif à partir du second trimestre 2008 pendant 5 trimestres. La baisse du PIB sur cette période a été de 3.5%.