Régression linéaire multiple avec Oracle
Contrairement à la régression linéaire simple, la mise en œuvre d’une régression linéaire multiple avec Oracle nécessite de disposer de l’option Advanced Analytics.
Dans l’exemple suivant, je m’intéresse aux critères influençant les prix de vente d’appartements à Paris. Pour disposer de données réelles, je suis passé par une étape de web-scraping pour extraire plusieurs centaines d’annonces depuis le site web d’ORPI.
Pour cela, j’ai utilisé l’outil en ligne « Kimono » et je dois dire que celui-ci m’a impressionné tant par sa facilité de mise en œuvre que par son ergonomie. De plus l’outil est gratuit pour des utilisations basiques comme la mienne!
Les données extraites – une fois retravaillées – sont accessibles ici: Immo.
De manière à disposer d’un échantillon cohérent, les appartements vendus en viager, ceux pour lesquels il est indiqué la nécessité de réaliser des travaux (rénovation ou rafraîchissement) ainsi que ceux valant plus de 500000€ ont été exclus de l’étude. A l’issue de ce tri, il reste un peu plus de 330 annonces.
Le prix de vente, l’arrondissement, la superficie, le nombre de pièces et le nombre de chambres ont été conservés tel quels.
D’autre part, via l’analyse de mots-clés dans le descriptif des annonces, la présence d’un parking, d’une cave, d’un concierge et d’un balcon/terrasse ont été déterminés.
A l’aide de ces informations, je cherche à déterminer un modèle linéaire du prix de vente qui soit le plus explicatif possible tout en comprenant un nombre minimum de prédicteurs (c’est à dire en excluant ceux qui n’ont pas de valeur ajoutée explicative flagrante). C’est le principe du rasoir d’Ockham.
La mise en œuvre est réalisée à l’aide du package DBMS_DATA_MINING.
Chargement des données
SQL> CREATE TABLE exttab_immo
2 (
3 arrondissement VARCHAR2 (10),
4 prix NUMBER,
5 superficie NUMBER,
6 nbpieces NUMBER,
7 nbchambres NUMBER,
8 balcon VARCHAR2 (1),
9 parking VARCHAR2 (1),
10 cave VARCHAR2 (1),
11 gardien VARCHAR2 (1)
12 )
13 ORGANIZATION EXTERNAL
14 ( TYPE oracle_loader
15 DEFAULT DIRECTORY datadir
16 ACCESS PARAMETERS
17 ( RECORDS DELIMITED BY NEWLINE
18 NOBADFILE NODISCARDFILE NOLOGFILE
19 SKIP 1
20 FIELDS TERMINATED BY ';'
21 MISSING FIELD VALUES ARE NULL )
22 LOCATION ('Immo.csv')
23 )
24 REJECT LIMIT 0
25 NOPARALLEL;
Table created.
SQL>
Normalisation des données via une vue
J’agrège les arrondissements 75116 et 75016 et les valeurs manquantes du champ nbchambres sont remplacées par 0.
SQL> CREATE OR REPLACE VIEW immo_paris_v 2 AS 3 SELECT CASE 4 WHEN arrondissement = '75116' THEN '75016' 5 ELSE arrondissement 6 END 7 arrondissement, 8 prix, 9 superficie, 10 nbpieces, 11 NVL (nbchambres, 0) nbchambres, 12 balcon, 13 parking, 14 cave, 15 gardien 16 FROM exttab_immo; View created. SQL>
Création d’une table de paramétrage du modèle
Ici, on spécifie que l’on veut utiliser un algorithme de GLM et qu’on ne souhaite pas qu’Oracle utilise la fonction d’auto-préparation des données (on s’en est chargé préalablement):
SQL> CREATE TABLE immo_lm_settings 2 ( 3 setting_name VARCHAR2 (30 BYTE), 4 setting_value VARCHAR2 (30 BYTE) 5 ); Table created. SQL> SQL> BEGIN 2 -- Utilisation d'un bloc PLSQL anonyme pour pouvoir référencer 3 -- les constantes du package DBMS_DATA_MINING 4 5 INSERT INTO immo_lm_settings (setting_name, setting_value) 6 VALUES ( 7 DBMS_DATA_MINING.algo_name, 8 DBMS_DATA_MINING.algo_generalized_linear_model); 9 10 INSERT INTO immo_lm_settings (setting_name, setting_value) 11 VALUES (DBMS_DATA_MINING.prep_auto, DBMS_DATA_MINING.prep_auto_off); 12 13 COMMIT; 14 END; 15 / PL/SQL procedure successfully completed. SQL>
Analyse de multicolinéarité
La détection de multicolinéarité est réalisée par l’analyse du coefficient VIF des variables continues (Pour les variables catégorielles, je ne sais pas faire!).
En pratique, au-delà d’un coefficient VIF de 5, on considère que le niveau de corrélation d’une variable aux autres prédicteurs est problématique.
Le coefficient VIF de chaque prédicteur est automatiquement calculé par Oracle lorsqu’on réalise une régression multiple.
SQL>
SQL> BEGIN
2 DBMS_DATA_MINING.create_model (
3 model_name => 'IMMO_REG',
4 mining_function => DBMS_DATA_MINING.regression,
5 data_table_name => 'immo_paris_v',
6 case_id_column_name => NULL,
7 target_column_name => 'PRIX',
8 settings_table_name => 'immo_lm_settings');
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> column ATTRIBUTE_NAME format a25
SQL> SELECT a.attribute_name, vif
2 FROM dba_mining_model_attributes a,
3 TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG')) b
4 WHERE a.model_name = 'IMMO_REG'
5 AND a.attribute_name = b.attribute_name
6 AND a.attribute_type = 'NUMERICAL';
ATTRIBUTE_NAME VIF
------------------------- ----------
NBCHAMBRES 3.91274389
NBPIECES 6.53909963
SUPERFICIE 4.50249349
SQL>
Le coefficient VIF de la variable NBPIECES dépasse 5. On la supprime du modèle (via une clause de transformation définie à l’aide de DBMS_DATA_MINING_TRANSFORM) et on réitère le processus:
SQL> BEGIN
2 DBMS_DATA_MINING.drop_model (model_name => 'IMMO_REG');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_xform DBMS_DATA_MINING_TRANSFORM.TRANSFORM_LIST;
3 l_vif NUMBER;
4 BEGIN
5 DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM (l_xform,
6 'NBPIECES',
7 NULL,
8 NULL,
9 NULL);
10
11 DBMS_DATA_MINING.create_model (
12 model_name => 'IMMO_REG',
13 mining_function => DBMS_DATA_MINING.regression,
14 data_table_name => 'immo_paris_v',
15 case_id_column_name => NULL,
16 target_column_name => 'PRIX',
17 settings_table_name => 'immo_lm_settings',
18 xform_list => l_xform);
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT a.attribute_name, vif
2 FROM dba_mining_model_attributes a,
3 TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG')) b
4 WHERE a.model_name = 'IMMO_REG'
5 AND a.attribute_name = b.attribute_name
6 AND a.attribute_type = 'NUMERICAL';
ATTRIBUTE_NAME VIF
------------------------- ----------
NBCHAMBRES 2.50441484
SUPERFICIE 2.50584067
SQL>
A ce stade, plus aucune variable continue ne présente un VIF supérieur à 5. Dans le cas contraire, on relancerait à nouveau le mécanisme itérativement en supprimant à chaque fois la variable continue dont le VIF est le plus élevé.
Paramétrage du modèle
Maintenant, on veut que le système détermine les prédicteurs les plus appropriés pour le modèle. Dans la terminologie ODM, cette fonctionnalité est appelée « Feature Selection » (GLMS_FTR_SELECTION_ENABLE).
Même si cela n’apparaît pas clairement dans la documentation, je pense qu’il s’agit d’un mécanisme de régression stepwise classique. Le critère AIC est utilisé pour conduire le choix des prédicteurs (GLMS_FTR_SEL_AIC).
J’indique aussi que je ne souhaite pas que les variables catégorielles soient intégrées partiellement (certaines modalités et pas d’autres) dans le modèle (GLMS_SELECT_BLOCK_ENABLE).
On désactive aussi le « pruning » de variables (GLMS_PRUNE_MODEL_DISABLE) dans la mesure ou cela fait double emploi avec la « Feature Selection »:
SQL> BEGIN 2 3 INSERT INTO immo_lm_settings (setting_name, setting_value) 4 VALUES ( 5 DBMS_DATA_MINING.glms_ftr_selection, 6 DBMS_DATA_MINING.glms_ftr_selection_enable); 7 8 INSERT INTO immo_lm_settings (setting_name, setting_value) 9 VALUES ( 10 DBMS_DATA_MINING.glms_select_block, 11 DBMS_DATA_MINING.glms_select_block_enable); 12 13 INSERT INTO immo_lm_settings (setting_name, setting_value) 14 VALUES ( 15 DBMS_DATA_MINING.glms_ftr_sel_crit, 16 DBMS_DATA_MINING.glms_ftr_sel_aic); 17 18 INSERT INTO immo_lm_settings (setting_name, setting_value) 19 VALUES ( 20 DBMS_DATA_MINING.glms_prune_model, 21 DBMS_DATA_MINING.glms_prune_model_disable); 22 23 COMMIT; 24 END; 25 / PL/SQL procedure successfully completed. SQL>
Création du modèle
Avec ce paramétrage en place, Oracle parvient à un modèle dont le coefficient de détermination R2 est très bon – de l’ordre de 91%. Celui-ci est basé sur les variables arrondissement (dont la modalité de référence est 75018 – absente de la sortie ci-dessous), superficie, balcon, nbchambres et parking. Les variables gardien et cave n’ont pas été jugés suffisamment explicatives pour être maintenues dans le modèle :
SQL> BEGIN
2 DBMS_DATA_MINING.drop_model (model_name => 'IMMO_REG');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_xform DBMS_DATA_MINING_TRANSFORM.transform_list;
3 l_vif NUMBER;
4 BEGIN
5 DBMS_DATA_MINING_TRANSFORM.set_transform (l_xform,
6 'NBPIECES',
7 NULL,
8 NULL,
9 NULL);
10
11 DBMS_DATA_MINING.create_model (
12 model_name => 'IMMO_REG',
13 mining_function => DBMS_DATA_MINING.regression,
14 data_table_name => 'immo_paris_v',
15 case_id_column_name => NULL,
16 target_column_name => 'PRIX',
17 settings_table_name => 'immo_lm_settings',
18 xform_list => l_xform);
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL> column ATTRIBUTE_VALUE format a15
SQL> column ATTRIBUTE_NAME format a15
SQL> set pages 100
SQL>
SQL> SELECT attribute_name,
2 attribute_value,
3 coefficient,
4 std_error,
5 p_value
6 FROM TABLE (DBMS_DATA_MINING.get_model_details_glm ('IMMO_REG'))
7 ORDER BY attribute_name, attribute_value;
ATTRIBUTE_NAME ATTRIBUTE_VALUE COEFFICIENT STD_ERROR P_VALUE
--------------- --------------- ----------- ---------- ----------
ARRONDISSEMENT 75001 106499.344 34330.2306 .002098227
ARRONDISSEMENT 75002 64289.5713 14900.7067 .000021553
ARRONDISSEMENT 75004 116459.601 20177.6793 1.9084E-08
ARRONDISSEMENT 75005 148231.163 24448.8049 3.8936E-09
ARRONDISSEMENT 75006 57145.9277 24670.9672 .021194649
ARRONDISSEMENT 75007 141475.919 13724.7105 1.3098E-21
ARRONDISSEMENT 75008 123214.781 37883.0255 .001270452
ARRONDISSEMENT 75009 63359.2722 17674.8806 .000392024
ARRONDISSEMENT 75010 48005.0852 8389.6645 2.4894E-08
ARRONDISSEMENT 75011 59944.9949 7903.44334 3.9418E-13
ARRONDISSEMENT 75012 47300.9304 9356.34809 7.3678E-07
ARRONDISSEMENT 75013 58318.578 8187.50435 7.4647E-12
ARRONDISSEMENT 75014 64808.4232 7560.54757 4.9552E-16
ARRONDISSEMENT 75015 58743.3835 8111.81683 3.5439E-12
ARRONDISSEMENT 75016 51561.3364 14707.9586 .000522883
ARRONDISSEMENT 75017 54922.3222 10601.204 3.9952E-07
ARRONDISSEMENT 75019 11844.7274 7618.904 .121053088
ARRONDISSEMENT 75020 12870.9792 7305.28203 .079079542
BALCON 1 -1026.2927 5409.68573 .849657874
NBCHAMBRES -4626.4141 4149.38511 .265732351
PARKING 1 -6919.0228 6909.36171 .317417839
SUPERFICIE 6973.8048 174.72017 5.630E-124
4781.73131 6565.00719 .466940877
23 rows selected.
SQL>
SQL> SELECT *
2 FROM TABLE (DBMS_DATA_MINING.get_model_details_global ('IMMO_REG'))
3 WHERE global_detail_name IN ('R_SQ',
4 'ADJUSTED_R_SQUARE',
5 'F_VALUE',
6 'MODEL_F_P_VALUE');
GLOBAL_DETAIL_NAME GLOBAL_DETAIL_VALUE
------------------------------ -------------------
R_SQ .917228114
ADJUSTED_R_SQUARE .91133497
F_VALUE 155.643255
MODEL_F_P_VALUE 0
SQL>
Les vues dba_mining_models, dba_mining_model_settings & dba_mining_model_attributes permettent de retrouver les paramétrages utilisés lors de la création des modèles.
Analyse des résidus
Une vue basée sur la fonction PREDICTION est créée pour produire les résidus du modèle. On peut alors en valider la normalité:
SQL> CREATE OR REPLACE VIEW glm_reg_resid
2 AS
3 SELECT prix - PREDICTION (immo_reg USING *) residu
4 FROM immo_paris_v;
View created.
SQL>
SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL>
SQL> DECLARE
2 l_mean NUMBER;
3 l_stdev NUMBER;
4 l_sig NUMBER;
5 BEGIN
6 DBMS_STAT_FUNCS.normal_dist_fit (ownername => USER,
7 tablename => 'GLM_REG_RESID',
8 columnname => 'RESIDU',
9 test_type => 'SHAPIRO_WILKS',
10 mean => l_mean,
11 stdev => l_stdev,
12 sig => l_sig);
13 DBMS_OUTPUT.put_line ('p-valeur: ' || ROUND (l_sig, 3));
14 DBMS_OUTPUT.put_line ('Moyenne: ' || ROUND (l_mean, 3));
15 DBMS_OUTPUT.put_line ('Ecart Type: ' || ROUND (l_stdev, 3));
16 DBMS_OUTPUT.put_line (' ');
17 END;
18 /
W value : .9936100515173995514217705152750797043781
p-valeur: .174
Moyenne: 0
Ecart Type: 32639.569
PL/SQL procedure successfully completed.
SQL>
Ici, la p-valeur ne permet pas de rejeter l’hypothèse H0. Les données sont donc compatibles avec une distribution normale.
Simulation
La fonction PREDICTION permet de simuler le prix d’un appartement en utilisant le modèle précédemment créé.
On peut par exemple estimer le prix d’un deux pièces de 23m2 dans le 20eme arrondissement:
SQL>
SQL> WITH appt
2 AS (SELECT '75020' arrondissement,
3 23 superficie,
4 1 nbchambres,
5 '0' balcon,
6 '0' parking
7 FROM DUAL)
8 SELECT PREDICTION (immo_reg USING *)
9 FROM appt;
PREDICTION(IMMO_REGUSING*)
--------------------------
173423.807
SQL>