Comment arrondir une moyenne à 2 décimales dans PostgreSQL ?

Question

J'utilise PostgreSQL via la gemme Ruby 'sequel'.

J'essaie d'arrondir à deux décimales.

Voici mon code :

SELECT ROUND(AVG(some_column),2)    
FROM table

J'obtiens l'erreur suivante :

PG::Error: ERROR:  function round(double precision, integer) does 
not exist (Sequel::DatabaseError)

Je n'obtiens aucune erreur lorsque j'exécute le code suivant :

SELECT ROUND(AVG(some_column))
FROM table

Quelqu'un sait-il ce que je fais de travers ?

Answer 1

PostgreSQL ne définit pas round(double precision, integer) . Pour des raisons Mike Sherrill "Cat Recall" (rappel de chat) explique dans les commentaires, la version de round qui prend une précision n'est disponible que pour les numeric .

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist

regress=> \df *round*
                           List of functions
   Schema   |  Name  | Result data type | Argument data types |  Type  
------------+--------+------------------+---------------------+--------
 pg_catalog | dround | double precision | double precision    | normal
 pg_catalog | round  | double precision | double precision    | normal
 pg_catalog | round  | numeric          | numeric             | normal
 pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)

regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
 round 
-------
  3.14
(1 row)

(Dans ce qui précède, il convient de noter que float8 est juste un alias pour double precision . Vous pouvez voir que PostgreSQL le développe dans la sortie).

Vous devez convertir la valeur à arrondir en numeric pour utiliser la forme à deux arguments de round . Il suffit d'ajouter ::numeric pour la version abrégée, comme round(val::numeric,2) .

---

Si vous mettez en forme pour l'affichage à l'utilisateur, n'utilisez pas la fonction round . Utiliser to_char (voir : fonctions de formatage des types de données dans le manuel), qui vous permet de spécifier un format et vous donne une text qui n'est pas affecté par les bizarreries que le langage de votre client peut faire avec numeric valeurs. Par exemple :

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
    to_char    
---------------
 3.14
(1 row)

to_char arrondit les chiffres pour vous dans le cadre du formatage. Les FM Le préfixe indique to_char que vous ne voulez pas de remplissage avec des espaces en début de ligne.

Answer 2

((ceci est un Wiki ! veuillez éditer pour améliorer !))

Essayez également l'ancienne syntaxe pour la coulée,

SELECT ROUND( AVG(some_column)::numeric, 2 ) FROM table;

fonctionne avec n'importe quelle version de PostgreSQL.
...Mais, en guise de solution définitive, vous pouvez surcharge la fonction ROUND.

La surcharge comme stratégie de coulée

CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $f$
  SELECT ROUND( CAST($1 AS numeric), $2 )
$f$ language SQL IMMUTABLE;

Votre instruction va maintenant fonctionner correctement, essayez cette comparaison complète :

SELECT trunc(n,3), round(n,3) n_round, round(f,3) f_round,
       pg_typeof(n) n_type, pg_typeof(f) f_type, pg_typeof(round(f,3)) f_round_type
FROM (SELECT 2.0/3.0, 2/3::float) t(n,f);

tronqué

n_round

f_round

n_type

f_type

f_round_type

0.666

0.667

0.667

numérique

double précision

numérique

En ROUND(float,int) est f_round Il renvoie un type de données NUMERIC (décimal), ce qui convient à certaines applications : problème résolu !

Dans d'autres applications, nous avons besoin d'un nombre flottant comme résultat. Une alternative est d'utiliser round(f,3)::float ou de créer un round_tofloat() fonction. Autre solution, la surcharge ROUND et en utilisant toutes les plages de la fonction exactitude-précision d'un nombre à virgule flottante, est de retourner un flottant lorsque la précision est définie (voir Réponse de IanKenney ),

CREATE FUNCTION ROUND(
  input float,     -- the input number
  accuracy float   -- accuracy, the "counting unit"
) RETURNS float AS $f$
   SELECT ROUND($1/accuracy)*accuracy
$f$ language SQL IMMUTABLE;

Essayer

  SELECT round(21.04, 0.05);     -- 21.05 float!
  SELECT round(21.04, 5::float); -- 20
  SELECT round(1/3., 0.0001);    -- 0.3333
  SELECT round(2.8+1/3., 0.5);   -- 3.15
  SELECT round(pi(), 0.0001);    -- 3.1416

PS : la commande \df round , le psql après les surcharges, il apparaîtra quelque chose comme ce tableau

 Schema     |  Name | Result  | Argument  
------------+-------+---------+------------------
 myschema   | round | numeric | float, int
 myschema   | round | float   | float, float
 pg\_catalog | round | float   | float            
 pg\_catalog | round | numeric | numeric   
 pg\_catalog | round | numeric | numeric, int          

où flotteur est synonyme de double precision y myschema es public lorsque vous n'utilisez pas de schéma. Les pg_catalog sont les fonctions par défaut, voir au Guide la fonction fonctions mathématiques intégrées .

Arrondissement et mise en forme

En to_char s'applique en interne à la fonction ronde Ainsi, lorsque votre objectif est uniquement d'afficher un résultat final dans le terminal, vous pouvez utiliser la procédure FM modificateur comme préfixe d'un modèle de format numérique :

SELECT round(x::numeric,2), trunc(x::numeric,2), to_char(x, 'FM99.99')
FROM (SELECT 2.0/3) t(x);

ronde

tronqué

to_char

0.67

0.66

.67

---

NOTES

Cause du problème

Il n'y a pas de surcharges dans certaines fonctions PostgreSQL, pourquoi ( ???): Je pense que "c'est un manque" ( !), mais @CraigRinger, @Catcall et l'équipe PostgreSQL sont d'accord à propos de "La raison d'être historique de la pg" .

