Forums PostgreSQL.fr

Anne-Lise B

Membre

Calculer une médiane pondérée

Bonjour,

Je souhaite calculer  la médiane d'une variable à partir d'une table de données incluant une pondération.

J'ai effectué le code suivant mais le résultat n'est pas le bon (j'ai calculé la médiane en amont). Quelqu'un saurait-il d'où vient l'erreur ?
Je suppose que je n'incorpore pas ma pondération au bon moment...

with
nom_table1 as (
select (variable*ponderation)/sum(ponderation) as variable_ponderee
from table_source
group by variable, ponderation)
select median (variable_ponderee)
from nom_table1

Merci d'avance pour votre éclairage

Dernière modification par Anne-Lise B (24/01/2018 17:40:14)

damalaan

Membre

Re : Calculer une médiane pondérée

Un exemple chiffré pourrait permettre de mieux y voir clair, mais vu la requête, je pense que vous retombez direct sur la valeur de votre variable, elle n'est donc pas pondérée.
Faites une mediane sur vos valeurs non pondérées pour voir.......
Bizarre également la fonction median........ce n'est pas implémenté tel que dans postgresql (il faut ça SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY champ) FROM TABLE pour avoir la mediane)

Anne-Lise B

Membre

Re : Calculer une médiane pondérée

Bonjour,

Merci pour ces premiers éléments. Pour être plus clair dans ma demande, voici un extrait simplifié de ma table brute :
Primary key    loyer_m2    Ponderation
1    10.431    3.564
2    10.938    5.554
3    12.121    5.554
4    10.606    5.554
5    11.045    3.182
6    9.219    11.705
7    11.000    11.705
8    7.789    5.419
9    8.479    12.553
10    8.409    5.419
11    11.443    10.145
12    11.864    8.484
13    11.106    8.484

Comme vous le voyez, j'ai un loyer au m² pour chaque ligne qui correspond à un logement. Ces résultats provenant d'une enquête, des pondérations ont été calculées afin d'être représentatifs.
Je souhaiterais donc à partir de cette table calculer le loyer au m² médian pondéré.
Je comprends que la fonction médian que j'ai utilisé n'est pas à conserver...

Quelle requête écririez-vous pour obtenir ce résultat ?

Merci pour vos éclairages !!!!!

damalaan

Membre

Re : Calculer une médiane pondérée

je ne connaissais pas la médiane pondérée, merci donc à wikipedia : https://en.wikipedia.org/wiki/Weighted_median#Examples
ça n'a pas l'air simple comme ça....
Il semble que des logiciel de stats tel R propose ce genre de fonction (weight.median) (une extension existe pour postgres)
Je ne pense pas que l'on puisse simplement trouver la médiane pondérée sans passer par un certain nb d'étape intermédiaire ou un prog en pg/sql.

Néanmoins, rien n'étant impossible, qu'attendez-vous comme résultat final avec le jeu de données précédent ?

dverite

Membre

Re : Calculer une médiane pondérée

C'est une requête compliquée mais a priori faisable directement avec les sommes cumulatives en fenêtrage.

Une requête MS-SQL qui a l'air de bien correspondre à la définition de wikipedia, est proposée ici sur un forum MS (c'est la réponse validée):

https://social.msdn.microsoft.com/Forum … ransactsql

WITH runsums AS (
   SELECT  x,             SUM(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runsum,
           SUM(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevsum,
           SUM(y) OVER ()  AS total
   FROM    Age
)
SELECT x
FROM   runsums
WHERE  total / 2 BETWEEN prevsum AND runsum

Elle est portable telle quelle sur PostgreSQL, il n'y a qu'à changer les noms des colonnes et de la table.

---

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

Anne-Lise B

Membre

Re : Calculer une médiane pondérée

Merci beaucoup pour tous ces éléments. Je vais tester tout de suite cette fonction pour voir si cela fonctionne !
Sinon, je passerai sous R qui propose effectivement comme SAS d'indiquer la présence d'une pondération via "weight"

damalaan

Membre

Re : Calculer une médiane pondérée

j'étais parti sur ce genre de chose avec les fonctions de fenêtrage mais en découpant beaucoup (trop) les requêtes, en ramenant les pondération à 1...... et j'obtiens 10.80

je vous mets quand même ma requête

with b as (
select sum(pond) as total from test),
c as (
select prix, round(pond / total,2) as pond2 from test, b),
d as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix range between unbounded preceding and current row) as cumul_asc
from c),
e as (
select prix, pond2,
sum(pond2) over (partition by 1 order by  prix desc range between unbounded preceding and current row) as cumul_desc
from c),
f as (
select max(d.prix) as resultat from d where cumul_asc <=.5 
union 
select min(e.prix)  from e where cumul_desc <=.5)
select avg(resultat)  from f

