that IS a brute ...
Gabe, August 21, 2007 - 4:46 pm UTC
Your
gcd is an infinite loop if b is null, since
mod(anything, null) is null ...
Which is what happens when you pass an integer to
to_fraction.
So, don't run that in production.gabe@XE> select num, representation from
2 (
3 with s as
4 (
5 select 0 num from dual union all
6 select 0.0 num from dual union all
7 select 1.25 num from dual union all
8 select 1.375 num from dual union all
9 select 1.625 num from dual union all
10 select 2.875 num from dual union all
11 select 2.475 num from dual union all
12 select 0.001 num from dual union all
13 select 5.000 num from dual union all
14 select 9.999 num from dual union all
15 select 12345 num from dual
16 )
17 ,t as ( select level l from dual connect by level <= 1000)
18 ,d as ( select s.num,min(t.l) l
19 from s, t
20 where s.num * t.l = floor(s.num * t.l)
21 group by s.num
22 )
23 select d.num
24 ,floor(d.num)
25 ,d.l * (d.num - floor(d.num))
26 ,d.l
27 ,to_char(floor(d.num))||
28 case d.l * (d.num - floor(d.num))
29 when 0 then null
30 else ' '||to_char(d.l * (d.num - floor(d.num))) || '/' || to_char(d.l)
31 end representation
32 from d
33 )
34 order by 1
35 ;
NUM REPRESENTATION
---------- --------------------
0 0
.001 0 1/1000
1.25 1 1/4
1.375 1 3/8
1.625 1 5/8
2.475 2 19/40
2.875 2 7/8
5 5
9.999 9 999/1000
12345 12345
10 rows selected.
Elapsed: 00:00:00.02
gabe@XE>
Thanks Gabe
Ravi, August 22, 2007 - 6:02 am UTC
Gabe,
Its really a very neat solution.This forum is turning a double learning treat for me.
Using GCD
Rod West, August 22, 2007 - 8:34 am UTC
You can still use the GCD function if you want and this avoids the need for a connect by level.
SQL> create or replace function gcd(a number, b number)
2 return number
3 as
4 begin
5 if nvl(b,0) = 0 then return a;
6 else return gcd(b,mod(a,b));
7 end if;
8 end;
9 /
Function created.
SQL>
SQL> select n, floor(n)||' '||nullif(x*p/gcd(x*p, p)||'/'||p/gcd(x*p, p), '0/1') representation
2 from (
3 select n, n-floor(n) x, power(10, length(to_char(n-floor(n)))-1) p
4 from (
5 select 0 n from dual union all
6 select 0.001 n from dual union all
7 select 1.25 n from dual union all
8 select 1.375 n from dual union all
9 select 1.625 n from dual union all
10 select 2.875 n from dual union all
11 select 2.475 n from dual union all
12 select 0.001 n from dual union all
13 select 5.000 n from dual union all
14 select 9.999 n from dual union all
15 select 12345 n from dual
16 ))
17 /
N REPRESENTATION
---------- --------------------
0 0
.001 0 1/1000
1.25 1 1/4
1.375 1 3/8
1.625 1 5/8
2.875 2 7/8
2.475 2 19/40
.001 0 1/1000
5 5
9.999 9 999/1000
12345 12345
11 rows selected.
SQL>
Fix for negative numbers
Rod West, August 22, 2007 - 8:48 am UTC
Fixed for negative numbers:
SQL> select n, trunc(n)||' '||nullif(x*p/gcd(x*p, p)||'/'||p/gcd(x*p, p), '0/1') representation
2 from (
3 select n, abs(n-trunc(n)) x, power(10, length(to_char(n-trunc(n)))-1) p
4 from (
5 select -1.250 n from dual
6 ))
7 /
N REPRESENTATION
---------- --------------------
-1.25 -1 1/4
SQL>
exponential computation
Duke Ganote, August 22, 2007 - 9:50 am UTC
I modestly generalized Gabe's implementation (below), but the computational requirement grows exponentially (see the POWER function below) with the number of right-side decimals.
SELECT cast(num as char(10))
|| ' = '||representation
as fraction_representation
FROM
(
with sample_data as
(
select 0.0 num from dual union all
select 1.25 num from dual union all
select 0.001 num from dual union all
select 5.000 num from dual union all
select 3.14159 num from dual union all
select 12345 num from dual
)
,count_max_decimals as
( select max(length(num-trunc(num))) max_decimals
from sample_data
)
,all_possible_denominators as
( select level l
from count_max_decimals
connect by level <= power(10,max_decimals)
)
,lowest_denominator as
( select s.num,min(t.l) l
from sample_data s,
all_possible_denominators t
where s.num * t.l = floor(s.num * t.l)
group by s.num
)
SELECT d.num
,floor(d.num)
,d.l * (d.num - floor(d.num))
,d.l
,CASE floor(d.num)
WHEN 0 THEN CASE d.l * (d.num - floor(d.num))
WHEN 0 THEN 0
END
ELSE floor(d.num)
END ||' '||
CASE d.l * (d.num - floor(d.num))
WHEN 0 THEN null
ELSE to_char(d.l * (d.num - floor(d.num)))
|| '/' || to_char(d.l)
END representation
FROM lowest_denominator d
)
order by 1
/
FRACTION_REPRESENTATION
----------------------------
.001 = 1/1000
0 = 0
1.25 = 1 1/4
12345 = 12345
3.14159 = 3 14159/100000
5 = 5
More than useful
Manish, August 22, 2007 - 10:18 am UTC
Thank you Tom and other community members. Just when I thought something cannot be done in SQL, you find a way. I must say, I fail to realize how you formulate a sql solution to a seemingly procedural solution.
Thanks,
Manish
SQL vs PL/SQL
Duke Ganote, August 22, 2007 - 10:48 am UTC
An even more general solution
Craig, August 22, 2007 - 11:46 am UTC
Here is a general case that handles terminating decimals, repeating decimals, improper fractions, negative fractions, whole numbers, and any combination of the preceding. I *think* I've tested just about every case. I'm sure this can be done more elegantly as well. This is infinitely more fun that what I should actually be working on.
DECLARE
p_decimal NUMBER := -7;
-- p_decimal NUMBER := 0.25;
-- p_decimal NUMBER := 3/4;
-- p_decimal NUMBER := 1.25;
-- p_decimal NUMBER := -5/3;
-- p_decimal NUMBER := -2/3;
-- p_decimal NUMBER := 1/3;
-- p_decimal NUMBER := 2/15;
-- p_decimal NUMBER := 7/15;
a NUMBER;
a_power NUMBER := 0;
a_factor NUMBER := 0;
ax NUMBER;
p NUMBER := 0;
diff NUMBER;
diff_prev NUMBER := 9999999999999999999;
lv_unit NUMBER;
lv_unit_v VARCHAR2(25);
lv_numerator NUMBER;
lv_denominator NUMBER;
lv_fraction VARCHAR2(25);
BEGIN
a := abs(p_decimal);
dbms_output.put_line(a);
LOOP
p := p + 1;
a_factor := power(10, a_power) * a;
ax := (power(10, p) * a);
diff := ax - a_factor - trunc(ax);
EXIT WHEN diff = floor(diff);
IF diff >= diff_prev THEN
a_power := a_power + 1;
a_factor := power(10, a_power) * a;
p := p - a_power;
diff_prev := 9999999999999999999;
ELSE
diff_prev := diff;
END IF;
END LOOP;
lv_numerator := ax - a_factor;
lv_denominator := (power(10,p) - power(10,a_power));
lv_fraction := (lv_numerator * sign(p_decimal)) || '/' || lv_denominator;
dbms_output.put_line('Unreduced: ' || lv_fraction);
-- Simplify the fraction
FOR i IN REVERSE 2..trunc(lv_numerator) LOOP
IF (lv_numerator / i) = trunc(lv_numerator / i)
AND (lv_denominator / i) = trunc(lv_denominator / i) THEN
lv_numerator := lv_numerator / i;
lv_denominator := lv_denominator / i;
END IF;
END LOOP;
lv_fraction := (lv_numerator * sign(p_decimal)) || '/' || lv_denominator;
dbms_output.put_line('Reduced, potentially improper: ' || lv_fraction);
-- elminate the improper fraction
IF lv_numerator > lv_denominator
AND lv_denominator != 1 THEN
lv_unit := MOD(lv_numerator, lv_denominator);
lv_numerator := lv_numerator - lv_denominator;
lv_unit_v := lv_unit * sign(p_decimal) || ' ';
ELSE
lv_unit_v := NULL;
lv_numerator := lv_numerator * sign(p_decimal);
END IF;
CASE WHEN lv_denominator = 1 THEN
lv_fraction := lv_unit_v || lv_numerator;
ELSE
lv_fraction := lv_unit_v || lv_numerator || '/' || lv_denominator;
END CASE;
dbms_output.put_line('Reduced and proper: ' || lv_fraction);
END;
/
August 22, 2007 - 2:24 pm UTC
... This is infinitely more fun that what I should
actually be working on.....
laughed out loud at that :) very nice
Fixed for recurring decimals
Rod West, August 23, 2007 - 8:15 am UTC
Tom, I haven't had so much fun for ages!
This version I think will work for all recurring decimals upto 9 places, as well as negatives and integers.
SQL> CREATE OR REPLACE FUNCTION gcd(a NUMBER, b NUMBER)
2 RETURN NUMBER
3 AS
4 BEGIN
5 IF NVL(b,0) = 0 THEN RETURN a;
6 ELSE RETURN gcd(b,MOD(a,b));
7 END IF;
8 END;
9 /
Function created.
SQL> SELECT n, DECODE(SIGN(n), 0, '0', -1, '-'||i||f, i||f) rep
2 FROM (
3 SELECT n, NULLIF(TRUNC(ABS(n))||' ','0 ') i, NULLIF(x*p/g||'/'||p/g, '0/1') f
4 FROM (
5 SELECT n, x, GREATEST(g1,g2, g3) g, DECODE(GREATEST(g1,g2,g3), g1, p1, g2, p2, p3) p
6 FROM (
7 SELECT n, x, gcd(x*p8, p8) g1, gcd(x*(p8-1), p8-1) g2, gcd(x*(p7-1), p7-1) g3, p8 p1, p8-1 p2, p7-1 p3
8 FROM (
9 SELECT n, ABS(n-TRUNC(n)) x, POWER(10, 36) p8, POWER(10, 35) p7
10 FROM (
11 SELECT -1/1023 n FROM dual UNION ALL
12 SELECT 3/7 n FROM dual UNION ALL
13 SELECT 7/15 n FROM dual UNION ALL
14 SELECT -1.75 n FROM dual UNION ALL
15 SELECT 17/1111111 n FROM dual UNION ALL
16 SELECT 12345678/111111111 n FROM dual UNION ALL
17 SELECT 123456789/1111111111 n FROM dual UNION ALL
18 SELECT -123 n FROM dual UNION ALL
19 SELECT 0 n FROM dual
20 )))))
21 /
N REP
---------------------------------------- ----------------------------------------------------------------------------------------------------
-.00097751710654936461388074291300097752 -1/1023
.428571428571428571428571428571428571429 3/7
.466666666666666666666666666666666666667 7/15
-1.75 -1 3/4
.00001530000153000015300001530000153 17/1111111
.111111102111111102111111102111111102111 1371742/12345679
.11111111011111111101111111110111111111 1100110001100110010011001100011001099/9900990099009900990099009900990099000
-123 -123
0 0
9 rows selected.
Pride comes before a fall
Craig, August 23, 2007 - 8:53 am UTC
My wife was a math major and as I was bragging to her about how clever I was last night. That is, until she pointed out that 5/3 is not equivalent to 2 2/3, as my script so claimed. Please accept my apologies, along with this revision.
-- elminate the improper fraction
IF lv_numerator > lv_denominator
AND lv_denominator != 1 THEN
lv_unit := (lv_numerator - mod(lv_numerator, lv_denominator)) / lv_denominator;
lv_numerator := mod(lv_numerator, lv_denominator);
lv_unit_v := lv_unit * sign(p_decimal) || ' ';
ELSE
lv_unit_v := NULL;
lv_numerator := lv_numerator * sign(p_decimal);
END IF;
SQL just for fun ...
Gabe, August 23, 2007 - 11:24 am UTC
Duke,
I was expecting that to come up ... yes, that brutish SQL solution will not scale as the number of inputs or the number of decimals increases. Having said that, in your query
power(10,max_decimals-1) would suffice.
For production use, a pl/sql solution based on
gcd would likely be better ...
deterministic functions,
result_cache in 11g ... yes.
For fixed number of decimals, 4 as mentioned by the OP ... even a do-it-yourself cache would be an option: create an IOT with 9999 rows having the results pre-computed for everything between 0.0001 and 0.9999 and join to it. Actually one only needs to compute the results for everything between 0.0001 and 0.5000 ... the rest till 0.9999 would be derived.
gabe@XE> with s as
2 (
3 select 1 id, 0.499 num from dual union all
4 select 2 id,1-0.499 num from dual union all
5 select 3 id, 0.375 num from dual union all
6 select 4 id,1-0.375 num from dual union all
7 select 5 id, 0.265 num from dual union all
8 select 6 id,1-0.265 num from dual
9 )
10 ,t as ( select level l from dual connect by level <= 1000)
11 ,d as ( select s.id,s.num,min(t.l) l
12 from s, t
13 where s.num * t.l = floor(s.num * t.l)
14 group by s.id, s.num
15 )
16 select d.num
17 ,floor(d.num) integer_part
18 ,d.l * (d.num - floor(d.num)) numerator
19 ,d.l denominator
20 from d
21 order by id
22 ;
NUM INTEGER_PART NUMERATOR DENOMINATOR
---------- ------------ ---------- -----------
.499 0 499 1000
.501 0 501 1000
.375 0 3 8
.625 0 5 8
.265 0 53 200
.735 0 147 200
Well, to avoid both recursive CPU and I/O, one might even consider a
result_cache pl/sql function having
relies_on such a table with pre-computed values.
Cheers.
Taming <i>Brute</i> SQL
Duke Ganote, August 24, 2007 - 10:28 am UTC
Gabe-- Exactly. The SQL was merely a starting point for reviewing the Fundamental Theorem of Arithematic, etc. "Infinitely more fun that what I should actually be working on" as Craig noted below. But one lacks infinite time for chasing all the rabbits in the field of dreams!
-- Duke
Fundamental Theorems, etc
Duke Ganote, August 24, 2007 - 10:42 am UTC
Very interesting discussion..
Venkat, September 25, 2007 - 2:22 pm UTC
I just saw this after returning from a long vacation and I had to try this out..
I created a modifed gcd so we can evaluate 0.6667 as 2/3 and 0.3333 as 1/3, by adding a precision parameter to the function.
create or replace function gcd (a number, b number,
p_precision number default null,
orig_larger_num number default null) return number is
v_orig_larger_num number := greatest(nvl(orig_larger_num,-1),a,b);
v_precision_level number := p_precision;
begin
if a is null or b is null or (a = 0 and b = 0) then return 1; end if; -- debatable
if p_precision is null or p_precision <= 0 then
v_precision_level := 4; -- within 1/10000
end if;
if b is null or b = 0 or
(b/v_orig_larger_num <= power(10,-1*v_precision_level)
and greatest(a,b) <> v_orig_larger_num) then
return a;
else
return (gcd(b,mod(a,b),v_precision_level,v_orig_larger_num));
end if;
end;
/
select num, int_part, numer, denom, gcdval,
case when num = 0 then '0'
when int_part = 0 then ''
else to_char(int_part)||' '
end ||
case when numer = 0 then ''
else to_char(trunc(numer/gcdval))||'/'||to_char(trunc(denom/gcdval))
end fraction
from (
select num, mod(num,1), length(mod(num,1))-1 pwr, trunc(num) int_part,
mod(num,1)*power(10,length(mod(num,1))-1) numer,
power(10,length(mod(num,1))-1) denom,
gcd(power(10,length(mod(num,1))-1),
mod(num,1)*power(10,length(mod(num,1))-1)) gcdval
from (
select 0 num from dual union all
select 0.0 num from dual union all
select 1.25 num from dual union all
select 1.375 num from dual union all
select 1.625 num from dual union all
select 2.875 num from dual union all
select 2.475 num from dual union all
select 0.001 num from dual union all
select 5.000 num from dual union all
select 9.999 num from dual union all
select 12345 num from dual union all
select .265 num from dual union all
select 0.735 num from dual union all
select .3333 num from dual union all
select .6667 num from dual union all
select .6666 num from dual
))
NUM INT_PART NUMER DENOM GCDVAL FRACTION
0 0 0 1 1 0
0 0 0 1 1 0
1.25 1 25 100 25 1 1/4
1.375 1 375 1000 125 1 3/8
1.625 1 625 1000 125 1 5/8
2.875 2 875 1000 125 2 7/8
2.475 2 475 1000 25 2 19/40
0.001 0 1 1000 1 1/1000
5 5 0 1 1 5
9.999 9 999 1000 1 9 999/1000
12345 12345 0 1 1 12345
0.265 0 265 1000 5 53/200
0.735 0 735 1000 5 147/200
0.3333 0 3333 10000 3333 1/3 <----- using default precision
0.6667 0 6667 10000 3333 2/3 <-----
0.6666 0 6666 10000 2 3333/5000
--Venkat.
What about TO_CHAR()?
Stevevn Meyer, March 28, 2023 - 4:51 pm UTC
It sure would be nice if those clever people at Oracle could find a way to incorporate this in as a Format Model for TO_CHAR(numeric_value, format_string);
I mean, if MS Excel can do it...
Displaying Formulas
Line Numerator Denominator Ratio Fraction
1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B2/C2 =D2
=A2+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B3/C3 =D3
=A3+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B4/C4 =D4
=A4+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B5/C5 =D5
Displaying (Possible) Results of Formulas
Line Numerator Denominator Ratio Fraction
1 137 78 1.7564 1 59/78
2 14 161 0.0870 2/23
3 85 93 0.9140 85/93
4 139 159 0.8742 7/8
March 29, 2023 - 1:14 pm UTC
What exactly are you proposing? How would it work?