By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
/
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
);
/
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,
CREATE FUNCTION LCM_AGG( value NUMBER )
RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING LCMAggregationType;
/
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;
6 rows affected
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;
| GROUP_ID | NUMERATOR | DENOMINATOR |
|---|---|---|
| 13 | 974 | 1260 |
| 28 | 4405473 | 4688140 |