Comment puis-je calculer efficacement les sommes cumulées dans MySQL ?

Somme cumulée sur les lignes dans MySQL

Pour déterminer la somme cumulée pour chaque identifiant, regroupée par heure de la journée, une approche commune est utilisée dans MySQL. En raison de l'absence de fonctions analytiques dans les versions antérieures de MySQL, deux méthodes principales sont utilisées : une sous-requête corrélée ou des variables utilisateur MySQL.

Approche des sous-requêtes corrélées

Cette stratégie implique une sous-requête qui calcule le total cumulé pour chaque ligne, en fonction de critères de correspondance. Cependant, cela peut être gourmand en ressources pour des ensembles de données importants.

Approche des variables utilisateur

Une méthode plus efficace implique des variables utilisateur MySQL et une requête encapsulée avec une clause ORDER BY pour garantir la séquence de traitement des lignes. Les étapes suivantes décrivent cette approche :

1. Initialisez les variables utilisateur pour éviter les interférences des valeurs de session existantes.
2. Incorporez la requête d'origine en tant que vue en ligne avec la clause ORDER BY.
3. Effectuez le calcul de la somme cumulée dans une instruction SELECT, en comparant les valeurs des lignes précédentes et actuelles.
4. Attribuez les valeurs de la ligne actuelle aux variables utilisateur pour la prochaine itération.

Un exemple de cette approche :

SELECT
  IF(@prev_id = c.id AND @prev_day = c.day
    , @cumtotal := @cumtotal + c.amount
    , @cumtotal := c.amount) AS cumulative_total
, @prev_id  := c.id  AS `id`
, @prev_day := c.day AS `day`
, c.hr
, c.amount AS `amount'
FROM
  (
    SELECT
      @prev_id  := NULL
    , @prev_day := NULL
    , @cumtotal := 0
  ) i
JOIN
  (
    SELECT id, day, hr, amount FROM
    (
      // Original query with multiple joins and unions
    ) a
    LEFT JOIN
    (
      // Unions on multiple tables
    ) b
    ON a.id = b.id
    ORDER BY 1, 2, 3
  ) c

Cette approche permet un calcul efficace des sommes cumulées dans les versions MySQL antérieures à 8.0, lorsque les fonctions de fenêtre ont été introduites .

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!