{"id":219,"date":"2015-06-09T08:52:44","date_gmt":"2015-06-09T08:52:44","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=219"},"modified":"2015-06-09T08:52:44","modified_gmt":"2015-06-09T08:52:44","slug":"regression-lineaire-simple-avec-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=219","title":{"rendered":"R\u00e9gression lin\u00e9aire simple avec Oracle"},"content":{"rendered":"

Oracle met \u00e0 disposition la famille de fonctions REGR_***<\/a> pour r\u00e9aliser des r\u00e9gressions lin\u00e9aires<\/a> simples (c’est \u00e0 dire avec une unique variable explicative) en employant la technique des moindres carr\u00e9s<\/a>.<\/p>\n

Ces fonctions peuvent \u00eatre utilis\u00e9es dans\u00a0des groupements classiques (GROUP BY) ou bien sous une forme analytique (OVER PARTITION BY) de mani\u00e8re \u00e0 op\u00e9rer les calculs sur des sous-ensembles fen\u00eatr\u00e9s.<\/p>\n

Dans l’exemple suivant, je cherche \u00e0 trouver les coefficients de la droite de r\u00e9gression liant la quantit\u00e9 de d\u00e9chets produite aux nombres d’habitants d’une zone g\u00e9ographique.<\/p>\n

Le site sinoe.org<\/a>\u00a0\u00e9dit\u00e9 par l’ADEME consolide et met \u00e0 disposition de nombreuses informations relatives aux fili\u00e8res de collecte et traitements des d\u00e9chets. Ici, j’ai utilis\u00e9 un export au format Excel des donn\u00e9es \u00ab\u00a0Collectes des ordures m\u00e9nag\u00e8res r\u00e9siduelles et s\u00e9lectives\u00a0\u00bb agr\u00e9g\u00e9es par d\u00e9partement pour l’ann\u00e9e 2013.<\/p>\n

Il m’a fallu retravailler manuellement le fichier original<\/a>\u00a0en raison de son formatage avanc\u00e9 et de la multitude d’informations contenues. J’ai aussi rencontr\u00e9 des probl\u00e8mes d’encodage des caract\u00e8res dont je n’ai pas r\u00e9ussi \u00e0 me sortir…<\/p>\n

Le fichier r\u00e9sultant ne contient que les quantit\u00e9s collect\u00e9es (par les camions-bennes ou en d\u00e9chetterie) par type de d\u00e9chet (ordures m\u00e9nag\u00e8res, collectes s\u00e9lectives…) et pour chaque d\u00e9partement ainsi que la population concern\u00e9e:\u00a0dechets_2013_par_dept<\/a><\/p>\n

En regroupant les diff\u00e9rents types de d\u00e9chets on obtient la quantit\u00e9 DMA pour \u00ab\u00a0d\u00e9chets m\u00e9nagers et assimil\u00e9s\u00a0\u00bb<\/a>. C’est sur cet indicateur que la r\u00e9gression va \u00eatre r\u00e9alis\u00e9e.<\/p>\n

1) Cr\u00e9ation de la table de destination<\/strong><\/p>\n

SQL> CREATE TABLE dechets\n  2  (\n  3     dept                  VARCHAR2 (40),\n  4     annee                 NUMBER,\n  5     omr                   NUMBER DEFAULT ON NULL 0,\n  6     verre                 NUMBER DEFAULT ON NULL 0,\n  7     emballages            NUMBER DEFAULT ON NULL 0,\n  8     dechets_verts         NUMBER DEFAULT ON NULL 0,\n  9     encombrants           NUMBER DEFAULT ON NULL 0,\n 10     dechets_dangereux     NUMBER DEFAULT ON NULL 0,\n 11     dechetterie           NUMBER DEFAULT ON NULL 0,\n 12     population            NUMBER DEFAULT ON NULL 0\n 13  );\n\nTable created.\n\nSQL>\n<\/pre>\n
A noter au passage l’emploi de l’option \u00ab\u00a0ON NULL\u00a0\u00bb de la clause DEFAULT. Celle-ci permet (\u00e0 partir de la version 12c) de sp\u00e9cifier que l’insertion explicite d’un NULL doit \u00eatre substitu\u00e9e par la valeur par d\u00e9faut de la colonne (ici 0).<\/p>\n
2) Chargement des donn\u00e9es<\/strong><\/p>\n
J\u2019utilise ici la fonctionnalit\u00e9 SQL*Loader Express:<\/p>\n
C:\\RTI\\Stats>sqlldr rafa\/rafa@localhost\/STATPDB data=dechets_2013_par_dept.csv table=dechets terminated_by=';' field_names=all_ignore\n\nSQL*Loader: Release 12.1.0.2.0 - Production on Tue Jun 2 14:41:44 2015\n\nCopyright (c) 1982, 2014, Oracle and\/or its affiliates. All rights reserved.\n\nExpress Mode Load, Table: DECHETS\nPath used: External Table, DEGREE_OF_PARALLELISM=AUTO\n\nTable DECHETS:\n 102 Rows successfully loaded.\n\nCheck the log files:\n dechets.log\n dechets_%p.log_xt\nfor more information about the load.\n\nC:\\RTI\\Stats><\/pre>\n
2) R\u00e9alisation de la r\u00e9gression<\/strong><\/p>\n
J’exclue la ligne \u00ab\u00a0France\u00a0\u00bb qui reprend l’ensemble des quantit\u00e9s d\u00e9partementales:<\/p>\n
SQL> WITH dma\n  2       AS (SELECT dept,\n  3                    omr\n  4                  + verre\n  5                  + emballages\n  6                  + dechets_verts\n  7                  + encombrants\n  8                  + dechets_dangereux\n  9                  + dechetterie\n 10                     dma,\n 11                  population\n 12             FROM dechets\n 13            WHERE dept != 'France')\n 14  SELECT REGR_SLOPE (dma, population) pente,\n 15         REGR_INTERCEPT (dma, population) ordonnee_origine,\n 16         REGR_R2 (dma, population) R2\n 17    FROM dma;\n\n     PENTE ORDONNEE_ORIGINE         R2\n---------- ---------------- ----------\n.540539448       33536.3722 .916356773\n\nSQL><\/pre>\n
L’ordonn\u00e9e \u00e0 l’origine de la droite de r\u00e9gression est 33536.<\/p>\n
Sa pente est de 0.540. Elle correspond donc \u00e0 la quantit\u00e9 moyenne de DMA produite annuellement en 2013 par un habitant fran\u00e7ais soit: 540kg.<\/p>\n
Le coefficient de d\u00e9termination R2 donne le ratio entre la variance expliqu\u00e9e par le mod\u00e8le et la variance totale. Plus le ratio est proche de 1 (ici, 0.91), plus le mod\u00e8le lin\u00e9aire \u00ab\u00a0explique\u00a0\u00bb les donn\u00e9es observ\u00e9es.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle met \u00e0 disposition la famille de fonctions REGR_*** pour r\u00e9aliser des r\u00e9gressions lin\u00e9aires simples (c’est \u00e0 dire avec une<\/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,13,14],"tags":[],"class_list":["post-219","post","type-post","status-publish","format-standard","hentry","category-oracle","category-regression","category-statistique"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/219","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=219"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/219\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}