Recherche la valeur de A quand la valeur B est maximale selon C

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : Recherche la valeur de A quand la valeur B est maximale selon C

Arjuna

Aircraft Ident.: F-MBSD

Alors voilà.

Je suis très régulièrement face à un problème bête et méchant...

Imaginons... Dans un système de calcul de prix pour un client, j'ai une table qui me permet de retrouver les prix.
Mais un prix calculé est prioritaire sur cette table : il s'agit du dernier prix auquel on a vendu le produit à ce client.

Pour retrouver le prix de vente de ce produit, je fais comment ?

Là, je retrouve toutes les commandes avec le prix de vente pour un client donné et un produit donné, en euros (ouais parceque sinon le résultat c'est le bordel )

Ca donne :

PRXVDU CODPRO
7.1 20020130
7.1 20020130
7.05 20021216
7.11 20030311
7.05 20030602
7.21 20031008
7.15 20040123
7.15 20040123
7.15 20040318
7.1 20020329

Actuellement, le seul moyen que je connaisse, c'est de faire... (beurk)

select evp.prxvdu, eve.dateve
from evp, eve
where eve.codsoc = 0
and eve.achvte = 'V'
and eve.typeve = 'CDE'
and eve.typtie = 'CLI'
and eve.sigtie = '000068'
and eve.coddev = 'EUR'
and evp.codsoc = eve.codsoc
and evp.achvte = eve.achvte
and evp.typeve = eve.typeve
and evp.numeve = eve.numeve
and evp.codpro = '1F00034'
and eve.dateve = (select max(eve2.dateve)
from evp evp2, eve eve2
where eve2.codsoc = eve.codsoc
and eve2.achvte = eve.achvte
and eve2.typeve = eve.typeve
and eve2.typtie = eve.typtie
and eve2.sigtie = eve.sigtie
and eve2.coddev = eve.coddev
and evp2.codsoc = eve2.codsoc
and evp2.achvte = eve2.achvte
and evp2.typeve = eve2.typeve
and evp2.numeve = eve2.numeve
and evp2.codpro = evp.codpro)

Ca donne :

PRXVDU CODPRO
7.15 20040318

Y'a bien moyen de faire ça plus proprement non ?
Parceque dans la réalité, bien souvent, je dois faire ce type de truc dans le cadre d'extracts portant sur un volume important de données...

Déjà là je tape dans eve (1 467 490 lignes) et evp (3 769 861 lignes), mais je ne récupère qu'une seule ligne... Et quand je dois aller taper dans les tables evs, evl pour retrouver les informations de la facture en fonction des petits bouts de livraison de la commande schédulée dans le temps (maxi bordel) non seulement j'ai un nombre pas croyable de données mais en plus la requête commence à devenir particulièrement bordelique !

Donc... Y'a pas un moyen plus propre de faire la chose ? Et surtout plus performant ? Bon, Oracle ne s'en sort pas trop mal (là la requête en production il me la sort en 94 msec) mais des fois ça commence à patatter dans la semoule grave, et vu la tronche du machin c'est pas étonnant

Publicité

sircam

I Like Trains

Tu ne stockerais pas tout simplement l'info en question ??

---------------
Now Playing: {SYNTAX ERROR AT LINE 1210}

Arjuna

Aircraft Ident.: F-MBSD

Non, impossible (le soft qui rempli la base n'est pas modifiable, c'est un ERP). Deplus, il y a trop de "cas" possibles pour rentre cette info stockable sans risque... Par exemple, si la commande est annulée, il faut retrouver le précédent montant. Le prix dépends dans tous les cas de la quantité commandée, ainsi, si j'ai commandé 10 unité à 1 , alors si j'en commande 5, je ne devrai pas reprendre cette commande, mais une précédente où la quantité était inférieure ou égale à 5.
Ingérable quoi

Beegee

A priori, je ne vois pas d'autre moyen, en tout cas pas de moyen plus performant.

C'est là que tu vois qu'un système 'générique' a quand même des inconvénients, ça donne des requêtes à rallonges pas très lisibles, avec un minimum de 20 jointures pour récupérer une info pourtant très basique

gizmo

Question con, pourquoi tu peux pas utiliser un order by?

Beegee

je pense pas que ça change grand chose question perfs, à mon avis un ORDER BY (puis un ROWNUM = 1 pour prendre la première ligne), ou un MAX, ça revient au même ...

Arjuna

Aircraft Ident.: F-MBSD

Beegee a écrit :

Le débat du générique n'a rien à voir dans ce problème. Que la base soit dédiée ou non, je ne vois pas comment un modèle pourrait stocker cette info. Deplus, dans la requête que j'ai posté, il n'y a pas une seule ligne superflue... "codsoc" c'est la société concernée par la commande, "achvte" indique une commande de vente, "typcde" un évènement de type commande, "typtie" un tiers de type client, quand à "codpro", c'est simplement le code du produit.

Tu peux t'amuser à splitter en 25 tables pour gérer la même chose, tu auras toujours le problème posé en titre du topic, au mieu ta requête sera plus courte, mais aura la même structure (ce qui implique un temps d'éxécution rigoureusement identique, volume des données à part)

