OracleSQLパズル
明智重蔵のブログ
明智重蔵のTwitter
記事(CodeZine)
PostgreSQLの再帰SQLの使用例
PostgreSQLパズル
PostgreSQL8.4新機能 window関数
概要
PostgreSQL Conference 2009 Japanで、講演させていただきまして、そのまとめページです。
分析関数とmodel句や、分析関数の衝撃の内容をふまえつつ、
Oracleコミュニティでよく見かけるSQLの問題を、PostgreSQLでwindow関数を使用して解いたり、
Oracle10gとDB2 V9.5の分析関数をPostgreSQLで模倣するSQLを紹介しながら、
私のSQLの思考法と脳内のイメージを解説しました。単純なサンプル中心のセッションにしました。
プログラム
1. window関数とは
select句とorder by句で使うことができる。order by句で使うことは、ほとんどない。
PostgreSQL8.4から使用可能
Oracle9i以降
DB2 Express
SQLServer2005以降
などでも使用可能
window関数は標準SQLなので、いずれは、他のDBでも使えるようになるはずです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
window関数のメリット
PostgreSQL8.3までは、自己結合や相関サブクエリを使ったり、親言語(PHPやRubyなど)やPL/pgSQLで、
求めていた結果を、SQLで容易に求めることができるようになります。
帳票作成やデータ分析で特に使います。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
window関数の使用頻度
最頻出
count max min sum Row_Number Lag Lead
頻出
Rank dense_rank array_agg bool_or bool_and
たまに
First_Value Last_Value nth_Value avg
レア
NTile regr_count
2. select文の件数取得
select文の結果の件数が欲しいといったことは、結構あります。そんな時に使うのが、window関数のcount関数です。
create table TestTable(ID,Val) as
select 1,10 union all
select 1,20 union all
select 2,10 union all
select 2,30 union all
select 2,50;
-- OLAPSample1
select ID,Val,
count(*) over() as recordCount
from TestTable;
-- OLAPSample1のサブクエリを使った代替方法
select ID,Val,
(select count(*) from TestTable b) as recordCount
from TestTable a;
ID | Val | recordCount
----+-----+-------------
1 | 10 | 5
1 | 20 | 5
2 | 10 | 5
2 | 30 | 5
2 | 50 | 5
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
where句や、group by句や、having句があっても、select文の結果の件数が求まります。
-- OLAPSample2
select ID,Val,
count(*) over() as recordCount
from TestTable
where Val in(10,20);
ID | Val | recordCount
----+-----+-------------
1 | 10 | 3
1 | 20 | 3
2 | 10 | 3
-- OLAPSample3
select ID,max(Val),
count(*) over() as recordCount
from TestTable
group by ID
having max(Val) = 20;
ID | max | recordCount
----+-----+-------------
1 | 20 | 1
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Limit句があるselect文の結果の件数を求めるには、
インラインビューを使うか、Least関数を使う必要があります。
distinctもしくはdistinct on指定があるselect文の結果の件数を求めるには、
インラインビューを使うか、count(*) over()の代わりにdense_rank関数と逆ソートを使う必要があります。
PostgreSQLのselect文は、
・from句
・where句
・group by句
・having句
・select句
・order by句
・distinctもしくはdistinct on
・Limit句
の順に動作するからです。
PostgreSQLのマニュアル SELECT
-- OLAPSample4
select ID,count(*) over() as recordCount
from TestTable
order by ID Limit 2;
id | recordcount
----+-------------
1 | 5
1 | 5
-- OLAPSample5
select ID,Least(2,count(*) over()) as recordCount
from TestTable
order by ID Limit 2;
id | recordcount
----+-------------
1 | 2
1 | 2
create table disT(ColA,ColB) as
select 1,null union all
select 1, 3 union all
select 1, 3 union all
select 2,null union all
select 2,null;
-- OLAPSample6
select distinct ColA,ColB,
-1+dense_rank() over(order by ColA asc ,ColB asc)
+dense_rank() over(order by ColA desc,ColB desc) as recordCount
from disT
order by ColA,ColB;
cola | colb | recordcount
------+------+-------------
1 | 3 | 3
1 | null | 3
2 | null | 3
-- OLAPSample7
select distinct ColA,ColB,
Least(2,-1+dense_rank() over(order by ColA asc ,ColB asc)
+dense_rank() over(order by ColA desc,ColB desc)) as recordCount
from disT
order by ColA,ColB Limit 2;
cola | colb | recordcount
------+------+-------------
1 | 3 | 2
1 | null | 2
-- OLAPSample8
select ColA,ColB,count(*) over() as recordCount
from (select distinct ColA,ColB
from disT
order by ColA,ColB Limit 2) a
order by ColA,ColB;
cola | colb | recordcount
------+------+-------------
1 | 3 | 2
1 | null | 2
3. except allとcount(*) over()
2つのselect文の結果が同じか確認するときや、同じ定義の2テーブルのデータが同じか確認する時に使えるのが、
except allとcount(*) over() の組み合わせです。
create table tableA(ColA integer,ColB integer);
create table tableB(ColA integer,ColB integer);
insert into tableA values(1,2);
insert into tableA values(3,4);
-- case1
truncate table tableB;
insert into tableB values(1,2);
-- case2
truncate table tableB;
insert into tableB values(1,2);
insert into tableB values(3,4);
insert into tableB values(5,6);
-- case3
truncate table tableB;
-- case4
truncate table tableB;
insert into tableB values(2,2);
insert into tableB values(3,3);
-- case5
truncate table tableB;
insert into tableB values(1,2);
insert into tableB values(3,4);
-- tableAとtableBのデータが同じか確認するselect文
select *,count(*) over() from tableA
except all
select *,count(*) over() from tableB;
上記のselect文の結果が0件になるのは、以下の少なくとも1つが成り立つ場合です。
・tableAが空集合(レコードが0件)
・tableAとtableBのデータが同じ
実際の業務において、空集合ということは、まずないので
上記のselect文の結果が0件なら、tableAとtableBのデータが同じと判定できます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
数学の集合では、集合の相等性を調べる公式として、以下が有名ですが、
(A ⊆ B ) かつ (A ⊇ B) ⇔ (A = B)
(集合Aと集合Bの要素数が等しい) かつ (A ⊆ B) ⇔ (A = B)
も成立します。
集合Aと集合Bが両方とも空集合の場合は、自明ですし、
集合Aと集合Bが両方とも空集合でない場合は、要素数が等しくて包含関係が成立するのは、A=Bの場合しかないからです。
要素数は、window関数のcount関数を使えば求まりますし、
包含関係は、差集合が空集合かどうかを調べれば分かります。
4. 最大値の行の取得
実によく見かける、定番問題です。IDごとに、Valが最大値の行を取得します。
create table TestTable2(ID,Val,extraCol) as
select 1,10,'A' union all
select 1,20,'B' union all
select 2,10,'C' union all
select 2,30,'D' union all
select 2,50,'E' union all
select 3,70,'F' union all
select 3,70,'G';
-- 単純に、IDごとのValの最大値が欲しいなら、これで可
select ID,max(Val)
from TestTable2
group by ID;
-- IDごとのValが最大値の行が必ず1行しかないなら、これで可
select distinct on(ID) ID,Val,extraCol
from TestTable2
order by ID,Val desc;
-- OLAPSample9
select ID,Val,extraCol
from (select ID,Val,extraCol,
max(Val) over(partition by ID) as maxVal
from TestTable2) a
where Val = maxVal;
-- OLAPSample9の相関サブクエリを使った代替方法
select ID,Val,extraCol
from TestTable2 a
where Val = (select max(b.Val)
from TestTable2 b
where b.ID = a.ID);
ID | Val | extraCol
----+-----+----------
1 | 20 | B
2 | 50 | E
3 | 70 | F
3 | 70 | G
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
window関数が使えるのは、select句かorder by句です。
なので、window関数の結果をwhere句で使うには、インラインビューを使う必要があります。
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
5. 順位を付ける
順位や連番を付けたい時に使うのが、window関数のRow_Number関数,rank関数,dense_rank関数です。
create table TestTable3(ID,score) as
select 1,100 union all
select 1,100 union all
select 1, 90 union all
select 1, 80 union all
select 2,100 union all
select 2, 70 union all
select 2, 70;
-- OLAPSample10
select ID,score,
Row_Number() over(partition by ID order by score desc) as "Row_Number",
rank() over(partition by ID order by score desc) as "rank",
dense_rank() over(partition by ID order by score desc) as "dense_rank"
from TestTable3
order by ID,score desc;
ID | score | Row_Number | rank | dense_rank
----+-------+------------+------+------------
1 | 100 | 1 | 1 | 1
1 | 100 | 2 | 1 | 1
1 | 90 | 3 | 3 | 2
1 | 80 | 4 | 4 | 3
2 | 100 | 1 | 1 | 1
2 | 70 | 2 | 2 | 2
2 | 70 | 3 | 2 | 2
Row_Number関数は、1から始まって、必ず連番になります。
rank関数は、同点があると順位が飛びます。
dense_rank関数は、同点があっても順位が飛びません。denseは、形容詞で、密集したという意味です。
-- OLAPSample10の相関サブクエリを使った代替方法
select ID,score,
(select count(*)+1 from TestTable3 b
where b.ID = a.ID
and (b.score > a.score
or b.score = a.score and b.ctid > a.ctid)) as "Row_Number",
(select count(*)+1 from TestTable3 b
where b.ID = a.ID and b.score > a.score) as "rank",
(select count(distinct b.score)+1 from TestTable3 b
where b.ID = a.ID and b.score > a.score) as "dense_rank"
from TestTable3 a
order by ID,"Row_Number";
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
6. 最大値の行の取得(ソートキーが複数)
4. 最大値の行の取得では、
ソートキーが1つだったので、window関数のmax関数を使いましたが、
ソートキーが複数だと、順位を付ける関数を使う必要があります。
IDごとで、sortKey1が最大の行の中でsortKey2が最大の行を取得します。
create table multiSortKey(ID,sortKey1,sortKey2,extraCol) as
select 1,10, 2,'AAA' union all
select 1,10, 3,'BBB' union all
select 1,30, 1,'CCC' union all
select 2,20, 1,'DDD' union all
select 2,50, 2,'EEE' union all
select 2,50, 2,'FFF' union all
select 3,60, 1,'GGG' union all
select 3,60, 2,'HHH' union all
select 3,60, 3,'III' union all
select 4,10,20,'JJJ';
-- IDごとで、sortKey1が最大の行の中でsortKey2が最大の行が必ず1行しかないなら、これで可
select distinct on(ID) ID,sortKey1,sortKey2,extraCol
from multiSortKey
order by ID,sortKey1 desc,sortKey2 desc;
-- OLAPSample11
select ID,sortKey1,sortKey2,extraCol
from (select ID,sortKey1,sortKey2,extraCol,
rank() over(partition by ID order by sortKey1 desc,sortKey2 desc) as rn
from multiSortKey) a
where rn = 1
order by ID,extraCol;
ID | sortKey1 | sortKey2 | extraCol
----+----------+----------+----------
1 | 30 | 1 | CCC
2 | 50 | 2 | EEE
2 | 50 | 2 | FFF
3 | 60 | 3 | III
4 | 10 | 20 | JJJ
-- OLAPSample11の相関サブクエリを使った代替方法1
select ID,sortKey1,sortKey2,extraCol
from multiSortKey a
where (sortKey1,sortKey2) = (select b.sortKey1,b.sortKey2
from multiSortKey b
where b.ID = a.ID
order by b.sortKey1 desc,b.sortKey2 desc Limit 1)
order by ID,extraCol;
-- OLAPSample11の相関サブクエリを使った代替方法2
select ID,sortKey1,sortKey2,extraCol
from multiSortKey a
where not exists(select 1 from multiSortKey b
where b.ID = a.ID
and (b.sortKey1 > a.sortKey1
or b.sortKey1 = a.sortKey1 and b.sortKey2 > a.sortKey2))
order by ID,extraCol;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
7. 前後の行の値
指定したソートキーでの、
前の行の値が欲しい時に使われるのが、Lag関数で、
後の行の値が欲しい時に使われるのが、Lead関数です。
create table TestTable4(ID,sortKey,Val) as
select 'AA',1,10 union all
select 'AA',3,20 union all
select 'AA',5,60 union all
select 'AA',7,30 union all
select 'BB',2,40 union all
select 'BB',4,80 union all
select 'BB',6,50;
-- OLAPSample12
select ID,sortKey,Val,
Lag(Val) over(partition by ID order by sortKey) as Prev,
Lead(Val) over(partition by ID order by sortKey) as Next
from TestTable4
order by ID,sortKey;
ID | sortKey | Val | Prev | Next
----+---------+-----+------+------
AA | 1 | 10 | null | 20
AA | 3 | 20 | 10 | 60
AA | 5 | 60 | 20 | 30
AA | 7 | 30 | 60 | null
BB | 2 | 40 | null | 80
BB | 4 | 80 | 40 | 50
BB | 6 | 50 | 80 | null
-- OLAPSample12の相関サブクエリを使った代替方法
select ID,sortKey,Val,
(select b.Val from TestTable4 b
where b.ID=a.ID and b.sortKey < a.sortKey
order by b.sortKey desc Limit 1) as Prev,
(select b.Val from TestTable4 b
where b.ID=a.ID and b.sortKey > a.sortKey
order by b.sortKey Limit 1) as Next
from TestTable4 a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数とLead関数は、キーブレイクの事前検知にも使えます。
ID列,sortKey列をソートキー、
ID列をブレークキーとして、
キーブレイクを検知してみます。
(case式を使うのが定番ですが、これは、case式を使わなくていいパターンです)
-- case式を使う定番の方法
select ID,sortKey,Val,
case when ID = Lag(ID) over(order by ID,SortKey) then 0 else 1 end as IsKeyBreaked,
case when ID = Lead(ID) over(order by ID,SortKey) then 0 else 1 end as willKeyBreak
from TestTable4
order by ID,sortKey;
-- OLAPSample13
select ID,sortKey,Val,
Lag (0,1,1) over(partition by ID order by sortKey) as IsKeyBreaked,
Lead(0,1,1) over(partition by ID order by sortKey) as willKeyBreak
from TestTable4
order by ID,sortKey;
ID | sortKey | Val | IsKeyBreaked | willKeyBreak
----+---------+-----+--------------+--------------
AA | 1 | 10 | 1 | 0
AA | 3 | 20 | 0 | 0
AA | 5 | 60 | 0 | 0
AA | 7 | 30 | 0 | 1
BB | 2 | 40 | 1 | 0
BB | 4 | 80 | 0 | 0
BB | 6 | 50 | 0 | 1
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
8. 累計
帳票で累計を求めたい時に使うのが、order byを指定した、window関数のsum関数です。
create table ValT(ID,sortKey,Val) as
select 'AA',1,10 union all
select 'AA',2,20 union all
select 'AA',3,40 union all
select 'AA',4,80 union all
select 'BB',1,10 union all
select 'BB',2,30 union all
select 'BB',3,90 union all
select 'CC',1,50 union all
select 'CC',2,60 union all
select 'CC',2,60;
-- OLAPSample14
select ID,sortKey,Val,
sum(Val) over(partition by ID order by sortKey) as runSum
from ValT
order by ID,sortKey;
ID | sortKey | Val | runSum
----+---------+-----+--------
AA | 1 | 10 | 10
AA | 2 | 20 | 30
AA | 3 | 40 | 70
AA | 4 | 80 | 150
BB | 1 | 10 | 10
BB | 2 | 30 | 40
BB | 3 | 90 | 130
CC | 1 | 50 | 50
CC | 2 | 60 | 170
CC | 2 | 60 | 170
-- OLAPSample14の相関サブクエリを使った代替方法
select ID,sortKey,Val,
(select sum(b.Val) from ValT b
where b.ID = a.ID and b.sortKey <= a.sortKey) as runSum
from ValT a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
order byを指定して、frame_clause(フレームクロウズ)を省略すると、
デフォルトの、RANGE UNBOUNDED PRECEDINGになります。
これは、Range between unbounded preceding and current rowと同じ意味です。
frame_clauseは、下記のように解釈すると分かりやすいと思います。
order by sortKey -- sortKeyの昇順で、
Range between -- 値の範囲は、
unbounded preceding -- 前方は、際限なく、
and current row -- 後方は、カレント行まで
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
9. First_ValueとLast_Valueとnth_Value
指定したソートキーでの、最初の行の値を求めるのが、First_Value
指定したソートキーでの、最後の行の値を求めるのが、Last_Value
指定したソートキーでの、(Row_Numberな順位が)n番目の行の値を求めるのが、nth_Value
となります。
create table nthT(ID,SortKey,Val) as
select 1,10,666 union all
select 1,30,333 union all
select 1,40,222 union all
select 1,50,444 union all
select 2,20,777 union all
select 2,25,111 union all
select 2,27,555 union all
select 3,60,999 union all
select 3,61,888;
-- OLAPSample15
select ID,SortKey,Val,
First_Value(Val) over(partition by ID order by SortKey) as FirVal,
Last_Value(Val) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as LastVal,
nth_Value(Val,2) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as SecondVal,
nth_Value(Val,3) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as thirdVal
from nthT
order by ID,SortKey;
ID | SortKey | Val | FirVal | LastVal | SecondVal | thirdVal
----+---------+-----+--------+---------+-----------+----------
1 | 10 | 666 | 666 | 444 | 333 | 222
1 | 30 | 333 | 666 | 444 | 333 | 222
1 | 40 | 222 | 666 | 444 | 333 | 222
1 | 50 | 444 | 666 | 444 | 333 | 222
2 | 20 | 777 | 777 | 555 | 111 | 555
2 | 25 | 111 | 777 | 555 | 111 | 555
2 | 27 | 555 | 777 | 555 | 111 | 555
3 | 60 | 999 | 999 | 888 | 888 | null
3 | 61 | 888 | 999 | 888 | 888 | null
-- OLAPSample15の相関サブクエリを使った代替方法
select ID,SortKey,Val,
(select b.Val from nthT b
where b.ID=a.ID order by b.SortKey Limit 1) as FirVal,
(select b.Val from nthT b
where b.ID=a.ID order by b.SortKey desc Limit 1) as LastVal,
(select b.Val from nthT b
where b.ID=a.ID order by b.SortKey Limit 1 offset 1) as SecondVal,
(select b.Val from nthT b
where b.ID=a.ID order by b.SortKey Limit 1 offset 2) as thirdVal
from nthT a
order by ID,SortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
10. array_agg関数で配列型にまとめる
PostgreSQL8.4で、集合関数のarray_agg関数が追加されました。
IDごとの、Valをカンマ区切りで連結した文字列を求めてみましょう。
create table ConCatVal(ID,Val) as
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'d' union all
select 2,'e' union all
select 3,'f';
-- OLAPSample16
select ID,Val,
array_to_string(array_agg(Val) over(partition by ID),',') as strAgg
from ConCatVal
order by ID,Val;
ID | Val | strAgg
----+-----+--------
1 | a | a,b,c
1 | b | a,b,c
1 | c | a,b,c
2 | d | d,e
2 | e | d,e
3 | f | f
-- OLAPSample16の相関サブクエリを使った代替方法
select ID,Val,
array_to_string
(array(select b.Val
from ConCatVal b
where b.ID = a.ID
order by b.Val),',') as strAgg
from ConCatVal a
order by ID,Val;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。array_agg関数では、脳内でベン図をイメージしてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
array_agg関数は、配列型を返しますので、列が1つしかない集合同士の包含関係を調べることもできます。
DBAzine.com: Relational Division(英語)や、
SQLパズル 第2版にも掲載されている有名な問題。
第21問 飛行機と飛行士をarray_agg関数を使って解いてみましょう。(工場が空集合のケースは考えません)
create table PilotSkills
(pilot text,
plane text,
primary key (pilot, plane));
insert into PilotSkills values('Celko' ,'A');
insert into PilotSkills values('Higgins','A');
insert into PilotSkills values('Higgins','B');
insert into PilotSkills values('Higgins','C');
insert into PilotSkills values('Jones' ,'B');
insert into PilotSkills values('Jones' ,'D');
insert into PilotSkills values('Smith' ,'B');
insert into PilotSkills values('Smith' ,'C');
insert into PilotSkills values('Smith' ,'E');
insert into PilotSkills values('Wilson' ,'B');
insert into PilotSkills values('Wilson' ,'C');
insert into PilotSkills values('Wilson' ,'E');
insert into PilotSkills values('Wilson' ,'F');
create table Hangar(plane text primary key);
insert into Hangar values('B');
insert into Hangar values('C');
insert into Hangar values('E');
Hangarテーブルのplaceを全て持つ、pilotを出力します。
select pilot
from PilotSkills a
group by pilot
having exists(select 1 from Hangar b
having array_agg(a.plane) @> array_agg(b.plane));
pilot
--------
Smith
Wilson
下記のように、boolean型を返すスカラーサブクエリをhaving句で使ってもいいです。
select pilot
from PilotSkills a
group by pilot
having (select array_agg(a.plane) @> array_agg(b.plane)
from Hangar b);
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
操縦可能な飛行機の一覧も出力結果として欲しいのであれば、
window関数のarray_agg関数の出番です。
select pilot,plane
from (select pilot,plane,
array_agg(plane) over(partition by pilot) as planeAgg
from PilotSkills) a
where (select a.planeAgg @> array_agg(b.plane)
from Hangar b);
pilot | plane
--------+-------
Smith | B
Smith | C
Smith | E
Wilson | B
Wilson | C
Wilson | E
Wilson | F
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。array_agg関数では、脳内でベン図をイメージしてます。
11. 移動累計(2行前からカレント行までの累計)
OracleSQLパズルのアクセス解析で、移動平均で検索してくる方は、結構多いです。
day1の昇順での移動累計(2行前からカレント行までの累計)を求めてみましょう。
create table idouT(day1,Val) as
select date '2009-04-16', 1 union all
select date '2009-04-17', 2 union all
select date '2009-04-20', 4 union all
select date '2009-04-21', 8 union all
select date '2009-04-22',20 union all
select date '2009-04-23',40 union all
select date '2009-04-24',90;
-- OLAPSample17
select day1,Val,
Val+Lag(Val,1,0) over(order by day1)
+Lag(Val,2,0) over(order by day1) as moveSum
from idouT
order by day1;
day1 | Val | moveSum
------------+-----+---------
2009-04-16 | 1 | 1
2009-04-17 | 2 | 3
2009-04-20 | 4 | 7
2009-04-21 | 8 | 14
2009-04-22 | 20 | 32
2009-04-23 | 40 | 68
2009-04-24 | 90 | 150
OracleやDB2では、Sum(Val) over(order by day1 Rows 2 preceding)が使えますが、PostgreSQL8.4では、使えません。
なので、代用案として、Lag関数で1行前と2行前を求めて(なければ0)、足し算してます。
-- OLAPSample17の相関サブクエリを使った代替方法1
select day1,Val,
(select sum(b.Val)
from idouT b
where (select count(*)
from idouT c
where c.day1 between b.day1 and a.day1)
between 1 and 3) as moveSum
from idouT a
order by day1;
-- OLAPSample17の相関サブクエリを使った代替方法2
select day1,Val,
(select sum(b.Val)
from (select c.Val
from idouT c
where c.Val <= a.Val
order by c.day1 desc Limit 3) b) as moveSum
from idouT a
order by day1;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数を何度も使いたくないのであれば、下記でもいいです。
-- OLAPSample18
select day1,Val,
runSum - Lag(runSum,3,0::bigInt) over(order by day1) as moveSum
from (select day1,Val,
sum(Val) over(order by day1) as runSum
from idouT) a
order by day1;
12. 全称肯定,全称否定,存在肯定,存在否定
これは、US-OTNでよく見かける問題です。
・全ての行が条件を満たすか？
・全ての行が条件を満たさないか？
・少なくとも1行が条件を満たすか？
・少なくとも1行が条件を満たさないか？
といった複数行にまたがったチェックをしたい時に使います。
create table TestTable8(
ID text,
Val integer not null);
insert into TestTable8 values('AA',10);
insert into TestTable8 values('AA',20);
insert into TestTable8 values('BB',10);
insert into TestTable8 values('BB',30);
insert into TestTable8 values('BB',50);
insert into TestTable8 values('CC',80);
insert into TestTable8 values('CC',90);
insert into TestTable8 values('DD',20);
insert into TestTable8 values('DD',70);
・check1 IDごとで、全ての行が Val<40 を満たすか？
・check2 IDごとで、全ての行が Val<40 を満たさないか？
・check3 IDごとで、少なくとも1つの行が Val<40 を満たすか？
・check4 IDごとで、少なくとも1つの行が Val<40 を満たさないか？
をチェックしてみましょう。
-- OLAPSample19
select ID,Val,
min(case when Val<40 then 1 else 0 end) over(partition by ID) as chk1,
min(case when Val<40 then 0 else 1 end) over(partition by ID) as chk2,
max(case when Val<40 then 1 else 0 end) over(partition by ID) as chk3,
max(case when Val<40 then 0 else 1 end) over(partition by ID) as chk4
from TestTable8
order by ID,Val;
ID | Val | chk1 | chk2 | chk3 | chk4
----+-----+------+------+------+------
AA | 10 | 1 | 0 | 1 | 0
AA | 20 | 1 | 0 | 1 | 0
BB | 10 | 0 | 0 | 1 | 1
BB | 30 | 0 | 0 | 1 | 1
BB | 50 | 0 | 0 | 1 | 1
CC | 80 | 0 | 1 | 0 | 1
CC | 90 | 0 | 1 | 0 | 1
DD | 20 | 0 | 0 | 1 | 1
DD | 70 | 0 | 0 | 1 | 1
SQLのロジックの解説は、割愛します。興味がある方は、PostgreSQLの分析関数の衝撃4や、
応用例であるPostgreSQLパズル 9-22 存在有無のブール値で論理演算を御覧になって下さい。
*************************************************************************************
PostgreSQLでは、bool_and関数やbool_or関数も使うことができます。
-- OLAPSample20
select ID,Val,
bool_and(Val<40) over(partition by ID) as chk1,
bool_and((Val<40) is false) over(partition by ID) as chk2,
bool_or (Val<40) over(partition by ID) as chk3,
bool_or ((Val<40) is false) over(partition by ID) as chk4
from TestTable8
order by ID,Val;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
全称肯定,全称否定,存在肯定,存在否定の、SQLへの変換公式は下記となります。
全称 → min
存在 → max
肯定 → (case when 条件 then 1 else 0 end)
否定 → (case when 条件 then 0 else 1 end)
まとめると
全称肯定命題なら min(case when 条件 then 1 else 0 end) = 1
全称否定命題なら min(case when 条件 then 0 else 1 end) = 1
存在肯定命題なら max(case when 条件 then 1 else 0 end) = 1
存在否定命題なら max(case when 条件 then 0 else 1 end) = 1
真なら1、偽なら0にしておくことにより、ブール値としても使えます。
*************************************************************************
bool_and関数やbool_or関数を使う際の、
全称肯定,全称否定,存在肯定,存在否定の、SQLへの変換公式は下記となります。
全称 → bool_and
存在 → bool_or
肯定 → 条件
否定 → (条件) is false もしくは、Not述語を使って、Not(条件)
まとめると
全称肯定命題なら bool_and(条件)
全称否定命題なら bool_and((条件) is false)
存在肯定命題なら bool_or (条件)
存在否定命題なら bool_or ((条件) is false)
ちなみに、否定をfalseかunknownとしたいのであれば、is not true述語を使うといいです。
13. 最頻値(モード)
create table DayWeather(day1,weather) as
select date '2008-01-02','sunny' union all
select date '2008-01-15','snowy' union all
select date '2008-01-30','snowy' union all
select date '2008-06-01','cloudy' union all
select date '2008-06-13','cloudy' union all
select date '2008-06-24','rainy' union all
select date '2008-06-30','rainy' union all
select date '2008-07-02','sunny' union all
select date '2008-07-14','sunny' union all
select date '2008-07-23','sunny' union all
select date '2008-07-31','sunny' union all
select date '2008-11-10','cloudy';
最頻値(モード)を求める問題は、結構見かけます。
weatherの最頻値を求めてみます。(最頻値が複数ある場合は、複数行返すようにします)
-- 最頻値が必ず1つだけなら、これでも可
select weather,count(*) as cnt
from DayWeather
group by weather
order by count(*) desc Limit 1;
-- OLAPSample21
select weather,cnt
from (select weather,count(*) as cnt,max(count(*)) over() as maxCnt
from DayWeather
group by weather) a
where cnt = maxCnt;
weather | cnt
---------+-----
sunny | 5
-- OLAPSample21のサブクエリを使った代替方法(all述語を使用)
select weather,count(*) as cnt
from DayWeather
group by weather
having count(*) >= all(select count(*)
from DayWeather
group by weather);
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。group by weatherに対応する赤線を引いてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
monthごとの最頻値(モード)を求めるような問題も結構見かけます。
-- monthごとに最頻値が必ず1つだけなら、これでも可
select distinct on(month1)
extract(month from day1) as month1,weather,count(*) as cnt
from DayWeather
group by month1,weather
order by month1,cnt desc;
-- OLAPSample22
select month1,weather,cnt
from (select extract(month from day1) as month1,weather,
count(*) as cnt,
max(count(*)) over(partition by extract(month from day1)) as maxCnt
from DayWeather
group by month1,weather) a
where cnt = maxCnt
order by month1,weather;
month1 | weather | cnt
--------+---------+-----
1 | snowy | 2
6 | cloudy | 2
6 | rainy | 2
7 | sunny | 4
11 | cloudy | 1
下記のように、group by句の他に、WINDOW句でも、列別名(month1)が使えるようです。
-- OLAPSample23
select month1,weather,cnt
from (select extract(month from day1) as month1,weather,
count(*) as cnt,
max(count(*)) over W1 as maxCnt
from DayWeather
group by month1,weather
WINDOW w1 AS (PARTITION BY month1)) a
where cnt = maxCnt
order by month1,weather;
-- OLAPSample23の相関サブクエリを使った代替方法(Limit句を使用)
select month1,weather,count(*) as cnt
from (select extract(month from day1) as month1,weather
from DayWeather) a
group by month1,weather
having count(*) = (select count(*)
from DayWeather b
where extract(month from b.day1) = a.month1
group by weather
order by count(*) desc Limit 1)
order by month1,weather;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
OLAPSample22の脳内のイメージ(第1段階)は、こうなります。
group by extract(month from day1),weatherに対応する赤線を引いてます。
脳内のイメージ(最終段階)は、こうなります。
partition by extract(month from day1)に対応する超極太赤線を引いてます。
14. 最小の空き番号の取得 (2人旅人算)
旅人算というのは、有名な算数の問題です。
旅人算の問題を解くには、速さの異なる複数の旅人を脳内でイメージする感覚が有効ですが、
SQLにおいて、旅人算の感覚を応用することができるのです。
create table SeqTbl(seq smallInt primary key);
-- case1
truncate table SeqTbl;
insert into SeqTbl values(1);
insert into SeqTbl values(2);
insert into SeqTbl values(3);
insert into SeqTbl values(5);
insert into SeqTbl values(6);
-- case2
truncate table SeqTbl;
insert into SeqTbl values(1);
insert into SeqTbl values(2);
insert into SeqTbl values(3);
insert into SeqTbl values(4);
insert into SeqTbl values(6);
-- case3
truncate table SeqTbl;
insert into SeqTbl values(2);
insert into SeqTbl values(3);
insert into SeqTbl values(4);
-- case4
truncate table SeqTbl;
insert into SeqTbl values(1);
insert into SeqTbl values(2);
insert into SeqTbl values(3);
insert into SeqTbl values(4);
insert into SeqTbl values(5);
-- case5
truncate table SeqTbl;
以下のように、歯抜けの最小値を求めるSQLを考えます。
case1では、歯抜けの最小値として4を返す。
case2では、歯抜けの最小値として5を返す。
case3では、歯抜けの最小値として1を返す。
case4では、歯抜けの最小値として6を返す。
case5では、歯抜けの最小値として1を返す。
-- OLAPSample24
select coalesce(max(seq),0)+1 as gap
from (select seq,Row_Number() over(order by seq) as rn
from SeqTbl) a
where seq = rn;
-- OLAPSample24の相関サブクエリを使った代替方法
select coalesce(max(seq),0)+1 as gap
from (select seq,
(select count(*)+1
from SeqTbl b where b.seq < a.seq) as rn
from SeqTbl a) c
where seq = rn;
SQLのロジックの解説は、割愛します。興味がある方は、PostgreSQLの分析関数の衝撃5を御覧になって下さい。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、こうなります。
Seqに対応する(速さが1以上の自然数)の旅人と、
Row_Number関数に対応する(速さが1)の旅人をイメージしてます。
15. 連続範囲の最小値と最大値 (3人旅人算)
US-OTNでよく見かけて、SQLパズル 第2版にも掲載されている問題。
第63問 連続的なグルーピングを旅人算の感覚を使って解いてみましょう。
create table Tabibito(sortKey,Val) as
select 1,'aaa' union all
select 2,'aaa' union all
select 3,'bbb' union all
select 6,'bbb' union all
select 8,'aaa' union all
select 20,'bbb' union all
select 22,'ccc' union all
select 23,'ccc' union all
select 31,'ddd' union all
select 33,'ddd';
-- OLAPSample25
select min(sortKey) as low,max(sortKey) as high,Val
from (select sortKey,Val,
Row_Number() over(order by sortKey)
-Row_Number() over(partition by Val order by sortKey) as distance
from Tabibito) a
group by Val,distance
order by min(sortKey);
low | high | Val
-----+------+-----
1 | 2 | aaa
3 | 6 | bbb
8 | 8 | aaa
20 | 20 | bbb
22 | 23 | ccc
31 | 33 | ddd
SQLのロジックの解説は、割愛します。興味がある方は、PostgreSQLの分析関数の衝撃5を御覧になって下さい。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使うSQLの脳内のイメージは、こうなります。
Row_Number() over(order by sortKey)に対応する1人の旅人と、
Row_Number() over(partition by Val order by sortKey)に対応する4人の旅人をイメージし、
group by Val,distanceに対応する赤線を引いてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLクックブックの「レシピ10.3 連続する値の範囲の最初と最後を求める」で紹介されている考え方を使って、
下記のSQLのように、Lag関数でシーケンス開始を検知してもいいです。
-- OLAPSample26
select min(sortKey) as low,max(sortKey) as high,Val
from (select sortKey,Val,
sum(willSum) over(order by sortKey) as GID
from (select sortKey,Val,
case when Val = Lag(Val) over(order by sortKey)
then 0 else 1 end as willSum
from Tabibito) a) a
group by GID,Val
order by GID;
SQLのロジックの解説は、割愛します。興味がある方は、PostgreSQLの分析関数の衝撃3を御覧になって下さい。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数でシーケンス開始を検知するSQLの脳内のイメージは、こうなります。
sum(willSum) over(order by sortKey) as GIDに対応する黄緑線と、
group by GID,Valに対応する赤線を引いてます。
16. 次の入社日を求める
SQLクックブックの「レシピ8.7 現在のレコードと次のレコードの日付の差を算出する」で扱われている、
Lead関数の応用問題である、次の入社日を求める問題を解いてみます。
重複を考慮して最小上界を求める問題と言えます。
create table emp2(ename,hireDate) as
select 'CLARK' ,date '1981-06-09' union all
select 'ant' ,date '1981-11-17' union all
select 'choi' ,date '1981-11-17' union all
select 'jim' ,date '1981-11-17' union all
select 'joe' ,date '1981-11-17' union all
select 'MILLER',date '1982-01-23' union all
select 'SCOTT' ,date '1999-10-30' union all
select 'TIGER' ,date '1999-10-30' union all
select 'JANE' ,date '2005-12-31';
-- OLAPSample27
select ename,hireDate,
Lead(hireDate,RevRank::integer) over(order by hireDate,ename) as nextHireDate
from (select ename,hireDate,
Row_Number() over(partition by hireDate order by ename desc) as RevRank
from emp2) a
order by hireDate,ename;
ename | hireDate | nextHireDate
--------+------------+--------------
CLARK | 1981-06-09 | 1981-11-17
ant | 1981-11-17 | 1982-01-23
choi | 1981-11-17 | 1982-01-23
jim | 1981-11-17 | 1982-01-23
joe | 1981-11-17 | 1982-01-23
MILLER | 1982-01-23 | 1999-10-30
SCOTT | 1999-10-30 | 2005-12-31
TIGER | 1999-10-30 | 2005-12-31
JANE | 2005-12-31 | null
-- OLAPSample27の相関サブクエリを使った代替方法
select ename,hireDate,
(select min(b.hireDate)
from emp2 b where a.hiredate < b.hiredate) as nextHireDate
from emp2 a
order by hireDate,ename;
OracleやDB2では、
min(hireDate) over(order by hireDate range between 1 following and unbounded following)
のようなSQLが使えますが、PostgreSQL8.4では、使えませんので、
Row_Number関数とLead関数を組み合わせてます。
SQLクックブックでは、Lead関数の引数に
件数 - 正順位 + 1 を指定してますが、
正順位 + 逆順位 = 件数 + 1 を移項すると
逆順位 = 件数 - 正順位 + 1 なので、同じ意味となります。
OracleSQLパズル 2-3-26 正順位と逆順位
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
Row_Number() over(partition by hireDate order by ename desc)に対応する赤線と黄緑線と、
Lead関数に対応する青線を引いてます。
第2部 Oracle10gとDB2 V9.5の分析関数をPostgreSQL8.4で模倣
17. count(distinct Val) over(partition by ID)
create table OracleDistinct(
ID integer,
Val integer not null);
insert into OracleDistinct values(1,111);
insert into OracleDistinct values(1,111);
insert into OracleDistinct values(1,222);
insert into OracleDistinct values(1,222);
insert into OracleDistinct values(1,333);
insert into OracleDistinct values(2,111);
insert into OracleDistinct values(2,111);
insert into OracleDistinct values(3,111);
insert into OracleDistinct values(3,222);
insert into OracleDistinct values(4,333);
Oracleでは、分析関数のcount関数でdistinctオプションが使えます。
PostgreSQL8.4で、下記のOracleのSQLと同じ結果を取得してみます。
-- 模倣対象のOracleのSQL
select ID,Val,count(distinct Val) over(partition by ID) as disCnt
from OracleDistinct;
ID Val disCnt
-- --- ------
1 111 3
1 111 3
1 222 3
1 222 3
1 333 3
2 111 1
2 111 1
3 111 2
3 222 2
4 333 1
-- OLAPSample28
select ID,Val,
-1+dense_rank() over(partition by ID order by Val asc )
+dense_rank() over(partition by ID order by Val desc) as disCnt
from OracleDistinct
order by ID,Val;
-- OLAPSample28の相関サブクエリを使った代替方法
select ID,Val,
(select count(distinct b.Val)
from OracleDistinct b where b.ID = a.ID) as disCnt
from OracleDistinct a
order by ID,Val;
正順位 + 逆順位 = 件数 + 1 を移項すると
件数 = -1 + 正順位 + 逆順位 になることをふまえてます。
下記のようにdense_rank関数の結果の最大値を取得してもいいです。
-- OLAPSample29
select ID,Val,max(rn) over(partition by ID) as disCnt
from (select ID,Val,dense_rank() over(partition by ID order by Val) as rn
from OracleDistinct) a
order by ID,Val;
ちなみに、count(distinct Val) は、Valがnullだとカウントしませんので、
Valがnullの場合も考慮するなら、下記のように存在肯定命題を使う必要があります。
-- OLAPSample30
select ID,Val,
-1+dense_rank() over(partition by ID order by Val asc )
+dense_rank() over(partition by ID order by Val desc)
-max(case when Val is null then 1 else 0 end) over(partition by ID) as disCnt
from OracleDistinct
order by ID,Val;
18. count(distinct Val) over(order by sortKey)
Oracle10gでも文法エラーとなるのですが、count(distinct Val) over(order by sortKey) を模倣してみます。
これは、US-OTNで結構見かける問題で、訪問者が、リピーターか新規かを見分ける時に使うSQLのようです。
create table VisiterT(sortKey,Visit) as
select 10,'AAAA' union all
select 20,'BBBB' union all
select 30,'AAAA' union all
select 40,'BBBB' union all
select 50,'CCCC' union all
select 60,'CCCC' union all
select 70,'DDDD' union all
select 80,'AAAA';
-- 模倣対象のSQL
select sortKey,Visit,
count(distinct Visit) over(order by sortKey) as disVisit
from VisiterT
order by sortKey;
sortKey Visit disVisit
------- ----- --------
10 AAAA 1 ← AAAAで1
20 BBBB 2 ← AAAA,BBBBで2
30 AAAA 2 ← AAAA,BBBBで2
40 BBBB 2 ← AAAA,BBBBで2
50 CCCC 3 ← AAAA,BBBB,CCCCで3
60 CCCC 3 ← AAAA,BBBB,CCCCで3
70 DDDD 4 ← AAAA,BBBB,CCCC,DDDDで4
80 AAAA 4 ← AAAA,BBBB,CCCC,DDDDで4
-- OLAPSample31
select sortKey,Visit,sum(willSum) over(order by sortKey) as disVisit
from (select sortKey,Visit,
case Row_Number() over(partition by Visit order by sortKey)
when 1 then 1 else 0 end as willSum
from VisiterT) a
order by sortKey;
-- OLAPSample31の相関サブクエリを使った代替方法
select sortKey,Visit,
(select count(distinct b.Visit)
from VisiterT b where b.sortKey <= a.sortKey) as disVisit
from VisiterT a
order by sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
sum(willSum) over(order by sortKey)に対応する黄緑線を引いてます。
19. sum(Val) Keep(Dense_Rank Last order by sortKey) over(partition by ID)
Oracleでは、集合関数や分析関数のcount関数やmax関数やsum関数などでKeepが使えます。
PostgreSQL8.4で、同じ結果を取得してみます。
create table OracleKeepDense1(
ID integer,
sortKey integer,
Val integer);
insert into OracleKeepDense1 values(1,1, 100);
insert into OracleKeepDense1 values(1,2, 200);
insert into OracleKeepDense1 values(1,3, 400);
insert into OracleKeepDense1 values(1,3, 500);
insert into OracleKeepDense1 values(1,3, 600);
insert into OracleKeepDense1 values(2,5, 700);
insert into OracleKeepDense1 values(2,8, 800);
insert into OracleKeepDense1 values(3,9, 900);
insert into OracleKeepDense1 values(3,9,1000);
insert into OracleKeepDense1 values(4,6,1100);
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
sum(Val) over(partition by ID) as sum1,
sum(Val) Keep(Dense_Rank Last order by sortKey) over(partition by ID) as sum2
from OracleKeepDense1
order by ID,sortKey,Val;
ID sortKey Val sum1 sum2
-- ------- ---- ---- ----
1 1 100 1800 1500 ←400+500+600
1 2 200 1800 1500
1 3 400 1800 1500
1 3 500 1800 1500
1 3 600 1800 1500
2 5 700 1500 800
2 8 800 1500 800
3 9 900 1900 1900 ←900+1000
3 9 1000 1900 1900
4 6 1100 1100 1100
-- OLAPSample32
select ID,sortKey,Val,
sum(Val) over(partition by ID) as sum1,
sum(case when sortKey = maxSortKey
then Val end) over(partition by ID) as sum2
from (select ID,sortKey,Val,
max(sortKey) over(partition by ID) as maxSortKey
from OracleKeepDense1) a
order by ID,sortKey,Val;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
partition by IDに対応する赤線を引いてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ソートキーが複数だったらdense_rank関数を使う必要があります。
create table OracleKeepDense2(
ID integer,
sortKey1 integer,
sortKey2 integer,
Val integer);
insert into OracleKeepDense2 values(1,1,10,100);
insert into OracleKeepDense2 values(1,2,20,200);
insert into OracleKeepDense2 values(1,3,30,300);
insert into OracleKeepDense2 values(1,3,40,400);
insert into OracleKeepDense2 values(1,3,40,500);
insert into OracleKeepDense2 values(2,1,60,600);
insert into OracleKeepDense2 values(2,2,80,700);
insert into OracleKeepDense2 values(2,3,50,800);
insert into OracleKeepDense2 values(2,3,50,900);
insert into OracleKeepDense2 values(3,1,20,100);
-- 模倣対象のOracleのSQL
select ID,sortKey1,sortKey2,Val,
sum(Val) Keep(Dense_Rank Last order by sortKey1,sortKey2) over(partition by ID) as sum3
from OracleKeepDense2
order by ID,sortKey1,sortKey2,Val;
ID sortKey1 sortKey2 Val sum3
-- -------- -------- --- ----
1 1 10 100 900 ←400+500
1 2 20 200 900
1 3 30 300 900
1 3 40 400 900
1 3 40 500 900
2 1 60 600 1700 ←800+900
2 2 80 700 1700
2 3 50 800 1700
2 3 50 900 1700
3 1 20 100 100
-- OLAPSample33
select ID,sortKey1,sortKey2,Val,
sum(case when rn=1 then Val end) over(partition by ID) as sum3
from (select ID,sortKey1,sortKey2,Val,
dense_rank() over(partition by ID order by sortKey1 desc,sortKey2 desc) as rn
from OracleKeepDense2) a
order by ID,sortKey1,sortKey2,Val;
order by sortKey1 asc ,sortKey2 asc の逆ソートである
order by sortKey1 desc,sortKey2 desc を使ってます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
partition by IDに対応する赤線を引いてます。
20. sum(Val) over(order by sortKey range between current row and unbounded following)
create table RowsFollowing(
ID integer,
sortKey integer,
Val integer);
insert into RowsFollowing values(1,1, 10);
insert into RowsFollowing values(1,2, 20);
insert into RowsFollowing values(1,3, 40);
insert into RowsFollowing values(1,3, 80);
insert into RowsFollowing values(1,3,160);
insert into RowsFollowing values(1,4,900);
insert into RowsFollowing values(2,5,320);
insert into RowsFollowing values(2,8,640);
insert into RowsFollowing values(3,9,100);
insert into RowsFollowing values(3,9,100);
insert into RowsFollowing values(4,6,300);
OracleやDB2の分析関数の、下記のRange指定と同じ結果を、PostgreSQL8.4で取得してみます。
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
sum(Val) over(partition by ID
order by sortKey Range between current row
and unbounded following) as FollowingSum
from RowsFollowing
order by ID,sortKey,Val;
ID sortKey Val FollowingSum
-- ------- --- ------------
1 1 10 1210 ←10+20+40+80+160+900
1 2 20 1200 ←20+40+80+160+900
1 3 40 1180 ←40+80+160+900
1 3 80 1180 ←40+80+160+900
1 3 160 1180 ←40+80+160+900
1 4 900 900
2 5 320 960 ←320+640
2 8 640 640
3 9 100 200 ←100+100
3 9 100 200
4 6 300 300
-- OLAPSample34
select ID,sortKey,Val,
sum(Val) over(partition by ID order by sortKey desc) as FollowingSum
from RowsFollowing
order by ID,sortKey,Val;
8. 累計で扱ったように、
order byを指定して、frame_clause(フレームクロウズ)を省略すると、
デフォルトの、RANGE UNBOUNDED PRECEDINGになります。
これは、Range between unbounded preceding and current rowと同じ意味です。
そして、
order by sortKey の逆ソートである
order by sortKey desc を使っているのです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
21. Rows 2 Precedingなcount(*)とminとmax
11. 移動累計(2行前からカレント行までの累計)では、
PostgreSQL8.4で使えないRows 2 precedingなsumを代用する方法を扱いました。
今度は、Rows 2 Precedingなcount(*)とminとmaxを代用してみます。
create table Rows2Preceding(
ID integer,
sortKey integer,
Val integer);
insert into Rows2Preceding values(1,1, 10);
insert into Rows2Preceding values(1,2, 90);
insert into Rows2Preceding values(1,3, 50);
insert into Rows2Preceding values(1,4, 70);
insert into Rows2Preceding values(2,1, 80);
insert into Rows2Preceding values(2,2, 20);
insert into Rows2Preceding values(2,3, 40);
insert into Rows2Preceding values(2,4, 50);
insert into Rows2Preceding values(3,1, 0);
insert into Rows2Preceding values(3,2, 30);
insert into Rows2Preceding values(3,3, 50);
insert into Rows2Preceding values(4,1, 60);
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
count(*) over(partition by ID order by sortKey Rows 2 Preceding) as cnt,
min(Val) over(partition by ID order by sortKey Rows 2 Preceding) as minVal,
max(Val) over(partition by ID order by sortKey Rows 2 Preceding) as maxVal
from Rows2Preceding
order by ID,sortKey,Val;
ID sortKey Val cnt minVal maxVal
-- ------- --- --- ------ ------
1 1 10 1 10 10
1 2 90 2 10 90
1 3 50 3 10 90
1 4 70 3 50 90
2 1 80 1 80 80
2 2 20 2 20 80
2 3 40 3 20 80
2 4 50 3 20 50
3 1 0 1 0 0
3 2 30 2 0 30
3 3 50 3 0 50
4 1 60 1 60 60
-- OLAPSample35
select ID,sortKey,Val,
1+case when Lag1 is null then 0 else 1 end
+case when Lag2 is null then 0 else 1 end as cnt,
Least(Val,Lag1,Lag2) as minVal,
Greatest(Val,Lag1,Lag2) as maxVal
from (select ID,sortKey,Val,
Lag(Val,1) over(partition by ID order by sortKey) as Lag1,
Lag(Val,2) over(partition by ID order by sortKey) as Lag2
from Rows2Preceding) a
order by ID,sortKey,Val;
Lag関数で1行前と2行前を求めて(なければnull)、case式やLeast関数やGreatest関数で使用してます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
22. Range 2 Precedingなcount(*)とmax
Rows 2 Precedingなcount(*)とminとmaxに続いて、
Range 2 Precedingなcount(*)とmaxを求めてみます。
create table Range2Preceding(
ID integer,
sortKey integer,
Val integer);
insert into Range2Preceding values(1,1,10);
insert into Range2Preceding values(1,2,50);
insert into Range2Preceding values(1,2,60);
insert into Range2Preceding values(1,2,90);
insert into Range2Preceding values(1,3,70);
insert into Range2Preceding values(1,4,80);
insert into Range2Preceding values(1,5,20);
insert into Range2Preceding values(2,1, 0);
insert into Range2Preceding values(2,1,50);
insert into Range2Preceding values(2,4,30);
insert into Range2Preceding values(2,4,90);
insert into Range2Preceding values(2,8,20);
insert into Range2Preceding values(3,1,60);
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
count(*) over(partition by ID order by sortKey Range 2 Preceding) as cnt,
max(Val) over(partition by ID order by sortKey Range 2 Preceding) as maxVal
from Range2Preceding
order by ID,sortKey,Val;
ID sortKey Val cnt maxVal
-- ------- --- --- ------
1 1 10 1 10
1 2 50 4 90
1 2 60 4 90
1 2 90 4 90
1 3 70 5 90
1 4 80 5 90
1 5 20 3 80
2 1 0 2 50
2 1 50 2 50
2 4 30 2 90
2 4 90 2 90
2 8 20 1 20
3 1 60 1 60
-- 相関サブクエリを使う方法
select ID,sortKey,Val,
(select count(*)
from Range2Preceding b
where b.ID = a.ID
and b.sortKey between a.sortKey-2
and a.sortKey) as cnt,
(select max(b.Val)
from Range2Preceding b
where b.ID = a.ID
and b.sortKey between a.sortKey-2
and a.sortKey) as maxVal
from Range2Preceding a
order by ID,sortKey,Val;
-- 自己結合を使う方法
select a.ID,a.sortKey,a.Val,count(*) as cnt,max(b.Val) as maxVal
from Range2Preceding a Join Range2Preceding b
on a.ID=b.ID
and b.sortKey between a.sortKey-2
and a.sortKey
group by a.ID,a.sortKey,a.Val
order by a.ID,a.sortKey,a.Val;
分析関数のrange指定は、相関サブクエリや自己結合を使えば代用できます。
データ構造によっては、Lag関数やLead関数を何度も使って、必要な可能性のある行の値を全て取得してから、
sum関数やcount関数ならばcase式を、
min関数ならばcase式とLeast関数を、
max関数ならばcase式とGreatest関数を使う。といった手段でも代用できます。
また、16. 次の入社日を求めるのような特殊な代用パターンもあります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
23. Last_Value関数のignore nulls(entire)
create table IgnoreNull(
ID integer,
sortKey integer,
Val integer);
insert into IgnoreNull values(1,1, 555);
insert into IgnoreNull values(1,2,null);
insert into IgnoreNull values(1,3, 111);
insert into IgnoreNull values(1,4,null);
insert into IgnoreNull values(2,1, 888);
insert into IgnoreNull values(2,2, 222);
insert into IgnoreNull values(2,3,null);
insert into IgnoreNull values(2,4, 444);
insert into IgnoreNull values(3,1, 777);
insert into IgnoreNull values(3,2,null);
insert into IgnoreNull values(3,3,null);
insert into IgnoreNull values(3,4,null);
insert into IgnoreNull values(3,5, 333);
insert into IgnoreNull values(4,1,null);
insert into IgnoreNull values(4,2,null);
Oracle10gやDB2 V9.5の分析関数では、First_Value関数やLast_Value関数でignore nullsを指定できます。
Last_Value(値 ignore nulls) over句 が基本的な使い方ですが、
Last_Value(case when 条件 then 値 end ignore nulls) over句 というふうに、
case式を組み合わせて使うほうが多いです。
下記の、ignore nullsを指定したOracleのSQLと同じ結果を、PostgreSQL8.4で取得してみます。
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
Last_Value(Val ignore nulls)
over(partition by ID
order by sortKey Rows between Unbounded Preceding
and Unbounded Following) as LastVal1
from IgnoreNull
order by ID,sortKey;
ID sortKey Val LastVal1
-- ------- ---- --------
1 1 555 111
1 2 null 111
1 3 111 111
1 4 null 111
2 1 888 444
2 2 222 444
2 3 null 444
2 4 444 444
3 1 777 333
3 2 null 333
3 3 null 333
3 4 null 333
3 5 333 333
4 1 null null
4 2 null null
-- OLAPSample36
select ID,sortKey,Val,
Last_Value(Val) over(partition by ID
order by case when Val is not null
then sortKey end nulls first
Rows between Unbounded Preceding
and Unbounded Following) as LastVal1
from IgnoreNull
order by ID,sortKey;
case式とnulls first指定で、Valがnullな行が最後にならないようにしてます。
-- OLAPSample36の相関サブクエリを使った代替方法
select ID,sortKey,Val,
(select b.Val
from IgnoreNull b
where b.ID = a.ID
and b.Val is not null
order by b.sortKey desc Limit 1) as LastVal1
from IgnoreNull a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
24. Last_Value関数のignore nulls(until)
前問を少し変更して、今度は、その行までを対象としたLast_Value関数(ignore nulls)を模倣します。
-- 模倣対象のOracleのSQL
select ID,sortKey,Val,
Last_Value(Val ignore nulls)
over(partition by ID order by sortKey) as LastVal2
from IgnoreNull
order by ID,sortKey;
ID sortKey Val LastVal2
-- ------- ---- --------
1 1 555 555
1 2 null 555
1 3 111 111
1 4 null 111
2 1 888 888
2 2 222 222
2 3 null 222
2 4 444 444
3 1 777 777
3 2 null 777
3 3 null 777
3 4 null 777
3 5 333 333
4 1 null null
4 2 null null
-- OLAPSample37
select ID,sortKey,Val,
max(case sortKey when maxSortKey then Val end)
over(partition by ID,maxSortKey) as LastVal2
from (select ID,sortKey,Val,
max(case when Val is not null then sortKey end)
over(partition by ID order by sortKey) as maxSortKey
from IgnoreNull) a
order by ID,sortKey;
最初にインラインビューでwindow関数のmax関数を使って、
sortKeyの昇順で、その行以前で最後にValがnullでなかったsortKeyを求めてます。
次に、そのsortKeyの行のValの値を、Pivotクエリでよく使われるmax関数とcase式を組み合わせる手法で求めてます。
-- OLAPSample37の相関サブクエリを使った代替方法
select ID,sortKey,Val,
(select b.Val
from IgnoreNull b
where b.ID = a.ID
and b.sortKey <= a.sortKey
and b.Val is not null
order by b.sortKey desc Limit 1) as LastVal2
from IgnoreNull a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージ(第1段階)は、こうなります。partition by IDに対応する赤線を引いてます。
脳内のイメージ(最終段階)は、こうなります。partition by ID,maxSortKeyに対応する紫線を引いてます。
25. Lag関数とLead関数のignore nulls
DB2 V9.5では、Lag関数とLead関数でignore nullsを指定することができます。
PostgreSQL8.4で、同じ結果を取得してみます。
create table ignLagLead(
SortKey integer,
Val integer);
insert into ignLagLead values
( 1,null),
( 2, 2),
( 3,null),
( 5, 4),
( 9,null),
(11, 6),
(12,null),
(14,null),
(16, 5),
(17,null),
(25, 3),
(26,null),
(27, 4),
(30,null);
-- 模倣対象のDB2のSQL
select SortKey,Val,
Lag (Val,2,999,'IGNORE NULLS') over(order by SortKey) as Lag2,
Lead(Val,2,999,'IGNORE NULLS') over(order by SortKey) as Lead2
from ignLagLead;
SortKey Val Lag2 Lead2
------- ---- ---- -----
1 null 999 4
2 2 999 6
3 null 999 6
5 4 999 5
9 null 2 5
11 6 2 3
12 null 4 3
14 null 4 3
16 5 4 4
17 null 6 4
25 3 6 999
26 null 5 999
27 4 5 999
30 null 3 999
-- OLAPSample38
with tmp as(
select SortKey,Val,
case when Val is not null
then Lag (Val,1,999) over(partition by Val is null order by SortKey) end as Lag1WK,
case when Val is not null
then Lag (Val,2,999) over(partition by Val is null order by SortKey) end as Lag2WK,
case when Val is not null
then Lead(Val,1,999) over(partition by Val is null order by SortKey) end as Lead1WK,
case when Val is not null
then Lead(Val,2,999) over(partition by Val is null order by SortKey) end as Lead2WK,
max(case when Val is not null then SortKey end) over(order by SortKey) as lasKey,
min(case when Val is not null then SortKey end) over(order by SortKey desc) as firKey
from ignLagLead)
select SortKey,Val,
case when Val is not null then Lag2WK
when lasKey is null then 999
else max(Lag1WK) over(partition by lasKey) end as Lag2,
case when Val is not null then Lead2WK
when firKey is null then 999
else max(Lead1WK) over(partition by firKey) end as Lead2
from tmp
order by SortKey;
インラインビューで、
nullを対象外としての、1行前をLag1WK、2行前をLag2WKとして、
nullを対象外としての、1行後をLead1WK、2行後をLead2WKとして、
SortKeyの昇順で、その行以前で最後にValがnullでなかったsortKeyをlasKeyとして、
SortKeyの昇順で、その行以降で最初にValがnullでないsortKeyをfirKeyとして、
求めてます。
次に、検索Case式で場合分けしつつ、
そのlasKeyおよびfirKeyの行のValの値を、max関数で求めてます。
-- OLAPSample38の相関サブクエリを使った代替方法
select SortKey,Val,
coalesce((select b.Val
from ignLagLead b
where b.Val is not null
and b.SortKey < a.SortKey
order by b.SortKey desc Limit 1 offset 1),999) as Lag2,
coalesce((select b.Val
from ignLagLead b
where b.Val is not null
and b.SortKey > a.SortKey
order by b.SortKey Limit 1 offset 1),999) as Lead2
from ignLagLead a
order by SortKey;
26. update文でwindow関数の値に更新
DB2 V9.5では、update文のset句で、分析関数の値を使えます。PostgreSQL8.4で、同じ更新を行ってみます。
create table updTes(
ID integer not null,
Val integer not null,
seq integer,
primary key(ID,Val));
insert into updTes values
(1, 1,null),
(1, 2,null),
(1, 4,null),
(1, 8,null),
(2, 16,null),
(2, 32,null),
(2, 64,null),
(2, 128,null),
(3, 1,null),
(3, 20,null),
(3, 30,null),
(4, 100,null),
(4, 123,null),
(4, 150,null);
-- 模倣対象のDB2のSQL
update updTes
set seq = Row_Number() over(partition by ID order by Val);
select ID,Val,seq from updTes;
ID Val seq
-- --- ---
1 1 1
1 2 2
1 4 3
1 8 4
2 16 1
2 32 2
2 64 3
2 128 4
3 1 1
3 20 2
3 30 3
4 100 1
4 123 2
4 150 3
-- OLAPSample39
update updTes a
set seq = b.rn
from (select ID,Val,
Row_Number() over(partition by ID order by Val) as rn
from updtes) b
where a.ID=b.ID
and a.Val=b.Val;
インラインビューでRow_Number関数を使って更新値を求め、IDとValをキーとして内部結合させてます。
PostgreSQLのマニュアル UPDATE
-- OLAPSample39の相関サブクエリを使った代替方法
update updtes a
set seq = (select count(*)+1
from updtes b
where b.ID = a.ID
and b.Val < a.Val);
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ちなみに、下記のように、
MySQLのUpdate文でのLimit句指定を模倣することもできます。
-- OLAPSample40
update updTes a
set seq = 200
from (select ID,Val,
Row_Number() over(order by Val) as rn
from updtes) b
where a.ID=b.ID
and a.Val=b.Val
and b.rn <= 2;
第3部 window関数の参考リソース