Filtrer sur une clause utilisant over (partition by)

Bonjour,

Je souhaite faire quelque chose d'assez simple, mais bon je bloque. Alors je me suis dis que quelqu'un ici serait surement me renseigner!

j'ai une table contenant entre autre les colonnes suivantes : IdContrat - IdPersonne - IdProduit

Je souhaite filtrer sur une clause count(IdProduit) over (parition by IdPersonne ) pour connaitre, par exemple, les personnes qui ont plus de 2 produits. HAVING ne fonctionne pas :?

Faut-il forcement passer par une sous-requête ?

Exemple :

Code:

---

1 2	select IdContrat,IdPersonne,IdProduit, count (distinct IdProduit) over (partition by IdPersonne) from DATA

---

r
Merci pour votre aide.

Manu

Il faut en effet passer par une sous-requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH src AS ( SELECT IdContrat , IdPersonne , IdProduit , COUNT(DISTINCT IdProduit) OVER (PARTITION BY IdPersonne) AS cpt FROM DATA ) SELECT IdContrat , IdPersonne , IdProduit FROM src WHERE cpt > 2 ;

---

Vérifie laquelle est la plus performante entre cette requête et la suivante, réponse classique à ce problème :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SELECT IdContrat , IdPersonne , IdProduit FROM DATA src WHERE EXISTS ( SELECT NULL FROM DATA cpt WHERE cpt.IdPersonne = src.IdPersonne HAVING COUNT(DISTINCT cpt.IdProduit) > 2 ) ;

---

Citation:

---

Envoyé par Manufr

...
HAVING ne fonctionne pas :?
...

---

Having est un opérateur qui filtre les groups obtenues par agrégation! Quand vous utilisez une fonction analytiques c'est parce que vous cherchez à obtenir le détail et l'agrégation ! C'est pour cela que Having ne marché pas, il n'y pas le group, juste le détail et (au moins) une colonne d'agrégation.

Super merci!

Merci encore pour ton aide :)

Citation:

---

Envoyé par mnitu

Having est un opérateur qui filtre les groups obtenues par agrégation! Quand vous utilisez une fonction analytiques c'est parce que vous cherchez à obtenir le détail et l'agrégation ! C'est pour cela que Having ne marché pas, il n'y pas le group, juste le détail et (au moins) une colonne d'agrégation.

---

Oui mais je suis du genre idéaliste, je pensais qu'il y aurait une fonction toute simple pour filtrer sur le détail !

:mrgreen:

Citation:

---

Envoyé par Manufr

Oui mais je suis du genre idéaliste, je pensais qu'il y aurait une fonction toute simple pour filtrer sur le détail !

:mrgreen:

---

Mais bien sur qu'il en a "une", c'est celle que vous avez fini par utiliser:

Code:

---

1 2	WHERE cpt > 2

---

Citation:

---

Envoyé par mnitu

Having est un opérateur qui filtre les groups obtenues par agrégation! Quand vous utilisez une fonction analytiques c'est parce que vous cherchez à obtenir le détail et l'agrégation ! C'est pour cela que Having ne marché pas, il n'y pas le group, juste le détail et (au moins) une colonne d'agrégation.

---

Je dirais plutôt que HAVING permet de filtrer les groupes selon une condition reposant sur une agrégation. Car l'agrégation (par exemple SUM(salaire)) ne produit pas de groupes ! Elle s'opère au sein des groupes déjà constitués par l'étape de regroupement définie par le GROUP BY; elle consiste à renvoyer une seule valeur calculée à partir de l'ensemble des valeurs figurant dans le groupe.

Code:

---

1 2 3 4 5 6 7 8 9

select deptno, count(*) as nombre_employes, sum(sal) as cumul_salaire from scott.emp having count(*) >3 group by deptno;   DEPTNO NOMBRE_EMPLOYES CUMUL_SALAIRE ---------- --------------- ------------- 30 6 9400 20 5 10875

---

