Discussion :

[Manufr]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

r
Merci pour votre aide.

Manu

[al1_24]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[mnitu]

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

[Manufr]

Super merci!

Merci encore pour ton aide

[Manufr]

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 !

[mnitu]

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
WHERE   cpt > 2

[Pomalaix]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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.

[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é.

[Pomalaix]

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 ?

[Manufr]

Merci pour toutes vos explications

[al1_24]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Pomalaix]

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

[mnitu]

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.

[Pomalaix]

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

On peut en savoir plus ?

[mnitu]

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

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

[Pomalaix]

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

Tu disais :

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.

[mnitu]

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 ?".