Note sur la performance et la réutilisation

Les fonctions intégrées, telles que ROUND de pg_catalog, peuvent être surchargées sans perte de performance par rapport à l'encodage direct. Deux précautions doivent être prises lors de l'implémentation de fonctions définies par l'utilisateur. des fonctions moulées pour des performances élevées :

• En IMMUTABLE est très importante pour les extraits de code comme celui-ci, car, comme le dit le Guide : "permet à l'optimiseur de pré-évaluer la fonction lorsqu'une requête l'appelle avec des arguments constants"

• PLpgSQL est le langage préféré, sauf pour le "SQL pur". Pour les Optimisations JIT (et parfois pour le parallélisme) language SQL peut obtenir de meilleures optimisations. Il s'agit par exemple de copier/coller un petit morceau de code au lieu d'utiliser un appel de fonction.

Conclusion : ce qui précède ROUND(float,int) après optimisation, est plus rapide que La réponse de @CraigRinger ; il compilera (exactement) le fichier même représentation interne . Ainsi, bien que ce ne soit pas un standard pour PostgreSQL, il peut l'être pour vos projets, grâce à une "bibliothèque de snippets" centralisée et réutilisable, telle que pg_pubLib .

---

Arrondir au nième bit ou autre représentation numérique

Certaines personnes affirment qu'il n'est pas logique pour PostgreSQL d'arrondir un nombre de type float, parce que float est une représentation binaire il faut arrondir le nombre de bits ou sa représentation hexadécimale.

Eh bien, résolvons le problème en ajoutant une suggestion exotique... Le but ici est de renvoyer un flotteur dans une autre fonction surchargée,
  ROUND(float, text, int) RETURNS float
En text est de proposer un choix entre

• 'dec' pour "représentation décimale",
• 'bin' pour la représentation "binaire" et
• 'hex' pour la représentation hexadécimale.

Ainsi, dans différentes représentations, nous avons un interprétation différente du nombre de chiffres à arrondir. Arrondir un nombre x avec une valeur approximativement plus courte, avec moins de "chiffres fractionnaires" (que sa valeur d'origine). d chiffres), sera plus courte lorsque d utilise des chiffres binaires au lieu de chiffres décimaux ou hexadécimaux.

Ce n'est pas facile sans C++, en utilisant du "SQL pur", mais ces extraits de code illustrent la situation et peuvent être utilisés comme solution de contournement :

-- Looking for a round_bin() function! this is only a workaround:
CREATE FUNCTION trunc_bin(x bigint, t int) RETURNS bigint AS $f$
    SELECT ((x::bit(64) >> t) << t)::bigint;
$f$ language SQL IMMUTABLE;

CREATE FUNCTION ROUND(
   x float, 
   xtype text,  -- 'bin', 'dec' or 'hex'
   xdigits int DEFAULT 0
) 
RETURNS FLOAT AS $f$
    SELECT CASE
       WHEN xtype NOT IN ('dec','bin','hex') THEN 'NaN'::float
       WHEN xdigits=0 THEN ROUND(x)
       WHEN xtype='dec' THEN ROUND(x::numeric,xdigits)
       ELSE (s1 ||'.'|| s2)::float
      END
    FROM (
      SELECT s1,
             lpad( 
               trunc_bin( s2::bigint, CASE WHEN xd<bin_bits THEN bin_bits - xd ELSE 0 END )::text,
               l2,
               '0'
             ) AS s2
      FROM (
        SELECT *, 
             (floor( log(2,s2::numeric) ) +1)::int AS bin_bits, -- most significant bit position
             CASE WHEN xtype='hex' THEN xdigits*4 ELSE xdigits END AS xd
        FROM (
          SELECT s[1] AS s1, s[2] AS s2, length(s[2]) AS l2
          FROM (SELECT regexp_split_to_array(x::text,'\.')) t1a(s)
        ) t1b
      ) t1c
    ) t2
$f$ language SQL IMMUTABLE;

Essayer

 SELECT round(1/3.,'dec',4);     -- 0.3333 float!
 SELECT round(2.8+1/3.,'dec',1); -- 3.1 float!
 SELECT round(2.8+1/3.,'dec');   -- ERROR, need to cast string 
 SELECT round(2.8+1/3.,'dec'::text); -- 3 float
 SELECT round(2.8+1/3.,'dec',0); -- 3 float

 SELECT round(2.8+1/3.,'hex',0); -- 3 float (no change)
 SELECT round(2.8+1/3.,'hex',1); -- 3.1266
 SELECT round(2.8+1/3.,'hex',3); -- 3.13331578486784

 SELECT round(2.8+1/3.,'bin',1);  -- 3.1125899906842625
 SELECT round(2.8+1/3.,'bin',6);  -- 3.1301821767286784
 SELECT round(2.8+1/3.,'bin',12); -- 3.13331578486784

Y \df round ont également :

 Schema     |  Name | Result  | Argument  
------------+-------+---------+---------------
 myschema   | round | float   | x float, xtype text, xdigits int DEFAULT 0

Answer 3

Essayez avec ceci :

SELECT to_char (2/3::float, 'FM999999990.00');
-- RESULT: 0.67

Ou simplement :

SELECT round (2/3::DECIMAL, 2)::TEXT
-- RESULT: 0.67

Answer 4

Vous pouvez utiliser la fonction ci-dessous

 SELECT TRUNC(14.568,2);

le résultat montrera :

14.56

vous pouvez également convertir votre variable au type désiré :

 SELECT TRUNC(YOUR_VAR::numeric,2)

Answer 5

Essayez de transformer votre colonne en un nombre comme :

SELECT ROUND(cast(some_column as numeric),2) FROM table