Calcul dans la requête de jointure de MySQL

Question

J'ai quelques calculs et je souhaite les effectuer dans une requête.

Il existe une table parent et enfant avec une relation un-à-plusieurs :

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

Pour trouver la valeur finale du parent, je dois parcourir les enfants et calculer la formule suivante : newParentValue = childMultiple(parentValue + childSum)

L'implémentation dans le code est la suivante :

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

Comment implémenter le calcul dans une requête ?

J'essaie de cette façon (en utilisant des variables temporaires) :

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;

J'ai défini la variable dans la condition de jointure (@value := p.value) pour réinitialiser la variable pour chaque nouveau parent.

Cette requête renvoie les lignes de chaque parent ainsi que le nombre d'enfants. J'ai besoin de la dernière ligne de chaque connexion parent comme réponse.

Mais cette méthode n'est pas durable. Y a-t-il une meilleure solution ?

Exemple :

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

Sur la base des données ci-dessus, je m'attendrais à la réponse suivante :

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

Pour parent.id=1, il y a trois enfants et parent.value est 10, donc après avoir calculé la formule pour le premier enfant, la nouvelle valeur est 1 * (10 + 1) = 11 ，第二个孩子的值是 1 * (11 + 1) = 12 正如预期的那样，第三个子值是 1 * (12 + 1) = 13 (qui est un multiple dans les trois enfants et la somme est égale à 1).

Pour parent.id=2, il y a deux enfants et parent.value est 20, donc après avoir calculé la formule pour le premier enfant, la nouvelle valeur est 2 * (20 + 2) = 44 ，第二个孩子的值是 2 * (44 + 2) = 92 (les deux enfants sont des multiples et la somme est égale à 2).

En fin de compte, je veux juste la valeur finale de chaque parent, donc mon résultat final attendu est :

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

Juste pour simplifier l'exemple, toutes les multiply 和 sum colonnes de la table enfant de chaque parent sont égales (en supposant des valeurs différentes) et la valeur finale est le maximum, la valeur finale peut ne pas être le maximum à chaque fois. < /p>

Answer 1

Utilisez la fonction ROW_NUMBER()窗口函数对children的行进行排名，按parent_id分区并按id和SUM()window pour trier afin d'obtenir la somme souhaitée.
Utilisez enfin la fonction FIRST_VALUE() window pour obtenir la dernière somme pour chaque identifiant :

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;

Voir Démo.

Answer 2

C'est un peu délicat car il faut réinitialiser sa valeur au milieu lorsque le parent change.

Essayez la requête suivante :

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;

Vous pouvez également utiliser des déclarations IF语句替换上述提到的CASE (plus lisibles)

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

Cela devrait vous donner le résultat souhaité.

J'ai utilisé deux variables@current_parent_value和@running_parent

@running_parent将帮助你确定前一行和当前行是否属于同一个parent，而@current_parent_value vous aidera à stocker les valeurs en cours d'exécution.