Do calculation in join query of MySQL

Question

I have some calculation and want to do this in the query.

There is parent and children tables with one-to-many relationship:

CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`)
);

CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL,
  `multiple` decimal(10,2) DEFAULT NULL,
  `sum` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
);

For find the final value of parent I should iterate in children and calculate following formula: newParentValue = childMultiple(parentValue + childSum)

The implementation in the code is as follows:

function calculateFinalParentValue($parentValue, $children)
{
    foreach ($children as $child) {
        $parentValue = $child['multiple'] * ($parentValue + $child['sum']);
    }
    return $parentValue;
}

How can I implement the calculation in the query?

I try this way (Using temporary variable):

set @value = 0; 

SELECT 
    p.id,
    @value := (c.multiple * (@value + c.sum)) AS value
FROM
    parent p
JOIN
    children c ON p.id = c.parent_id AND @value := p.value;

I set the variable in the join condition (@value := p.value) to reset the variable for each new parent.

This query returns rows for each parent with the number of children, and I need the last row in join for each parent as the answer.

But this way is not sustainable, Is there a better way?

Example:

mysql> select * from parent;
+----+-------+
| id | value |
+----+-------+
|  1 | 10.00 |
|  2 | 20.00 |
+----+-------+

mysql> select * from children;
+----+-----------+----------+------+
| id | parent_id | multiple | sum  |
+----+-----------+----------+------+
|  1 |         1 |     1.00 | 1.00 |
|  2 |         1 |     1.00 | 1.00 |
|  3 |         1 |     1.00 | 1.00 |
|  4 |         2 |     2.00 | 2.00 |
|  5 |         2 |     2.00 | 2.00 |
+----+-----------+----------+------+

With the above data, I expect following answer:

+----+--------+
| id | value  |
+----+--------+
|  1 |  11.00 |
|  1 |  12.00 |
|  1 |  13.00 | <- final value for parant.id = 1
|  2 |  44.00 |
|  2 |  92.00 | <- final value for parant.id = 2
+----+--------+

For parent.id=1 there is three children and parent.value is 10, so after calculate formula for first child new value is 1 * (10 + 1) = 11 and after second child value is 1 * (11 + 1) = 12 as expected after third child value is 1 * (12 + 1) = 13 (In all three children multiple and sum is equal to 1).

For parent.id=2 there is two children and parent.value is 20, so after calculate formula for first child new value is 2 * (20 + 2) = 44 and after second child value is 2 * (44 + 2) = 92 (In both two children multiple and sum is equal to 2).

And finally I want only final value for each parent so my final expected result is:

+----+--------+
| id | value  |
+----+--------+
|  1 |  13.00 |
|  2 |  92.00 |
+----+--------+

Just in this case to simplify the example, all multiply and sum column of children table for each parent are equal (assume different values) and the final value is the maximum, the final value may not be the maximum every time.

Answer 1

Use ROW_NUMBER() window function to rank the rows of children partitioned by parent_id and ordered by id and SUM() window function to get the sums that you want.
Finally use FIRST_VALUE() window function to get the last sum of each id:

WITH 
  cte_children AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) rn FROM children), 
  cte_sums AS (
    SELECT p.id,
           c.rn,
           POW(c.multiple, c.rn) * p.value + SUM(POW(c.multiple, c.rn)) OVER (PARTITION BY p.id ORDER BY c.rn) * c.sum value
    FROM parent p INNER JOIN cte_children c
    ON c.parent_id = p.id
  )
SELECT DISTINCT id, 
       FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY rn DESC) value
FROM cte_sums;

See the demo.