Discussion :

[afkiba]

Bonjour,
j'ai une base de donnée administrée par MySQL dont je veux trier les données selon un argument de 31.
J'ai une table qui contient des nombres (la fréquence d'une opération). Je veux pouvoir faire la somme des nombres de chaque ligne pour afficher les lignes qui tomberaient sur les chiffres 31,62,93,124,155,186....
Quelqu'un aurait-il une idée de comment je peux écrire cette requête sql.
Merci

[Manequin]

Bonsoir, propose ce que tu as d'abord fait. Je veux dire la requête et le message d'erreur. Fait également une capture d'écran de table. Comme ça pourra mieux t'orienter.

Cordialement.

[afkiba]

Voici un extrait (image) de la base que je traite pour ressortir les lignes concernées. Il faut noté que la base comporte plusieurs lignes cachées.


Voici la requête que j'ai écrit qui me permet d'affichée la somme des nombres

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT mv.numero,mv.date_mouvement ,cp.num_client, mv.num_agent, mv.num_compte,cp.montant_mise,(sum(nombre)) AS CUMUL FROM tontinier.mouvement mv inner join tontinier.compte cp on mv.num_compte=cp.numero where cp.num_client ="3554" and typeop="Mise" group by cp.numero;

Voici le résultat que cela me présente


Je veux bien affiché les lignes qui tomberais sur les chiffres 31,62,93,124,155,186.... lors du calcul de la somme (CUMUL)
Merci pour votre aide.

[Manequin]

Comment peux-tu additionner les entiers, les dates et les chaînes de caractères ? . Là je suis perdu.
Exprime toi clairement.

Cordialement.

[afkiba]

Bonjour,
j'ai juste additionné les entiers [(sum(nombre)) AS CUMUL] en tenant compte des numéro de compte, seulement que le syntaxe sum() regroupe toutes les lignes.
En observant la deuxième image où il y a la colonne CUMUL,
- pour la 1ère ligne, je voulais que la requête me retourne la ligne au cours de laquelle la somme à atteint 31.
- pour la 2ème ligne, je boulais que la requête me retourne deux lignes: la première celle au cours de laquelle la somme à atteint 31 et la deuxième celle au cours de laquelle la somme à atteint 62.

Avez vous une autre manière à me proposée?
Merçi.

[CinePhil]

Bonjour.

1) Un code indenté et aéré est plus agréable à lire et à déboguer.
Voici votre requête un peu plus agréable à lire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise,
	SUM(nombre) AS CUMUL 
FROM tontinier.mouvement mv 
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero 
WHERE cp.num_client = "3554" 
	AND typeop = "Mise" 
GROUP BY cp.numero;

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.
Et c'est exactement ce qui se passe ici.

Si je comprends bien, vous voulez d'abord faire la somme des nombres par compte (cumul) puis extraire les lignes de mouvement ayant permis d'atteindre certains résultats de cumul.
On va donc procéder par étape, en supposant que la colonne nombre est dans la table mouvement...

A) D'abord on calcule les cumuls par compte et on ne garde que les cumuls désirés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT num_compte, 
	SUM(nombre) AS cumul
FROM tontinier.mouvement
GROUP BY num_compte
HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)

B) On extrait le "montant_mise" du compte et les lignes de mouvement correspondantes en utilisant la requête précédente en sous-requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise,
	tmp.cumul
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT num_compte, 
	SUM(nombre) AS cumul
	FROM tontinier.mouvement
	GROUP BY num_compte
	HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)
) tmp ON tmp.num_compte = mv.num_compte

[afkiba]

Merci pour l'intérêt que vous avez pour ce poste,
C'est exactement ce que je faire ça.

Si je comprends bien, vous voulez d'abord faire la somme des nombres par compte (cumul) puis extraire les lignes de mouvement ayant permis d'atteindre certains résultats de cumul.

Je viens de tester la requête que vous m'avez proposé. Elle me renvoie toutes les mouvements d'un compte respectant la condition [HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)] alors que je voulais que la requête me renvoie uniquement les lignes au cours de laquelle la condition est respectée.
Pour une bonne visibilité du resultat, j'ai ajouté ORDER BY mv.num_compte et j'ai aussi précisé le type de mouvements concernés.
Voici un extrait de la base (image)


Lorsque j'exécute la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise,
	tmp.cumul
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT num_compte, 
	SUM(nombre) AS cumul
	FROM tontinier.mouvement WHERE typeop="Mise"
	GROUP BY num_compte
	HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)
) tmp ON tmp.num_compte = mv.num_compte ORDER BY mv.num_compte

Voici le résultat que ça m'affiche (image)

Merci.

[CinePhil]

