Обсуждение: Rounding problems
Hi,
I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4.
The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS value - give 1.3.
Which could be the reason ??
-- data
fulldate timestamp; tables_seb.tbl_arvier_chamencon.id_1- reals
2009-03-29 00:00:00; 1.3
2009-03-29 00:30:00; 1.4
--Good query
SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric), 1 ) AS value
FROM
(
SELECT _master_30.fulldate AS data,
cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value
--tables_seb.tbl_arvier_chamencon.id_1 AS value
FROM _master_30
LEFT JOIN tables_seb.tbl_arvier_chamencon ON _master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate
WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND '2009-03-29 00:59:59'
ORDER BY data
) foo
GROUP BY 1 ORDER BY 1;
-- value = 1.4 OK
--wrong query
SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric), 1 ) AS value
FROM
(
SELECT _master_30.fulldate AS data,
--cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value
tables_seb.tbl_arvier_chamencon.id_1 AS value
FROM _master_30
LEFT JOIN tables_seb.tbl_arvier_chamencon ON _master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate
WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND '2009-03-29 00:59:59'
ORDER BY data
) foo
GROUP BY 1 ORDER BY 1
-- value = 1.3 NOT OK
-- test
select round(cast( (1.3 + 1.4)::real / 2 as numeric), 1);
-- value = 1.4 OK
Using PostgreSQL 8.3.7 on Windows Server 2008
Thank in advance,
Paolo Saudin
<br /><br /> Paolo Saudin wrote: <blockquote cite="mid:002801c9cbeb$f6b59c20$e420d460$@it" type="cite"><style>
<!--/* Font Definitions */@font-face{font-family:Wingdings;panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph,
div.MsoListParagraph{mso-style-priority:34;margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
span.StileMessaggioDiPostaElettronica17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
.MsoChpDefault{mso-style-type:export-only;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 2.0cm 2.0cm 2.0cm;}
div.Section1{page:Section1;}/* List Definitions */@list
l0{mso-list-id:361589963;mso-list-type:hybrid;mso-list-template-ids:-1476211862-1560001026 68157443 68157445 68157441
6815744368157445 68157441 68157443 68157445;}
@list
l0:level1{mso-level-start-at:0;mso-level-number-format:bullet;mso-level-text:\F06E;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;font-family:Wingdings;mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
@list l1{mso-list-id:892347903;mso-list-type:hybrid;mso-list-template-ids:686571502 512508354 68157443 68157445
6815744168157443 68157445 68157441 68157443 68157445;}
@list
l1:level1{mso-level-start-at:0;mso-level-number-format:bullet;mso-level-text:\F06E;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;font-family:Wingdings;mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
@list l2{mso-list-id:989483843;mso-list-type:hybrid;mso-list-template-ids:-722197268 311070654 68157443 68157445
6815744168157443 68157445 68157441 68157443 68157445;}
@list
l2:level1{mso-level-start-at:0;mso-level-number-format:bullet;mso-level-text:\F06E;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;font-family:Wingdings;mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
ol{margin-bottom:0cm;}
ul{margin-bottom:0cm;}
--> </style><div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I have a problem
witha query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average
withone decimals, should give 1.4.<p class="MsoNormal">The first query with - cast(
tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value - give the expected result, while the second one with -
tables_seb.tbl_arvier_chamencon.id_1AS value - give 1.3. <p class="MsoNormal"> <p class="MsoNormal">Which could be the
reason??<p class="MsoNormal"> <br /></div></blockquote> My first thought is whats with all the castings??? <br /><br
/>Castings are mostly likely the cause of your problems,<b> </b>What is <b>tbl_arvier_chamencon.id_1 </b>data
type???<br/><br /> I'm guessing its something other than numeric. All other floating point data types will have
problemscaused by Binary Floating-Point Arithmetic<br /><br /> Numeric data type uses different functions to do its
mathfor the stated purpose of being exact yet being allot slower. <br /><br /> In one query casting is done prior to
avg()yet in the other casting is done after avg(). This will allow Postgres to use different functions to calculate
averagegiving an unexpected result.<br />
"Paolo Saudin" <paolo@ecometer.it> writes:
> I have a problem with a query wich simple aggregate values. In the sample
> below I have two values, 1.3 and 1.4. Rounding their average with one
> decimals, should give 1.4.
You seem way overoptimistic about float4 values being exact. They are
not. The actual computation being done here is more like
regression=# select (1.3::real + 1.4::real) / 2 ;
?column?
------------------
1.34999990463257
(1 row)
If you want an exact sum with no roundoff error you should be storing
all your values as numeric (and taking the consequent speed and space
hit :-().
regards, tom lane
>Paolo Saudin wrote:
>Hi,
>
>I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4.
>The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS >value - give 1.3.
>
>Which could be the reason ??
>
>
>My first thought is whats with all the castings???
>
>Castings are mostly likely the cause of your problems, What is tbl_arvier_chamencon.id_1 data type???
>
>I'm guessing its something other than numeric. All other floating point data types will have problems caused by Binary Floating-Point Arithmetic
>
>Numeric data type uses different functions to do its math for the stated purpose of being exact yet being allot slower.
>
>In one query casting is done prior to avg() yet in the other casting is done after avg(). This will allow Postgres to use different functions to calculate average giving an unexpected >result.
Here is the table layout
CREATE TABLE tables_seb.tbl_arvier_chamencon
(
fulldate timestamp without time zone NOT NULL DEFAULT '2000-01-01 00:00:00'::timestamp without time zone,
id_1 real,
id_1_cod smallint,
id_2 real,
id_2_cod smallint,
id_3 real,
id_3_cod smallint,
id_4 real,
id_4_cod smallint,
CONSTRAINT tbl_arvier_chamencon_pkey PRIMARY KEY (fulldate)
) WITH ( OIDS=FALSE);
Thanks,
Paolo Saudin
>>"Paolo Saudin" <paolo@ecometer.it> writes: >> I have a problem with a query wich simple aggregate values. In the sample >> below I have two values, 1.3 and 1.4. Rounding their average with one >> decimals, should give 1.4. > >You seem way overoptimistic about float4 values being exact. They are >not. The actual computation being done here is more like > >regression=# select (1.3::real + 1.4::real) / 2 ; > ?column? >------------------ > 1.34999990463257 > (1 row) > >If you want an exact sum with no roundoff error you should be storing >all your values as numeric (and taking the consequent speed and space >hit :-(). > > regards, tom lane > I converted all the fields in numeric type instead of real and now both queries return the same result ! Now I need to test about performances ... Thank you very much !! Paolo Saudin