On 2017/07/11 3:51 AM, Richard Hipp wrote:
On 7/10/17, Jean-Marie CUAZ <jm.c...@orens.fr> 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.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(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||'0000',1,length(printf('%0.3f',Val))) AS Tr_Simon,
CAST(substr(Val||'0000',1,length(printf('%0.3f',Val))) AS REAL) AS
Tr_SimonFix,
CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
round(Val-0.000499999,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 | 00000
| 0.0 | 0.0 | 0.0
-- 1.0000001 | 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.99999999 | 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.555555555 | 1.556 | 1.556 | 1.555 | 1.555
| 1.555 | 1.555 | 1.555
-- 1.4999999 | 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.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
)
SELECT Val,
round(Val,6) AS Rounded,
printf('%0.6f', Val) AS PrntF,
round(CAST(Val AS INTEGER)+ (((Val * 1000000) %
1000000)/1000000),6) AS Tr_JeanM,
substr(Val||'0000000',1,length(printf('%0.6f',Val))) AS Tr_Simon,
CAST(substr(Val||'0000000',1,length(printf('%0.6f',Val))) AS REAL) AS
Tr_SimonFix,
CAST(Val*1000000 AS INT) / 1000000.0 AS Tr_Keith,
round(Val-0.000000499999,6) AS Tr_Ryan
FROM SRC
;
-- Val | Rounded | PrntF | Tr_JeanM |
Tr_Simon | Tr_SimonFix | Tr_Keith | Tr_Ryan
-- --------------- | ------------ | ------------ | ------------ |
------------ | ------------ | ------------ | ------------
-- 0 | 0.0 | 0.000000 | 0.0 |
00000000 | 0.0 | 0.0 | 0.0
-- 1.0000001 | 1.0 | 1.000000 | 1.0 |
1.000000 | 1.0 | 1.0 | 1.0
-- 1.12345678 | 1.123457 | 1.123457 | 1.123456 |
1.123456 | 1.123456 | 1.123456 | 1.123456
-- 1.99999999 | 2.0 | 2.000000 | 1.999999 |
1.999999 | 1.999999 | 1.999999 | 1.999999
-- 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.555555555 | 1.555556 | 1.555556 | 1.555555 |
1.555555 | 1.555555 | 1.555555 | 1.555555
-- 1.4999999 | 1.5 | 1.500000 | 1.499999 |
1.499999 | 1.499999 | 1.499999 | 1.499999
-- 1.49494999 | 1.49495 | 1.494950 | 1.494949 |
1.494949 | 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
