Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
2520 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  sélectionner les bonnes colonnes après une requete

 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

sélectionner les bonnes colonnes après une requete

n°2307152
PierreC
Posté le 31-10-2017 à 10:28:30  profilanswer
 

Hello à tous.
 
  Je rentre immédiatement dans le vif du sujet pour expliquer mon besoin.
 
  Exemple de données d'une table achats, avec 3 colonnes : id_client, vetement, date_achat (il n'y a pas de cle primaire).
  Soit ces données :

pierre  ; jean  ; 10/10/2010
pierre  ; pull  ; 10/12/2010
pierre  ; veste ; 10/01/2008
paul    ; pull  ; 12/12/2013
paul    ; jean  ; 12/04/2004
paul    ; veste ; 10/10/2015
jacques ; pull  ; 12/06/2004
jacques ; veste ; 10/07/2007


 
Je souhaite tout d'abord sélectionner (avec UN SEUL SELECT !) les personnes qui ont acheté des jean et des pull, la requête est :

SELECT id_client,sum(vetement='jean') as c1, sum(vetement='pull') as c2 FROM achats GROUP BY id_client HAVING (c1*c2)>0

Ca peut sembler complexe mais cela à l'avantage de sélectionner ce que je veux avec un seul SELECT, très important car mon volume de données est très important.  
 
A présent mon problème est de sélectionner une des dates d'achat correspondant à une des lignes qui à fait que la personne à été sélectionné. Possible de faire une 2eme requête.
 
Pierre et Paul ont acheté des jean et des pull, et je dois pour pierre obtenir soit la date du 10/10/2010, soit 10/12/2010 et pour paul soit 12/12/2013, soit 12/04/2004
 
des idées ?


Message édité par PierreC le 31-10-2017 à 11:47:12

---------------
Du tofu en Alsace : www.tofuhong.com
mood
Publicité
Posté le 31-10-2017 à 10:28:30  profilanswer
 

n°2307159
rufo
Pas me confondre avec Lycos!
Posté le 31-10-2017 à 13:47:06  profilanswer
 

C'est bon, je pense que je l'ai ta requête :

Code :
  1. SELECT id_client, SUM(vetement='jean') AS c1, SUM(vetement='pull') AS c2,
  2. MAX(IF((vetement='jean') OR (vetement='pull'), date_achat, NULL)) AS c3
  3. FROM achats
  4. GROUP BY id_client
  5. HAVING c1*c2 > 0


 
Edit : y'a peut-être moyen de virer le OR dans mon test pour encore accélérer la requête...


Message édité par rufo le 31-10-2017 à 13:48:43

---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
n°2307162
PierreC
Posté le 31-10-2017 à 14:17:35  profilanswer
 

humm, intéressant.  :)  
 
Faut que j'analyse avec des requêtes plus complexe, car je peux avoir plusieurs tables reliées en jointures avec des conditions and / or dans tout les sens


Message édité par PierreC le 31-10-2017 à 15:06:18

---------------
Du tofu en Alsace : www.tofuhong.com
n°2307204
TotalRecal​l
Posté le 02-11-2017 à 10:12:28  profilanswer
 

Par curiosité c'est quel SGBDR qui propose d'utiliser directement des tests d'égalité dans un opérateur d'agrégat et qui permet dans le having de mettre les alias de colonne donnés dans le select au lieu de devoir remettre toute l'expression ? C'est du mySQL ?

 

Avec T-SQL par exemple, exactement la requête du premier post donnerait un truc du genre :
SELECT id_client,
sum(case when vetement='jean' then 1 else 0 end) as c1,
sum(case when vetement='pull' then 1 else 0 end) as c2
FROM achats GROUP BY id_client
HAVING (sum(case when vetement='jean' then 1 else 0 end)*sum(case when vetement='pull' then 1 else 0 end))>0

 

Et je ne parle même pas de la tambouille à faire pour le MAX de rufo :o

 

edit : ou à partir de la version 2012 on peut utiliser IIF au lieu de CASE mais ça reste lourdingue.


Message édité par TotalRecall le 02-11-2017 à 10:18:39

---------------
Réalisation amplis classe D / T      Topic .Net - C# @ Prog
n°2307205
rufo
Pas me confondre avec Lycos!
Posté le 02-11-2017 à 10:18:59  profilanswer
 

C'est MySQL. Il est très souple avec la norme SQL. Dans certains cas, c'est bien pratique :D
 
Edit : faut reconnaître que pouvoir utiliser les alias dans le HAVING, c'est quand même pratique car ça allège la longueur de la requête et la rend plus facile à comprendre.

