Berechnung in der Join-Abfrage von MySQL

Question

Ich habe einige Berechnungen und möchte diese in einer Abfrage durchführen.

Es gibt Eltern-- und Kind-Tabellen mit einer Eins-zu-viele-Beziehung:

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

Um den endgültigen Wert des übergeordneten Elements zu ermitteln, sollte ich über die untergeordneten Elemente iterieren und die folgende Formel berechnen:

newParentValue = childMultiple(parentValue + childSum)

Die Implementierung im Code ist wie folgt:

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

Wie implementiert man eine Berechnung in einer Abfrage?

Ich versuche es so (mit temporären Variablen):

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;

Ich habe die Variable in der Join-Bedingung festgelegt

, um die Variable für jedes neue übergeordnete Element zurückzusetzen. (@value := p.value)

Diese Abfrage gibt die Zeilen für jedes übergeordnete Element zusammen mit der Anzahl der untergeordneten Elemente zurück. Als Antwort benötige ich die letzte Zeile in jeder übergeordneten Verbindung.

Aber dieser Weg ist nicht nachhaltig.

Gibt es einen besseren Weg?

Beispiel:

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

Basierend auf den oben genannten Daten würde ich die folgende Antwort erwarten:

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

Für parent.id=1 gibt es drei Kinder und parent.value ist 10, sodass nach der Berechnung der Formel für das erste Kind der neue Wert

ist (was ein Vielfaches aller drei Kinder ist und die Summe 1 ergibt). 1 * (10 + 1) = 11 ，第二个孩子的值是 1 * (11 + 1) = 12 正如预期的那样，第三个子值是 1 * (12 + 1) = 13

Für parent.id=2 gibt es zwei Kinder und parent.value ist 20, sodass nach der Berechnung der Formel für das erste Kind der neue Wert

ist (beide Kinder sind Vielfache und die Summe entspricht 2). 2 * (20 + 2) = 44 ，第二个孩子的值是 2 * (44 + 2) = 92

Am Ende möchte ich nur den Endwert jedes Elternteils, also ist mein endgültiges erwartetes Ergebnis:

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

Um das Beispiel zu vereinfachen: Alle

Spalten der untergeordneten Tabelle jedes Elternteils sind gleich (unter der Annahme unterschiedlicher Werte) und der Endwert ist das Maximum. Der Endwert ist möglicherweise nicht jedes Mal das Maximum. multiply 和 sum

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将帮助你存储当前的运行值。