Médiane avec PostgreSQL

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 209
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Médiane avec PostgreSQL

Message par Phil » mar. 16 oct. 2018 11:59

Merci à Yves pour sa question :

La fonction « median » n’existant pas directement actuellement, nous cherchons le moyen le plus approprié d’obtenir la mediane en postgreSQL (avec, si possible, le même comportement qu’en oracle).
• On a pu trouver la piste de la création d’une fonction d’agrégat ( https://wiki.postgresql.org/wiki/Aggregate_Median ) mais les exemples paraissaient imparfaits (gestion du null, vélocité) ou nécessiteraient une implémentation externe.
• On a aussi vu une notation en 9.4+ (https://forums.postgresql.fr/viewtopic.php?id=4218 ).
with truc as ( select 1 as machin
union all
select 3 as machin
union all
select 4 as machin
union all
select null as machin
union all
select null as machin
union all
select null as machin
union all
select 100 as machin
)
SELECT (percentile_disc(0.5) WITHIN GROUP (ORDER BY machin)) FROM truc;
Mais elle nous renvoie => 3 (alors que le median d’oracle nous renverrait un 3.5)
Nous poursuivons les recherches mais aurais tu connaissance, de ton côté, d’un moyen ou d’une forme plus appropriée (existante ou à venir) pour couvrir notre besoin ?


Réponse :

Tu peux reprendre l'idée de Julien mais utiliser percentile_cont plutôt que percentile_disc.
Exemple :

Code : Tout sélectionner


select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

with truc as (     select 1 as machin
                               union all
                               select 3 as machin
                               union all
                               select 4 as machin
                               union all
                               select null as machin
                               union all
                               select null as machin
                               union all
                               select null as machin
                               union all
                               select 100 as machin
                         )
SELECT (percentile_cont(0.5) WITHIN GROUP (ORDER BY machin)) FROM truc;

percentile_cont
-----------------
             3.5
             
A noter que ce code fonctionne aussi avec Oracle Database, exemple :

Code : Tout sélectionner

Live SQL 18.3.4, running Oracle Database 18c Enterprise Edition - 18.3.0.0.0

with truc as (     select 1 as machin from dual
                               union all
                               select 3 as machin from dual
                               union all
                               select 4 as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select 100 as machin from dual
                         )
SELECT (percentile_cont(0.5) WITHIN GROUP (ORDER BY machin)) FROM truc;

(PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYMACHIN))
3.5

with truc as (     select 1 as machin from dual
                               union all
                               select 3 as machin from dual
                               union all
                               select 4 as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select null as machin from dual
                               union all
                               select 100 as machin from dual
                         )
SELECT median(machin) FROM truc;

MEDIAN(MACHIN)
3.5
Cela devrait fonctionner avec DB2, SQL Server...et même MariaDB depuis la version 10.3.3.
Cdlt. Phil - pgphil.ovh

Répondre