Explication détaillée de l'utilisation de OVER (PARTITION BY ..) dans les requêtes Oracle

Cet article présente principalement l'utilisation de OVER (PARTITION BY ..) dans les requêtes Oracle. Veuillez vous référer au contenu et au code. J'espère que cela pourra aider tout le monde. Afin de faciliter l'apprentissage et les tests de chacun, tous les exemples sont créés sous le propre utilisateur d'Oracle, Scott.

Remarque : l'ordre rouge dans le titre indique que l'ordre doit être inclus lors de l'utilisation de cette méthode.

1. Rank()/dense_rank() over(partition by ...order by ...)

Maintenant, le client a une telle demande, interrogez l'employé avec le salaire le plus élevé de chaque département Informations, je crois que les étudiants ayant certaines connaissances des applications Oracle peuvent écrire les instructions SQL suivantes :

select e.ename, e.job, e.sal, e.deptno 
 from scott.emp e, 
    (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me 
 where e.deptno = me.deptno 
  and e.sal = me.sal;

Tout en répondant aux besoins des clients, tout le monde doit s'habituer à cela Réfléchissez bien s'il existe d'autres moyens. C'est sûr, utilisez simplement la syntaxe Rank() over(partition by...) ou dense_rank() over(partition by...) dans le titre de cette section. Le SQL est le suivant :

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        dense_rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

Pourquoi obtient-il le même résultat que la déclaration ci-dessus ? Voici une explication supplémentaire de la syntaxe Rank()/dense_rank() over(partition by e.deptno order by e.sal desc).

suite : A quelles conditions.

partition par e.deptno : Diviser (partition) par numéro de département.

order by e.sal desc : Trier par salaire du plus élevé au plus bas (lors de l'utilisation de Rank()/dense_rank(), l'ordre par doit être inclus sinon c'est illégal)

rank()/dense_rank() : classement

La signification de l'ensemble de l'énoncé est la suivante : sur la base de la division par département, les employés sont classés du haut au bas en fonction du salaire, et le "niveau" va de petit à petit. Représenté par un grand nombre (la valeur minimale doit être 1).

Alors, quelle est la différence entre Rank() et dense_rank() ?

rank() : Tri par saut, s'il y a deux premiers niveaux, le suivant est le troisième niveau.

dense_rank() : Tri continu, s'il y a deux premiers niveaux, le suivant est toujours le deuxième niveau.

Petits devoirs : Interrogez les informations sur les employés du salaire minimum du département.

2. min()/max() over(partition by...)

Maintenant que nous avons obtenu le salaire le plus élevé/minimum du département, la demande du client revient, interrogeant informations sur l'employé En même temps, calculez la différence entre le salaire de l'employé et le salaire le plus élevé/minimum du département. C'est relativement simple. Sur la base de l'instruction groupby de la première section, modifiez-la comme suit :

select e.ename, 
     e.job, 
     e.sal, 
     e.deptno, 
     e.sal - me.min_sal diff_min_sal, 
     me.max_sal - e.sal diff_max_sal 
  from scott.emp e, 
     (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal 
      from scott.emp e 
      group by e.deptno) me 
  where e.deptno = me.deptno 
  order by e.deptno, e.sal;

Ci-dessus, nous avons utilisé min() et max(. ), le premier trouve la valeur minimale et le second trouve la valeur maximale. Quel sera l'effet si ces deux méthodes sont utilisées avec over(partition by...) ? Jetez un œil à l'instruction SQL suivante :

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, 
    nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal 
 from scott.emp e;

Les résultats de la requête de ces deux instructions sont les mêmes. ) trouve en fait les valeurs minimales et maximales, mais il est basé partition par partition.

Petit devoir : Si vous ajoutez order by dans cet exemple, quel sera le résultat ?

3. lead()/lag() over(partition by ... order by ...)

Les Chinois aiment comparer, sauver la face et sont mondialement connus. Le client a encore plus apprécié cela. Après l'avoir comparé au salaire le plus élevé/minimum, il a toujours estimé que ce n'était pas suffisant. Cette fois, il a présenté une exigence plutôt perverse, à savoir calculer la différence entre son salaire personnel et celui de. une personne supérieure ou inférieure à lui. Cette exigence me rend vraiment très embarrassé. Je ne sais pas comment la mettre en œuvre dans l'instruction groupby. mais. . . . Maintenant que nous avons fini (partition par...), tout semble si simple. Comme suit :

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, 
    lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, 
    nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 
      0) diff_lead_sal, 
    nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal 
 from scott.emp e;

Après avoir lu la phrase ci-dessus, pensez-vous qu'il s'agissait d'une fausse alerte ? Est-ce facile d'attraper froid) ? Expliquons les deux nouvelles méthodes utilisées ci-dessus.

lead(column name,n,m) : La valeur de e147dec42e83e3b84883e9b9d2f9cc02 enregistrée dans la nième ligne après l'enregistrement actuel, sinon, la valeur par défaut est m s'il n'y en a pas; paramètres n,m , puis recherchez la valeur de l'enregistrement 17bf18f6f4d3a323a3d4ef8bada85df9 dans la première ligne après l'enregistrement actuel, sinon la valeur par défaut est nulle.

lag(nom de la colonne,n,m) : La valeur de 17bf18f6f4d3a323a3d4ef8bada85df9 enregistrée dans la nième ligne avant l'enregistrement actuel, sinon, la valeur par défaut est m s'il n'y en a pas; paramètre n,m , puis recherchez la valeur de l'enregistrement 17bf18f6f4d3a323a3d4ef8bada85df9 dans la première ligne avant l'enregistrement actuel, sinon la valeur par défaut est nulle.

Ce qui suit répertorie les applications de certaines méthodes couramment utilisées dans cette grammaire (Remarque : les méthodes avec une clause order by indiquent que order by doit être utilisée lors de l'utilisation de cette méthode) :

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    first_value(e.sal) over(partition by e.deptno) first_sal, 
    last_value(e.sal) over(partition by e.deptno) last_sal, 
    sum(e.sal) over(partition by e.deptno) sum_sal, 
    avg(e.sal) over(partition by e.deptno) avg_sal, 
    count(e.sal) over(partition by e.deptno) count_num, 
    row_number() over(partition by e.deptno order by e.sal) row_num 
 from scott.emp e;

Vous aurez peut-être des malentendus après avoir lu cet article, c'est-à-dire que OVER (PARTITION BY ..) est meilleur que GROUP BY. Ce n'est pas réellement le cas. ce dernier, et l'efficacité d'exécution du premier n'est pas aussi élevée que celle du second, mais le premier offre plus de fonctions, j'espère donc que vous pourrez choisir en fonction de vos besoins lors de son utilisation.

Recommandations associées :

Conseils de développement de programmes Oracle

Comparaison des cas de déclencheurs utilisés dans Oracle et des déclencheurs utilisés dans MySQL

99 instructions de requête couramment utilisées dans la base de données Oracle

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!