avec la solution de dverite, on obtient 10.94

En R, avec la fonction weighted.median de la library spatstat, avec le jeu de données fourni j'obtiens 10.87

Dernière modification par damalaan (30/01/2018 10:37:10)

dverite

Membre

Re : Calculer une médiane pondérée

Avec un nombre de valeurs impaires, je m'attendrais à ce que la médiane soit exactement un élément de la liste.

10.94 doit correspondre à 10.938 avec un arrondi à 2 décimales, mais 10.87 correspondrait à quoi?

---

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

damalaan

Membre

Re : Calculer une médiane pondérée

d'après ce que j'ai pu comprendre et vérifier par le calcul, R recherche les "limites" encadrantes et calcule la moyenne pondérée de ces limites :

prix	pondération
10.606	5.554
11	11.705

en calculant la moyenne pondérée on tombe sur 10.873
(10.606*5.554+11*11.705)/(5.554+11.705)

damalaan

Membre

Re : Calculer une médiane pondérée

si je pondère ma moyenne finale j'obtiens la même chose qu'en R : 10.873 (c'est moche et tordu.....)

with b as (
select sum(pond) as total from test
),
c as (
select prix, round(pond / total,2) as pond2 from test, b),
d as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix range between unbounded preceding and current row) as cumul_asc
from c),
e as (
select prix, pond2,
sum(pond2) over (partition by 1 order by  prix desc range between unbounded preceding and current row) as cumul_desc
from c),
f as (
select max(d.prix) as resultat from d where cumul_asc <=.5 
union 
select min(e.prix)  from e where cumul_desc <=.5)
select sum(resultat*pond)/sum(pond) from f,test where resultat=prix

Anne-Lise B

Membre

Re : Calculer une médiane pondérée

Bonjour et un grand merci pour votre aide.
J'ai appliqué l'ensemble de vos solutions à ma base globale. Je connaissais le loyer médian pondéré à trouver et souhaitais pouvoir effectuer d'autres médianes (par exemple selon le type de pièce, l'âge de construction, ...).
J'ai donc appliqué vos requêtes pour calculer la médiane global que je connaissais et je retrouve bien le résultat avec le programme suivant :

WITH runsums AS (
   SELECT  x,             SUM(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runsum,
           SUM(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevsum,
           SUM(y) OVER ()  AS total
   FROM    Age
)
SELECT x
FROM   runsums
WHERE  total / 2 BETWEEN prevsum AND runsum

J'avoue ne pas savoir pourquoi seul ce programme fonctionne mais c'est bon à savoir

Pour information, ma variable pondération étant en double précision, j'ai du modifier la fonction round du programme de damalaan
round(pond / total,2) est devenu round(pond / total)

Encore merci à vous et pour votre collaboration !!!!

dverite

Membre

Re : Calculer une médiane pondérée

d'après ce que j'ai pu comprendre et vérifier par le calcul, R recherche les "limites" encadrantes et calcule la moyenne pondérée de ces limites

Si l'on en croit l'explication de wikipedia, on doit faire ça quand on tombe dans le "Special Case", pas dans le "General Case".

"Consider a set of elements in which two of the elements satisfy the general case. This occurs when both element's respective weights border the midpoint of the set of weights without encapsulating it; Rather, each element defines a partition equal to 1 / 2 . These elements are referred to as the lower weighted median and upper weighted median"

Je n'ai pas fait le calcul avec les données d'exemple pour savoir dans quel cas on était, mais la requête que j'ai proposé étant centrée sur le  "SELECT x FROM runsums", il me paraît clair qu'elle ne gère que le "General Case".

---

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