Lister les x premiers articles en stock selon un critère

[dcpc007]

Bonjour,

J'ai un petit soucis pour faire une requête. J'utlise Mysql 5.1 avec ligne de commande ou phpmyadmin pour le moment.
De préférence je préfèrerais donc éviter de devoir coder dans un langage a côté, et donc pouvoir obtenir le résultat juste avec du sql (sinon php ou ruby ?)

Table avec 4 champs utilisés pour cette requete :
nom -> varchar (20)
qte -> int (0 à 10 000 en gros)
valeur1 -> int (0 à 1000)
valeur2 -> int (0 à 1000)

1ere étape : quels sont les x premiers objets (12 par ex) ayant le cout "valeur1" le plus élevé ?

ex :
aaa 10 1000 12
bbb 5 250 15
ccc 3 500 17

résultat attendu :
aaa 10 1000 12
ccc 2 500 17


2ème étape : pareil mais avec une règle en plus pour gérer les égalités : on priorise avec la valeur2 la plus grande en cas d'égalité


ex :
aaa 10 1000 12
bbb 5 250 15
ccc 3 500 17
ddd 4 500 21

résultat attendu :
aaa 10 1000 12
ddd 2 500 21


3ème étape au cas où : si valeur1 et valeur2 sont identiques comment cela se passerait-il ? (je ne suis pas sûr que le cas soit possible mais bon )

Mon idée : de toute façon le champ d'index sur un texte est une "hérésie", il devrait donc y avoir un index numérique en auto-incrément, et je pense du coup appliquer la même solution mais avec cet index, en suivant la règle : on préfèrera l'article le plus récent, donc celui avec le plus haut index.

Ou plus propre encore ajouter un index unique de "préférence" mais géré manuellement, qui donne l'ordre des produit. L'auto-incrément pourrait poser problème si on insère une valeur après coup.

Merci encore :o)
David

[CinePhil]

Tu as un essai de requête à nous proposer ?

[dcpc007]

Non justement, ça fait quelques années que je ne touche plus trop à SQL sauf avec des requêtes basiques.

Je sais que j'ai vu ça il y a très longtemps, mais impossible de m'en souvenir, et j'avoue que j'ai du mal a trouver l'intitulé à rédiger pour une recherche google

Je réfléchis à une solution avec des min/max d'un compte de valeurs et ajouter une clause sur cette variable, ce qui ferait une sorte de requête dynamique, mais je ne sais même pas si cela est toléré .

Un petit kkechose comme

Code :

1
2
3
4
SELECT nom, valeur1, tot=sum(qte)
FROM stock
WHERE tot = 12
GROUP BY nom  <-- pas beau mais obligé pour le sum

1er prob : tot = 12, dans mon exemple on ne tombe pas sur une égalité, il faudrait donc que j'ai une clause s'arrêtant au premier total au dessus du seuil.

2ème prob : avec ceci je ne peux pas trier sur les valeur1 et valeur2

[dcpc007]

Autre idée si cela est applicable en sql avec une table tempo

créer la table tempo pour "éclater les lignes avec qte > 1"
du genre exploser mon aaa avec qte 10 en 10 lignes "aaa"
ensuite requête basique avec order by et limit 12 :o)

1°) mais comment créer cette table ?
2°) optimisation, si on devrait compter des qte possibles jusqu'à 10 000 et de 20 à 100 items ... ca risque peut-être de faire lourd comme table tempo ?

-----
L'autre algo simple mais avec programme aussi :
select des lignes avec order by sur les champs valeurs
et on incrémente une valeur dans une boucle jusqu'à ce que total > seuil.

-----
SInon on m'a parlé de procédures stockées, mais là j'avoue, ce n'est pas dans mon cursus, et je ne connais pas les possibilités.

[dcpc007]

Ho j'ai une autre idée si c'est possible

J'ajoute un champ total à ma table

Je trie ma table par valeur1,valeur2,sort_id (croissant) <-- sort_id = index de préférence manuel (point 3)
J'édite pour chaque ligne le champ total avec un sum des lignes ayant un ID <= a mon id courant !
et ensuite on doit pouvoir demander la ligne la plus petite étant supérieure à un seuil !

Code :

1
2
3
UPDATE total=sum(qte)
FROM stock
WHERE sort_id <= sort_id

--> Cette requête ne doit pas être aussi simple en fait
[edit] oui pas du tout ça en fait il faudrait que j'arrive à identifier déjà les lignes "inférieures" à mon produits, ce qui nécessite des "if" ou des "case" => zut
[/edit]

Code :

1
2
3
SELECT nom, qte, total
FROM stock
WHERE total <= 12

--> problème, il me manque la dernière ligne si le total <> seuil, zut !

[aieeeuuuuu]

Bonjour

pour la 1/

Code sql :

