ホームページ > 記事 > データベース > Oracle クエリでの OVER (PARTITION BY ..) の使用法の詳細な説明

Oracle クエリでの OVER (PARTITION BY ..) の使用法の詳細な説明

小云云オリジナル: 2017-12-11 13:23:118541ブラウズ

この記事ではOracleクエリでのOVER(PARTITION BY ..)の使い方を中心に紹介していますので、ぜひ参考にしてください。全員の学習とテストを容易にするために、すべてのサンプルは Oracle 自身のユーザー Scott の下で作成されています。

注: タイトルの赤い「order by」は、この方法を使用する場合は order by を含める必要があることを意味します。

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

さて、顧客は各部門で最も給与の高い従業員の情報をクエリする必要があると思います。 Oracle アプリケーションの知識がある程度ある生徒は次の SQL ステートメントを書くことができます:

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;

顧客のニーズに応えながら、他の方法がないかどうかを全員が習慣的に考える必要があります。これは確かに、このセクションのタイトルにある Rank() over(partition by...) または Density_rank() over(partition by...) 構文を使用するだけです。 SQL は次のとおりです。

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;

なぜ上記のステートメントと同じ結果が得られるのでしょうか? Rank()/dense_rank() over(partition by e.deptno order by e.sal desc)の構文について補足説明します。

over: どのような条件で。

e.deptno によるパーティション: 部門番号 (パーティション) で分割されます。

order by e.sal desc: 給料の高い順に並べ替えます (rank()/dense_rank() を使用する場合、order by を含める必要があります。そうでないと不正です)

rank()/dense_rank(): 等級付け

ステートメント全体の意味は次のとおりです。部門ごとの区分に基づいて、従業員は給与に応じて高いものから低いものまで等級付けされます。「レベル」は小さいものから大きいものまでの数値で表されます (最小値は 1 である必要があります)。）。

それでは、rank()とdense_rank()の違いは何でしょうか?

rank(): ジャンプソート。最初のレベルが 2 つある場合、次は 3 番目のレベルです。

dense_rank(): 連続ソート。最初のレベルが 2 つある場合、次のレベルは依然として 2 番目のレベルです。

小さな課題: 部門の最低賃金の従業員情報をクエリします。

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

部門の最高/最低給与をクエリして取得したので、従業員情報をクエリしているときに、顧客の要求が再び来ました。従業員の給与と部門の最高給与/最低賃金の差も計算できます。これは比較的単純です。最初のセクションの groupby ステートメントに基づいて、次のように変更します。

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;

上記では min() と max() を使用し、前者は最小値を見つけ、後者は最小値を見つけます。最大値。これら 2 つのメソッドを over(partition by...) と一緒に使用すると、どのような影響がありますか?次の SQL ステートメントを見てください:

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;

これら 2 つのステートメントのクエリ結果は同じであることがわかりますが、min() と max() は実際には最小値と最大値を検索します。パーティションごとに。

ちょっとした宿題: この例に order by を追加すると、どのような結果が得られますか?

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

中国人は比較するのが大好きで、面子を保ち、世界的に有名です。顧客はこれをさらに気に入って、最高/最低給与と比較した後、それでも不十分だと感じ、今度は自分の給与と従業員の給与の差を計算するというかなり変態的な要求を出しました。彼より上または下の人が 1 人。この要件を groupby ステートメントに実装する方法がわかりません。しかし。。。。 over(partition by...) を実行したので、すべてが非常に単純に見えます。以下の通り：

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;

上記の文章を読んで、あなたも誤報（冷や汗をかいた後に急に凍りつき、風邪を引きやすくなる）だと感じませんか？上記で使用された 2 つの新しいメソッドについて説明しましょう。

lead(列名, n, m): 現在のレコードの後の n 番目の行に記録されるの値。パラメータ n がない場合、デフォルト値は m です。 m、現在のレコードの後に検索します。最初の行には、の値が記録されます。そうでない場合、デフォルト値は null です。

lag(列名, n, m): 現在のレコードの前の n 行目に記録されているの値。パラメータ n がない場合、デフォルト値は m です。 m、現在のレコードの前を検索します。最初の行には、の値が記録されます。そうでない場合、デフォルト値は null です。

以下は、この文法で使用されるいくつかの一般的なメソッドのリストです (注: order by 句を含むメソッドは、このメソッドを使用するときに order by を含める必要があることを示します):

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;

これを読んだ後の皆さん、後でいくつかの誤解があるかもしれませんこの記事、つまり OVER (PARTITION BY ..) の方が GROUP BY よりも優れているというわけではありません。また、前者の実行効率は後者ほど高くありません。機能が豊富なだけなので、用途に合わせて選んで使っていただければと思います。

Oracle クエリでの OVER (PARTITION BY ..) の使用法の詳細な説明

関連記事