在MySQL的联接查询中进行计算

Question

我有一些计算，并想在查询中执行此操作。

存在具有一对多关系的父和子表：

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`)
);

为了找到父级的最终值，我应该迭代子级并计算以下公式： newParentValue = childMultiple(parentValue + childSum)

代码中的实现如下：

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

如何在查询中实现计算？

我尝试这种方式（使用临时变量）：

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;

我在连接条件 (@value := p.value) 中设置变量以重置每个新父级的变量。

此查询返回每个父级的行以及子级的数量，我需要每个父级连接中的最后一行作为答案。

但是这种方式不可持续，有更好的方式吗？

示例：

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 |
+----+-----------+----------+------+

根据上述数据，我期望得到以下答案：

+----+--------+
| 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
+----+--------+

对于parent.id=1，有三个孩子，parent.value为10，因此在计算第一个孩子的公式后，新值是 1 * (10 + 1) = 11 ，第二个孩子的值是 1 * (11 + 1) = 12 正如预期的那样，第三个子值是 1 * (12 + 1) = 13 （在所有三个子项中都是倍数，总和等于 1）。

对于parent.id=2，有两个孩子，parent.value为20，因此在计算第一个孩子的公式后，新值是 2 * (20 + 2) = 44 ，第二个孩子的值是 2 * (44 + 2) = 92 （两个孩子都是倍数且总和等于 2）。

最后我只想要每个父母的最终值，所以我的最终预期结果是：

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

只是为了简化示例，每个父级的子表的所有 multiply 和 sum 列都是相等的（假设不同的值）并且最终值为最大值，最终值可能不是每次都是最大值。< /p>

Answer 1

使用ROW_NUMBER()窗口函数对children的行进行排名，按parent_id分区并按id和SUM()窗口函数进行排序，以获得所需的总和。
最后使用FIRST_VALUE()窗口函数获取每个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;

查看演示。

Answer 2

有一点棘手，因为在父级发生变化时，你必须在中间重置你的值。

尝试以下查询：

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;

你也可以用IF语句替换上述提到的CASE语句（更易读一些）

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),

这应该给你想要的输出。

我使用了两个变量@current_parent_value和@running_parent

@running_parent将帮助你确定前一行和当前行是否属于同一个parent，而@current_parent_value将帮助你存储当前的运行值。