Arf, j'ai oublié votre WHERE !
Mais apparemment, vous l'avez ajouté donc c'est ?

[afkiba]

Non, ce n'est pas encore résolue.
Je veux juste les lignes qui tomberais sur les chiffres 31,62,93,124,155,186.... Mais dans ce cas, il m'affiche toutes les lignes des comptes qui remplissent les conditions de [HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)].
Je veux que la requête m'affiche la ligne qui a permit de remplir la condition de HAVING.
De plus je veux crée cette condition comme une fonction linéaire de type (31*X +1) avec 31 l'argument.

[CinePhil]

Votre besoin n'est vraiment pas clair ! Il va falloir nous donner un exemple positif et un autre négatif.

Est-ce que vous voulez ne garder que le dernier mouvement enregistré ?

[CinePhil]

Si ce que j'ai compris ci-dessus est juste, alors il faut chercher le mouvement qui a la date la plus haute et aller chercher les infos souhaitée en faisant une jointure avec la requête qui calcule et cherche le dernier mouvement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise,
	tmp.cumul
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT tmp.num_compte, 
		MAX(mv1.date_mouvement) AS date_max
	FROM tontinier.mouvement mv1
	INNER JOIN
	(
		SELECT num_compte, 
		SUM(nombre) AS cumul
		FROM tontinier.mouvement 
		WHERE typeop = 'Mise'
		GROUP BY num_compte
		HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)
	) tmp ON tmp.num_compte = mv1.num_compte
) tmp1 
	ON mv.num_compte = tmp1.num_compte
	AND mv.date_mouvement = tmp1.date_max

[afkiba]

Je m'explique, mon travail consiste à traiter les commissions payées par des clients sur les opérations qu'ils effectues dans leurs comptes.
Un client peux avoir plusieurs comptes dans lesquels il mène des opérations (mouvements).
Une commission est une mise que le client paie pour avoir effectué 31 mises. Donc par chaque tranche de 31 mises d'un compte, le client paie 1 mise comme commission. C'est ce ci qui m'amène à poser la condition de 31,62,93,124,155,186....

Initialement quant j'ai commencé le traitement, j'ai calculé les commissions sur chaque compte d'un client.
Voici l'extrais de la base (image) pour le client 0017


Avec cette requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT mv.numero,mv.date_mouvement ,cp.num_client, mv.num_agent, mv.num_compte,
		cp.montant_mise,floor(sum(nombre)/31) AS Tranche, 
		((sum(nombre)/31)- floor(sum(nombre)/31))*31 as Mise, 
		(floor(sum(nombre)/31)*cp.montant_mise) as Commission 
FROM tontinier.mouvement mv 
INNER JOIN tontinier.compte cp on mv.num_compte=cp.numero 
WHERE cp.num_client ="0017" and typeop="Mise" group by cp.numero;

j'ai puis calculé les commissions et constitué les tranches de ce client
Voici le resultat


Cet image montre que ce client (0017) a 4 comptes

Cette procédé comportes des limites à savoir:
- je n'arrive pas à identifier exactement la mise qui constitue la commission;
- lorsque je lance la requête cela prend un peu de temps alors que je veux faire le traitement sur l'ensemble des comptes;

Pour palier à ces limites, j'ai pensé faire la somme des mises afin d'affichées celles qui tomberais sur les chiffres 31,62,93,124,155,186.... ceux sont eux qui constitues les commissions que je recherche.

Avec la requête que vous m'aviez proposée, cela à afficher tous les mises additionnée pour remplir la condition mais moi je veux la dernière qui a permis de remplir la condition.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise,
	tmp.cumul
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT num_compte, 
	SUM(nombre) AS cumul
	FROM tontinier.mouvement WHERE typeop="Mise"
	GROUP BY num_compte
	HAVING SUM(nombre) IN (31, 62, 93, 124, 155, 186)
) tmp ON tmp.num_compte = mv.num_compte ORDER BY mv.num_compte

L'objectif final est de pouvoir stocker les commissions dans une table autre que celle des mouvements car ma base s'alourdir et les traitements prennent du temps.
Merci pour votre aide.

[CinePhil]

je n'arrive pas à identifier exactement la mise qui constitue la commission;

Est-ce le mouvement sur le compte qui permet d'arriver à l'une des sommes que vous avez mise en liste ?

Si oui, il me semble donc que c'est, chronologiquement, le dernier mouvement qui a permis d'atteindre le cumul, non ?

[afkiba]

Si oui, il me semble donc que c'est, chronologiquement, le dernier mouvement qui a permis d'atteindre le cumul, non ?

Exactement.

[CinePhil]

Je commence à voir la difficulté si vous souhaitez avoir dans la même requête le 31ème mouvement, le 62ème...