Arjuna

Aircraft Ident.: F-MBSD

gizmo a écrit :

Question con, pourquoi tu peux pas utiliser un order by?

Pour deux raisons.
La première, c'est que si la requête retourne 20 000 lignes, ça va tout faire rammer pour rien.
La seconde, bien plus importante, c'est que si je veux faire un état indiquant "Le prix de vente de tout un ensemble de produits pour un ensemble de clients donnés", je ne pourrai pas me passer d'une fonction de regroupement pour retourner exactement les lignes que je veux, qui ne sont plus au nombre d'une seule, et qu'aucun critère de tri ne permet de rammener dans le bon ordre.

Message édité par Arjuna le 29-10-2004 à 02:11:09

Beegee

Arjuna a écrit :

je parlais uniquement de la taille de la requête, et de son esthétisme
(suite à ton 1er post, où tu dis que tout devient bordélique )

Sinon franchement, je ne vois pas d'autre moyen que le max que tu fais ...

Quand tu voudras faire ça pour plusieurs clients, il te faudra faire quelquechose du genre :

Code :

SELECT t1.*
FROM table1 t1,
(SELECT max(my_date) as maxdate, key
FROM t2
GROUP BY key) maxresults
WHERE t1.key = maxresults.key
AND t1.my_date = maxresults.maxdate;

Après faut voir comment tourne cette requête, y a moyen de tweaker ça pour que ça tourne correctement.

Message édité par Beegee le 29-10-2004 à 08:24:36

Arjuna

Aircraft Ident.: F-MBSD

Nan, en fait, pour les "tous les clients et tous les produits", la requête complète c'est ça

select 0, typprx, typtie, sigtie, codpro, prxvdu, qtemin, coddev, etbcod, codbar, sigrep, codeop, pricestatus
from (
-- Corporate and Standard Prices:
-- Select * from tas
-- if codreg = ' ' then 'Corporate' else 'Standard'
select decode(tas.codreg, ' ', '10.Corporate', '09.Standard') typprx, 'CLI' typtie, ' ' sigtie, tas.codpro, tas.prxstd1 prxvdu, 1 qtemin, tas.coddev, tas.codreg etbcod, ' ' codbar, ' ' sigrep, ' ' codeop, '1' pricestatus
from tas, pro
where pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and tas.codsoc = 0
and tas.achvte = 'V'
and tas.codpro = pro.codpro
and to_char(sysdate, 'YYYYMMDD') between tas.datapp1 and decode(tas.datval1, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp2 and decode(tas.datval2, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval2) and tas.datapp2 > tas.datapp1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp3 and decode(tas.datval3, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval3) and tas.datapp3 > tas.datapp1)
union all
-- Quantity Level Prices
-- Select * from tac where codbar <> ' '
select decode(tac.codreg, ' ', '08.Quantity GIE', '07.Quantity REG') typprx, 'CLI' typtie, ' ' sigtie, tac.codpro, tac.prxcol1 prxvdu, tac.qtemin, tac.coddev, tac.codreg etbcod, tac.codbar, ' ' sigrep, ' ' codeop, '1' pricestatus
from tac, tas, pro
where pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and tas.codsoc = 0
and tas.achvte = 'V'
and tas.codpro = pro.codpro
and to_char(sysdate, 'YYYYMMDD') between tas.datapp1 and decode(tas.datval1, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp2 and decode(tas.datval2, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval2) and tas.datapp2 > tas.datapp1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp3 and decode(tas.datval3, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval3) and tas.datapp3 > tas.datapp1)
and tac.codsoc = 0
and tac.achvte = 'V'
and tac.codpro = tas.codpro
and tac.codreg = tas.codreg
and tac.codbar = ' '
union all
-- Scale Prices
-- Select * from tac where codbar = ' '
select decode(tac.codreg, ' ', '06.Scale GIE', '05.Scale REG') typprx, 'CLI' typtie, ' ' sigtie, tac.codpro, tac.prxcol1 prxvdu, tac.qtemin, tac.coddev, tac.codreg etbcod, tac.codbar, tbl.lib2 sigrep, ' ' codeop, tbl.lir pricestatus
from tbl, tac, tas, pro
where pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and tas.codsoc = 0
and tas.achvte = 'V'
and tas.codpro = pro.codpro
and to_char(sysdate, 'YYYYMMDD') between tas.datapp1 and decode(tas.datval1, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp2 and decode(tas.datval2, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval2) and tas.datapp2 > tas.datapp1)
and not (to_char(sysdate, 'YYYYMMDD') between tas.datapp3 and decode(tas.datval3, ' ', to_char(sysdate, 'YYYYMMDD'), tas.datval3) and tas.datapp3 > tas.datapp1)
and tac.codsoc = 0
and tac.achvte = 'V'
and tac.codpro = tas.codpro
and tac.codreg = tas.codreg
and tac.codbar <> ' '
and tbl.codsoc = 0
and tbl.codtbl = '310'
and tbl.lir = tac.codbar
and tbl.lib3 = decode(tac.codreg, ' ', 'GIE', tac.codreg)
and tbl.num1 in (1, 2)
union all
-- Specific Price CLI : tqui = 1 / tqoi = 501
select '04.Specific Price CLI' typprx, tie.typtie, tie.sigtie, pro.codpro, cts.prxnet prxvdu, cts.qtemin, cts.coddev, cts.etbcod, tie.codbar, tie.sigrep, ' ' codeop, '1' pricestatus
from cts, pro, tie
where tie.codsoc = 0
and tie.typtie = 'CLI'
and tie.codett = 'CUS'
and pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and cts.codsoc = 0
and cts.achvte = 'V'
and cts.typcot = 'LIG'
and cts.tqui = '1'
and cts.tqoi = '501'
and cts.codcre = 'PRN'
and to_char(sysdate, 'YYYYMMDD') between cts.datapp and cts.datval
and cts.clequi = tie.sigtie
and cts.cleqoi = pro.codpro
and cts.datapp = (select max(cts2.datapp)
from cts cts2
where cts2.codsoc = cts.codsoc
and cts2.achvte = cts.achvte
and cts2.typcot = cts.typcot
and cts2.tqui = cts.tqui
and cts2.tqoi = cts.tqoi
and cts2.codcre = cts.codcre
and to_char(sysdate, 'YYYYMMDD') between cts2.datapp and cts2.datval
and cts2.clequi = cts.clequi
and cts2.cleqoi = cts.cleqoi)
union all
-- Specific Price GRC : tqui = 3 / tqoi = 501
select '03.Specific Price GRC' typprx, tie.typtie, tie.sigtie, pro.codpro, cts.prxnet prxvdu, cts.qtemin, cts.coddev, cts.etbcod, tie.codbar, tie.sigrep, ' ' codeop, '1' pricestatus
from cts, pro, tie
where tie.codsoc = 0
and tie.typtie = 'GRC'
and tie.codett = 'CUS'
and pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and cts.codsoc = 0
and cts.achvte = 'V'
and cts.typcot = 'LIG'
and cts.tqui = '3'
and cts.tqoi = '501'
and cts.codcre = 'PRN'
and to_char(sysdate, 'YYYYMMDD') between cts.datapp and cts.datval
and cts.clequi = tie.sigtie
and cts.cleqoi = pro.codpro
and cts.datapp = (select max(cts2.datapp)
from cts cts2
where cts2.codsoc = cts.codsoc
and cts2.achvte = cts.achvte
and cts2.typcot = cts.typcot
and cts2.tqui = cts.tqui
and cts2.tqoi = cts.tqoi
and cts2.codcre = cts.codcre
and to_char(sysdate, 'YYYYMMDD') between cts2.datapp and cts2.datval
and cts2.clequi = cts.clequi
and cts2.cleqoi = cts.cleqoi)
union all
-- Specific Price GRC : tqui = 4 / tqoi = 501
-- And cts.clequi = cnx.numcnt
select '02.Agreement' typprx, tie.typtie, tie.sigtie, pro.codpro, cts.prxnet prxvdu, cts.qtemin qte, cts.coddev, cts.etbcod, tie.codbar, tie.sigrep, ' ' codeop, '1' pricestatus
from cts, pro, cnt, tie
where tie.codsoc = 0
and tie.typtie in ('CLI', 'GRC')
and tie.codett = 'CUS'
and cnt.codsoc = 0
and cnt.achvte = 'V'
and cnt.typeve = 'CNT'
and to_char(sysdate, 'YYYYMMDD') between cnt.datapp and cnt.datval
and cnt.typtie = tie.typtie
and cnt.sigtie = tie.sigtie
and pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and cts.codsoc = 0
and cts.achvte = 'V'
and cts.typcot = 'LIG'
and cts.tqui = '4'
and cts.tqoi = '501'
and cts.codcre = 'PRN'
and to_char(sysdate, 'YYYYMMDD') between cts.datapp and cts.datval
and cts.clequi = cnt.numcnt
and cts.cleqoi = pro.codpro
union all
-- Promotion : tqui = 85 / tqoi = 501
-- Need to match codeop each time the user look for prices (need to be entered mannualy)
select '01.Promotion' typprx, tie.typtie, tie.sigtie, pro.codpro, cts.prxnet prxvdu, cts.qtemin, cts.coddev, tie.etbcod, tie.codbar, tie.sigrep, ope.codeop, '1' pricestatus
from cts, pro, tie, opc, ope
where ope.codsoc = 0
and to_char(sysdate, 'YYYYMMDD') between ope.datdeb and ope.datfin
and opc.codsoc = 0
and opc.codeop = ope.codeop
and opc.typtie in ('CLI', 'GRC')
and tie.codsoc = 0
and tie.typtie = opc.typtie
and tie.sigtie = opc.sigtie
and tie.codett = 'CUS'
and pro.codsoc = 0
and pro.codblocage in ('11','11B', 'INV', '15')
and not (pro.fampro = '5BSERV' and pro.sfapro = 'CHARGE')
and upper(substr(pro.codpro, 3, 1)) != 'X'
--and zod.codsoc = 0
--and zod.typzod = 'PRO'
--and zod.numzod = '109'
--and zod.valzod != ' '
--and zod.clezod = pro.codpro
and cts.codsoc = 0
and cts.achvte = 'V'
and cts.typcot = 'LIG'
and cts.tqui = '85'
and cts.tqoi = '501'
and cts.codcre = 'PRN'
and cts.clequi = ope.codeop
and cts.cleqoi = pro.codpro
) tmp

:sleep:

Bon, c'est rapide : temps d'éxécution = 594 msec pour 390 000 lignes sur le serveur de production en plein journée (charge intense du serveur, d'autant plus qu'on est en plein closing, avec 1 million de batchs qui tournent dans tous les sens pour consolider les données financières )

Mais bon, 0.6 secondes par ci, + 0.6 secondes par là, on arrive rapidement à faire des goulots d'étrangelement, et du coup j'aimerais pouvoir améliorer encore un peu la chose...

Message édité par Arjuna le 29-10-2004 à 12:05:28

Publicité

Arjuna

Aircraft Ident.: F-MBSD

Tiens, je suis en train de voir qu'en fait, j'ai même pas le dernier prix d'achat dans la liste des prix :heink:

Merde, le site web va afficher n'importe quoi comme prix :sweat:

Beegee

Le plus simple, c'est quand même de raisonner comme je l'ai fait, sur un cas plus simple à écrire (mais équivalent en terme de perfs).

Et de toutes façons, faut pas t'attendre à pouvoir le faire en moins d'un dixième de seconde je pense, c'est logique d'avoir un bon paquet de calculs à faire dès qu'on a besoin de récupérer des infos dépendant de maxima.
En interne, ça implique tout un tas de comparaisons

(ou alors faut le prévoir en amont, en plombant légèrement les perfs des programmes qui remplissent les données, pour par exemple maintenir une table contenant pour chaque type de produit et chaque client, la référence du dernier produit acheté ...).

Arjuna

Aircraft Ident.: F-MBSD

Je suis pas d'accord avec le premier point.
Parceque si en effet, ça semble plus simple au premier abords, avoir un nombre réduit de tables, dont on maîtrise parfaitement le fonctionnement est un atout énorme : un simple copy/paste avec changement d'une ou deux constantes permet de récupérer indépendemment les commandes d'achat, de vente, les livraisons d'achat et de vente, les factures d'achat et de vente etc.
Au final, ça accélère considérablement les développement, et surtout, ça évite de se casser le nez systématiquement avec des tables n'ayant pas la même structure/noms de champs alors qu'elles contiennent une info similaire (quelle est la différence entre une commande d'achat et une commande de vente ? mise à part le sens dans lequel elles fonctionne, il n'y en a aucune... alors risquer d'avoir des infos stockées de façon différentes d'un type de commande à l'autre est une énorme gène au développement, et surtout à la maintenance.

Pour ce qui est du dernier point, je suis plus ou moins d'accord : ce serait un monde idéal. Seulement, il y a trop d'information calculées qu'on pourrait ainsi vouloir stocker, et ça deviendrait rapidement un joyeux bordel. Tout au mieu, on peut faire des vues ou des fonctions PL/SQL qui permettent de retrouver ces infos sans se taper des requêtes de fou. Le stockage dans une table est à la fois très risqué (on oublie toujours un trigger dans un coin) et trop volumineux (on a vite fait de vouloir stocker l'âge du dentiste du livreur de pizza qui a donné l'idée à notre client de passer sa commande chez nous...)

FORUM HardWare.fr

Programmation

SQL/NoSQL

Recherche la valeur de A quand la valeur B est maximale selon C

Sujets relatifs
PHP upload_max_filesize => modifier valeur par default	[batch] Recherche de fichier
Valeur de retour d'une fonction si erreur	[script IRC] recherche script de reponse auto sur mot clef
Recherche sur un code postal avec SUBSTRING	Modéliser un assistant de recherche ?
recherche de script	[VB]Afficher une recherche SQL dans une ListView
{Script de création d'installation skinable} je recherche...
Plus de sujets relatifs à : Recherche la valeur de A quand la valeur B est maximale selon C

Page générée en 0.057 secondes