« 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%.

 

Laisser un commentaire

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

− 1 = three