Mais pour revenir à la question, le fait qu'il faille une sous-requête est lié à l'ordre d'exécution des différentes clauses du SELECT.
La fonction analytique est calculée après le WHERE, et avant l'ORDER BY éventuel. Par conséquent, si on veut opérer un filtrage sur le résultat de la fonction analytique, il faut une sous-requête.

D'après la doc :

Citation:

---

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

---

Si vous utilisiez Teradata, vous auriez droit à la clause QUALIFY, qui est l'équivalent de HAVING mais avec les fonctions de regroupement analytiques.
Elle s'applique après WHERE, GROUP BY et HAVING, donc une fois que le reste de la requête est exécuté.

Citation:

---

Envoyé par al1_24

Si vous utilisiez Teradata, vous auriez droit à la clause QUALIFY, qui est l'équivalent de HAVING mais avec les fonctions de regroupement analytiques.
Elle s'applique après WHERE, GROUP BY et HAVING, donc une fois que le reste de la requête est exécuté.

---

Je suppose qu'il faut surtout comprendre que cette clause s'applique après le SELECT, lequel comporte les fonctions analytiques ?

Merci pour toutes vos explications :P

Citation:

---

Envoyé par Pomalaix

Je suppose qu'il faut surtout comprendre que cette clause s'applique après le SELECT, lequel comporte les fonctions analytiques ?

---

Comme avec HAVING, la fonction analytique (de regroupement pour HAVING) sur laquelle est appliquée la restriction n'a pas besoin d'être spécifiée dans le SELECT.
Exemple, les trois dernières commandes de chaque client :

Code:

---

1 2 3 4 5 6

select client , dt_commande from commande qualify row_number() over (partition by client order by dt_commande desc) <= 3 order by client , dt_commande

---

Merci pour l'exemple, c'est très clair !

Citation:

---

Envoyé par Pomalaix

Je dirais plutôt que HAVING permet de filtrer les groupes selon une condition reposant sur une agrégation. Car l'agrégation (par exemple SUM(salaire)) ne produit pas de groupes ! Elle s'opère au sein des groupes déjà constitués par l'étape de regroupement définie par le GROUP BY; elle consiste à renvoyer une seule valeur calculée à partir de l'ensemble des valeurs figurant dans le groupe.
...

---

Permettez moi de ne pas être de accord avec vous.

Citation:

---

Envoyé par mnitu

Permettez moi de ne pas être de accord avec vous.

---

On peut en savoir plus ?

Les fonctions d'agrégation impliquent la notion de groupe.
Il est possible d'utiliser Having sans avoir une condition reposant sur une fonction d'agrégation (quoi que cela est très probablement juste une autre exemple de mauvaise écriture).

Citation:

---

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view

---

Et alors ?
Je ne vois pas où il y a un désaccord avec ce que j'ai dit.

Tu disais :

Citation:

---

Having est un opérateur qui filtre les groups obtenues par agrégation!

---

Je maintiens que l'agrégation ne produit pas de groupes, mais s'applique à l'intérieur des groupes déjà constitués.

La formulation est importante, et je me suis donc permis de la relever, car je constate fréquemment que les notions de regroupement et d'agrégation sont allègrement confondues, et il faut éviter d'entretenir l’ambiguïté.

Qu'on puisse avoir une agrégation sans GROUP BY, certes ; qu'on puisse parfois utiliser à mauvais escient un HAVING à la place d'un WHERE, certes.
Mais je le répète, je ne vois pas matière à désaccord avec ce que j'ai dit.

Et je ne vois qu'une polémique stérile qui s'éloigne fortement du but de mon intervention initiale: différence entre fonctions d'agrégations et analytiques.
Quels sont les requêtes écrites pour produire des groupes sans agrégation ? Et à quoi elles servent ?
Et c'est quoi "l'ambiguïté entre les notions d'agrégation et de regroupement" ?
Ne sommes-nous tombé dans le débat vieux débat "que est-ce qu'il y avait au départ la poule ou l'œuf ?".