Forums PostgreSQL.fr

slenoble

Membre

Calcule d'une médiane sous postGreSql

Bonjour à tous,
je suis confronté à un problème qui me semblait simple à la base mais qui au final me bloque depuis quelques temps.
J'ai besoin de calculé une médiane sur un champs d'une table.
J'ai trouvé un code qui fait presque le travail (sur Postgre version au dela de 10) :

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='{}'
);

Je dis que ça fait presque le job car j'ai un pb de valeur manquante …. cette fonction, considère les valeurs "NULL" comme une modalité … je voudrais la même chose mais en ne tenant pas compte des valeurs NULL.

J'ai un niveau de base en SQL, et je n'ai jusque là jamais crée de fonction, du coup j'avoue ne pas tout comprendre au code ci-dessus.

Est ce que quelqu'un peut m'aider ?

Merci beaucoup par avance,

Stéphane

Marc Cousin

Membre

Re : Calcule d'une médiane sous postGreSql

Bonjour,

Une fonction d'agregation, c'est pas vraiment ce qui se fait de plus simple pour commencer, mais bon, essayons quand même...

Ce qu'on dit avec le create aggregate, c'est que pour calculer cet agrégat, on a une variable d'état qui est un tableau de numeric, qu'à chaque nouvel élément on l'ajoute à la fin du tableau (array_append), et qu'une fois qu'on a vu tous les enregistrements de l'agrégat, on appelle _final_median pour retourner la valeur de l'agrégat. initcond permet d'initialiser la variable d'état à un tableau vide.

La fonction est une fonction SQL (donc une simple requête). La valeur de retour de la fonction est donc le résultat de la requête. Les paramètres sont numérotés $1, $2, etc… (on peut leur donner des noms, mais là, vu qu'il n'y en a qu'un…)

Comme $1 est un tableau, on l'unnest (pour en refaire une liste d'enregistrements). On trie par la sortie de l'unnest (logique), et on récupère soit un soit deux enregistrements (suivant la valeur du modulo 2 de array_upper($1,1), c'est à dire la taille du tableau). Donc si le tableau a un nombre impair d'enregistrements, on prend 1 enregistrements, si il a un nombre pair, on en prend deux (pour calculer la moyenne). Même idée pour l'offset, on prend le ceil(milieu du tableau). Donc ce subselect nous retourne le ou les deux enregistrements du «milieu». La requête principale en retourne la moyenne.

Maintenant que tout ça est posé, pourquoi on comptabilise les null? Parce qu'ils sont dans le tableau. Donc pour éviter ça, deux solutions (désolé je vais dire des évidences ):
- ne pas les avoir dans le tableau
- les ignorer à la fin

Pour ne pas les avoir dans le tableau, il faut utiliser une fonction "stricte" d'état, c'est à dire une fonction déclarée comme stricte (c'est un des arguments de create function). C'est à dire une fonction qui retourne NULL quand on lui passe NULL. Évidemment, array_append n'est pas stricte. On peut par exemple pour se sortir de votre problème déclarer une fonction stricte qui fait comme array_append, mais déclarée stricte. Quelque chose comme (je n'ai pas testé):

CREATE  OR REPLACE FUNCTION _strict_array_append(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY STRICT AS 
$$
  SELECT array_append($1,$1)
$$

(array_append est polymorphique, autant faire pareil)

Qu'il suffira d'utiliser dans l'agrégat à la place de l'autre.

Sinon, l'autre solution, c'est juste d'ignorer les NULL dans la fonction finale (moins bon d'un point de vue algorithmique évidemment, mais peut être plus simple). Là il y a plein de solutions… je pense que le plus simple, c'est une fonction intermédiaire qui supprime les null avant de les envoyer à la fonction finale... array_remove le fait très bien.

CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
   RETURNS NUMERIC AS
$$
   SELECT _final_median_filtered(array_remove($1,NULL));
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION _final_median_filtered(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;

---

Marc.

dverite

Membre

Re : Calcule d'une médiane sous postGreSql

Pour un calcul de médiane tout fait en postgresql moderne, on peut utiliser percentile_cont ou percentile_disc.
Voir https://forums.postgresql.fr/viewtopic.php?id=4218

---

@DanielVerite
http://blog-postgresql.verite.pro/

slenoble

Membre

Re : Calcule d'une médiane sous postGreSql

Bonjour,
merci beaucoup.
C'est déjà beaucoup plus clair (et presque évident avec l'explication) sur le fonctionnement du code dont je disposais.
Je vais tester tout cela et je vous tiens au courant.

Stéphane

slenoble

Membre

Re : Calcule d'une médiane sous postGreSql

Pour un calcul de médiane tout fait en postgresql moderne, on peut utiliser percentile_cont ou percentile_disc.
Voir https://forums.postgresql.fr/viewtopic.php?id=4218

tout simplement en fait.
un grand grand merci !!!