読者です 読者をやめる 読者になる 読者になる

搾りたての工数と干からびた社畜

99%の雑記と1%プログラミングメモなど

【SQL】集計にはウィンドウ関数が便利

いやー、2年目のくせに未だにSQLのウィンドウ関数を本格的に覚えていないので調べました。

 

スキーマはお馴染みscotto/tiger@Oracle

結論から言うとデータを集計するのに使う関数です。

 

■書き方

MAX(EMP.SAL) OVER ()

 ・OVER以前

   ★集約関数(SUMとかMAXとか)とRANK関数(順位決める関数)が使用可能

 ・OVER以後

   ★PARTITION BY(省略可能)

    どの列で区切るかを指定。GROUP BYみたいなもんですな。

   ★ORDER BY(省略可能)

              どの列で整列させるか。

 

さて、emp表から従業員番号と従業員名と各個人の給料と一番高い給与額を算出するよー。

 

ウィンドウ関数使わないとこうなる(SELECT句にサブクエリ。僕のヘボ知識じゃこれが限界です)

select emp.EMPNO,
emp.ENAME,
emp.SAL,
(select max (emp.SAL) from emp) as max
from emp

 

ウィンドウ関数を使うとこうなる

select emp.EMPNO,
emp.ENAME,
emp.SAL,
max (emp.SAL) over ()
from emp

 

結果はどっちも同じ

EMPNO ENAME SAL MAX
7369 SMITH 800 5000
7499 ALLEN 1600 5000
7521 WARD 1250 5000
7566 JONES 2975 5000
7654 MARTIN 1250 5000
7698 BLAKE 2850 5000
7782 CLARK 2450 5000
7839 KING 5000 5000
7844 TURNER 1500 5000
7900 JAMES 950 5000
7902 FORD 3000 5000
7934 MILLER 1300 5000

 

 

 

■で、メリットは?

・集計するのにいちいちGROUP BYとかサブクエリを書かなくていい。

GROUP BY句を書くとSELECTする項目に制限がかかってきます。(GROUP BY句にない項目はSELECT出来ない)

僕がやったようにSELECT句に一々サブクエリを書かなくても良い(サブクエリって一行取ってくるならまだしもデータが多くなると遅くなってパフォーマンスに影響が出るとか)

 

うーん、便利。と共に勉強不足を指摘された気分で苦々しい。