IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Filtrer sur une clause utilisant over (partition by) [11g]


Sujet :

SQL Oracle

  1. #1
    Membre régulier
    Homme Profil pro
    Développeur décisionnel
    Inscrit en
    Mars 2013
    Messages
    82
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur décisionnel
    Secteur : Santé

    Informations forums :
    Inscription : Mars 2013
    Messages : 82
    Points : 102
    Points
    102
    Par défaut 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 : 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

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    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
            )
    ;
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par Manufr Voir le message
    ...
    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.

  4. #4
    Membre régulier
    Homme Profil pro
    Développeur décisionnel
    Inscrit en
    Mars 2013
    Messages
    82
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur décisionnel
    Secteur : Santé

    Informations forums :
    Inscription : Mars 2013
    Messages : 82
    Points : 102
    Points
    102
    Par défaut
    Super merci!

    Merci encore pour ton aide

  5. #5
    Membre régulier
    Homme Profil pro
    Développeur décisionnel
    Inscrit en
    Mars 2013
    Messages
    82
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur décisionnel
    Secteur : Santé

    Informations forums :
    Inscription : Mars 2013
    Messages : 82
    Points : 102
    Points
    102
    Par défaut
    Citation Envoyé par mnitu Voir le message
    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 !


  6. #6
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par Manufr Voir le message
    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:

  7. #7
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    Citation Envoyé par mnitu Voir le message
    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.
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  8. #8
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    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é.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  9. #9
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    Citation Envoyé par al1_24 Voir le message
    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 ?
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  10. #10
    Membre régulier
    Homme Profil pro
    Développeur décisionnel
    Inscrit en
    Mars 2013
    Messages
    82
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur décisionnel
    Secteur : Santé

    Informations forums :
    Inscription : Mars 2013
    Messages : 82
    Points : 102
    Points
    102
    Par défaut
    Merci pour toutes vos explications

  11. #11
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    Citation Envoyé par Pomalaix Voir le message
    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
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  12. #12
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    Merci pour l'exemple, c'est très clair !
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  13. #13
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par Pomalaix Voir le message
    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.

  14. #14
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Permettez moi de ne pas être de accord avec vous.
    On peut en savoir plus ?
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  15. #15
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    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

  16. #16
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    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.
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  17. #17
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    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 ?".

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. projection de points sur une structure utilisant une image
    Par speedy_g dans le forum Traitement d'images
    Réponses: 12
    Dernier message: 17/09/2007, 11h33
  2. Problème de grant sur une vue utilisant un db_link
    Par squallJ dans le forum Administration
    Réponses: 2
    Dernier message: 04/09/2007, 12h33
  3. Creation Procedure : erreur sur une clause LIMIT
    Par breuil dans le forum SQL Procédural
    Réponses: 14
    Dernier message: 28/08/2007, 00h44
  4. je bloque sur une clause where
    Par bruno bdf dans le forum Langage SQL
    Réponses: 3
    Dernier message: 21/07/2006, 10h55
  5. [forms] Bloc basé sur une clause from
    Par plaineR dans le forum Forms
    Réponses: 11
    Dernier message: 16/12/2004, 13h02

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo