Re: [sqlite] Example/recipe for truncating fp numbers

[Keith Medcalf]

So here is the epsilon value for various scales of double precision floating 
point numbers (scale is the absolute base10 exponent):

scale=   0 epsilon= 2.220446049250313e-16
scale=   1 epsilon= 1.7763568394002505e-15
scale=   2 epsilon= 1.4210854715202004e-14
scale=   3 epsilon= 1.1368683772161603e-13
scale=   4 epsilon= 1.8189894035458565e-12
scale=   5 epsilon= 1.4551915228366852e-11
scale=   6 epsilon= 1.1641532182693481e-10
scale=   7 epsilon= 1.862645149230957e-09
scale=   8 epsilon= 1.4901161193847656e-08
scale=   9 epsilon= 1.1920928955078125e-07
scale=  10 epsilon= 1.9073486328125e-06
scale=  11 epsilon= 1.52587890625e-05
scale=  12 epsilon= 0.0001220703125
scale=  13 epsilon= 0.001953125
scale=  14 epsilon= 0.015625
scale=  15 epsilon= 0.125
scale=  16 epsilon= 2.0
scale=  17 epsilon= 16.0
scale=  18 epsilon= 128.0
scale=  19 epsilon= 2048.0
scale=  20 epsilon= 16384.0
scale=  21 epsilon= 131072.0
scale=  22 epsilon= 2097152.0
scale=  23 epsilon= 16777216.0
scale=  24 epsilon= 134217728.0
scale=  25 epsilon= 2147483648.0
scale=  26 epsilon= 17179869184.0
scale=  27 epsilon= 137438953472.0
scale=  28 epsilon= 219902322.0
scale=  29 epsilon= 17592186044416.0
scale=  30 epsilon= 140737488355328.0
scale=  31 epsilon= 1125899906842624.0

As you can see, the precision is based on the size of number.  So if you are 
doing operations on numbers that are all "relatively close" to each other in 
scale, the precision will be maintained.  However, if you do operations that 
mix scales, the precision of the result may be quite different from what one 
expects.

So, for example, if you are averaging 1000 fp numbers that are each in the 
range of 1000 (and thus contain 13 accurate digits after the decimal point 
maximum), when you have added them up the sum only has 10 significant digits 
(after the decimal point).  When you then divide by 1000, the resulting average 
will only be accurate to a maximum of 10 digits after the decimal point, not 
the 13 that you might expect because of the size of the result.

So, when computing an average that spans lots of values of varying sizes, you 
need to do either a running calculation (which keeps the "average so far", and 
moves that by the impact on the average of each additional value so that as 
there become more and more values the effect becomes less and less), or sort 
the input data by the absolute value of the magnitude and sum smallest 
(absolute value wise) first.

This is a simple example, but I am sure you can imagine the effect on a number 
of sequential operations (mixed addition and multiplications) can get out of 
hand quite quickly.  Modern CPUs will mitigate the effects somewhat by 
performing the actual individual operation in extended floating point (higher 
precision) and then converting the result back to double precision which will 
allow a much larger variability in the scale of the operands without 
significantly affecting the precision of the result.

As to ordering the set, I doubt it makes much difference whether you use a WITH 
subquery to generate the sorted set or do it in a subquery.  Of course, whether 
there is any difference in the result at all depends on the scale spread of the 
numbers you are working with.

select sum(i)/count(i) from (select i from dataset order by abs(i));

may or may not be (significantly) different from the simple

select avg(i) from dataset;

however the former (sorted input) will generate the more precise result, 
especially if there are many "smaller scale" numbers relative to the number of 
"larger scale" numbers (since those may accumulate to a value that is 
representable within the epsilon of the larger value, whereas if you do the sum 
in the other direction, the impact of the smaller scale numbers is just noise 
at the limits of precision).  (So for example if you were doing money, you add 
up the pennies, nickles, dimes, etc first.  They may accumulate to a large 
enough number to affect the sum even if one of the values added to the sum is a 
trillion dollars.  If you do it the other way around, then the effect of adding 
a penny to a trillion dollars is negligible -- though it would still be within 
the precision limits of double precision floating point).


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jean-Marie CUAZ
> Sent: Wednesday, 12 July, 2017 03:34
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Example/recipe for truncating fp numbers
> 
> Thank you M. Medcalf for your nice explanation.
> 
> In my first post, I gave half of the used solution : when storing a
> "truncated value", eventual remaining digits are allso, separately,
> stored as a whole integer.
> 
> Both parts are reassembled later when needed (i.e. when doing set
> agregation). The intent is to (imperfectly) recover lost precision

Re: [sqlite] Example/recipe for truncating fp numbers

[David Raymond]

Thanks, never in the deepest corner of my mind would I have expected it to be 
integer only. It was only reading this thread and wondering why no one had 
answered "just do x - x % .001" that made me go check it out. Fortunately I 
haven't used it any queries.

Is this an SQL standard? A C standard? SQLite specific? Other?

Returning a NULL I can deal with, but returning the "wrong answer" (my words) 
still freaks me out. That's like quietly having 1.3 + 1.4 = 2

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Tuesday, July 11, 2017 9:38 AM
To: SQLite mailing list
Subject: Re: [sqlite] Example/recipe for truncating fp numbers

On Tuesday, 11 July, 2017 07:24, David Raymond  said:


> Not to be the new guy here, but would someone be so good as to explain why
> no one else is panicking that the modulo operator is horrifically broken?
> In http://www.sqlite.org/lang_expr.html it just says:
> "The operator % outputs the value of its left operand modulo its right
> operand."
> 
> There's nothing in there that says anything about requirements for the
> operands to be integers or about quietly rounding the results on you etc.
> 
> sqlite> select 1. % .001;
> 1. % .001
> NULL
> Shouldn't that be .0004?
> 
> sqlite> select 1. % 2;
> 1. % 2
> 1.0
> Why is this not 1.? This is worse than the NULL by blatantly giving a
> wrong answer and letting you think it succeeded.

You are correct.  The modulus operator only works on integers, not on floating 
point.  The operands are converted to integer and the return value is the 
result of that integer modulus operation.  This should probably be noted in the 
documentation for the % operator.

SQLite does not natively implement the fmod(x, y) function from the platform 
math library.  You would have to add that yourself either as a loadable or 
builtin extension.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Jean-Marie CUAZ]

Thank you M. Medcalf for your nice explanation.

In my first post, I gave half of the used solution : when storing a 
"truncated value", eventual remaining digits are allso, separately, 
stored as a whole integer.


Both parts are reassembled later when needed (i.e. when doing set 
agregation). The intent is to (imperfectly) recover lost precision 
resulting from truncation at elementary level : naîve and ugly, but the 
best I'm able to do.


From your explanation, can I conclude that when doing summation on 
large sets of fp numbers, a prior sort on this set gives chances to a 
better accuracy of the result ?  In SQL, if the usage of a separate 
table to be filled in sorted order is not wanted, what would be the 
differences in efficiency/memory usage between a sub-select in the FROM 
clause compared to the same ordered set processed via a WITH clause ?


Thanks again.

-jm






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Keith Medcalf]

The issue here is that you are "truncating" using Base10 (Decimal) but the data 
is stored in Base2 Floating Point.  Therefore you will always get approximate 
results and never an exact one.  On modern computers floating point arithmetic 
is defined in such a way as to represent approximations to fixed point numbers 
with a few ULP of the scaled binary representation.  For magnitude 1 the 
epsilon (accuracy) of standard double-precision floating point is 
2.220446049250313e-16 (which is why we say that double precision floating point 
is accurate to 15 to 17 decimal digits, depending on who you ask).

When you perform floating point arithmetic operations, a sequence of pathologic 
calculations will end up with a representation accuracy equal to the ULP of the 
largest number in the calculation sequence.  There are various numerical 
calculation methods that avoid doing calculations in pathological order so that 
the representation is maintained to within a few ULP of the scale of the 
result.  (that is, since the result of any operation between two floating point 
numbers x and y with result z has a precision of max(epsilon(x), epsilon(y), 
epsilon(z)), one can ensure maximum precision by re-ordering operations to 
ensure that epsilon(x) and epsilon(y) are as close to the same as possible, for 
each operation in the chain, such as by doing running averages rather than 
simple sum/division averages, etc.)

However, when you take any floating point number and "truncate" it, it no 
longer has the precision defined by the machine representation epsilon.  Using 
that truncated result in any future calculation will (likely depending on what 
you do with it) lead to massive precision loss.  "Rounding" intermediates, by 
whatever method, also usually leads to massive precision loss.

That is why one should always store and work with floating point data at its 
native precision and only truncate/round a result that will never participate 
in any further calculation.

While cast(value * 1000 as integer) / 1000.0 is very fast, it will of course 
only work for numbers where the scaled number fits in a 64-bit signed integer.

trunc(value, 3) which does the truncation (towards 0) and maintains precision

means that the operation is performed by the math library or floating point 
unit in its usual extended precision and the result is then converted back to 
double precision using the IEEE-754 standard conversion, so the result is 
represented as an approximation to within a few ULP of the truncated value.  
However, the value of the input and the value of the output may be entirely 
different as they are still approximations (to different values also).

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jean-Marie CUAZ
> Sent: Tuesday, 11 July, 2017 05:35
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Example/recipe for truncating fp numbers
> 
> To M. Slavin, Medcalf ans Hipp:
> 
> Thanks for your attention.
> 
> Yes, "cast(value * 1000 as integer) / 1000.0" is much better and I will
> switch to your formula when possible, thanks very much.
> 
> Thank you for your offer of C functions : a trunc() function is
> certainely what is needed, but I don't understand the sentence
> 
> "trunc(value, 3) which does the truncation (towards 0) and maintains
> precision"
> 
> What is needed is something like substr(value,1,Y) (as M. Slavin guessed
> it), but on numbers rather than strings, without any alteration of the
> subset returned compared to the orginal in the same range : just discard
> everything above Y = nb of digits on the right side of the decimal
> separator . So trunc(123.1246, 3) = trunc(123.1242, 3) = 123.124  (and
> trunc(123.1, 3) = 123.1 greatly prefered to = 123.100)
> 
> Precision 1 : the need of this truncation of fp numbers is only at
> storage time, (the intermediate calculus in "/expression"/ in my example
> is done (in SQL) at full system-available precision of course.
> 
> Precision 2 : the number of digits kept on the right side of the decimal
> separator results from mandatory external constraint, and is not a
> developper's choice. With truncation, the loss of precision can, if
> needed and with additional efforts, be (imperfectly) restored; I don't
> know how to to do the same when rounding.
> 
> To M. Hipp :
> 
> round(123.45678, 3) returns 123.457 and what is needed is a "never
> round" method : 123.456
> 
> Reason : the processing done at SQL level involves lot of
> multiplications / divisions on real numbers and unfortunately "integer
> arythmetics" is not an option (readability pb + error prone) . The
> purpose of the truncation done here (wich I believe is a method allso
> 

Re: [sqlite] Example/recipe for truncating fp numbers

[Jean-Marie CUAZ]

To M. Slavin, Medcalf ans Hipp:

Thanks for your attention.

Yes, "cast(value * 1000 as integer) / 1000.0" is much better and I will 
switch to your formula when possible, thanks very much.


Thank you for your offer of C functions : a trunc() function is 
certainely what is needed, but I don't understand the sentence


"trunc(value, 3) which does the truncation (towards 0) and maintains precision"

What is needed is something like substr(value,1,Y) (as M. Slavin guessed 
it), but on numbers rather than strings, without any alteration of the 
subset returned compared to the orginal in the same range : just discard 
everything above Y = nb of digits on the right side of the decimal 
separator . So trunc(123.1246, 3) = trunc(123.1242, 3) = 123.124  (and 
trunc(123.1, 3) = 123.1 greatly prefered to = 123.100)


Precision 1 : the need of this truncation of fp numbers is only at 
storage time, (the intermediate calculus in "/expression"/ in my example 
is done (in SQL) at full system-available precision of course.


Precision 2 : the number of digits kept on the right side of the decimal 
separator results from mandatory external constraint, and is not a 
developper's choice. With truncation, the loss of precision can, if 
needed and with additional efforts, be (imperfectly) restored; I don't 
know how to to do the same when rounding.


To M. Hipp :

round(123.45678, 3) returns 123.457 and what is needed is a "never 
round" method : 123.456


Reason : the processing done at SQL level involves lot of 
multiplications / divisions on real numbers and unfortunately "integer 
arythmetics" is not an option (readability pb + error prone) . The 
purpose of the truncation done here (wich I believe is a method allso 
used in similar softwares) is an effort to reduce the loss of 
associative property of addition over multiplication. If you send a 
private mail to me I'll give more details.


Thanks

-jm






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Keith Medcalf]

On Tuesday, 11 July, 2017 07:24, David Raymond  said:


> Not to be the new guy here, but would someone be so good as to explain why
> no one else is panicking that the modulo operator is horrifically broken?
> In http://www.sqlite.org/lang_expr.html it just says:
> "The operator % outputs the value of its left operand modulo its right
> operand."
> 
> There's nothing in there that says anything about requirements for the
> operands to be integers or about quietly rounding the results on you etc.
> 
> sqlite> select 1. % .001;
> 1. % .001
> NULL
> Shouldn't that be .0004?
> 
> sqlite> select 1. % 2;
> 1. % 2
> 1.0
> Why is this not 1.? This is worse than the NULL by blatantly giving a
> wrong answer and letting you think it succeeded.

You are correct.  The modulus operator only works on integers, not on floating 
point.  The operands are converted to integer and the return value is the 
result of that integer modulus operation.  This should probably be noted in the 
documentation for the % operator.

SQLite does not natively implement the fmod(x, y) function from the platform 
math library.  You would have to add that yourself either as a loadable or 
builtin extension.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[David Raymond]

Not to be the new guy here, but would someone be so good as to explain why no 
one else is panicking that the modulo operator is horrifically broken? In 
http://www.sqlite.org/lang_expr.html it just says:
"The operator % outputs the value of its left operand modulo its right operand."

There's nothing in there that says anything about requirements for the operands 
to be integers or about quietly rounding the results on you etc.

sqlite> select 1. % .001;
1. % .001
NULL
Shouldn't that be .0004?

sqlite> select 1. % 2;
1. % 2
1.0
Why is this not 1.? This is worse than the NULL by blatantly giving a wrong 
answer and letting you think it succeeded.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[R Smith]

On 2017/07/11 2:31 PM, Richard Hipp wrote:

Truncation is a string operation, not a mathematical operation.  So I
suggest using string functions:

WITH SRC(Val) AS (
 VALUES (0),(1.001),(1.12345678),(1.),(1.888),
 (9.87654321),(1.5),(1.499),(1.49494999),
 (12345.67890123), (1234.56)
   UNION ALL
 SELECT -Val FROM SRC LIMIT 18
)
SELECT substr(val,1,instr(val,'.')+3) FROM src;


Yep, that's a good method, exactly what Simon suggested, and as with his 
suggestion, if we simply add a CAST to REAL we end up with a numeric 
value that can be used in further computations. I opted for a length() 
on a printf statement (when implementing Simon's suggestion) to 
determine the truncation point because I'm not exactly sure if the 
decimal separator will always be a "." inside SQLite regardless of any 
localization setting - but if it is, this method is certainly shorter.


I still think Keith's method will win for speed, if string functions are 
slower than math functions, but I could be wrong. I suppose a speedtest 
is in order, but I have meetings, perhaps someone else fancies some testing.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Richard Hipp]

Truncation is a string operation, not a mathematical operation.  So I
suggest using string functions:

WITH SRC(Val) AS (
VALUES (0),(1.001),(1.12345678),(1.),(1.888),
(9.87654321),(1.5),(1.499),(1.49494999),
(12345.67890123), (1234.56)
  UNION ALL
SELECT -Val FROM SRC LIMIT 18
)
SELECT substr(val,1,instr(val,'.')+3) FROM src;



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[R Smith]

In case any astute mathematicians were paying attention to this thread - 
the previous solution I offered would obviously fail on Negative values 
- which may or may not be a requirement.


Either way, I've amended it to work for all values, but I think Keith's 
method might be better than this amended method, if not in speed, then 
certainly in complexity terms:


  -- 


WITH SRC(Val) AS (
VALUES 
(0),(1.001),(1.12345678),(1.),(1.888),(9.87654321),(1.5),(1.499),(1.49494999)

  UNION ALL
SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
   round(CAST(Val AS INTEGER)+ (((Val * 1000) % 1000)/1000),3) AS 
Tr_JeanM,

   CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
   round(Val-(ifnull(Val/abs(Val),0)*0.00049),3) AS Tr_Ryan
  FROM SRC
;

  --  Val |   Tr_JeanM |   Tr_Keith | Tr_Ryan
  --  | -- | -- | --
  --0 |0.0 |0.0 | 0.0
  --1.001 |1.0 |1.0 | 1.0
  --   1.12345678 |  1.123 |  1.123 | 1.123
  --   1. |  1.999 |  1.999 | 1.999
  --1.888 |  1.888 |  1.888 | 1.888
  --   9.87654321 |  9.876 |  9.876 | 9.876
  --  1.5 |  1.555 |  1.555 | 1.555
  --1.499 |  1.499 |  1.499 | 1.499
  --   1.49494999 |  1.494 |  1.494 | 1.494
  --0 |0.0 |0.0 | 0.0
  --   -1.001 |   -1.0 |   -1.0 | -1.0
  --  -1.12345678 | -1.123 | -1.123 | -1.123
  --  -1. | -1.999 | -1.999 | -1.999
  --   -1.888 | -1.888 | -1.888 | -1.888
  --  -9.87654321 | -9.876 | -9.876 | -9.876
  -- -1.5 | -1.555 | -1.555 | -1.555
  --   -1.499 | -1.499 | -1.499 | -1.499
  --  -1.49494999 | -1.494 | -1.494 | -1.494



WITH SRC(Val) AS (
VALUES 
(0),(1.001),(1.12345678),(1.),(1.888),(9.87654321),(1.5),(1.499),(1.49494999)

  UNION ALL
SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
   round(CAST(Val AS INTEGER)+ (((Val * 100) % 
100)/100),6) AS Tr_JeanM,

   CAST(Val*100 AS INT) / 100.0 AS Tr_Keith,
   round(Val-(ifnull(Val/abs(Val),0)*0.0049),6) AS Tr_Ryan
  FROM SRC
;

  --  Val |  Tr_JeanM |  Tr_Keith |   Tr_Ryan
  --  | - | - | -
  --0 |   0.0 |   0.0 |   0.0
  --1.001 |   1.0 |   1.0 |   1.0
  --   1.12345678 |  1.123456 |  1.123456 |  1.123456
  --   1. |  1.99 |  1.99 |  1.99
  --1.888 | 1.888 | 1.888 | 1.888
  --   9.87654321 |  9.876543 |  9.876543 |  9.876543
  --  1.5 |  1.55 |  1.55 |  1.55
  --1.499 |  1.49 |  1.49 |  1.49
  --   1.49494999 |  1.494949 |  1.494949 |  1.494949
  --0 |   0.0 |   0.0 |   0.0
  --   -1.001 |  -1.0 |  -1.0 |  -1.0
  --  -1.12345678 | -1.123456 | -1.123456 | -1.123456
  --  -1. | -1.99 | -1.99 | -1.99
  --   -1.888 |-1.888 |-1.888 |-1.888
  --  -9.87654321 | -9.876543 | -9.876543 | -9.876543
  -- -1.5 | -1.55 | -1.55 | -1.55
  --   -1.499 | -1.49 | -1.49 | -1.49
  --  -1.49494999 | -1.494949 | -1.494949 | -1.494949



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[R Smith]

On 2017/07/11 3:51 AM, Richard Hipp wrote:

On 7/10/17, Jean-Marie CUAZ  wrote:

Hello,

Below is a recipe on a "best effort" basis, to truncate fp numbers on
the right side of the decimal separator with SQLite.

I don't understand how this is different from "round(N,3)"?

What are you trying to do to the fp number N that "round(N,3)" does not do?

What am I missing?


Well, Truncation is different from Rounding. Floor(N,3) would probably 
be a closer match if that existed.
To demonstrate the difference in output, and also compare all the 
methods offered, including one of mine, see the next results.


Simon's string method works well, but retains trailing zeroes, not sure 
if this is acceptable to the OP, but I included a simple cast version to 
fix that.
I believe Keith's and my methods will win (by a very small margin) on 
speed tests, but they are certainly less complex.


I included two tests with both 3rd and 6th decimal truncation for all 
methods using source values designed to be tricky:

(I hope the formatting holds)


  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



WITH SRC(Val) AS (
VALUES 
(0),(1.001),(1.12345678),(1.),(1.888),(9.87654321),(1.5),(1.499),(1.49494999)

)
SELECT Val,
   round(Val,3) AS Rounded,
   printf('%0.3f', Val) AS PrntF,
   round(CAST(Val AS INTEGER)+ (((Val * 1000) % 1000)/1000),3) AS 
Tr_JeanM,

   substr(Val||'',1,length(printf('%0.3f',Val))) AS Tr_Simon,
CAST(substr(Val||'',1,length(printf('%0.3f',Val))) AS REAL) AS 
Tr_SimonFix,

   CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
   round(Val-0.00049,3) AS Tr_Ryan
  FROM SRC
;
  -- |   |   | |   | Tr_Simon- 
|   |
  -- Val |   Rounded | PrntF |  Tr_JeanM | Tr_Simon 
|   Fix |  Tr_Keith |   Tr_Ryan
  -- --- | - | - | - | - | 
- | - | -
  --   0 |   0.0 | 0.000 |   0.0 | 0 
|   0.0 |   0.0 |   0.0
  --   1.001 |   1.0 | 1.000 |   1.0 | 1.000 
|   1.0 |   1.0 |   1.0
  --  1.12345678 | 1.123 | 1.123 | 1.123 | 1.123 
| 1.123 | 1.123 | 1.123
  --  1. |   2.0 | 2.000 | 1.999 | 1.999 
| 1.999 | 1.999 | 1.999
  --   1.888 | 1.888 | 1.888 | 1.888 | 1.888 
| 1.888 | 1.888 | 1.888
  --  9.87654321 | 9.877 | 9.877 | 9.876 | 9.876 
| 9.876 | 9.876 | 9.876
  -- 1.5 | 1.556 | 1.556 | 1.555 | 1.555 
| 1.555 | 1.555 | 1.555
  --   1.499 |   1.5 | 1.500 | 1.499 | 1.499 
| 1.499 | 1.499 | 1.499
  --  1.49494999 | 1.495 | 1.495 | 1.494 | 1.494 
| 1.494 | 1.494 | 1.494




WITH SRC(Val) AS (
VALUES 
(0),(1.001),(1.12345678),(1.),(1.888),(9.87654321),(1.5),(1.499),(1.49494999)

)
SELECT Val,
   round(Val,6) AS Rounded,
   printf('%0.6f', Val) AS PrntF,
   round(CAST(Val AS INTEGER)+ (((Val * 100) % 
100)/100),6) AS Tr_JeanM,

   substr(Val||'000',1,length(printf('%0.6f',Val))) AS Tr_Simon,
CAST(substr(Val||'000',1,length(printf('%0.6f',Val))) AS REAL) AS 
Tr_SimonFix,

   CAST(Val*100 AS INT) / 100.0 AS Tr_Keith,
   round(Val-0.0049,6) AS Tr_Ryan
  FROM SRC
;


  -- Val |  Rounded |PrntF | Tr_JeanM | 
Tr_Simon |  Tr_SimonFix | Tr_Keith | Tr_Ryan
  -- --- |  |  |  | 
 |  |  | 
  --   0 |  0.0 | 0.00 |  0.0 | 
 |  0.0 |  0.0 |  0.0
  --   1.001 |  1.0 | 1.00 |  1.0 | 
1.00 |  1.0 |  1.0 |  1.0
  --  1.12345678 | 1.123457 | 1.123457 | 1.123456 | 
1.123456 | 1.123456 | 1.123456 | 1.123456
  --  1. |  2.0 | 2.00 | 1.99 | 
1.99 | 1.99 | 1.99 | 1.99
  --   1.888 |1.888 | 1.888000 | 1.888 | 
1.888000 |1.888 |1.888 |1.888
  --  9.87654321 | 9.876543 | 9.876543 | 9.876543 | 
9.876543 | 9.876543 | 9.876543 | 9.876543
  -- 1.5 | 1.56 | 1.56 | 1.55 | 
1.55 | 1.55 | 1.55 | 1.55
  --   1.499 |  1.5 | 1.50 | 1.49 | 
1.49 | 1.49 | 1.49 | 1.49
  --  1.49494999 |  1.49495 | 1.494950 | 1.494949 | 
1.494949 | 1.494949 | 1.494949 | 1.494949




_

Re: [sqlite] Example/recipe for truncating fp numbers

[Keith Medcalf]

Though I would use:

  trunc(value * pow(10, places)) / pow(10, places)

so that all the operations are performed using full floating point, but then I 
have the whole math library loaded into SQLite3 ...

I just added an override for the math library trunc function that takes two 
arguments so you can do:

  trunc(value, 3)

which does the truncation (towards 0) and maintains precision.

On the other hand, I have never seen any need to truncate a floating point 
number, though there are many times when one needs to apply proper rounding 
(half-to-even) for which I have written a "roundhe" function ...

Truncation or rounding of course only *ever* applied to "display to user and 
discarded" results and never ever applied to intermediates or stored so having 
the ability to do this inside the SQLite engine rather than at the application 
level is of dubious value ... other than if you need to keep a log or something 
that does not participate in further calculations.

Here is the roundhe function to do statistical / stochastic / bankers' / 
half-even rounding:

/*
** Define a Statistical (Gaussian/Bankers) rounding function
**
** Round < 0.5 towards zero
**   = 0.5 towards even
**   > 0.5 away from zero
**
** Implements recommended IEEE round-half-even
*/

SQLITE_PRIVATE void _heroundingFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;

if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
p = p > 15 ? 15 : (p < 0 ? 0 : p);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
if (log10(xval) > 16.0)
{
sqlite3_result_double(context, x);
return;
}
fpart = modf(xval, &ipart);
if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}

and I just added one that truncates without rounding:

/*
** Define a floating point truncation function that allows truncation to some 
number of decimal places
** Retains 1 ulp precision
*/

SQLITE_PRIVATE void _fptruncateFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;

if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
fpart = modf(xval, &ipart);
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}

 
> Why not just use:
> 
>   cast(value * 1000 as integer) / 1000.0
> 
> 
> > Hello,
> >
> > Below is a recipe on a "best effort" basis, to truncate fp numbers on
> > the right side of the decimal separator with SQLite.
> >
> > It is not intended to correct any fp numbers processing, but only to
> > discard, without any rounding, unwanted fractional digits.
> >
> > The following is directed to discard all digits of a number after the
> > third fractional position (slight and easy adjustments must be done for
> > different positional truncations) :
> >
> >  round(  CAST(/expression/ AS INTEGER)
> >
> >+ /((expression/  * 1000 % 1000 ) / 1000 ), 3)
> >
> > (round() is used against eventually imprecise representation resulting
> > from the top level addition operation)
> >
> > Caveat : force an eventual INTEGER representation resulting from
> > /expression/ to its equivalent REAL representation (ie : x -> x.0)
> >
> > (not a pb for me)
> >
> > Improvements/comments welcome (and wil be happy when an equivalent
> > function finds its way into SQLite)
> >
> > Thanks.
> >
> > -jm
> >
> >
> >
> >
> >
> >
> > ---
> > L'absence de virus dans ce courrier électronique a été vérifiée par le
> > logiciel antivirus Avast.
> > https://www.avast.com/antivirus
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Richard Hipp]

On 7/10/17, Jean-Marie CUAZ  wrote:
> Hello,
>
> Below is a recipe on a "best effort" basis, to truncate fp numbers on
> the right side of the decimal separator with SQLite.

I don't understand how this is different from "round(N,3)"?

What are you trying to do to the fp number N that "round(N,3)" does not do?

What am I missing?

>
> It is not intended to correct any fp numbers processing, but only to
> discard, without any rounding, unwanted fractional digits.
>
> The following is directed to discard all digits of a number after the
> third fractional position (slight and easy adjustments must be done for
> different positional truncations) :
>
>  round(  CAST(/expression/ AS INTEGER)
>
>+ /((expression/  * 1000 % 1000 ) / 1000 ), 3)
>
> (round() is used against eventually imprecise representation resulting
> from the top level addition operation)
>
> Caveat : force an eventual INTEGER representation resulting from
> /expression/ to its equivalent REAL representation (ie : x -> x.0)
>
> (not a pb for me)
>
> Improvements/comments welcome (and wil be happy when an equivalent
> function finds its way into SQLite)
>
> Thanks.
>
> -jm
>
>
>
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Keith Medcalf]

Why not just use:

  cast(value * 1000 as integer) / 1000.0


> Hello,
> 
> Below is a recipe on a "best effort" basis, to truncate fp numbers on
> the right side of the decimal separator with SQLite.
> 
> It is not intended to correct any fp numbers processing, but only to
> discard, without any rounding, unwanted fractional digits.
> 
> The following is directed to discard all digits of a number after the
> third fractional position (slight and easy adjustments must be done for
> different positional truncations) :
> 
>  round(  CAST(/expression/ AS INTEGER)
> 
>+ /((expression/  * 1000 % 1000 ) / 1000 ), 3)
> 
> (round() is used against eventually imprecise representation resulting
> from the top level addition operation)
> 
> Caveat : force an eventual INTEGER representation resulting from
> /expression/ to its equivalent REAL representation (ie : x -> x.0)
> 
> (not a pb for me)
> 
> Improvements/comments welcome (and wil be happy when an equivalent
> function finds its way into SQLite)
> 
> Thanks.
> 
> -jm
> 
> 
> 
> 
> 
> 
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example/recipe for truncating fp numbers

[Simon Slavin]

On 10 Jul 2017, at 6:17pm, Jean-Marie CUAZ  wrote:

> Improvements/comments welcome

I would suggest you try an equivalent function, starting by turning the number 
into a string and looking for the decimal point in it.  This may or may not 
work better, but a second way of doing things is always interesting.

>  (and wil be happy when an equivalent function finds its way into SQLite)

As I’m sure you know by now, the developers of SQLite consider that SQLite is 
for store and recall, and that processing should be done by your own code (or 
"awk" or whatever you use).

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Example/recipe for truncating fp numbers

[Jean-Marie CUAZ]

Hello,

Below is a recipe on a "best effort" basis, to truncate fp numbers on 
the right side of the decimal separator with SQLite.


It is not intended to correct any fp numbers processing, but only to 
discard, without any rounding, unwanted fractional digits.


The following is directed to discard all digits of a number after the 
third fractional position (slight and easy adjustments must be done for 
different positional truncations) :


round(  CAST(/expression/ AS INTEGER)

  + /((expression/  * 1000 % 1000 ) / 1000 ), 3)

(round() is used against eventually imprecise representation resulting 
from the top level addition operation)


Caveat : force an eventual INTEGER representation resulting from 
/expression/ to its equivalent REAL representation (ie : x -> x.0)


(not a pb for me)

Improvements/comments welcome (and wil be happy when an equivalent 
function finds its way into SQLite)


Thanks.

-jm






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users