make numeric values homonyms fractions in sql table

Question

i have a table like this:

ID	num_A	num_B
1	1	168
2	1	4
2	5	24
2	6	24
3	1	36

So, num_A and num_B represent a fraction. That means for ID=1, i have 1/168, ID=2 ---> (1/4)+(5/24)+(6/24) = 17/24, ID=3 --> 1/36....

I need to add 2 columns, one with the sum(num_A) and one with the denominator num_B, for those with the same ID. So the example should be:

ID	num_A	num_B	sumA	denom_B
1	1	168	1	168
2	1	4	17	24
2	5	24	17	24
2	6	24	17	24
3	1	36	1	36

My problem is that i dont know how to calculate the denominator for each different fraction in postgres.

How is num_b derived? You have 2 different num_b's, also 3 different num_a's, in the example source data for id 2 — Nathan_Sav, Aug 23 '22 at 09:15
@Nathan_Sav you are right, i just fixed the result table. I want to sum fractions with the same id and the result columns should be the same for those, with the same denominator and the same numerator — mikasa, Aug 23 '22 at 09:21
I don't understand how it was possible to get the expected results from the sample data you provided. Take rows with `ID` = 2. You say that `sumA` should be the sum of `num_A` of all input lines with the same `ID` value (2). You have 17 as the value of `sumA`, but the sum of `num_A` for `ID = 2` is 12. And tell us why all the values of `denom_B` are 24? — Alexey, Aug 23 '22 at 09:37
the new columns represent the numerator and the denominator of the result fraction. So, its sumA=17 and denom_B=24 because i added all fractions with ID=2-----> (6*1)/(6*4) + 5/24 +6/24= 17/24 — mikasa, Aug 23 '22 at 09:44
What would be if, for example, you had a row `ID - num_a - num_b` as `2 - 1 - 5` instead of `2 - 1 - 4`? What do you expect in the `sumA` and `denom_B` columns for `ID` = 2 in the resulting table? — Alexey, Aug 23 '22 at 11:18
SQL doesn't do fraction arithmetic. You'd have to find the distinct denominators for each group, find a common denominator (even if that meant multiplying the distinct denominators) and then convert all fractions to it. That would require multiple nested operations and I doubt it would even be possible in a single query. What you want is far easier in languages like Python. — Panagiotis Kanavos, Aug 23 '22 at 11:21
@Alexey in that case i would expect sum_a=9 and denom_b=20, because the adding process would be (1/4 +1/5 = (5+4)/(4*5) — mikasa, Aug 23 '22 at 11:44

score 1 · Accepted Answer · answered Aug 23 '22 at 12:44

In general PostgreSQL provides the LCM function that returns the least common multiple (the smallest strictly positive number that is an integral multiple of both inputs), but it takes only two arguments and cannot be used to process rowset column values.

Thus, to get the LCM of rows with the same ID value, you can use a recursive CTE to process the rows one by one, using the LCM function with the LCM calculated in the previous step (in the first step equal to the value of num_B ) and the current value of num_B as arguments. This will produce the LCM value of all previous num_B and the current value for each row.

Finally, you can get the maximum (the last if to be exact, it would be the maximum anyway) calculated LCM value for rows grouped by ID and that will be the LCM for all num_B values with the same ID.

The rest is simple - divide, multiply and sum.

Query:

WITH t_rn AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY num_b) AS rn FROM t
),
least_common_multiple AS (
    WITH RECURSIVE least_multiples AS (
        SELECT 
            id,
            num_b,
            num_b AS lm,
            rn
        FROM t_rn
        WHERE rn = 1
        UNION ALL
        SELECT 
            t_rn.id, 
            t_rn.num_b,
            LCM(t_rn.num_b, lm.lm),
            t_rn.rn
        FROM t_rn
        JOIN least_multiples lm ON lm.id = t_rn.id AND t_rn.rn = lm.rn + 1
    )
    SELECT 
        id,
        MAX(lm) AS lcm
    FROM least_multiples
    GROUP BY id
)
SELECT 
    t.*,
    SUM(t.num_a * lm.lcm / t.num_b) OVER (PARTITION BY t.id) AS suma,
    lm.lcm AS denom_b
FROM t
JOIN least_common_multiple lm ON t.id = lm.id

Output

id	num_a	num_b	suma	denom_b
1	1	168	1	168
2	1	4	17	24
2	5	24	17	24
2	6	24	17	24
3	1	36	1	36

DEMO

ahmed · Answer 2 · 2022-08-25T01:07:19.217

I think you are trying to simulate fraction addition, Try the following query:

    with find_mutiplication As
    (
      Select Id, num_a, num_b,
      ROUND(EXP(SUM(LN(ABS(num_b))) over (partition by id))) as mutiplication,
      ROUND(EXP(SUM(LN(ABS(num_b))) over (partition by id))) / num_b * num_a as unified
      From mytable
    )
    ,
    calc as
    (
      Select *, 
             mutiplication/ GCD(mutiplication::int, SUM(unified::int)over (partition by id)) denom_B,
             num_a * (mutiplication/ GCD(mutiplication::int, SUM(unified::int)over (partition by id)) / num_b) as dv
      From find_mutiplication
    )
    
    Select id, num_a, num_b,
           SUM(dv) Over (Partition By id) As sumA,
           denom_b
    From calc
    Order By id

See demo from db<>fiddle.

To understand how the query works consider the following image:

where the ROUND(EXP(SUM(LN(num_b)) over (partition by id))) will find the multiplication of the dividends for each id. (According to this post)

make numeric values homonyms fractions in sql table

2 Answers2