Calculation of Least Common Multiple of the Denominator in Oracle Table

Question

I want to create a table to better illustrate my question

This is a sample of my data in oracle 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 SAMPLE.lcm(a number, b number) return number is begin return (a*b)/gcd(a,b); end;

CREATE OR REPLACE function SAMPLE.gcd(a number, b number)
return number is
begin if b = 0 then return a; else return gcd(b,mod(a,b)); end if; end;

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.

Answer 1

[TL;DR] Write your own custom aggregation function.

---

Given your GCD and LCM functions:

CREATE FUNCTION GCD(
  a IN NUMBER,
  b IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  IF b = 0 THEN
    RETURN a;
  ELSE
    RETURN GCD(b,MOD(a,b));
  END IF;
END;
/

CREATE FUNCTION LCM(
  a IN NUMBER,
  b IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN (a*b)/GCD(a,b);
END;
/

Then you can create a specification for a type to use in a custom aggregation:

CREATE TYPE LCMAggregationType AS OBJECT(
  value NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT LCMAggregationType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT LCMAggregationType,
    value       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT LCMAggregationType,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT LCMAggregationType,
    ctx         IN OUT LCMAggregationType
  ) RETURN NUMBER
);
/

With the body:

CREATE OR REPLACE TYPE BODY LCMAggregationType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT LCMAggregationType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := LCMAggregationType( 1 );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT LCMAggregationType,
    value       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NOT NULL THEN
      self.value := LCM( self.value, value );
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT LCMAggregationType,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT LCMAggregationType,
    ctx         IN OUT LCMAggregationType
  ) RETURN NUMBER
  IS
  BEGIN
    self.value := LCM( self.value, ctx.value );
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

You can then create the custom aggregation function:

CREATE FUNCTION LCM_AGG( value NUMBER )
RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING LCMAggregationType;
/

Then if you have the sample data:

CREATE TABLE table_name ( id, group_id, numerator, denominator ) AS
SELECT 1, 13,  4,  12 FROM DUAL UNION ALL
SELECT 2, 13, 33, 126 FROM DUAL UNION ALL
SELECT 3, 13,  8,  45 FROM DUAL UNION ALL
SELECT 4, 28, 56, 137 FROM DUAL UNION ALL
SELECT 5, 28, 13, 236 FROM DUAL UNION ALL
SELECT 6, 28, 69, 145 FROM DUAL;

You can use the query:

SELECT group_id,
       SUM( numerator ) AS numerator,
       MAX( denominator ) AS denominator
FROM   (
  SELECT group_id,
         numerator * LCM_AGG( denominator ) OVER ( PARTITION BY group_id ) / denominator
           AS numerator,
         LCM_AGG( denominator ) OVER ( PARTITION BY group_id ) as denominator
  FROM   table_name
)
GROUP BY group_id;

Which outputs:

GROUP_ID | NUMERATOR | DENOMINATOR
-------: | --------: | ----------:
      13 |       974 |        1260
      28 |   4405473 |     4688140

db<>fiddle here