Régression linéaire simple avec Oracle

Oracle met à disposition la famille de fonctions REGR_*** pour réaliser des régressions linéaires simples (c’est à dire avec une unique variable explicative) en employant la technique des moindres carrés.

Ces fonctions peuvent être utilisées dans des groupements classiques (GROUP BY) ou bien sous une forme analytique (OVER PARTITION BY) de manière à opérer les calculs sur des sous-ensembles fenêtrés.

Dans l’exemple suivant, je cherche à trouver les coefficients de la droite de régression liant la quantité de déchets produite aux nombres d’habitants d’une zone géographique.

Le site sinoe.org édité par l’ADEME consolide et met à disposition de nombreuses informations relatives aux filières de collecte et traitements des déchets. Ici, j’ai utilisé un export au format Excel des données « Collectes des ordures ménagères résiduelles et sélectives » agrégées par département pour l’année 2013.

Il m’a fallu retravailler manuellement le fichier original en raison de son formatage avancé et de la multitude d’informations contenues. J’ai aussi rencontré des problèmes d’encodage des caractères dont je n’ai pas réussi à me sortir…

Le fichier résultant ne contient que les quantités collectées (par les camions-bennes ou en déchetterie) par type de déchet (ordures ménagères, collectes sélectives…) et pour chaque département ainsi que la population concernée: dechets_2013_par_dept

En regroupant les différents types de déchets on obtient la quantité DMA pour « déchets ménagers et assimilés ». C’est sur cet indicateur que la régression va être réalisée.

1) Création de la table de destination

SQL> CREATE TABLE dechets
  2  (
  3     dept                  VARCHAR2 (40),
  4     annee                 NUMBER,
  5     omr                   NUMBER DEFAULT ON NULL 0,
  6     verre                 NUMBER DEFAULT ON NULL 0,
  7     emballages            NUMBER DEFAULT ON NULL 0,
  8     dechets_verts         NUMBER DEFAULT ON NULL 0,
  9     encombrants           NUMBER DEFAULT ON NULL 0,
 10     dechets_dangereux     NUMBER DEFAULT ON NULL 0,
 11     dechetterie           NUMBER DEFAULT ON NULL 0,
 12     population            NUMBER DEFAULT ON NULL 0
 13  );

Table created.

SQL>

A noter au passage l’emploi de l’option « ON NULL » de la clause DEFAULT. Celle-ci permet (à partir de la version 12c) de spécifier que l’insertion explicite d’un NULL doit être substituée par la valeur par défaut de la colonne (ici 0).

2) Chargement des données

J’utilise ici la fonctionnalité SQL*Loader Express:

C:\RTI\Stats>sqlldr rafa/rafa@localhost/STATPDB data=dechets_2013_par_dept.csv table=dechets terminated_by=';' field_names=all_ignore

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jun 2 14:41:44 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Express Mode Load, Table: DECHETS
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO

Table DECHETS:
 102 Rows successfully loaded.

Check the log files:
 dechets.log
 dechets_%p.log_xt
for more information about the load.

C:\RTI\Stats>

2) Réalisation de la régression

J’exclue la ligne « France » qui reprend l’ensemble des quantités départementales:

SQL> WITH dma
  2       AS (SELECT dept,
  3                    omr
  4                  + verre
  5                  + emballages
  6                  + dechets_verts
  7                  + encombrants
  8                  + dechets_dangereux
  9                  + dechetterie
 10                     dma,
 11                  population
 12             FROM dechets
 13            WHERE dept != 'France')
 14  SELECT REGR_SLOPE (dma, population) pente,
 15         REGR_INTERCEPT (dma, population) ordonnee_origine,
 16         REGR_R2 (dma, population) R2
 17    FROM dma;

     PENTE ORDONNEE_ORIGINE         R2
---------- ---------------- ----------
.540539448       33536.3722 .916356773

SQL>

L’ordonnée à l’origine de la droite de régression est 33536.

Sa pente est de 0.540. Elle correspond donc à la quantité moyenne de DMA produite annuellement en 2013 par un habitant français soit: 540kg.

Le coefficient de détermination R2 donne le ratio entre la variance expliquée par le modèle et la variance totale. Plus le ratio est proche de 1 (ici, 0.91), plus le modèle linéaire « explique » les données observées.