在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將幫助你儲存目前的運行值。