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
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