Calculation of Least Common Multiple of the Denominator in Postgresql Database

Question

I want to create a table to better illustrate my question

This is a sample of my data in postgresql database

The work I want to do is step by step as follows

1-Group the values in the denominator columns by group_id column and calculate the least common multiple. For this action, I created lcm (least common multiple) and gcd (greatest common divisor) functions. added it here.

CREATE OR REPLACE FUNCTION gcd(bigint, bigint) RETURNS bigint AS
$BODY$
 WITH RECURSIVE t(a, b) AS (
    VALUES (abs($1) :: bigint, abs($2) :: bigint)
UNION ALL
    SELECT b, mod(a,b) FROM t
    WHERE b > 0
)
SELECT a AS gcd FROM t WHERE b = 0;
$BODY$
IMMUTABLE
STRICT
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION lcm(bigint, bigint)
  RETURNS bigint AS
$BODY$
  SELECT $1 / gcd($1, $2) * $2;
$BODY$
IMMUTABLE
STRICT
LANGUAGE SQL;

2-To increase the numerator values proportionally to values of the denominator column. mathematical formula like this :

(lcm(values of denominator(1..to -n)) / values of denominator ) * values of the numerator

3-summing the new calculated values by grouping them by group_id value

All of the items like sql, function, view that will make this work complete are suitable for me.

What can I do for this.

What about using [an extension](https://github.com/begriffs/pg_rational) rather than reinventing the wheel? — Laurenz Albe, Nov 23 '20 at 17:30
I changed your tag plsql to psql. Plsql is proprietary Oracle. — Belayer, Nov 23 '20 at 17:39
@LaurenzAlbe thanks for the advice but I couldn't create the extension because of owner issues. I don't have the owners of PostgreSQL. — Emre Tuna, Nov 23 '20 at 18:04

score 0 · Accepted Answer · answered Jan 15 '21 at 06:18

i used a method like this to solve this situation. This may not be best solution but it answers my need.

DO
$do$
DECLARE
   arr int[] := '{5,7,11,798,4212}';
   i int :=1;
   res int ;
begin 
    res = lcm(arr[i],arr[i+1]);
    RAISE NOTICE '%',res;
   FOR i IN array_lower(arr,1)  .. array_upper(arr, 1)-2
   LOOP    
      i:=i+1;
      res := lcm(res,arr[i+1]);      
    RAISE NOTICE 'lcm(%,%)',res, arr[i+1];
    RAISE NOTICE '%',res;
   END LOOP;
END
$do$

Calculation of Least Common Multiple of the Denominator in Postgresql Database

1 Answers1