Forums PostgreSQL.fr

Fanch

Membre

Date mediane

Bonjour,

Je cherche à calculer la date médiane (ou à défaut moyenne) par groupe, de la table suivante :
id  gr   date_stade
1   A     2014-05-22
2   A     2014-05-19
3   A     2014-05-20
4   B     2014-05-15
5   B     2014-05-13
6   B     2014-05-14
7   B     2014-05-16

Pour donner un peu de contexte, je suis des plants de blé (1 id = 1 plant de blé) qui ont atteint un stade particulier : l'épiaison qui se caractérise par une date. Je souhaite pour chaque variété (=chaque groupe), connaître la date à laquelle 50% de mes plants de blé ont passé le stade en question, soit la date médiane. J'ai déjà pas mal cherché et je ne trouve pas de solution adaptée pour calculer une date médiane au travers d'une requête ou de la création d'une fonction.

Le résultat attendu serait ici:
   gr    date_stade_mediane
1  A    2014-05-20
2  B    2014-05-14

Pouvez-vous m'aider ?

Merci.

damalaan

Membre

Re : Date mediane

Bonjour

un truc dans ce genre là :

select gr, min(date_stade) + (max(date_stade) - min(date_stade))/2
 from latable group by gr;

Dernière modification par damalaan (22/05/2014 12:00:44)

Fanch

Membre

Re : Date mediane

Merci pour cette première solution qui me donne bien la date moyenne.

Néanmoins, la question est toujours ouverte pour le calcul d'une date médiane : date à laquelle 50% de mes plants de blé ont passé le stade en question.

damalaan

Membre

Re : Date mediane

voici un lien qui pourrait vous intéresser :
http://www.bortzmeyer.org/postgresql-quantiles.html

meles

Membre

Re : Date mediane

Bonjour, s'agissant de traiter une médiane, j'imagine qu'au delà de la date médiane, il s'agit de calculer le délai median entre la plantation et l'épiaison. Pourquoi dans ce cas ne pas calculer ces délais en amont, quitte à reconvertir en date si  besoin. Faire une médiane sur un entier est déjà plus simple. Ne pas oublier également que la médiane se calcule différemment suivant que le nombre de valeurs est pair ou impair.

Mais faire des stats sur des dates, c'est toujours assez prise de tête.

@+

meles

Membre

Re : Date mediane

Sinon, pour repartir de ton exemple:

/*
create table test ( id int, gr char(1), stade date);
insert into test values (1,'A','2014-05-22');
insert into test values (2,'A','2014-05-19');
insert into test values (3,'A','2014-05-20');
insert into test values (4,'B','2014-05-15');
insert into test values (5,'B','2014-05-13');
insert into test values (6,'B','2014-05-14');
insert into test values (7,'B','2014-05-16');
*/

with foo as(
 with tmp as (
  select gr, stade, rank() over (partition by gr order by stade asc) from test
  )
 select gr, round(max(rank+1)/2) as rank
 from tmp 
 group by gr), bar as (select gr, stade, rank() over (partition by gr order by stade asc) from test
)
select bar.gr, bar.stade
from foo inner join bar using (gr, rank)

me donne:
"A";"2014-05-20"
"B";"2014-05-14"

mais vu le code alambiqué, je doutes que ce soit très perfomant sur des très grosse séries.

Cordialement

meles

Membre

Re : Date mediane

Autre solution, en se référent à la formule de cette page: http://wiki.postgresql.org/wiki/Aggregate_Median
on convertit les dates en délais (admettons que ma plantation soit en 2014-03-01):

select gr,median(stade-'2014-03-01')  from test group by gr

a rajouter à la date de plantation (mais j'ai du mal avec les opération sur les dates).

Cordialement

Dernière modification par meles (23/05/2014 20:24:01)

Fanch

Membre

Re : Date mediane

Bonjour,
Encore merci pour vos suggestions. Quelques précisions sont ici à prendre en compte:
- Dans le cas bien particulier de mes dates de stade, c'est bien une date et non pas un délai. En effet, on cherche la date médiane d'atteinte du stade quelque soit la date de semis qui peut légèrement varier. Par contre utiliser une date de référence autre que le semis comme tu le proposes me paraît intéressant.
- Dans le cas de date de stade, la contrainte de calcul de la médiane nombre pair/impair à moins de sens, on peut s'en affranchir. On cherche véritablement la date à partir de laquelle au moins 50% des plants on passé le stade.

Enfin, j'ai testé ta requête meles, et celle ci me va complètement (le volume de mes données n'est pas énorme), et me donne un bon exemple que je tacherai d'utiliser par la suite. Pour la dernière proposition basée sur l'utilisation de la fonction median proposées ici http://wiki.postgresql.org/wiki/Aggregate_Median, je m'étais bien penché dessus et ne voyais pas comment l'utiliser dans mon cas, je vais regarder ça de plus prêt.

En tout les cas, merci pour vos réponses.

Fanch

Membre

Re : Date mediane

PS : Finalement, la requête utilisée :

1/ Je crée la fonction médiane d'après http://wiki.postgresql.org/wiki/Aggregate_Median:

CREATE FUNCTION _final_median(anyarray) RETURNS float8 AS $$ 
  WITH q AS
  (
     SELECT val
     FROM unnest($1) val
     WHERE VAL IS NOT NULL
     ORDER BY 1
  ),
  cnt AS
  (
    SELECT COUNT(*) AS c FROM q
  )
  SELECT AVG(val)::float8
  FROM 
  (
    SELECT val FROM q
    LIMIT  2 - MOD((SELECT c FROM cnt), 2)
    OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)  
  ) q2;
$$ LANGUAGE sql IMMUTABLE;
 
CREATE AGGREGATE median(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_median,
  INITCOND='{}'
);

2/ Je l'utilise dans mon cas bien particulier en définissant une date d'origine antérieure à toute mes dates (en prenant le 1 mars 1900 dans mon cas) :

SELECT num_year, id_station, txt_variety, txt_stage,
	cast(median(dat_stage-'1900-03-01') as integer) + '1900-03-01'::date dat_stage_median
FROM t_plot JOIN t_stage ON t_plot.id_plot=t_stage.id_plot
GROUP BY num_year, id_station, txt_variety, txt_stage
ORDER BY num_year, id_station, txt_variety, txt_stage

Encore merci.

SQLpro

Membre

Re : Date mediane

À lire : Calcul de la médiane en SQL

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *