MYSQL SUM jusqu'au dernier jour de Chaque mois pour les 12 derniers mois

Voici comment j'aborderais probablement cela dans MySQL 8 avec SUM OVER :

1. Obtenez les 12 derniers mois.
2. Utilisez ces mois pour ajouter des lignes de mois vides aux données d'origine, car MySQL ne prend pas en charge les jointures externes complètes.
3. Obtenez les totaux cumulés pour tous les mois.
4. N'afficher que les douze derniers mois.

La requête:

with months (date) as
(
  select last_day(current_date - interval  1 month) union all
  select last_day(current_date - interval  2 month) union all
  select last_day(current_date - interval  3 month) union all
  select last_day(current_date - interval  4 month) union all
  select last_day(current_date - interval  5 month) union all
  select last_day(current_date - interval  6 month) union all
  select last_day(current_date - interval  7 month) union all
  select last_day(current_date - interval  8 month) union all
  select last_day(current_date - interval  9 month) union all
  select last_day(current_date - interval 10 month) union all
  select last_day(current_date - interval 11 month) union all
  select last_day(current_date - interval 12 month)
)
, data (date, amount, type) as
(
  select last_day(a.date), a.amount, b.type
  from a
  join b on b.id = a.b_id
  where b.type in (5, 7)
  union all
  select date, null, null from months
)
select
  date,
  sum(sum(case when type = 5 then amount end)) over (order by date) as t5,
  sum(sum(case when type = 7 then amount end)) over (order by date) as t7
from data
group by date
order by date
limit 12;

Démo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ddeb3ab3e086bfc182f0503615fba74b

Je ne sais pas si c'est plus rapide que votre propre requête ou non. Essayez-le. (Vous obtiendrez ma requête beaucoup plus rapidement en ajoutant une colonne générée pour last_day (date) à votre table et en l'utilisant. Si vous en avez besoin souvent, cela peut être une option.)

Vous obtenez des réponses compliquées. Je pense que c'est plus facile. Commencez par savoir que nous pouvons facilement additionner pour chaque mois:

SET @running5 := 0;
SET @running7 := 0;

SELECT 
    LAST_DAY(CONCAT(year,'-',month,'-',1)),
    (@running5 := @running5 + (CASE WHEN type=5 THEN monthtotal ELSE 0 END)) as running5, 
    (@running7 := @running7 + (CASE WHEN type=7 THEN monthtotal ELSE 0 END)) as running7
FROM 
(SELECT SUM(amount) as monthtotal,type,MONTH(date) as month,YEAR(date) as year FROM A LEFT JOIN B on A.B_id=B.id GROUP BY type,month,year) AS totals
ORDER BY year,month

À partir de ces données, nous pouvons utiliser une variable pour obtenir le total cumulé. Il est préférable de le faire en initialisant la variable, mais ce n'est pas nécessaire. Nous pouvons obtenir les données nécessaires comme ceci

SET @running := 0;

SELECT (@running := @running + monthtotal) as running, type, LAST_DAY(CONCAT(year,'-',month,'-',1))
FROM 
(SELECT SUM(amount) as monthtotal,type,MONTH(date) as month,YEAR(date) as year FROM A LEFT JOIN B on A.B_id=B.id GROUP BY type,month,year) AS totals
ORDER BY year,month

Vous devez vraiment avoir un connecteur qui prend en charge plusieurs instructions, ou faire plusieurs appels pour initialiser la variable. Bien que vous puissiez annuler la vérification de la variable et la valeur par défaut à 0, vous rencontrez toujours un problème si vous exécutez la requête une deuxième fois.

Dernière chose, si vous voulez vraiment que les types soient additionnés séparément: p >

SELECT SUM(amount) as monthtotal,
        type,
        MONTH(date) as month,
        YEAR(date) as year 
FROM A LEFT JOIN B on A.B_id=B.id 
GROUP BY type,month,year

Nous n'affichons toujours pas les mois sans données. Je ne suis pas sûr que ce soit une exigence. Mais cela ne devrait nécessiter qu'un seul passage de la table A.

Assurez-vous également que l'identifiant de la table B est indexé.