Discussion :

[nemikap]

Bonjour à tous,

Quelques infos pratiques avant de commencer (je ne sais pas si toutes les infos seront utiles)

OS : Windows 7 (64 bits)
Processeur : Intel Core i7
RAM : 12 Go
Version de PostGreSQL : 9.3 (installé dans D:\Programmes\PostgreSQL\9.3)
Version de R : 3.0.2 (installé dans C:\Program Files\R\R-3.0.2)
Interface d'utilisation de PostGreSQL : pgAdmin III
Forme d'utilisation de PostGreSQL : Utilisation en local uniquement pour du SQL
Niveau en SQL : Novice - J'ai quand même les notions de base

Mon problème

Je dispose d'une base de données de ce type (en plus complet bien entendu ~53 000 lignes) :



Via une requête SQL, pour chaque OBS_ID, je souhaite connaître la valeur médiane du critère ANNEE.

Il semblerait que la solution à mon problème soit toute trouvée en passant par le langage PL/R (comme indiqué sur ce lien).

La première difficulté aura été d'installer l'extension PLR dans PostGreSQL. J'avais pas mal de messages d'erreur liés à des manques dans le répertoire lib de PostGreSQL. J'ai trouvé des indications pour régler tout ça en consultant ces 2 liens :
- www.bostongis.com - Installing PL/R
- georezo.net - Problème chargement langage PLR pour PostGreSQL 9.0

Remarque : Pour que ça fonctionne bien chez moi, il aura fallu que je redémarre le PC après avoir édité les variables d'environnement et placé les bons ddl dans le dossier lib de PostGreSQL.

Donc, une fois le langage PL/R installé dans PostGreSQL et prêt à l'emploi, j'ai testé via l'exemple donné dans le 1er lien indiqué en saisissant les requêtes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE FUNCTION r_median(_float8)
RETURNS float AS 'median(arg1)'
LANGUAGE 'plr';
 
CREATE AGGREGATE median (
sfunc = plr_array_accum,
basetype = float8,
stype = _float8,
finalfunc = r_median
);
 
CREATE TABLE foo(f0 int, f1 text, f2 float8);
INSERT INTO foo values(1,'cat1',1.21);
INSERT INTO foo values(2,'cat1',1.24);
INSERT INTO foo values(3,'cat1',1.18);
INSERT INTO foo values(4,'cat1',1.26);
INSERT INTO foo values(5,'cat1',1.15);
INSERT INTO foo values(6,'cat2',1.15);
INSERT INTO foo values(7,'cat2',1.26);
INSERT INTO foo values(8,'cat2',1.32);
INSERT INTO foo values(9,'cat2',1.30);

Jusque là tout va bien. Et lorsque je saisis la requête finale, censée me donner le résultat attendu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT f1, median(f2) FROM foo GROUP BY f1 ORDER BY f1;

J'ai droit au message d'erreur suivant :

ERREUR: R interpreter expression evaluation error
DETAIL: Error in (PLR32995 <- function(arg1) { : could not find function "median"
CONTEXT: In PL/R function r_median
********** Erreur **********

ERREUR: R interpreter expression evaluation error
État SQL :22000
Détail :Error in (PLR32995 <- function(arg1) { : could not find function "median"
Contexte : In PL/R function r_median

Avec mes maigres compétences et malgré de nombreuses recherches sur le net, je n'ai pas réussi à trouver l'origine de ce problème et la solution pour le régler... Si parmi vous il y en a qui sauraient m'aider, je les remercie par avance.

[Waldar]

Pour PL/R je ne saurais pas vous aider, mais la fonction médiane vous pouvez l'implémenter en SQL :
http://wiki.postgresql.org/wiki/Aggregate_Median

[SQLpro]

Faites un PERCENT_RANK() (fonction de fenêtrage) et cherchez la valeur la plus proche de 0.5.
À lire, sur les fonctions de fenêtrage : http://sqlpro.developpez.com/article...clause-window/

A +

[nemikap]

Pour PL/R je ne saurais pas vous aider, mais la fonction médiane vous pouvez l'implémenter en SQL :
http://wiki.postgresql.org/wiki/Aggregate_Median

Merci beaucoup Waldar, cela a complètement réglé mon problème !

Éventuellement, pour ceux que ça pourrait intéresser, voici ce que j'ai fait

Surpression de la fonction median que j'avais créé lors de la tentative d'utilisation du langage PL/R avec la commande suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DROP AGGREGATE median (float8);

Pour mémoire, pour voir si la fonction existe bien, on peut le savoir avec la commande suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pg_proc WHERE "proname" LIKE 'median';

J'ai ensuite rentré les requêtes indiquées dans le lien de Waldar :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE FUNCTION _final_median(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
 
CREATE AGGREGATE median(numeric) (
  SFUNC=array_append,
  STYPE=numeric[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);

Ce qui m'a permis de faire fonctionner la requête finale (testé sur l'exemple de table foo évoquée dans le 1er post) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT f1, median(f2) FROM foo GROUP BY f1 ORDER BY f1;

Et pour finir, juste une petit remarque pour préciser qu'il faut bien veiller à ce que le type des données pour lesquelles on souhaite connaître la médiane soit identique à celui renseigné dans la fonction median. Dans le cas présent, il fallait que les données f2 soient de type numeric. Par conséquent pour créer la table foo, la requête correcte était la suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TABLE foo(f0 int, f1 text, f2 numeric);

Faites un PERCENT_RANK() (fonction de fenêtrage) et cherchez la valeur la plus proche de 0.5.
À lire, sur les fonctions de fenêtrage : http://sqlpro.developpez.com/article...clause-window/

A +

Merci également SQLpro ! Je testerai cette solution pour comparer. Par ailleurs, votre lien m'a permis de découvrir qu'il existait une fonction NTILE qui pourrait m'être bien utile pour connaître les déciles et centiles !