1
2
3
4
5
 
SELECT *
FROM Stock
ORDER BY Valeur1 DESC
LIMIT 12

pour la 2/

Code sql :

1
2
3
4
5
 
SELECT *
FROM Stock
ORDER BY Valeur1 DESC, Valeur2 DESC
LIMIT 12

pour la 3/ si cela arrive, le SGBDR renverra... dans l'ordre qui lui convient le mieux, a toi donc de rajotuer une autre regle si tu en as besoin, par exemple pour trier par nom

Code sql :

1
2
3
4
5
 
SELECT *
FROM Stock
ORDER BY Valeur1 DESC, Valeur2 DESC, Nom ASC
LIMIT 12

[dcpc007]

tes requetes vont me renvoyer les 12 premières lignes de la table, soit les 12 premiers noms de produits, pas la quantité.

-> regarde les exemples attendus.
le 12 attendus correspond a 10 produits "aaa" + 2 produits "ccc"

[aieeeuuuuu]

ok

j'avais mal compris la question, alors il faut faire

Code sql :

1
2
 
ORDER BY (qte*valeur1) DESC, (qte*Valeur2) DESC

[dcpc007]

Je ne comprends toujours pas trop

Code :

1
2
3
4
SELECT *
FROM Stock
ORDER BY (qte*valeur1) DESC, (qte*Valeur2) DESC
LIMIT 12

