SAK 図書館
SAK 図書館
SQL 問い合わせ - 分析ファンクション、over、partition by、range、dense_rank
分析ファンクションの使い方が良く分からないので試してみた。
ほとんどの集計関数が分析関数として使えるみたいである。
これは Oracle8i からあった機能なんだろうか。
■PARTITION BY
・グループ集計基本型。
select 品番, count(*), sum(受注数), avg(受注数) from 受注m group by 品番;
品番 COUNT(*) SUM(受注数) AVG(受注数)
------------------ ---------- ----------- -----------
a001 25 10562 422.48
b001 7 16797 2399.57143
c001 3 6010 2003.33333
・総集計基本型。
select count(*), sum(受注数), avg(受注数) from 受注m;
COUNT(*) SUM(受注数) AVG(受注数)
---------- ----------- -----------
35 33369 953.4
・品番単位の平均受注数を明細に追加。
partition by は、グループ化分割する。
select 受注番号, 品番, 受注数,
avg(受注数) over (partition by 品番 order by 品番) a受注数
from 受注m order by 品番, 受注番号
;
受注番号 品番 受注数 A受注数
---------- ------------------ ---------- ----------
000001-001 a001 70 422.48
000001-002 a001 500 422.48
000001-056 a001 500 422.48
...
・partition by がないと総集計になる。
select 受注番号, 品番, 受注数,
avg(受注数) over () a受注数
from 受注m order by 品番, 受注番号
;
受注番号 品番 受注数 A受注数
---------- ------------------ ---------- ----------
000001-001 a001 70 953.4
000001-002 a001 500 953.4
000001-056 a001 500 953.4
...
■RANGE、BETWEEN
・品番単位の件数を明細に追加。
partition by は、グループ化分割する。
select 受注番号, 品番, 受注数,
count(*) over (partition by 品番 order by 品番) ct
from 受注m order by 品番, 受注番号
;
受注番号 品番 受注数 CT
---------- ------------------ ---------- ----------
000001-001 a001 70 25
000001-002 a001 500 25
000001-056 a001 500 25
...
・受注数より -1000 ～ +4500 の品番毎の件数を明細に追加。
select 受注番号, 品番, 受注数,
count(*) over (partition by 品番 order by 受注数
range between 1000 preceding and 4500 following) ct
from 受注m order by 品番, 受注番号
;
受注番号 品番 受注数 CT
---------- ------------------ ---------- ----------
000001-061 c001 1500 2
000921-001 c001 10 3
100011-002 c001 4500 1
...
■DENSE_RANK、RANK
・受注数の多い順にランクを付ける。
select 受注番号, 品番, 受注数,
dense_rank() over (order by 受注数 desc) rank
from 受注m order by rank, 品番, 受注番号
;
受注番号 品番 受注数 RANK
---------- ------------------ ---------- ----------
000011-001 b001 6000 1
000019-001 b001 5555 2
100011-002 c001 4500 3
...
・rank は同じランクがあるとランク値が飛ぶ。
select 受注番号, 品番, 受注数,
rank() over (order by 受注数 desc) rank
from 受注m order by rank, 品番, 受注番号
;
受注番号 品番 受注数 RANK
---------- ------------------ ---------- ----------
000011-001 b001 6000 1
000019-001 b001 5555 2
100011-002 c001 4500 3
...
・例は悪いが、仕入先CD の最初のランクに対する最小受注数と
仕入先CD の最後のランクに対する最大受注数を求める。
select 品番,
min(受注数) keep (dense_rank first order by 得意先CD) min,
max(受注数) keep (dense_rank last order by 得意先CD) max
from 受注m group by 品番
;
品番 MIN MAX
------------------ ---------- ----------
a001 10 4000
b001 2000 6000
c001 10 1500
...
・同じく明細レベルで行う。
select 受注番号, 品番,
min(受注数) keep (dense_rank first order by 得意先CD)
over(partition by 品番) min,
max(受注数) keep (dense_rank last order by 得意先CD)
over(partition by 品番) max
from 受注m order by 品番
;
受注番号 品番 MIN MAX
---------- ------------------ ---------- ----------
000001-002 a001 10 4000
000001-001 a001 10 4000
000001-056 a001 10 4000
...
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK