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 :

 
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 :

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 ?

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

SELECT id_client, SUM(vetement='jean') AS c1, SUM(vetement='pull') AS c2, 
MAX(IF((vetement='jean') OR (vetement='pull'), date_achat, NULL)) AS c3 
FROM achats 
GROUP BY id_client 
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...

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

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

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

C'est MySQL. Il est très souple avec la norme SQL. Dans certains cas, c'est bien pratique
 
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.

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:
 

Merci pour la confirmation.

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 . 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
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)

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)
 

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

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.

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
 
Pour info, en combien de temps se fait la requête actuellement ?

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.

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

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.

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 ?

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

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 ) 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...)

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