Message cité 1 fois
Message édité par rufo le 02-11-2017 à 10:20:11

---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
n°2307206
PierreC
Posté le 02-11-2017 à 10:20:50  profilanswer
 

mysql le supporte très bien car mysql est crade comme SGBD ! :)
 
Mysql fait de la conversion de type de données à la volé, donc pour faire une sum de booléen il convertit les booléen en numérique.
 
C'est pratique, mais pas vraiment une bonne pratique
 
Postgres et ColumnStore sont comme T-SQL, il faut coder la conversion. Je le fait plutot avec des IF:
 

sum(if (sexe='M',1,0))


---------------
Du tofu en Alsace : www.tofuhong.com
n°2307208
TotalRecal​l
Posté le 02-11-2017 à 10:39:00  profilanswer
 

Merci pour la confirmation.

 
rufo a écrit :


Edit : faut reconnaître que pouvoir utiliser les alias dans le HAVING, c'est quand même pratique car ça allège la longueur de la requête et la rend plus facile à comprendre.


En fait c'est tellement plus simple, court, pratique et logique d'utiliser l'alias plutôt que de tout réécrire que je n'ai jamais compris pourquoi ça n'est pas pareil en T-SQL par exemple [:spamafote]. Après il y a peut être une syntaxe secrète que je ne connais pas mais j'en doute.

 

PierreC > oui c'est moche d'utiliser un booléen comme un entier dans un sum, c'est clair. Mais c'est concis :o
Le IF, c'est clairement le plus propre tout en étant lisible. le CASE WHEN c'est trop lourd. (Donc IIF pour SQL Server maintenant que c'est là, IF ça ne marche pas dans un SELECT, juste dans un batch)


Message édité par TotalRecall le 02-11-2017 à 10:40:06

---------------
Réalisation amplis classe D / T      Topic .Net - C# @ Prog
n°2307211
bosstime
H1N1 ready
Posté le 02-11-2017 à 17:13:30  profilanswer
 

:hello: PierreC,
 
Juste par curiosité, quelle est la volumétrie de ta table (en lignes et taille totale) et les indexes ? Et quelle est le temps de réponse souhaité ?
Pour quelle raison pas de PK ?
 
Sinon, en sql vanilla, on peut voir aussi avec une auto-jointure, si 1 des articles est très peu acheté (même si j'en doute dans ton cas)
 

Code :
  1. SELECT
  2. aj.id_client, aj.vetement, aj.date
  3. FROM
  4. achat aj
  5. INNER JOIN achat ap on aj.id_client=ap.id_client AND ap.vetement='pull'
  6. WHERE
  7. aj.vetement = 'jean'


L'optimiseur partira de la table qui contient peu d'article (sous réserve que les stats soient à jour :D) et la jointure ne devrait être pas trop couteuse... A comparer avec un group by, on a parfois des surprises...

n°2307219
PierreC
Posté le 02-11-2017 à 17:55:52  profilanswer
 

Le nombre de lignes des tables peuvent variées de 5 millions à 300 millions. Je peux aussi avoir des jointures (souvent 1 ou 2, mais ca peut être 10), donc une solution ajoutant encore des jointures est exclus.
 
Pas de clef primaire pour 2 raisons : on n'a a jamais eu besoin du coté métier ou développement (mais on pourrais imaginer en ajouter), et columnstore ne gère pas les PK.


---------------
Du tofu en Alsace : www.tofuhong.com
n°2307226
bosstime
H1N1 ready
Posté le 02-11-2017 à 20:13:13  profilanswer
 

Les jointures ne sont pas forcément à proscrire, une requête avec 20 jointures sur des tables de plusieurs 100aines de millions de lignes peut très bien donner des temps de réponses acceptables. Il suffit qu'une des jointures soit suffisamment discriminante. Mais tu es le mieux placé pour savoir quelle sont les autres tables ;)
 
Le mieux est de tester les différentes requêtes et voir via l'explain ce qui marche le mieux. Idéalement tester avec le jeu de données en prod car suivant la volumétrie des tables, l'optimiseur peut prendre différents chemins. 1 requête sur une base de dev peut souvent être plus rapide qu'une autre et l'inverse se produit une fois en prod :D
 
Pour info, en combien de temps se fait la requête actuellement ?

mood
Publicité
Posté le 02-11-2017 à 20:13:13  profilanswer
 

n°2307228
PierreC
Posté le 02-11-2017 à 21:09:45  profilanswer
 

On développe un outil de comptage où l'utilisateur choisit sur ce quoi il veut compter (homme en Alsace, qui ont achetés des jeans et des pull par exemple) . On ne connaît donc pas les requêtes qui seront exécutées,  et parfois en effet on aura que des critères non discriminant.
 
Les requêtes durent entre 0.1 seconde et 60 secondes.
 
D'ici 2 ans le volume de données peut être multiplié par 10, je dois donc faire attention dès maintenant.


---------------
Du tofu en Alsace : www.tofuhong.com
n°2307229
TotalRecal​l
Posté le 02-11-2017 à 22:58:00  profilanswer
 

Ca ressemble furieusement à du BI ton truc, vous savez qu'il y a des outils tous faits dont certains gratos ?


---------------
Réalisation amplis classe D / T      Topic .Net - C# @ Prog
n°2307231
PierreC
Posté le 03-11-2017 à 00:25:12  profilanswer
 

Oui c'est du BI, en effet.
 
Mon client vend des outils sur mesure, le moteur de comptage n'est qu'une brique autour d'autre brique.


---------------
Du tofu en Alsace : www.tofuhong.com
n°2307238
bosstime
H1N1 ready
Posté le 03-11-2017 à 09:01:31  profilanswer
 

Dans le cas du BI, effectivement, plus tu auras de critères de recherche (donc potentiellement plus de jointures sur les autres tables), plus normalement la requête ira vite car restreindra l'intervalle de recherche dans ta plus grosse table.
 
J'imagine qu'actuellement lorsque ta requête répond quasi instantanément c'est parce que tu restreins par la date ou le client (ou autre critère) ? Sinon quel profil de requête fait que ca peut aller jusqu'à la minute ?
 
Mais ca n'empêchera pas d'avoir des temps de réponse assez longs si tu recherches sur peu de critères avec un sgdb relationnel et une grosse volumétrie (ceci dit, je n'ai pas encore testé columnstore). Tu as le choix de la techno ou pas ? :D

n°2307239
PierreC
Posté le 03-11-2017 à 09:16:51  profilanswer
 

dans le cas de jointure où les critères sont reliés OR est par exemple un cas compliqué, car le WHERE est très peu discriminant.
 
Le plus complexe c'est le mélange de ce qu'on appel les variables liées et non liées. Les variables liées se requête avec un where (à acheter des jeans de plus de 100 € ) , les non liées se font en having (comme la requete ci dessus, des personnes ayant acheter des jeans et des pull)
 
l'utilisateur peut avoir un ensemble de variables liées, non liées, avec des AND / OR . Avec cela une seule requête devient impossible, j'ai plusieurs requête générées et reliées avec des UNION


---------------
Du tofu en Alsace : www.tofuhong.com
n°2307243
bosstime
H1N1 ready
Posté le 03-11-2017 à 10:37:19  profilanswer
 

Perso, je ne suis pas très fan des UNION mais c'est certain que parfois, suivant la complexité de la demande, on n'a pas le choix.  
 
Enfin, si il y a toujours plusieurs façon de faire mais toutes ne donnent pas les mêmes performances et ca varie justement suivant le profil de la requête.
 
Typiquement, même si je n'ai pas une vue complète du schéma, ta requête sur les jeans de + de 100€ (normalement critère hyper discriminant, sauf si ton magasin est spécialisé dans les jeans de luxe :D ) ET des pulls ira je pense plus vite avec une auto-jointure qu'avec un GROUP BY + HAVING + SUM dans les selects.
 
Difficile de trouver une requête standard qui donne des perfs correctes quelque soit la recherche demandée (globale, discriminante, etc...) :/

n°2307244
PierreC
Posté le 03-11-2017 à 11:05:13  profilanswer
 

Pour l'instant (la première mise en prod date de septembre dernier) on est sur une requête standard fonctionnant dans 100% des cas. Et donc pas optimisé pour les cas particulier.
 
Ensuite il est prévu d'analyser les requêtes lentes, et les requêtes simple qui sont souvent exécutées, et créer des exceptions dans le code pour rendre ces requêtes plus rapides


---------------
Du tofu en Alsace : www.tofuhong.com

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  sélectionner les bonnes colonnes après une requete

 

Sujets relatifs
Lanceur de requête SQL pour gain de temps en recette[RÉSOLU] Garder colonnes H et I masquées pendant Affichage de G et J
Problème de requête sous AccessProblème requete MSSQLSERVER_4104
ACCESS 2010 – Requête de sélection à partir d’un formulaireprocesseur + requete SQL
VBA Excel Requète WEBexécuter requete avec select row
ajout d'une valeur dans résultat d'une requête d'une autre tableHabiller un tableau résultant d'une requête SQL
Plus de sujets relatifs à : sélectionner les bonnes colonnes après une requete


Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR