Analyse de corrélation avec Oracle
Avec l’organisation ce mois-ci d’un exercice d’envergure de simulation d’inondation à Paris, on a beaucoup parlé du risque de crue centennale de la Seine. Cela m’a amené à m’intéresser aux outils de suivi de l’état des cours d’eau en France. J’ai ainsi découvert deux sites très bien faits: vigicrues et la banque hydro.
Vivant en bordure de la Loire, je me suis demandé comment les évolutions de débits se propageaient en différents points du fleuve. Pour cela, à partir de la banque Hydro, j’ai extrait pour la période [14/01 – 06/03] l’ensemble des relevés de débit (périodicité de 1h) sur 5 points de son lit:
|
Les données sont accessibles ici : Debits_Loire.xlsx
Mon idée était de voir comment l’évolution du débit de la Loire à Nevers se manifestait dans les villes en aval. En effet, de nombreux paramètres entrent en ligne de compte : effet des précipitations locales, impact des affluents, élargissement/rétrécissements du lit etc…
Pour ce faire, j’ai eu recours à des mesures de corrélation entre les divers échantillons. A cet effet, Oracle propose diverses fonctions CORR, CORR_S, CORR_K.
La fonction CORR calcule le coefficient de corrélation de Pearson. Il est adapté lorsque la relation entre les échantillons est de type linéaire.
Les fonctions CORR_S et CORR_K calculent respectivement les coefficients de corrélation de rang de Spearman et de Kendall. Ces coefficients permettent de mettre en évidence une corrélation monotone (évolution simultanée des échantillons) mais pas forcément linéaire. Leur calcul est basé sur le rang des observations plutôt que sur les valeurs.
L’implémentation d’Oracle permet en outre d’obtenir une p-value pour ces deux fonctions (mais pas pour la fonction CORR).
Chargement des données
Le chargement des données a été effectué à l’aide du module d’import de SQLDeveloper. Cf billet http://blog.tiran.info/chargement-de-donnees-au-format-excel-dans-une-table-oracle
Préparation des données
Le format des dates ne correspondant pas à un masque type proposé par Oracle, ces dernières ont été chargées dans un champ défini en VARCHAR2. La conversion est opérée dans un second temps à l’aide d’une colonne virtuelle. Le champ original est ensuite masqué via la fonctionnalité 12c de colonne invisible :
SQL> ALTER TABLE debits_loire 2 ADD DT_MES AS (TO_DATE (REPLACE (REPLACE (REPLACE (dt, 'JAN.', '01'), 'FEV.', '02'), 'MAR.', '03'), 'DD MM YYYY HH24:MI')); Table altered. SQL> SQL> ALTER TABLE debits_loire 2 MODIFY dt INVISIBLE; Table altered. SQL> SQL> ALTER SESSION SET nls_date_format='DD/MM/YYYY HH24:MI'; Session altered. SQL> SQL> SELECT * 2 FROM debits_loire 3 ORDER BY dt_mes 4 FETCH FIRST 10 ROWS ONLY; NEVERS SAINT_SATUR ORLEANS BLOIS TOURS DT_MES ------ ----------- ------- ----- ----- ---------------- 437 655 526 525 484 14/01/2016 00:00 439 656 530 529 486 14/01/2016 01:00 440 658 534 532 487 14/01/2016 02:00 441 658 538 538 489 14/01/2016 03:00 442 658 542 542 491 14/01/2016 04:00 443 658 545 547 493 14/01/2016 05:00 442 658 549 551 496 14/01/2016 06:00 441 658 553 557 498 14/01/2016 07:00 440 657 556 563 501 14/01/2016 08:00 439 656 560 569 504 14/01/2016 09:00 10 rows selected. SQL>
Matrices de corrélation
Une matrice de corrélation peut-être constituée en calculant le coefficient pour chaque combinaison d’échantillon. Le bloc PLSQL ci-dessous réalise les permutations et stocke les résultats (coef. de Pearson et de Spearman) dans une table res_corr :
SQL> CREATE TABLE res_corr
2 (
3 v1 VARCHAR2 (30),
4 v2 VARCHAR2 (30),
5 corr_pearson NUMBER,
6 corr_spearman NUMBER
7 );
Table created.
SQL>
SQL> BEGIN
2 FOR rec
3 IN (SELECT a.column_name ville1, b.column_name ville2
4 FROM user_tab_cols a, user_tab_cols b
5 WHERE a.table_name = 'DEBITS_LOIRE'
6 AND a.table_name = b.table_name
7 AND a.column_name != 'DT_MES'
8 AND b.column_name != 'DT_MES'
9 AND a.hidden_column = 'NO'
10 AND b.hidden_column = 'NO')
11 LOOP
12 EXECUTE IMMEDIATE
13 'INSERT INTO res_corr
14 SELECT :v1,
15 :v2,
16 CORR ('
17 || rec.ville1
18 || ', '
19 || rec.ville2
20 || '),
21 CORR_S ('
22 || rec.ville1
23 || ', '
24 || rec.ville2
25 || ')
26 FROM debits_loire'
27 USING rec.ville1, rec.ville2;
28 END LOOP;
29
30 COMMIT;
31 END;
32 /
PL/SQL procedure successfully completed.
SQL>
En pivotant les données de res_corr on peut alors obtenir la présentation matricielle qui nous intéresse:
SQL> set numw 4
SQL> SELECT *
2 FROM (SELECT v1 pearson, v2, corr_pearson FROM res_corr) PIVOT (MAX (corr_pearson)
3 FOR v2
4 IN ('NEVERS' AS NEVERS,
5 'SAINT_SATUR' AS SAINT_SATUR,
6 'ORLEANS' AS ORLEANS,
7 'BLOIS' AS BLOIS,
8 'TOURS' AS TOURS))
9 ORDER BY 2 DESC;
PEARSON NEVERS SAINT_SATUR ORLEANS BLOIS TOURS
------------------------------ ------ ----------- ------- ----- -----
NEVERS 1 .958 .84 .766 .721
SAINT_SATUR .958 1 .943 .888 .847
ORLEANS .84 .943 1 .985 .963
BLOIS .766 .888 .985 1 .989
TOURS .721 .847 .963 .989 1
SQL>
SQL> list 2
2* FROM (SELECT v1 pearson, v2, corr_pearson FROM res_corr) PIVOT (MAX (corr_pearson)
SQL> c/pearson/spearman/
2* FROM (SELECT v1 spearman, v2, corr_pearson FROM res_corr) PIVOT (MAX (corr_pearson)
SQL> c/pearson/spearman/
2* FROM (SELECT v1 spearman, v2, corr_spearman FROM res_corr) PIVOT (MAX (corr_pearson)
SQL> c/pearson/spearman/
2* FROM (SELECT v1 spearman, v2, corr_spearman FROM res_corr) PIVOT (MAX (corr_spearman)
SQL> /
SPEARMAN NEVERS SAINT_SATUR ORLEANS BLOIS TOURS
------------------------------ ------ ----------- ------- ----- -----
NEVERS 1 .917 .804 .729 .689
SAINT_SATUR .917 1 .951 .902 .868
ORLEANS .804 .951 1 .985 .944
BLOIS .729 .902 .985 1 .975
TOURS .689 .868 .944 .975 1
SQL>
On constate que plus les points sont éloignés, plus le coefficient de corrélation diminue. C’est somme toute assez logique.
Les coefficients restent néanmoins relativement élevés (>0.72 pour Pearson et >0.68 pour Spearman). Le fait que le coefficient de corrélation de Pearson soit élevé et systématiquement supérieur à celui de Spearman est un bon indicateur de l’existence d’une liaison linéaire entre les débits aux divers points de mesure.
Etude de décalage
On peut raisonnablement penser qu’une évolution (augmentation ou diminution) de débit en amont met un certain temps à se propager en aval. Ainsi, une forte pluie à Nevers y fera augmenter le niveau de la Loire, mais cette crue ne sera visible à Tours qu’au bout d’une certaine durée.
Afin de déterminer cette durée, on peut essayer de « décaler temporellement » les échantillons afin de trouver pour quel décalage leur corrélation est la plus forte.
Dans le principe, on mesure le coefficient de corrélation de l’échantillon de Nevers avec celui de Tours dont on a supprimé la première ligne (ce qui correspond à un décalage de 1 heure), puis on réitère le processus en supprimant une seconde ligne et ainsi de suite. A chaque étape on compare le coefficient de corrélation avec celui obtenu à la phase précédente. Le décalage pour lequel le coefficient de corrélation est maximal correspond à la durée de propagation des évolutions de débit entre les deux villes.
Le bloc PL/SQL suivant permet de réaliser cette analyse. Le décalage est opéré grâce à la fonction analytique LEAD qui accepte un paramètre d’offset que l’on fait progressivement augmenter :
SQL> set serveroutput on;
SQL>
SQL> DECLARE
2 l_max_corr_p NUMBER := 0;
3 l_corr_p NUMBER;
4 l_max_decal_p NUMBER;
5 --
6 l_max_corr_s NUMBER := 0;
7 l_corr_s NUMBER;
8 l_max_decal_s NUMBER;
9 --
10 l_max_decal CONSTANT NUMBER := 120;
11 BEGIN
12 FOR rec
13 IN (SELECT column_name ville
14 FROM user_tab_cols
15 WHERE table_name = 'DEBITS_LOIRE'
16 AND column_name != 'DT_MES'
17 AND hidden_column = 'NO'
18 AND column_name != 'NEVERS')
19 LOOP
20 l_max_corr_p := 0;
21 l_max_corr_s := 0;
22
23 FOR i IN 1 .. l_max_decal
24 LOOP
25 EXECUTE IMMEDIATE
26 'SELECT CORR ( NEVERS, '
27 || rec.ville
28 || '), CORR_S ( NEVERS, '
29 || rec.ville
30 || ')
31 FROM ( SELECT NEVERS, LEAD ('
32 || rec.ville
33 || ', :i) OVER (ORDER BY dt_mes) '
34 || rec.ville
35 || ' FROM debits_loire
36 ORDER BY dt_mes)'
37 INTO l_corr_p, l_corr_s
38 USING i;
39
40 IF (l_max_corr_p < l_corr_p)
41 THEN
42 l_max_decal_p := i;
43 l_max_corr_p := l_corr_p;
44 END IF;
45
46 IF (l_max_corr_s < l_corr_s)
47 THEN
48 l_max_decal_s := i;
49 l_max_corr_s := l_corr_s;
50 END IF;
51 END LOOP;
52
53 DBMS_OUTPUT.put_line (CHR (10) || CHR (13) || rec.ville);
54 DBMS_OUTPUT.put_line (
55 '. Pearson - Décalage optimal '
56 || l_max_decal_p
57 || ' heures (Coef: '
58 || ROUND (l_max_corr_p, 3)
59 || ')');
60 DBMS_OUTPUT.put_line (
61 '. Spearman - Décalage optimal '
62 || l_max_decal_s
63 || ' heures (Coef: '
64 || ROUND (l_max_corr_s, 3)
65 || ')');
66 END LOOP;
67 END;
68 /
SAINT_SATUR
. Pearson - Décalage optimal 14 heures (Coef: .986)
. Spearman - Décalage optimal 16 heures (Coef: .954)
ORLEANS
. Pearson - Décalage optimal 36 heures (Coef: .982)
. Spearman - Décalage optimal 37 heures (Coef: .94)
BLOIS
. Pearson - Décalage optimal 47 heures (Coef: .973)
. Spearman - Décalage optimal 47 heures (Coef: .924)
TOURS
. Pearson - Décalage optimal 54 heures (Coef: .964)
. Spearman - Décalage optimal 52 heures (Coef: .878)
PL/SQL procedure successfully completed.
SQL>
On constate – très logiquement – que le décalage auquel le coefficient de corrélation est maximal augmente avec l’éloignement. Ce décalage avoisine 2 jours et 6 heures entre Nevers et Tours.