{"id":490,"date":"2015-12-08T06:00:22","date_gmt":"2015-12-08T06:00:22","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=490"},"modified":"2015-12-08T06:00:22","modified_gmt":"2015-12-08T06:00:22","slug":"pattern-matching-avec-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=490","title":{"rendered":"\u00ab\u00a0Pattern matching\u00a0\u00bb avec Oracle"},"content":{"rendered":"

Depuis le portail internet OECD.Stat<\/a>, l’OCDE<\/a> met \u00e0 la disposition du grand public une multitude d’indicateurs \u00e9conomiques et soci\u00e9taux. Les informations sont pr\u00e9sent\u00e9es via de nombreux tableaux de bords facilement param\u00e9trables et dont les donn\u00e9es peuvent \u00eatre extraites.<\/p>\n

Pour ce billet, je me suis int\u00e9ress\u00e9 \u00e0 l’\u00e9volution du PIB des pays de la zone Euro. Plus pr\u00e9cis\u00e9ment, je me suis interrog\u00e9 sur la fr\u00e9quence de survenue de ph\u00e9nom\u00e8nes de r\u00e9cession.<\/p>\n

Selon la page Wikipedia consacr\u00e9e, une r\u00e9cession \u00e9conomique<\/a> correspond \u00e0 2 ou 3 trimestres cons\u00e9cutifs de contraction du PIB.<\/p>\n

C’est un bon cas d’utilisation de la fonction MATCH_RECOGNIZE<\/a> (introduite par Oracle 12c) qui permet de r\u00e9aliser simplement la d\u00e9tection de s\u00e9quences au sein de s\u00e9ries temporelles<\/a>.\u00a0Il s’agit d’une fonction analytique op\u00e9rant sur un dataset par une approche fen\u00eatr\u00e9e pour rep\u00e9rer des successions de lignes correspondant \u00e0 un \u00ab\u00a0profil\u00a0\u00bb sp\u00e9cifi\u00e9 via des expressions r\u00e9guli\u00e8res.<\/p>\n

Les donn\u00e9es\u00a0utilis\u00e9es ci-dessous proviennent du tableau de bord \u00ab\u00a0Comptes nationaux trimestriels : PIB historique – approche par les d\u00e9penses\u00a0\u00bb dont\u00a0j’ai limit\u00e9 le p\u00e9rim\u00e8tre aux pays de l’Eurozone (hors Malte et Chypre) et \u00e0 la p\u00e9riode 2000 \u00e0 2015:\u00a0http:\/\/stats.oecd.org\/\/Index.aspx?QueryId=69551<\/a><\/p>\n

Chargement des donn\u00e9es<\/h3>\n

Une table externe est utilis\u00e9e pour acc\u00e9der les donn\u00e9es r\u00e9cup\u00e9r\u00e9es depuis OECD.Stat dans un fichier csv:<\/p>\n

SQL>\nSQL> CREATE TABLE exttab_pib_evol\n  2  (\n  3     pays               VARCHAR2 (50),\n  4     sujet              VARCHAR2 (200),\n  5     mesure             VARCHAR2 (200),\n  6     frequence          VARCHAR2 (40),\n  7     periode            VARCHAR2 (20),\n  8     unit               VARCHAR2 (50),\n  9     powercode          VARCHAR2 (20),\n 10     reference_period   VARCHAR2(20),\n 11     valeur             NUMBER,\n 12     flags              VARCHAR2 (30)\n 13  )\n 14  ORGANIZATION EXTERNAL\n 15  (\n 16      TYPE oracle_loader\n 17      DEFAULT DIRECTORY datadir\n 18      ACCESS PARAMETERS\n 19          (\n 20          RECORDS DELIMITED BY NEWLINE\n 21          SKIP 1\n 22          FIELDS TERMINATED BY '|'\n 23          OPTIONALLY ENCLOSED BY '"' AND '"'\n 24          MISSING FIELD VALUES ARE NULL\n 25          )\n 26      LOCATION ('QNA_01122015114635142.csv')\n 27  )\n 28  REJECT LIMIT 0\n 29  NOPARALLEL;\n\nTable created.\n\nSQL><\/pre>\n
Le \u00ab\u00a0d\u00e9codage\u00a0\u00bb des p\u00e9riodes d’int\u00e9r\u00eat (\u00ab\u00a0T3 – 2014\u00a0\u00bb en \u00ab\u00a03\u00a0\u00bb et \u00ab\u00a02014\u00a0\u00bb) est r\u00e9alis\u00e9 par l’interm\u00e9diaire d’une vue:<\/p>\n
SQL>\nSQL> CREATE OR REPLACE VIEW pib_evol\n  2  AS\n  3     SELECT pays,\n  4            TO_NUMBER (SUBSTR (periode, INSTR (periode, '-') + 1)) annee,\n  5            TO_NUMBER (SUBSTR (periode, 2, 1)) trimestre,\n  6            TO_NUMBER (\n  7               valeur * CASE powercode WHEN 'milliards' THEN 1000 ELSE 1 END)\n  8               pib\n  9       FROM exttab_pib_evol;\n\nView created.\n\nSQL><\/pre>\n
On peut jeter un \u0153il sur\u00a0les informations ainsi transform\u00e9es:<\/p>\n
SQL>\nSQL> column PAYS format a20\nSQL> set pages 100\nSQL>\nSQL>      SELECT *\n  2         FROM pib_evol\n  3  FETCH FIRST 10 ROWS ONLY;\n\nPAYS                      ANNEE  TRIMESTRE        PIB\n-------------------- ---------- ---------- ----------\nAutriche                   2000          1 297275.421\nAutriche                   2000          2 301052.975\nAutriche                   2000          3 302616.903\nAutriche                   2000          4 307202.832\nAutriche                   2001          1 306744.543\nAutriche                   2001          2  305478.39\nAutriche                   2001          3 304888.539\nAutriche                   2001          4 306391.908\nAutriche                   2002          1 309353.755\nAutriche                   2002          2 310525.205\n\n10 rows selected.\n\nSQL><\/pre>\n
 <\/p>\n
D\u00e9tection de s\u00e9quences<\/h3>\n
Ici, on va donc chercher les s\u00e9quences de 3 (ou plus) baisses successives du PIB d’un pays. On consid\u00e8re qu’une baisse correspond \u00e0 une diminution\u00a0de plus de 0.1% du PIB. Entre 0 et 0.1%, il s’agit plut\u00f4t d’une stagnation.<\/p>\n
La\u00a0sp\u00e9cification<\/a>\u00a0du m\u00e9canisme de fen\u00eatrage est tout \u00e0 fait standard (PARTITION BY … ORDER BY…). La clause MEASURES permet d’indiquer les informations \u00e0 restituer – ici, on renvoie l’ann\u00e9e et le trimestre de la premi\u00e8re survenue (op\u00e9rateur FIRST) de chaque s\u00e9quence, le nombre d’\u00e9l\u00e9ments de la s\u00e9quence, le num\u00e9ro d’ordre de la s\u00e9quence (fonction MATCH_NUMBER) ainsi que l’\u00e9volution du PIB sur l’ensemble de la s\u00e9quence (en s’appuyant sur les op\u00e9rateurs FIRST et LAST).<\/p>\n
On indique aussi comment la restitution doit \u00eatre op\u00e9r\u00e9e (une ligne par s\u00e9quence) et ou reprendre la recherche de nouvelle s\u00e9quence (apr\u00e8s la derni\u00e8re ligne de la s\u00e9quence courante).<\/p>\n
La clause DEFINE permet de d\u00e9finir une ou plusieurs mesures sur lesquelles on va pr\u00e9ciser le PATTERN que l’on recherche. Ici, la mesure BAISSE est d\u00e9finie comme une diminution de plus de 0.1% entre une ligne et sa pr\u00e9d\u00e9cesseur \u00ab\u00a0prev ()\u00a0\u00bb. Les s\u00e9quences recherch\u00e9es contiennent 3 ou plus occurrences cons\u00e9cutives\u00a0de la mesure BAISSE:<\/p>\n
SQL>\nSQL>   SELECT *\n  2      FROM pib_evol MATCH_RECOGNIZE (\n  3                       PARTITION BY pays\n  4                       ORDER BY annee, trimestre\n  5                       MEASURES FIRST (annee) annee,\n  6                       FIRST (trimestre) trimestre,\n  7                       COUNT (*) nb_trim,\n  8                       match_number () recess#,\n  9                       ROUND (\n 10                            100\n 11                          * (FIRST (baisse.pib) - LAST (baisse.pib))\n 12                          \/ FIRST (baisse.pib),\n 13                          1) pib_reduc\n 14                       ONE ROW PER MATCH\n 15                       AFTER MATCH SKIP PAST LAST ROW\n 16                       PATTERN (baisse{3, })\n 17                       DEFINE baisse AS ROUND (\n 18                                             100\n 19                                           * (baisse.pib - prev (baisse.pib))\n 20                                           \/ prev (baisse.pib),\n 21                                           1) <= -0.1)\n 22  ORDER BY 1 ASC;\n\nPAYS                      ANNEE  TRIMESTRE    NB_TRIM    RECESS#  PIB_REDUC\n-------------------- ---------- ---------- ---------- ---------- ----------\nAllemagne                  2008          2          4          1        6.7\nAutriche                   2001          1          3          1         .6\nAutriche                   2008          2          5          2        5.1\nBelgique                   2001          2          3          1         .3\nBelgique                   2008          3          4          2        3.3\nEspagne                    2008          3          6          1        3.9\nEspagne                    2011          1         10          2          5\nEstonie                    2008          3          5          1       17.9\nFinlande                   2008          4          3          1          7\nFinlande                   2012          2          3          2        1.3\nFrance                     2008          2          5          1        3.5\nGr\u00e8ce                      2008          2          4          1        6.1\nGr\u00e8ce                      2010          1         13          2       21.9\nIrlande                    2008          1          8          1        9.3\nItalie                     2001          2          3          1         .5\nItalie                     2008          2          5          2        6.9\nItalie                     2011          3          8          3        4.7\nItalie                     2014          1          3          4         .3\nLettonie                   2007          4          8          1       21.8\nLettonie                   2010          1          3          2         .8\nLituanie                   2008          3          4          1       14.5\nLuxembourg                 2002          3          3          1        1.8\nLuxembourg                 2008          2          5          2        8.3\nPays-Bas                   2008          3          4          1        4.2\nPortugal                   2002          2          3          1        1.2\nPortugal                   2008          2          4          2        3.8\nPortugal                   2010          4          9          3        7.8\nSlov\u00e9nie                   2008          3          4          1        9.2\nSlov\u00e9nie                   2011          3          7          2        4.3\n\n29 rows selected.\n\nSQL><\/pre>\n
 <\/p>\n
On voit donc que la France n’a connu qu’un seul \u00e9pisode r\u00e9cessif \u00e0 partir du second trimestre 2008 pendant 5 trimestres. La baisse du PIB sur cette p\u00e9riode a \u00e9t\u00e9 de 3.5%.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Depuis le portail internet OECD.Stat, l’OCDE met \u00e0 la disposition du grand public une multitude d’indicateurs \u00e9conomiques et soci\u00e9taux. Les<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[6,15],"tags":[],"class_list":["post-490","post","type-post","status-publish","format-standard","hentry","category-oracle","category-statistique-exploratoire"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=490"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/490\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}