{"id":318,"date":"2015-07-28T08:54:37","date_gmt":"2015-07-28T08:54:37","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=318"},"modified":"2015-07-28T08:54:37","modified_gmt":"2015-07-28T08:54:37","slug":"regression-lineaire-multiple-avec-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=318","title":{"rendered":"R\u00e9gression lin\u00e9aire multiple avec Oracle"},"content":{"rendered":"

Contrairement \u00e0 la r\u00e9gression lin\u00e9aire simple, la mise en \u0153uvre d’une r\u00e9gression lin\u00e9aire multiple<\/a> avec Oracle n\u00e9cessite de disposer de l’option Advanced Analytics<\/a>.<\/p>\n

Dans l’exemple suivant, je m’int\u00e9resse aux crit\u00e8res influen\u00e7ant les prix de vente d’appartements \u00e0 Paris. Pour disposer de donn\u00e9es r\u00e9elles, je suis pass\u00e9 par une \u00e9tape de web-scraping<\/a> pour extraire plusieurs centaines d’annonces depuis le site web d’ORPI.<\/p>\n

Pour cela, j’ai utilis\u00e9 l’outil en ligne \u00ab\u00a0Kimono<\/a>\u00a0\u00bb et je dois dire que celui-ci m’a impressionn\u00e9 tant par sa facilit\u00e9 de mise en \u0153uvre que par son ergonomie. De plus l’outil est gratuit pour des utilisations basiques comme la mienne!<\/p>\n

Les donn\u00e9es extraites – une fois retravaill\u00e9es – sont accessibles ici: Immo<\/a>.
\nDe mani\u00e8re \u00e0 disposer d’un \u00e9chantillon coh\u00e9rent, les appartements vendus en viager, ceux pour lesquels il est indiqu\u00e9 la n\u00e9cessit\u00e9 de r\u00e9aliser des travaux (r\u00e9novation ou rafra\u00eechissement) ainsi que ceux valant plus de 500000\u20ac ont \u00e9t\u00e9 exclus de l’\u00e9tude. A l’issue de ce tri, il reste un peu plus de 330 annonces.<\/p>\n

Le prix de vente, l’arrondissement, la superficie, le nombre de pi\u00e8ces et le nombre de chambres ont \u00e9t\u00e9 conserv\u00e9s tel quels.
\nD’autre part, via l’analyse de mots-cl\u00e9s dans le descriptif des annonces, la pr\u00e9sence d’un parking, d’une cave, d’un concierge et d’un balcon\/terrasse ont \u00e9t\u00e9 d\u00e9termin\u00e9s.<\/p>\n

A l’aide de ces informations, je cherche \u00e0 d\u00e9terminer un mod\u00e8le lin\u00e9aire du prix de vente qui soit le plus explicatif possible tout en comprenant un nombre minimum de pr\u00e9dicteurs (c’est \u00e0 dire en excluant ceux qui n’ont pas de valeur ajout\u00e9e explicative flagrante). C’est le principe du rasoir d’Ockham<\/a>.<\/p>\n

La mise en \u0153uvre est r\u00e9alis\u00e9e \u00e0 l’aide du package DBMS_DATA_MINING<\/a>.<\/p>\n

Chargement des donn\u00e9es<\/strong><\/p>\n

SQL> CREATE TABLE exttab_immo\n  2  (\n  3     arrondissement   VARCHAR2 (10),\n  4     prix             NUMBER,\n  5     superficie       NUMBER,\n  6     nbpieces         NUMBER,\n  7     nbchambres       NUMBER,\n  8     balcon           VARCHAR2 (1),\n  9     parking          VARCHAR2 (1),\n 10     cave             VARCHAR2 (1),\n 11     gardien          VARCHAR2 (1)\n 12  )\n 13  ORGANIZATION EXTERNAL\n 14    (  TYPE oracle_loader\n 15       DEFAULT DIRECTORY datadir\n 16       ACCESS PARAMETERS\n 17         ( RECORDS DELIMITED BY NEWLINE\n 18      NOBADFILE NODISCARDFILE NOLOGFILE\n 19           SKIP 1\n 20           FIELDS TERMINATED BY ';'\n 21           MISSING FIELD VALUES ARE NULL )\n 22       LOCATION ('Immo.csv')\n 23    )\n 24  REJECT LIMIT 0\n 25  NOPARALLEL;\n\nTable created.\n\nSQL><\/pre>\n
Normalisation des donn\u00e9es via une vue<\/strong><\/p>\n
J’agr\u00e8ge les arrondissements 75116 et 75016 et les valeurs manquantes du champ nbchambres sont remplac\u00e9es par 0.<\/p>\n
SQL> CREATE OR REPLACE VIEW immo_paris_v\n  2  AS\n  3     SELECT CASE\n  4               WHEN arrondissement = '75116' THEN '75016'\n  5               ELSE arrondissement\n  6            END\n  7               arrondissement,\n  8            prix,\n  9            superficie,\n 10            nbpieces,\n 11            NVL (nbchambres, 0) nbchambres,\n 12            balcon,\n 13            parking,\n 14            cave,\n 15            gardien\n 16       FROM exttab_immo;\n\nView created.\n\nSQL><\/pre>\n
 <\/p>\n
Cr\u00e9ation d’une table de param\u00e9trage du mod\u00e8le<\/strong><\/p>\n
Ici, on sp\u00e9cifie que l’on veut utiliser un algorithme de GLM et qu’on ne souhaite pas qu’Oracle utilise la fonction d’auto-pr\u00e9paration des donn\u00e9es (on s’en est charg\u00e9 pr\u00e9alablement):<\/p>\n
SQL> CREATE TABLE immo_lm_settings\n  2  (\n  3     setting_name    VARCHAR2 (30 BYTE),\n  4     setting_value   VARCHAR2 (30 BYTE)\n  5  );\n\nTable created.\n\nSQL>\nSQL> BEGIN\n  2     -- Utilisation d'un bloc PLSQL anonyme pour pouvoir r\u00e9f\u00e9rencer\n  3     -- les constantes du package DBMS_DATA_MINING\n  4\n  5     INSERT INTO immo_lm_settings (setting_name, setting_value)\n  6             VALUES (\n  7                       DBMS_DATA_MINING.algo_name,\n  8                       DBMS_DATA_MINING.algo_generalized_linear_model);\n  9\n 10     INSERT INTO immo_lm_settings (setting_name, setting_value)\n 11          VALUES (DBMS_DATA_MINING.prep_auto, DBMS_DATA_MINING.prep_auto_off);\n 12\n 13     COMMIT;\n 14  END;\n 15  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL><\/pre>\n
Analyse de multicolin\u00e9arit\u00e9<\/strong><\/p>\n
La d\u00e9tection de multicolin\u00e9arit\u00e9 <\/a>est r\u00e9alis\u00e9e par l’analyse du coefficient VIF<\/a> des variables continues (Pour les variables cat\u00e9gorielles, je ne sais pas faire!).
\nEn pratique, au-del\u00e0 d’un coefficient VIF de 5, on consid\u00e8re que le niveau de corr\u00e9lation d’une variable aux autres pr\u00e9dicteurs est probl\u00e9matique.<\/p>\n
Le coefficient VIF de chaque pr\u00e9dicteur est automatiquement calcul\u00e9 par Oracle lorsqu’on r\u00e9alise une r\u00e9gression multiple.<\/p>\n
SQL>\nSQL> BEGIN\n  2     DBMS_DATA_MINING.create_model (\n  3        model_name            => 'IMMO_REG',\n  4        mining_function       => DBMS_DATA_MINING.regression,\n  5        data_table_name       => 'immo_paris_v',\n  6        case_id_column_name   => NULL,\n  7        target_column_name    => 'PRIX',\n  8        settings_table_name   => 'immo_lm_settings');\n  9  END;\n 10  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL>\nSQL> column ATTRIBUTE_NAME format a25\nSQL> SELECT a.attribute_name, vif\n  2    FROM dba_mining_model_attributes a,\n  3         TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG')) b\n  4   WHERE     a.model_name = 'IMMO_REG'\n  5         AND a.attribute_name = b.attribute_name\n  6         AND a.attribute_type = 'NUMERICAL';\n\nATTRIBUTE_NAME                   VIF\n------------------------- ----------\nNBCHAMBRES                3.91274389\nNBPIECES                  6.53909963\nSUPERFICIE                4.50249349\n\nSQL><\/pre>\n
Le coefficient VIF de la variable NBPIECES d\u00e9passe 5. On la supprime du mod\u00e8le (via une clause de transformation d\u00e9finie \u00e0 l’aide de DBMS_DATA_MINING_TRANSFORM<\/a>) et on r\u00e9it\u00e8re le processus:<\/p>\n
SQL> BEGIN\n  2     DBMS_DATA_MINING.drop_model (model_name => 'IMMO_REG');\n  3  END;\n  4  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL>\nSQL> DECLARE\n  2     l_xform   DBMS_DATA_MINING_TRANSFORM.TRANSFORM_LIST;\n  3     l_vif     NUMBER;\n  4  BEGIN\n  5     DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM (l_xform,\n  6                                               'NBPIECES',\n  7                                               NULL,\n  8                                               NULL,\n  9                                               NULL);\n 10\n 11     DBMS_DATA_MINING.create_model (\n 12        model_name            => 'IMMO_REG',\n 13        mining_function       => DBMS_DATA_MINING.regression,\n 14        data_table_name       => 'immo_paris_v',\n 15        case_id_column_name   => NULL,\n 16        target_column_name    => 'PRIX',\n 17        settings_table_name   => 'immo_lm_settings',\n 18        xform_list            => l_xform);\n 19  END;\n 20  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL>\nSQL> SELECT a.attribute_name, vif\n  2    FROM dba_mining_model_attributes a,\n  3         TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG')) b\n  4   WHERE     a.model_name = 'IMMO_REG'\n  5         AND a.attribute_name = b.attribute_name\n  6         AND a.attribute_type = 'NUMERICAL';\n\nATTRIBUTE_NAME                   VIF\n------------------------- ----------\nNBCHAMBRES                2.50441484\nSUPERFICIE                2.50584067\n\nSQL><\/pre>\n
A ce stade, plus aucune variable continue ne pr\u00e9sente un VIF sup\u00e9rieur \u00e0 5. Dans le cas contraire, on relancerait \u00e0 nouveau le m\u00e9canisme it\u00e9rativement en supprimant \u00e0 chaque fois la variable continue dont le VIF est le plus \u00e9lev\u00e9.<\/p>\n
Param\u00e9trage du mod\u00e8le<\/strong><\/p>\n
Maintenant, on veut que le syst\u00e8me d\u00e9termine les pr\u00e9dicteurs les plus appropri\u00e9s pour le mod\u00e8le.\u00a0Dans la terminologie ODM, cette fonctionnalit\u00e9 est appel\u00e9e \u00ab\u00a0Feature Selection<\/a>\u00a0\u00bb (GLMS_FTR_SELECTION_ENABLE).<\/p>\n
M\u00eame si cela n\u2019appara\u00eet pas clairement dans la documentation, je pense qu’il s’agit d’un m\u00e9canisme de r\u00e9gression stepwise<\/a> classique.\u00a0Le crit\u00e8re AIC<\/a>\u00a0est utilis\u00e9 pour conduire le choix des pr\u00e9dicteurs (GLMS_FTR_SEL_AIC).<\/p>\n
J’indique aussi que je ne souhaite pas que les variables cat\u00e9gorielles soient int\u00e9gr\u00e9es partiellement (certaines modalit\u00e9s et pas d’autres) dans le mod\u00e8le (GLMS_SELECT_BLOCK_ENABLE).
\nOn d\u00e9sactive aussi le \u00ab\u00a0pruning\u00a0\u00bb de variables (GLMS_PRUNE_MODEL_DISABLE) dans la mesure ou cela fait double emploi avec la \u00ab\u00a0Feature Selection\u00a0\u00bb:<\/p>\n
SQL> BEGIN\n  2\n  3     INSERT INTO immo_lm_settings (setting_name, setting_value)\n  4             VALUES (\n  5                       DBMS_DATA_MINING.glms_ftr_selection,\n  6                       DBMS_DATA_MINING.glms_ftr_selection_enable);\n  7\n  8     INSERT INTO immo_lm_settings (setting_name, setting_value)\n  9             VALUES (\n 10                       DBMS_DATA_MINING.glms_select_block,\n 11                       DBMS_DATA_MINING.glms_select_block_enable);\n 12\n 13     INSERT INTO immo_lm_settings (setting_name, setting_value)\n 14             VALUES (\n 15                       DBMS_DATA_MINING.glms_ftr_sel_crit,\n 16                       DBMS_DATA_MINING.glms_ftr_sel_aic);\n 17\n 18     INSERT INTO immo_lm_settings (setting_name, setting_value)\n 19             VALUES (\n 20                       DBMS_DATA_MINING.glms_prune_model,\n 21                       DBMS_DATA_MINING.glms_prune_model_disable);\n 22\n 23     COMMIT;\n 24  END;\n 25  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL><\/pre>\n
Cr\u00e9ation du mod\u00e8le<\/strong><\/p>\n
Avec ce param\u00e9trage en place, Oracle parvient \u00e0 un mod\u00e8le dont le coefficient de d\u00e9termination\u00a0R2 est tr\u00e8s bon – de l’ordre de 91%. Celui-ci est\u00a0bas\u00e9 sur\u00a0les variables arrondissement (dont la modalit\u00e9 de r\u00e9f\u00e9rence est 75018 – absente de la sortie ci-dessous), superficie, balcon, nbchambres et parking. Les variables gardien et cave n’ont pas \u00e9t\u00e9 jug\u00e9s suffisamment explicatives pour \u00eatre maintenues dans le mod\u00e8le\u00a0:<\/p>\n
SQL> BEGIN\n  2     DBMS_DATA_MINING.drop_model (model_name => 'IMMO_REG');\n  3  END;\n  4  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL>\nSQL> DECLARE\n  2     l_xform   DBMS_DATA_MINING_TRANSFORM.transform_list;\n  3     l_vif     NUMBER;\n  4  BEGIN\n  5     DBMS_DATA_MINING_TRANSFORM.set_transform (l_xform,\n  6                                               'NBPIECES',\n  7                                               NULL,\n  8                                               NULL,\n  9                                               NULL);\n 10\n 11     DBMS_DATA_MINING.create_model (\n 12        model_name            => 'IMMO_REG',\n 13        mining_function       => DBMS_DATA_MINING.regression,\n 14        data_table_name       => 'immo_paris_v',\n 15        case_id_column_name   => NULL,\n 16        target_column_name    => 'PRIX',\n 17        settings_table_name   => 'immo_lm_settings',\n 18        xform_list            => l_xform);\n 19  END;\n 20  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL> \nSQL> column ATTRIBUTE_VALUE format a15\nSQL> column ATTRIBUTE_NAME format a15\nSQL> set pages 100\nSQL> \nSQL>   SELECT attribute_name,\n  2           attribute_value,\n  3           coefficient,\n  4           std_error,\n  5           p_value\n  6      FROM TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG'))\n  7  ORDER BY attribute_name, attribute_value;\n\nATTRIBUTE_NAME  ATTRIBUTE_VALUE COEFFICIENT  STD_ERROR    P_VALUE\n--------------- --------------- ----------- ---------- ----------\nARRONDISSEMENT  75001            106499.344 34330.2306 .002098227\nARRONDISSEMENT  75002            64289.5713 14900.7067 .000021553\nARRONDISSEMENT  75004            116459.601 20177.6793 1.9084E-08\nARRONDISSEMENT  75005            148231.163 24448.8049 3.8936E-09\nARRONDISSEMENT  75006            57145.9277 24670.9672 .021194649\nARRONDISSEMENT  75007            141475.919 13724.7105 1.3098E-21\nARRONDISSEMENT  75008            123214.781 37883.0255 .001270452\nARRONDISSEMENT  75009            63359.2722 17674.8806 .000392024\nARRONDISSEMENT  75010            48005.0852  8389.6645 2.4894E-08\nARRONDISSEMENT  75011            59944.9949 7903.44334 3.9418E-13\nARRONDISSEMENT  75012            47300.9304 9356.34809 7.3678E-07\nARRONDISSEMENT  75013             58318.578 8187.50435 7.4647E-12\nARRONDISSEMENT  75014            64808.4232 7560.54757 4.9552E-16\nARRONDISSEMENT  75015            58743.3835 8111.81683 3.5439E-12\nARRONDISSEMENT  75016            51561.3364 14707.9586 .000522883\nARRONDISSEMENT  75017            54922.3222  10601.204 3.9952E-07\nARRONDISSEMENT  75019            11844.7274   7618.904 .121053088\nARRONDISSEMENT  75020            12870.9792 7305.28203 .079079542\nBALCON          1                -1026.2927 5409.68573 .849657874\nNBCHAMBRES                       -4626.4141 4149.38511 .265732351\nPARKING         1                -6919.0228 6909.36171 .317417839\nSUPERFICIE                        6973.8048  174.72017 5.630E-124\n                                 4781.73131 6565.00719 .466940877\n\n23 rows selected.\n\nSQL>\nSQL> SELECT *\n  2    FROM TABLE (DBMS_DATA_MINING.get_model_details_global ('IMMO_REG'))\n  3   WHERE global_detail_name IN ('R_SQ',\n  4                                'ADJUSTED_R_SQUARE',\n  5                                'F_VALUE',\n  6                                'MODEL_F_P_VALUE');\n\nGLOBAL_DETAIL_NAME             GLOBAL_DETAIL_VALUE\n------------------------------ -------------------\nR_SQ                                    .917228114\nADJUSTED_R_SQUARE                        .91133497\nF_VALUE                                 155.643255\nMODEL_F_P_VALUE                                  0\n\nSQL><\/pre>\n
Les vues dba_mining_models<\/a>, dba_mining_model_settings<\/a> & dba_mining_model_attributes<\/a> permettent de retrouver les param\u00e9trages utilis\u00e9s lors de la cr\u00e9ation des mod\u00e8les.<\/p>\n
Analyse des r\u00e9sidus<\/strong><\/p>\n
Une vue bas\u00e9e sur la fonction PREDICTION <\/a>est cr\u00e9\u00e9e pour produire les r\u00e9sidus du mod\u00e8le. On peut alors en valider la normalit\u00e9:<\/p>\n
SQL> CREATE OR REPLACE VIEW glm_reg_resid\n  2  AS\n  3     SELECT prix - PREDICTION (immo_reg USING *) residu\n  4       FROM immo_paris_v;\n\nView created.\n\nSQL>\nSQL> SET SERVEROUTPUT ON FORMAT WRAPPED\nSQL>\nSQL> DECLARE\n  2     l_mean    NUMBER;\n  3     l_stdev   NUMBER;\n  4     l_sig     NUMBER;\n  5  BEGIN\n  6     DBMS_STAT_FUNCS.normal_dist_fit (ownername    => USER,\n  7                                      tablename    => 'GLM_REG_RESID',\n  8                                      columnname   => 'RESIDU',\n  9                                      test_type    => 'SHAPIRO_WILKS',\n 10                                      mean         => l_mean,\n 11                                      stdev        => l_stdev,\n 12                                      sig          => l_sig);\n 13     DBMS_OUTPUT.put_line ('p-valeur: ' || ROUND (l_sig, 3));\n 14     DBMS_OUTPUT.put_line ('Moyenne: ' || ROUND (l_mean, 3));\n 15     DBMS_OUTPUT.put_line ('Ecart Type: ' || ROUND (l_stdev, 3));\n 16     DBMS_OUTPUT.put_line (' ');\n 17  END;\n 18  \/\nW value : .9936100515173995514217705152750797043781\np-valeur: .174\nMoyenne: 0\nEcart Type: 32639.569\n\n\nPL\/SQL procedure successfully completed.\n\nSQL><\/pre>\n
Ici, la p-valeur ne permet pas de rejeter l’hypoth\u00e8se H0. Les donn\u00e9es sont donc compatibles avec une distribution normale.<\/p>\n
Simulation<\/strong><\/p>\n
La fonction PREDICTION permet de simuler le prix d’un appartement en utilisant le mod\u00e8le pr\u00e9c\u00e9demment cr\u00e9\u00e9.
\nOn peut par exemple\u00a0estimer le prix d’un deux pi\u00e8ces de 23m2 dans le 20eme arrondissement:<\/p>\n
SQL>\nSQL> WITH appt\n  2       AS (SELECT '75020' arrondissement,\n  3                  23 superficie,\n  4                  1 nbchambres,\n  5                  '0' balcon,\n  6                  '0' parking\n  7             FROM DUAL)\n  8  SELECT PREDICTION (immo_reg USING *)\n  9    FROM appt;\n\nPREDICTION(IMMO_REGUSING*)\n--------------------------\n                173423.807\n\nSQL><\/pre>\n","protected":false},"excerpt":{"rendered":"
Contrairement \u00e0 la r\u00e9gression lin\u00e9aire simple, la mise en \u0153uvre d’une r\u00e9gression lin\u00e9aire multiple avec Oracle n\u00e9cessite de disposer de<\/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,7,13,14],"tags":[],"class_list":["post-318","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracle-advanced-analytics","category-regression","category-statistique"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/318","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=318"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/318\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}