C'est peut-être possible avec les nouvelles fonctions de fenêtrage de MySQL (que je n'ai jamais encore utilisées) disponibles en version 8. Sinon je crains qu'il faille faire ça par une procédure SQL ou bien dans un langage de programmation externe.
Quelle version de MySQL utilisez-vous ?


Pour débuter :
1) Sélection des comptes dont le nombre de mises est un multiple de 31

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT num_compte,
	SUM(nombre) AS nb_mises
FROM tontinier.mouvement
WHERE typeop = 'Mise'
GROUP BY num_compte
HAVING SUM(nombre) % 31 = 0; -- Reste de la division du nombre de mise par 31 = 0 => on a tous les multiples de 31

2) Récupération de tous les mouvements des comptes sélectionnés ci-dessus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise, tmp.nb_mises
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT num_compte,
		SUM(nombre) AS nb_mises
	FROM tontinier.mouvement
	WHERE typeop = 'Mise'
	GROUP BY num_compte
	HAVING SUM(nombre) % 31 = 0; -- Reste de la division du nombre de mise par 31 = 0 => on a tous les multiples de 31
) tmp ON tmp.num_compte = mv.num_compte
ORDER BY mv.num_compte, mv.date_mouvement

À partir de là, on peut parcourir le résultat de cette dernière requête et s'arrêter, pour chaque compte, sur la ligne qui permet d'arriver à la 31ème mise, 62ème mise... et faire les opérations de calcul et traitements souhaités.

[afkiba]

J'utilise MySQL 5.6
J'ai vraiment de difficulté à m'en sortir.
La base de donnée est devenir lourde (environ 98Mo en fichier sql) et parfois quand je lance une recherche ça prend plusieurs heures avant d'affichée le résultat. J'ai donc pensé traité une foi de bonne et stocké la commission dans une autre pour que les fois la recherche me soit facile.
Si vous avez d'autres conseil à me donnée ça me ferra plaisir.

Tout à l'heure je viens d'essayé la requête que vous avez proposé, mais me renvoi toutes les mouvement des comptes ayant au moins 31 mises. Pour le tri des commissions seul comment je peux faire ?
Merci.

[CinePhil]

Comme j'ai dit, il faut faire une procédure SQL ou un programme externe qui parcourt le résultat de ma requête et cherche la ligne qui déclenche la commission. C'est plus compliqué mais pérenne. À moins qu'un petit génie de MySQL ait une autre idée...

Sinon, si c'est pour du one shot : autant exporter le résultat dans un tableur et faire une colonne de cumul et travailler avec les lignes qui atteignent ou dépassent 31 puis 62...
Si c'est pour mettre les commissions dans une autre table, c'est peut-être le plus facile et rapide.

[afkiba]

Merci pour l'aide.
Je vais faire le traitement en programmation avec java.
a+

[afkiba]

Bonsoir,
Je suis entrain de poursuivre mon traitement en java.
Je voulais juste savoir si c'est possible que je puisse faire le cumul (somme) progressif des nombres par chaque compte au lieu d'avoir ça en somme brisque ?
Cela me permettra d'exploité ce code par la suite:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT mv.numero, mv.date_mouvement, cp.num_client, mv.num_agent, mv.num_compte, cp.montant_mise, tmp.nb_mises
FROM tontinier.mouvement mv
INNER JOIN tontinier.compte cp ON mv.num_compte = cp.numero
INNER JOIN 
(
	SELECT num_compte,
		SUM(nombre) AS nb_mises
	FROM tontinier.mouvement
	WHERE typeop = 'Mise'
	GROUP BY num_compte
	HAVING SUM(nombre) % 31 = 0; -- Reste de la division du nombre de mise par 31 = 0 => on a tous les multiples de 31
) tmp ON tmp.num_compte = mv.num_compte
ORDER BY mv.num_compte, mv.date_mouvement

Merci

[afkiba]

Bonjour,
Après des recherche j'ai puis avoir cette requête qui me permet de faire le cumul progressive.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT t1.date_mouvement, t1.num_compte,t1.num_agent,t1.nombre
, (
    SELECT SUM(t2.nombre)
    FROM tontinier.mouvement t2
    WHERE t2.date_mouvement <= t1.date_mouvement
) AS cumul
FROM tontinier.mouvement t1
GROUP BY t1.num_compte 
ORDER BY t1.date_mouvement

Mais la vrai limite à ça est que le temps de recherche est trop depuis plus de 2 heures je n'ai pas le résultat de ma requête.
Quand je l'ai fait sur un seul compte, la requête a pris environ 4 minute pour afficher le résultat.

Comment pourrai-je optimiser cette requête pour gagner du temps ?