How can I write 2 float data with fractional number?

Admin 1
Viewed 127

How can I write 2 float data with fractional number on last column in SQL?

I am reaching a solution in a very long way. Is there a more logical and short way?

My SQL code;

 WITH TESTDATA (TestID, Numerator, Denumerator) AS 
    (
    SELECT
        *
    FROM (
        VALUES
        (1, 28, 49),            -->  4/7
        (2, -28, -49),          -->  4/7
        (3, 28, -49),           -->  -4/7
        (4, -28, 49),           -->  -4/7

        (5, 22, 6),             -->  11/3
        (6, -22, -6),           -->  11/3
        (7, 22, -6),            -->  -11/3
        (8, -22, 6),            -->  -11/3
        
        (9, 28, 14),            -->  2
        (10, -28, -14),         -->  2
        (11, 28, -14),          -->  -2
        (12, -28, 14),          -->  -2

        (13, 7919, 2687),       -->  7919/2687
        (14, -7, -2),           -->  7/2
        (15, 11, -17),          -->  -11/17
        (16, -19, 9),           -->  -19/9

        (17, 15, -3),           -->  -5
        (18, -20, 4),           -->  -5
        (19, -10, -2),          -->  5
        (20, 5, 1),             -->  5

        (21, 1, 1),             -->  1
        (22, -1, -1),           -->  1
        (23, 1, -1),            -->  -1
        (24, -1, 1),            -->  -1
        (25, -7, -7),           -->  1
        (26, 7, -7),            -->  -1

        (27, 0, 2),             -->  0
        (28, 0, -2),            -->  0
        (29, 0, 1),             -->  0
        (30, 0, -1),            -->  0

        (31, 2, 0),             -->  +Infinity
        (32, -100, 0),          -->  -Infinity
        (33, 0, 0),             -->  NaN

        (34, 10, NULL),         -->  NULL
        (35, -5, NULL),         -->  NULL
        (36, 0, NULL),          -->  NULL
        (37, NULL, 11),         -->  NULL
        (38, NULL, -8),         -->  NULL
        (39, NULL, 0),          -->  NULL
        (40, NULL, NULL)        -->  NULL
    ) 
    TESTDATA (TestID, Numerator, Denumerator)
),
SET1 (TestID,Numerator,Denumerator,Simplified) AS
(
    SELECT
        TestID,
        Numerator,
        Denumerator,
               Simplified='Undefined'
        FROM TESTDATA
        WHERE Denumerator IS NULL OR Numerator IS NULL
),
SET2 (TestID,Numerator,Denumerator,Simplified) AS
(
    SELECT
        TestID,
        Numerator,
        Denumerator,
        CASE 
            WHEN Denumerator = 0 AND Numerator < 0 THEN '- infinity'
            WHEN Denumerator = 0 AND Numerator > 0  THEN '+ infinity'
            WHEN Numerator = 0 AND Denumerator = 0 THEN 'NaN'
            WHEN Numerator = 0 AND Denumerator != 0 THEN '0'
            WHEN Numerator / Denumerator = -1 THEN '-1'
            WHEN Numerator / Denumerator = 1 THEN '1'
            WHEN Denumerator = 1 AND Numerator % Denumerator = 0 THEN  CONVERT(varchar(10), Numerator)
            ... <<<<<<<<<<<<<<<<<<<<<<<<<<HERE
            ELSE 
            ...
            END AS Simplified
    FROM TESTDATA
    WHERE Numerator IS NOT NULL AND Denumerator IS NOT NULL
),
RESULT(TestID,Numerator,Denumerator,Simplified) AS 
(
    SELECT * FROM SET1
    UNION
    SELECT * FROM SET2
)

SELECT
    TestID, 
    Numerator, 
    Denumerator, 
    Simplified
FROM RESULT
ORDER BY TestID

wanted output : sql

sql
1 Answers

It is a little unclear how the negative numbers are handled and why you have integers instead of pairs like 2/1 when the fraction reduces to a whole number.

But basically what you want to do is to calculate the greatest-common-divisor (GCD) of the two numbers. When you divide the original numbers by this factor, you have reduced the fraction to the "lowest" level.

You can do this using an iterative approach, where you calculate the smallest remainder dividing one value by the other. Then you repeat the process with the smallest remainder and the smaller of the two values.

For instance:

step   num1   num2
  1     28     49
  2     21     28    -- 21 = 49 % 28, 28 = least(28, 49)
  3      7     21    -- 7  = 28 % 21, 21 = least(21, 28)
  4      0      7    -- 0  = 21 % 7,   7 = least(7, 21)

7 is the GCD and you just divide the original two numbers by this factor.

As a note: This is guaranteed to converge because at each step the numbers are positive (note I take the absolute values to begin) and in each iteration the numbers are smaller than in the previous iteration.

This can all be calculated using a recursive CTE:

with recursive cte as (
      select testid, numerator, denominator, abs(numerator) as val1, abs(denominator) as val2, 0 as lev
      from testdata
      where numerator <> 0 and denominator <> 0
      union all
      select cte.testid, cte.numerator, cte.denominator, least(v.gcd, cte.val2, cte.val1), least(val1, val2), 1 + lev
      from cte cross join lateral
           (values (least(val1 % val2, val2 % val1))) v(gcd)
      where val1 > 0 and val2 > 0
     )
select distinct on (testid) cte.*,
      cte.*, numerator / greatest(val1, val2), denominator / greatest(val1, val2)
from cte
order by 1, lev desc;

This may need to be tweaked to handle 0s and NULL values and negative numbers exactly as you want this, but it solves the gist of your problem.

Here is a db<>fiddle.

Note: All the code uses Postgres as a convenience. All major databases now support recursive CTEs, although the syntax varies slightly among them.

user_11440350
Related