va quand même afficher 12 lignes de produits,
tu change juste le tri, avec un calcul (qui me semble étrange en plus, je ne vois pas l'intérêt de multiplier la quantité par la valeur1 comme valeur de tri)

le tri c'est les valeurs les plus chères en premier, ensuite, il faut limiter le nombre de produits (et non de lignes de produits) sur un seuil.

[aieeeuuuuu]

???

Envoyé par dcpc007

va quand même afficher 12 lignes de produits,

Ca n'est pas ce que tu veux ?


explique nous plus clairement, notamment, a quoi correspond cette table ?

Envoyé par dcpc007

tu change juste le tri, avec un calcul (qui me semble étrange en plus, je ne vois pas l'intérêt de multiplier la quantité par la valeur1 comme valeur de tri)

Je pensais que tu voulais trier sur la valeur totale, donc valeur * qté... mais apparemetn ca n'est pas ca

Envoyé par dcpc007

le tri c'est les valeurs les plus chères en premier, ensuite, il faut limiter le nombre de produits (et non de lignes de produits) sur un seuil.

c'est a dire ? ne considerer que les produit dont la quantité depasse un seuil ?

Je ne comprend pas non plus. D'ou sort ton 2 sur la deuxieme ligne de ton resultat attendu :

Envoyé par dcpc007

ex :
aaa 10 1000 12
bbb 5 250 15
ccc 3 500 17
ddd 4 500 21

résultat attendu :
aaa 10 1000 12
ddd 2 500 21

[dcpc007]

J'ai mis la structure de table :

Envoyé par dcpc007

Table avec 4 champs utilisés pour cette requete :
nom -> varchar (20)
qte -> int (0 à 10 000 en gros)
valeur1 -> int (0 à 1000)
valeur2 -> int (0 à 1000)

Ensuite 3 cas de plus en plus compliqués avec à chaque fois la question, les données d'entrées et les données attendues en résultat :

Envoyé par dcpc007

1ere étape : quels sont les x premiers objets (12 par ex) ayant le cout "valeur1" le plus élevé ?

ex :
nom qte valeur1 valeur2
aaa 10 1000 12
bbb 5 250 15
ccc 3 500 17

résultat attendu :
nom qte valeur1 valeur2
aaa 10 1000 12
ccc 2 500 17

Note : si ce n'était pas clair je viens de remettre le 1er cas avec les en-têtes de colonnes.

Et re précision sur le besoin, la table comporte des lignes de produit, chaque produit, on en a une certaine quantité en stock (colonne "qte").

Tes requêtes vont me retourner 12 lignes de la table, soit 12 lignes de produits.
Ma demande : je voudrait les 12 premiers "produits", pas de ligne produit.

Ceci est montré dans le résultat attendu : 10 x le produit "aaa" et 2 x le produit "ccc" qui sont les 12 produits les plus chers de mon stock.

Une autre idée si ce n'est pas clair, par exemple tu peux remplacer les "nom produit" par "nom du logiciel" et "qte" par "nb boites en stock".
La question devient, quelles sont mes 12 boites de logiciels ayant la plus forte valeur.

[aieeeuuuuu]

ok, j'ai compris la subtilité (enfin je crois)

est-ce que cette requete te donne ce que tu veux ?

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
SELECT s1.nom, qte, valeur1, valeur2
FROM stock s1
WHERE qte + (
	SELECT IFNULL(SUM(s2.qte),0) 
	FROM stock AS s2 
	WHERE s2.valeur1>s1.valeur1 
	OR (S2.valeur1 = s1.valeur1 AND s2.valeur2>s1.valeur2)
	) <= 12
UNION
SELECT s1.nom
	, 12 - (
		SELECT IFNULL(SUM(s2.qte),0) 
		FROM stock AS s2 
		WHERE s2.valeur1>s1.valeur1 
		OR (S2.valeur1 = s1.valeur1 AND s2.valeur2>s1.valeur2)
	)
	, valeur1
	, valeur2
FROM stock s1
WHERE (
	SELECT IFNULL(SUM(s2.qte),0) 
	FROM stock AS s2 
	WHERE s2.valeur1>s1.valeur1 
	OR (S2.valeur1 = s1.valeur1 AND s2.valeur2>s1.valeur2)
) < 12
AND qte + (
	SELECT IFNULL(SUM(s2.qte),0) 
	FROM stock AS s2 
	WHERE s2.valeur1>s1.valeur1 
	OR (S2.valeur1 = s1.valeur1 AND s2.valeur2>s1.valeur2)
) > 12

[Maljuna Kris]

Citons à nouveau Boileau, comme Cinephil l'affiche dans sa signature

« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ».

On veut savoir, en fonction du stock disponible, le budget maximum que l'on pourrait refourguer pour un nombre d'articles précis (12 en l'occurrence)
C'est bien ça ?

[dcpc007]

@Maljuna Kris : Oui par exemple.

En fait j'ai ce besoin pour plusieurs cas très différents, mais qui répondent à la même problématique.

J'ai essayé de retranscrire un seul exemple pour simplifier mais je n'ai peut-être pas très bien réussi.

Autre exemple : J'ai des documents, à chacun j'affecte un "poids", une note,... (qui sera calculé selon certains critères) qui reflète son importance.
Ensuite je voudrais connaître les 100 documents les plus importants pour les sauvegarder de manière différente.

Autre exemple sur un jeu : Quelles sont mes 100 meilleures unités, tous types confondus.

[Maljuna Kris]

Ça sent le besoin de récursivité, or MySQL et la récursivité....

[aieeeuuuuu]

Envoyé par Maljuna Kris

Ça sent le besoin de récursivité, or MySQL et la récursivité....

La requete que j'ai postée plus haut fonctionne sous MSSQL, j'ai remplacé les COALESCE par des IFNULL pour que ca passe sous MySQL (il y a peut être d'autre détails de syntaxe a corriger, je n'ai pas de MySQL sous la main, et je le connais trop peu), et il n'y a pas de récursivité.

en soit elle est simple :
je calcul pour chaque article le la somme des article "mieux classé" (valeur1 plus grande, ou valeur1 égale et valeur2 plus grande)
si cette somme + la quantité de l'article en question est inférieure a 12, alors l'article entre complètement dans le critère de sélection.
Il n'y a que pour le dernier article que ca se complique un peu, car il faut calculer le delta entre la quantité dudit article, et la quantité totale voulue (ici, 12)
c'est le but de l'UNION dans ma requete (la deuxième requete après l'UNION ne sert qu'a ajouter l'article dont on ne prend pas la quantité totale). cependant, on doit pouvoir faire mieux (genre avec un simple CASE, mais je ne sais pas si ca sera vraiment mieux niveau perf)

[CinePhil]

Envoyé par aieeeuuuuu

j'ai remplacé les COALESCE par des IFNULL pour que ca passe sous MySQL

COALESCE existe aussi sous MySQL !

[dcpc007]

@aieeeuuuuu

Bon j'ai testé, ça fonctionne ! Merci beaucoup
J'ai fais les cas avec 1 seul articles, plusieurs, cas d'égalités,...

Note : j'ai juste corrigé quelques "S2" en "s2".

Par contre j'avoue ne pas avoir vu encore ce IFNULL, je vais aller voir les tutos Et je n'ai pas encore bien compris comment cela fonctionne vraiment, je vias essayer de la découper pour voir.

Petite question annexe, peut-on paramétrer la quantité pour ne la saisir qu'une fois ?
Ou sinon est-il possible de rajouter une table "param" avec un champ de critere, et ajouter ce champ dans la requête, même si cette table n'aura pas de liaison "relationnelle" avec les tables de données ?

David

[CinePhil]

COALESCE est plus respectueux de la norme SQL que IFNULL qui est je crois spécifique à MySQL. Il vaut mieux connaître COALESCE que tu pourras utiliser aussi quand tu seras confronté à un autre SGBD.

[aieeeuuuuu]

Envoyé par CinePhil

COALESCE existe aussi sous MySQL !

Arfff, j'ai encore usé mon clavier pour rien alors...

merci CinePhil de l'info car comme je disais, je ne connais pas trop MySQL.