Calculation in MySQL's join query

Question

I have some calculations and want to perform this in a query.

There are parent and child tables with a 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`)
);

To find the final value of the parent, I should iterate over the children and calculate the 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 to implement calculations in queries?

I tried this way (using temporary variables):

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 the rows for each parent along with the number of children, I need the last row in each parent join as the answer.

But this method 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 |
+----+-----------+----------+------+

Based on the above data, I expect the following answers:

+----+--------+
| 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 are three children and parent.value is 10, so after calculating the formula for the first child, the new value is 1 * (10 1) = 11, No. The value of the second child is 1 * (11 1) = 12 As expected, the value of the third child is 1 * (12 1) = 13 (in all three children are all multiples, and the sum is equal to 1).

For parent.id=2, there are two children and parent.value is 20, so after calculating the formula of the first child, the new value is 2 * (20 2) = 44, The value of the second child is 2 * (44 2) = 92 (both children are multiples and the sum equals 2).

In the end I just want the final value of each parent, so my final expected result is:

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

Just to simplify the example, all multiply and sum columns of each parent's child table are equal (assuming different values) and the final value is the maximum value, The final value may not be the maximum value every time. < /p>

Answer 1

Use ROW_NUMBER() window function to rank rows for children, partitioned by parent_id and divided by id and SUM()Window function sorts to obtain the desired sum.
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;

ViewDemo.

Answer 2

It's a little tricky because you have to reset your value in the middle when the parent changes.

Try the following query:

SELECT 
parentId,
ROUND(iteratingValue, 2) reqValue
 FROM 
        (SELECT 
        parentId,
        `childMultiple`,
        childSum,
        @running_parent,
        (CASE WHEN @current_parent_value=0 THEN @current_parent_value:=parentValue ELSE @current_parent_value=@current_parent_value END) ,
        (CASE WHEN @running_parent!=parentId   THEN @current_parent_value:=parentValue ELSE @current_parent_value:=@current_parent_value END),
        @current_parent_value:=(`childMultiple`*(@current_parent_value+childSum)) AS iteratingValue,
        @running_parent:=parentId
        FROM (SELECT 
        p.`id` parentId,
        c.`multiple`childMultiple,
        p.`value` parentValue,
        c.`sum` AS childSum,
        @current_parent_value:=0,
        @running_parent:=0
        FROM parent p
        JOIN `children` c ON c.`parent_id`=p.`id`
) subTable ORDER BY parentId) finalTable;

You can also replace the CASE statement mentioned above with the IF statement (more readable)

IF(@current_parent_value=0, @current_parent_value:=parentValue, @current_parent_value=@current_parent_value),
IF(@running_parent!=parentId, @current_parent_value:=parentValue, @current_parent_value:=@current_parent_value),

This should give you the output you want.

I used two variables @current_parent_value and @running_parent

@running_parent will help you determine if the previous row and the current row belong to the same parent, and @current_parent_value will help you store the current running value.