OracleSQLパズル
明智重蔵のブログ
明智重蔵のTwitter
記事(CodeZine)
DB2 SQLパズル
DB2の分析関数の使用例
概要
Club DB2の「第98回 【SQL上級編】 DB2の分析関数の使用例」で、
私が講師をさせていただきまして、そのまとめページです。
分析関数とmodel句や、PostgreSQL8.4新機能 window関数や、分析関数の衝撃の内容をふまえつつ、
Oracleコミュニティでよく見かけるSQLの問題を、DB2 V9.7で分析関数を使用して解いたり、
Oracle11gR2の分析関数をDB2 V9.7で模倣するSQLを紹介しながら、私のSQLの思考法と脳内のイメージを解説しました。
単純なサンプル中心の勉強会にしました。
プログラム
1. 分析関数とは
select句とorder by句で使うことができる。order by句で使うことは、ほとんどない。
DB2 V6あたりから使用可能
Oracle9i以降
PostgreSQL8.4
SQLServer2005以降
などでも使用可能
分析関数は標準SQLなので、いずれは、他のDBでも使えるようになるはずです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数のメリット
自己結合や相関サブクエリを使ったり、親言語(JavaやRubyなど)や表関数で、
求めていた結果を、SQLで容易に求めることができるようになります。
帳票作成やデータ分析で特に使います。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数の使用頻度
最頻出 count max min sum Row_Number
頻出 Lag Lead Rank dense_rank
たまに First_Value Last_Value avg
2. select文の件数取得
select文の結果の件数が欲しいといったことは、結構あります。そんな時に使うのが、分析関数のcount関数です。
create table TestTable(ID integer,Val integer);
insert into TestTable values(1,10),
(1,20),
(2,10),
(2,30),
(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) as maxVal,
count(*) over() as recordCount
from TestTable
group by ID
having max(Val) = 20;
ID maxVal recordCount
-- ------ -----------
1 20 1
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
fetch first句があるselect文の結果の件数を求めるには、
インラインビューを使うか、Least関数を使う必要があります。
distinct指定があるselect文の結果の件数を求めるには、
インラインビューを使うか、count(*) over()の代わりにdense_rank関数と逆ソートを使う必要があります。
DB2の使い方 SQL編.pdfの24ページに書いてあるように、
DB2のselect文は、
１ from句
２ where句
３ group by句
４ having句
５ select句
６ distinct
７ order by句
８ fetch first句
の順に動作するからです。
-- OLAPSample4
select ID,count(*) over() as recordCount
from TestTable
order by ID fetch first 2 rows only;
ID recordCount
-- -----------
1 5
1 5
-- OLAPSample5
select ID,Least(2,count(*) over()) as recordCount
from TestTable
order by ID fetch first 2 rows only;
ID recordCount
-- -----------
1 2
1 2
create table disT(ColA integer,ColB integer);
insert into disT values(1,null),
(1, 3),
(1, 3),
(2,null),
(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 fetch first 2 rows only;
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 fetch first 2 rows only)
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),
(3,4);
-- case1
delete from tableB;
insert into tableB values(1,2);
-- case2
delete from tableB;
insert into tableB values(1,2),
(3,4),
(5,6);
-- case3
delete from tableB;
-- case4
delete from tableB;
insert into tableB values(2,2),
(3,3);
-- case5
delete from tableB;
insert into tableB values(1,2),
(3,4);
-- tableAとtableBのデータが同じか確認するselect文
select a.*,count(*) over() from tableA a
except all
select a.*,count(*) over() from tableB a;
上記の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の場合しかないからです。
要素数は、分析関数のcount関数を使えば求まりますし、
包含関係は、差集合が空集合かどうかを調べれば分かります。
4. 最大値の行の取得
実によく見かける、定番問題です。IDごとに、Valが最大値の行を取得します。
create table TestTable2(ID integer,Val integer,extraCol char(1));
insert into TestTable2 values(1,10,'A'),
(1,20,'B'),
(2,10,'C'),
(2,30,'D'),
(2,50,'E'),
(3,70,'F'),
(3,70,'G');
-- 単純に、IDごとのValの最大値が欲しいなら、これで可
select ID,max(Val)
from TestTable2
group by ID;
-- OLAPSample9
select ID,Val,extraCol
from (select ID,Val,extraCol,
max(Val) over(partition by ID) as maxVal
from TestTable2)
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
分析関数が使えるのは、select句かorder by句です。
なので、分析関数の結果をwhere句で使うには、インラインビューを使う必要があります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
5. 順位を付ける
順位や連番を付けたい時に使うのが、分析関数のRow_Number関数,rank関数,dense_rank関数です。
create table TestTable3(ID integer,score integer);
insert into TestTable3 values(1,100),
(1,100),
(1, 90),
(1, 80),
(2,100),
(2, 70),
(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の相関サブクエリを使った代替方法 (Row_Number関数以外)
select ID,score,
(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,"rank";
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
6. 最大値の行の取得(ソートキーが複数)
4. 最大値の行の取得では、
ソートキーが1つだったので、分析関数のmax関数を使いましたが、
ソートキーが複数だと、順位を付ける関数を使う必要があります。
IDごとで、sortKey1が最大の行の中でsortKey2が最大の行を取得します。
create table multiSortKey(ID integer,sortKey1 integer,sortKey2 integer,extraCol char(3));
insert into multiSortKey values(1,10, 2,'AAA'),
(1,10, 3,'BBB'),
(1,30, 1,'CCC'),
(2,20, 1,'DDD'),
(2,50, 2,'EEE'),
(2,50, 2,'FFF'),
(3,60, 1,'GGG'),
(3,60, 2,'HHH'),
(3,60, 3,'III'),
(4,10,20,'JJJ');
-- 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)
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) in(select b.sortKey1,b.sortKey2
from multiSortKey b
where b.ID = a.ID
order by b.sortKey1 desc,b.sortKey2 desc
fetch first 1 rows only)
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 char(2),sortKey integer,Val integer);
insert into TestTable4 values('AA',1,10),
('AA',3,20),
('AA',5,60),
('AA',7,30),
('BB',2,40),
('BB',4,80),
('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
Lag関数とLead関数は、DB2 V9.5からの機能なので、古いDB2では、
Oracle開発者のためのDB2 UDB SQLリファレンスの92ページと95ページのサンプルのように、
Rows指定で代用する必要があります。
-- OLAPSample12のRows指定での代用方法
select ID,sortKey,Val,
max(Val) over(partition by ID order by sortKey
Rows between 1 Preceding
and 1 Preceding) as Prev,
max(Val) over(partition by ID order by sortKey
Rows between 1 Following
and 1 Following) as Next
from TestTable4
order by ID,sortKey;
-- 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 fetch first 1 rows only) as Prev,
(select b.Val from TestTable4 b
where b.ID=a.ID and b.sortKey > a.sortKey
order by b.sortKey fetch first 1 rows only) 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で、脳内で赤線を引くと分かりやすいです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
キーブレイクの事前検知は、分析関数のcount関数を使ってもいいですが、
否定条件となってしまったため、分かりにくい気がします。
-- OLAPSample14
select ID,sortKey,Val,
count(*) over(partition by ID order by sortKey
Rows between 1 preceding
and 1 preceding) as IsNotKeyBreaked,
count(*) over(partition by ID order by sortKey
Rows between 1 following
and 1 following) as willNotKeyBreak
from TestTable4
order by ID,sortKey;
ID sortKey Val IsNotKeyBreaked willNotKeyBreak
-- ------- --- --------------- ---------------
AA 1 10 0 1
AA 3 20 1 1
AA 5 60 1 1
AA 7 30 1 0
BB 2 40 0 1
BB 4 80 1 1
BB 6 50 1 0
分析関数のorder by以降は、下記のように解釈すると分かりやすいと思います。
order by sortKey -- sortKeyの昇順で、
Rows between -- 行の範囲は、
1 preceding -- 1行前から
and 1 preceding -- 1行前まで
order by sortKey -- sortKeyの昇順で、
Rows between -- 行の範囲は、
1 following -- 1行先から
and 1 following -- 1行先まで
8. 累計
帳票で累計を求めたい時に使うのが、order byを指定した、分析関数のsum関数です。
create table ValT(ID char(2),sortKey integer,Val integer);
insert into ValT values('AA',1,10),
('AA',2,20),
('AA',3,40),
('AA',4,80),
('BB',1,10),
('BB',2,30),
('BB',3,90),
('CC',1,50),
('CC',2,60),
('CC',2,60);
-- OLAPSample15
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
-- OLAPSample15の相関サブクエリを使った代替方法
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;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数でRows指定やRange指定を省略した時のデフォルトは、
Range between unbounded preceding and current rowとなるようです。
下記のように解釈すると分かりやすいと思います。
order by sortKey -- sortKeyの昇順で、
Range between -- 値の範囲は、
unbounded preceding -- 前方は、際限なく、
and current row -- 後方は、カレント行まで
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
9. 移動累計
OracleSQLパズルのアクセス解析で、移動平均で検索してくる方は、結構多いです。3日移動累計を求めてみます。
create table idouT(day1 date,Val integer);
insert into idouT values(date '2010-01-16', 10),
(date '2010-01-17', 20),
(date '2010-01-20', 60),
(date '2010-01-21', 100),
(date '2010-01-22', 200),
(date '2010-01-23', 600),
(date '2010-01-24',1000),
(date '2010-01-30',2000);
-- OLAPSample16
select day1,Val,
sum(Val) over(order by day1 rows 2 preceding) as moveSum1,
sum(Val) over(order by days(day1) range 2 preceding) as moveSum2
from idouT
order by day1;
day1 Val moveSum1 moveSum2
---------- ---- -------- --------
2010-01-16 10 10 10
2010-01-17 20 30 30
2010-01-20 60 90 60
2010-01-21 100 180 160
2010-01-22 200 360 360
2010-01-23 600 900 900
2010-01-24 1000 1800 1800
2010-01-30 2000 3600 2000
-- OLAPSample16の相関サブクエリを使った代替方法
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 2+1) as moveSum1,
(select sum(b.Val)
from idouT b
where days(b.day1) between days(a.day1)-2 and days(a.day1)) as moveSum2
from idouT a
order by day1;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数のorder by以降の、省略時の仕様として、
order by day1 rows 3 precedingは、
order by day1 rows between 3 preceding
and current row
と同じ扱いとなります。
同様に、
order by days(day1) range 3 precedingは、
order by days(day1) range between 3 preceding
and current row
と同じ扱いとなります。
rows指定とrange指定の違いは、
rowsは、ソートキーで並べた時の、前もしくは後ろの行数(Rows)の指定であり、
rangeは、ソートキーが、どれだけ前もしくは後ろかの指定であることです。
それぞれ、下記のように解釈すると分かりやすいと思います。
order by day1 -- day1の昇順で、
rows between -- 行の範囲は、
3 preceding -- 3行前から
and current row -- カレント行まで
order by days(day1) -- days(day1)の昇順で、
range between -- 値の範囲は、
3 preceding -- 3日前から
and current row -- カレント行まで
なお、day1はdate型ですので、days関数を使って、
西暦1年1月1日からの経過日数といった数値型に変換してます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
10. First_ValueとLast_Value
指定したソートキーでの、最初の行の値を求めるのが、First_Value
指定したソートキーでの、最後の行の値を求めるのが、Last_Value
となります。
create table FirstLast(ID integer,SortKey integer,Val integer);
insert into FirstLast values(1,10,666),
(1,30,333),
(1,40,222),
(1,50,444),
(2,20,777),
(2,25,111),
(2,27,555),
(3,60,999),
(3,61,888);
-- OLAPSample17
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
from FirstLast
order by ID,SortKey;
ID SortKey Val FirVal LastVal
-- ------- --- ------ -------
1 10 666 666 444
1 30 333 666 444
1 40 222 666 444
1 50 444 666 444
2 20 777 777 555
2 25 111 777 555
2 27 555 777 555
3 60 999 999 888
3 61 888 999 888
-- OLAPSample17の相関サブクエリを使った代替方法
select ID,SortKey,Val,
(select b.Val from FirstLast b
where b.ID=a.ID order by b.SortKey fetch first 1 rows only) as FirVal,
(select b.Val from FirstLast b
where b.ID=a.ID order by b.SortKey desc fetch first 1 rows only) as LastVal
from FirstLast a
order by ID,SortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
11. First_ValueとLast_Value(IGNORE NULLS)
create table IgnoreNull(ID char(2),sortKey integer,Val integer);
insert into IgnoreNull values('aa',1,null),
('aa',2, 555),
('aa',3, 111),
('aa',4,null),
('bb',1, 888),
('bb',2, 222),
('bb',3,null),
('bb',4, 444),
('cc',1, 777),
('cc',2,null),
('cc',3,null),
('cc',4, 333),
('dd',1,null),
('dd',2,null);
First_Value関数やLast_Value関数の第2引数に、'IGNORE NULLS'を指定できます。
Last_Value(値,'IGNORE NULLS') over句 が基本的な使い方ですが、
Last_Value(case when 条件 then 値 end,'IGNORE NULLS') over句 というふうに、
case式を組み合わせて使うほうが多いです。
IDごとで、sortKeyの昇順で、最初にNULLでないValと、最後のNULLでないValを求めてみます。
-- OLAPSample18
select ID,sortKey,Val,
First_Value(Val,'IGNORE NULLS')
over(partition by ID
order by sortKey Rows between Unbounded Preceding
and Unbounded Following) as FirstVal1,
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 FirstVal1 LastVal1
-- ------- ---- --------- --------
aa 1 null 555 111
aa 2 555 555 111
aa 3 111 555 111
aa 4 null 555 111
bb 1 888 888 444
bb 2 222 888 444
bb 3 null 888 444
bb 4 444 888 444
cc 1 777 777 333
cc 2 null 777 333
cc 3 null 777 333
cc 4 333 777 333
dd 1 null null null
dd 2 null null null
-- OLAPSample18の相関サブクエリを使った代替方法
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 fetch first 1 rows only) as FirstVal1,
(select b.Val
from IgnoreNull b
where b.ID = a.ID
and b.Val is not null
order by b.sortKey desc fetch first 1 rows only) as LastVal1
from IgnoreNull a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
前問を少し変更して、今度は、
IDごとで、その行以降で最初のNULLでないVal (FirstVal2)と
その行までで最後のNULLでないVal (LastVal2)を求めてみます。
-- OLAPSample19
select ID,sortKey,Val,
First_Value(Val,'IGNORE NULLS')
over(partition by ID order by sortKey Range Unbounded Following) as FirstVal2,
Last_Value(Val,'IGNORE NULLS')
over(partition by ID order by sortKey) as LastVal2
from IgnoreNull
order by ID,sortKey;
ID sortKey Val FirstVal2 LastVal2
-- ------- ---- --------- --------
aa 1 null 555 null
aa 2 555 555 555
aa 3 111 111 111
aa 4 null null 111
bb 1 888 888 888
bb 2 222 222 222
bb 3 null 444 222
bb 4 444 444 444
cc 1 777 777 777
cc 2 null 333 777
cc 3 null 333 777
cc 4 333 333 333
dd 1 null null null
dd 2 null null null
-- OLAPSample19の相関サブクエリを使った代替方法
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 fetch first 1 rows only) as FirstVal2,
(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 fetch first 1 rows only) as LastVal2
from IgnoreNull a
order by ID,sortKey;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
12. 全称肯定,全称否定,存在肯定,存在否定
これは、US-OTNでよく見かける問題です。
・全ての行が条件を満たすか？
・全ての行が条件を満たさないか？
・少なくとも1行が条件を満たすか？
・少なくとも1行が条件を満たさないか？
といった複数行にまたがったチェックをしたい時に使います。
create table boolCheckT(ID char(2),Val integer);
insert into boolCheckT values('AA',10),
('AA',20),
('BB',10),
('BB',30),
('BB',50),
('CC',80),
('CC',90),
('DD',20),
('DD',70);
・check1 IDごとで、全ての行が Val<40 を満たすか？
・check2 IDごとで、全ての行が Val<40 を満たさないか？
・check3 IDごとで、少なくとも1つの行が Val<40 を満たすか？
・check4 IDごとで、少なくとも1つの行が Val<40 を満たさないか？
・check5 IDごとで、少なくとも1つの行が Val=10 を満たし、
かつ、少なくとも1つの行が Val=50 を満たすか？
をチェックしてみましょう。
-- OLAPSample20
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,
max(case when Val=10 then 1 else 0 end) over(partition by ID)
*max(case when Val=50 then 1 else 0 end) over(partition by ID) as chk5
from boolCheckT
order by ID,Val;
ID Val chk1 chk2 chk3 chk4 chk5
-- --- ---- ---- ---- ---- ----
AA 10 1 0 1 0 0
AA 20 1 0 1 0 0
BB 10 0 0 1 1 1
BB 30 0 0 1 1 1
BB 50 0 0 1 1 1
CC 80 0 1 0 1 0
CC 90 0 1 0 1 0
DD 20 0 0 1 1 0
DD 70 0 0 1 1 0
分析関数のmax関数やmin関数で、
条件を満たせば1、満たさなければ0を値とするcase式や、
条件を満たせば0、満たさなければ1を値とするcase式を使用してます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
全称肯定,全称否定,存在肯定,存在否定の、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
存在肯定命題の論理積なら max(case when 条件A then 1 else 0 end)
*max(case when 条件B then 1 else 0 end) = 1
真なら1、偽なら0にしておくことにより、ブール値としても使えます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
集約関数のmin関数やmax関数でも似たようなことを行うことができます。
-- 集約関数での全称肯定命題など
select ID,
substr(XMLCast(XMLGroup(',' || Val as wk) as varchar(10)),2) as ListVal,
min(case when Val<40 then 1 else 0 end) as chk1,
min(case when Val<40 then 0 else 1 end) as chk2,
max(case when Val<40 then 1 else 0 end) as chk3,
max(case when Val<40 then 0 else 1 end) as chk4,
max(case when Val=10 then 1 else 0 end)
*max(case when Val=50 then 1 else 0 end) as chk5
from boolCheckT
group by ID
order by ID;
ID ListVal chk1 chk2 chk3 chk4 chk5
-- -------- ---- ---- ---- ---- ----
AA 10,20 1 0 1 0 0
BB 10,30,50 0 0 1 1 1
CC 80,90 0 1 0 1 0
DD 20,70 0 0 1 1 0
select句よりも、下記のようにhaving句で使われることが多いです。
-- having句での存在肯定命題
select ID,
substr(XMLCast(XMLGroup(',' || Val as wk) as varchar(10)),2) as ListVal
from boolCheckT
group by ID
having max(case when Val<40 then 1 else 0 end) = 1
order by ID;
ID ListVal
-- --------
AA 10,20
BB 10,30,50
DD 20,70
13. 最頻値(モード)
create table DayWeather(day1 date,weather char(6));
insert into DayWeather values(date '2008-01-02','sunny' ),
(date '2008-01-15','snowy' ),
(date '2008-01-30','snowy' ),
(date '2008-06-01','cloudy'),
(date '2008-06-13','cloudy'),
(date '2008-06-24','rainy' ),
(date '2008-06-30','rainy' ),
(date '2008-07-02','sunny' ),
(date '2008-07-14','sunny' ),
(date '2008-07-23','sunny' ),
(date '2008-07-31','sunny' ),
(date '2008-11-10','cloudy');
最頻値(モード)を求める問題は、結構見かけます。
weatherの最頻値を求めてみます。(最頻値が複数ある場合は、複数行返すようにします)
-- 最頻値が必ず1つだけなら、これでも可
select weather,count(*) as cnt
from DayWeather
group by weather
order by count(*) desc fetch first 1 rows only;
-- OLAPSample21
select weather,cnt
from (select weather,count(*) as cnt,
max(count(*)) over() as maxCnt
from DayWeather
group by weather)
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ごとの最頻値(モード)を求めるような問題も結構見かけます。
-- OLAPSample22
select month1,weather,cnt
from (select month(day1) as month1,weather,
count(*) as cnt,
max(count(*)) over(partition by month(day1)) as maxCnt
from DayWeather
group by month(day1),weather)
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
-- OLAPSample22の相関サブクエリを使った代替方法(fetch first句を使用)
select month1,weather,count(*) as cnt
from (select month(day1) as month1,weather
from DayWeather) a
group by month1,weather
having count(*) = (select count(*)
from DayWeather b
where month(b.day1) = a.month1
group by weather
order by count(*) desc fetch first 1 rows only)
order by month1,weather;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
OLAPSample22の脳内のイメージ(第1段階)は、こうなります。
group by month(day1),weatherに対応する赤線を引いてます。
脳内のイメージ(最終段階)は、こうなります。
partition by month(day1)に対応する超極太赤線を引いてます。
14. 連続範囲の最小値と最大値 (2人旅人算)
US-OTNでよく見かける問題である、連続範囲の最小値と最大値を求めるSQLです。
分析関数の応用例として有名なものだと思います。
create table NumTable(
NumVal integer not null primary key);
insert into NumTable values( 1),
( 2),
( 3),
( 5),
( 6),
( 7),
(10),
(11),
(12),
(20),
(21);
-- OLAPSample23 旅人算の感覚を使う
select min(NumVal) as StaVal,
max(NumVal) as EndVal,count(*) as cnt
from (select NumVal,
NumVal-Row_Number() over(order by NumVal) as distance
from NumTable)
group by distance
order by StaVal;
StaVal EndVal cnt
------ ------ ---
1 3 3
5 7 3
10 12 3
20 21 2
上記のSQLでは、2人の旅人(旅人Xと旅人A)を脳内でイメージしてます。
旅人Xは、速度が1です。(Row_Number() over(order by NumVal))
旅人Aは、速度が1以上の自然数です。(NumVal)
そして、旅人Xと旅人Aの距離でグループ化してます。 (group by distance)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う脳内のイメージは、下のようになります。
中学受験の算数の旅人算の感覚を取得するには、
図表付きで解説している、このサイトの問題を解くのがオススメです。
(方程式や連立方程式を使って解いてもいいです)
今月は旅人算 - 学びの場.com
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLクックブックの「レシピ10.3 連続する値の範囲の最初と最後を求める」で紹介されている考え方を使って、
下記のSQLのように、Lag関数でシーケンス開始を検知してもいいです。
-- OLAPSample24 Lag関数でシーケンス開始を検知
select min(NumVal) as StaVal,
max(NumVal) as EndVal,count(*) as cnt
from (select NumVal,sum(willSum) over(order by NumVal) as GID
from (select NumVal,
case when Lag(NumVal) over(order by NumVal) = NumVal-1
then 0 else 1 end as willSum
from NumTable))
group by GID
order by StaVal;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数でシーケンス開始を検知する脳内のイメージは、下のようになります。
15. 連続範囲の最小値と最大値 (3人旅人算)
前問の応用であり、US-OTNでよく見かけて、SQLパズル 第2版にも掲載されている問題。
第63問 連続的なグルーピングを旅人算の感覚を使って解いてみましょう。
create table Tabibito(
sortKey integer not null,
Val char(3),
primary key(sortKey));
insert into Tabibito values( 1,'aaa'),
( 2,'aaa'),
( 3,'bbb'),
( 6,'bbb'),
( 8,'aaa'),
(20,'bbb'),
(22,'ccc'),
(23,'ccc'),
(31,'ddd'),
(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)
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では、5人の旅人(旅人Xと旅人A,B,C,D)を脳内でイメージしてます。
旅人Xは、必ず1進みます。(Row_Number() over(order by sortKey))
旅人Aは、Val='aaa'の時のみ1進みます。(Row_Number() over(partition by Val order by sortKey))
旅人Bは、Val='bbb'の時のみ1進みます。(Row_Number() over(partition by Val order by sortKey))
旅人Cは、Val='ccc'の時のみ1進みます。(Row_Number() over(partition by Val order by sortKey))
旅人Dは、Val='ddd'の時のみ1進みます。(Row_Number() over(partition by Val order by sortKey))
そして、旅人の種類と、旅人Xとの距離でグループ化してます。 (group by Val,distance)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う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))
group by GID,Val
order by GID;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Lag関数でシーケンス開始を検知するSQLの脳内のイメージは、こうなります。
sum(willSum) over(order by sortKey) as GIDに対応する黄緑線と、
group by GID,Valに対応する赤線を引いてます。
16. 次の入社日を求める
SQLクックブックの「レシピ8.7 現在のレコードと次のレコードの日付の差を算出する」で扱われている、
Lead関数の応用問題である、次の入社日を求める問題を解いてみます。
重複を考慮して最小上界を求める問題と言えます。
create table emp2(
ename char(6) not null primary key,
hireDate date);
insert into emp2 values('CLARK' ,date '1981-06-09'),
('ant' ,date '1981-11-17'),
('choi' ,date '1981-11-17'),
('jim' ,date '1981-11-17'),
('joe' ,date '1981-11-17'),
('MILLER',date '1982-01-23'),
('SCOTT' ,date '1999-10-30'),
('TIGER' ,date '1999-10-30'),
('JANE' ,date '2005-12-31');
-- OLAPSample27
select ename,hireDate,
min(hireDate)
over(order by days(hireDate)
range between 1 following
and Unbounded Following) as nextHireDate
from emp2
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;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
17. 次の次の入社日を求める
前問の応用問題として、今度は、次の次の入社日を、
下記のようにRow_Number関数とLead関数の'IGNORE NULLS'を組み合わせて求めてみます。
-- OLAPSample28
select ename,rn,hireDate,
Lead(case rn when 1 then hireDate end,2,cast(null as date),'IGNORE NULLS')
over(order by hireDate,ename) as next2HireDate
from (select ename,hireDate,
Row_Number() over(partition by hireDate order by ename) as rn
from emp2)
order by hireDate,ename;
ename rn hireDate next2HireDate
------ -- ---------- -------------
CLARK 1 1981-06-09 1982-01-23
ant 1 1981-11-17 1999-10-30
choi 2 1981-11-17 1999-10-30
jim 3 1981-11-17 1999-10-30
joe 4 1981-11-17 1999-10-30
MILLER 1 1982-01-23 2005-12-31
SCOTT 1 1999-10-30 null
TIGER 2 1999-10-30 null
JANE 1 2005-12-31 null
-- OLAPSample28の相関サブクエリを使った代替方法
select ename,hireDate,
(select min(b.hireDate)
from emp2 b
where b.hireDate > (select min(c.hireDate)
from emp2 c
where c.hireDate > a.hireDate)) as next2HireDate
from emp2 a
order by hireDate,ename;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
下記のように、dense_rank関数とrangeスキャンを組み合わせてもいいです。
-- OLAPSample29
select ename,rn,hireDate,
max(hireDate)
over(order by rn range between 2 following
and 2 following) as next2HireDate
from (select ename,hireDate,
dense_rank() over(order by hireDate) as rn
from emp2)
order by hireDate,ename;
ename rn hireDate next2HireDate
------ -- ---------- -------------
CLARK 1 1981-06-09 1982-01-23
ant 2 1981-11-17 1999-10-30
choi 2 1981-11-17 1999-10-30
jim 2 1981-11-17 1999-10-30
joe 2 1981-11-17 1999-10-30
MILLER 3 1982-01-23 2005-12-31
SCOTT 4 1999-10-30 null
TIGER 4 1999-10-30 null
JANE 5 2005-12-31 null
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
18. 2日前の値を求める
7. 前後の値で説明したように、
Lag関数を使えば、指定したソートキーの順序での、1行前の行の値や2行前の行の値などが取得できます。
分析関数のrange指定を使えば、
指定した列の値(数値型)が、1小さい行の値や、2小さい行の値などが取得できますし、
指定した列の値(日付型)が、1日前の行の値や、2日前の行の値なども取得できます。
create table OLAPRangeT(Day1 date,Val integer);
insert into OLAPRangeT values(date '2010-10-26', 10),
(date '2010-10-28', 20),
(date '2010-10-31', 40),
(date '2010-11-03', 80),
(date '2010-11-04',120),
(date '2010-11-05',250),
(date '2010-11-06',380),
(date '2010-11-11',590);
Day1が2日前の行のValを取得してみます。
-- OLAPSample30
select Day1,Val,
max(Val) over(order by days(Day1)
range between 2 Preceding
and 2 Preceding) as ValBefore2day
from OLAPRangeT
order by Day1;
Day1 Val ValBefore2day
---------- --- -------------
2010-10-26 10 null
2010-10-28 20 10
2010-10-31 40 null
2010-11-03 80 null
2010-11-04 120 null
2010-11-05 250 80
2010-11-06 380 120
2010-11-11 590 null
分析関数のorder by以降は、下記のように解釈すると分かりやすいでしょう。
order by days(Day1) --days(Day1)の昇順で、
range between --値の範囲は、
2 Preceding --小さいほうは、2小さい行から
2 Preceding --大きいほうは、2小さい行まで
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
19. update文で分析関数の値に更新
分析関数を使ったselect文を扱ってきましたが、
分析関数を使ったupdate文を扱ってみます。
create table updTes1(ID integer,Val integer,seq integer);
create table updTes2(ID integer,Val integer,seq integer);
create table updTes3(ID integer,Val integer,seq integer);
create table updTes4(ID integer,Val integer,seq integer);
insert into updTes1 values(1, 1,null),
(1, 2,null),
(1, 7,null),
(2, 10,null),
(2, 40,null),
(3, 100,null),
(3, 130,null),
(4, 600,null),
(4, 650,null);
insert into updTes2 select * from updTes1;
insert into updTes3 select * from updTes1;
insert into updTes4 select * from updTes1;
seqをIDごとのValの昇順な連番にupdateしてみます。
set句で分析関数を使用すると、分析関数の結果でupdateすることができます。
-- OLAPSample31
update updTes1
set seq = Row_Number() over(partition by ID order by Val);
updTes1の更新結果
ID Val seq
-- --- ---
1 1 1
1 2 2
1 7 3
2 10 1
2 40 2
3 100 1
3 130 2
4 600 1
4 650 2
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
select文でwhere句の後に、分析関数が評価されるのと同様に、
update文でもwhere句の後に、set句の分析関数が評価されることが、下記のupdate文から分かります。
-- OLAPSample32
update updTes2
set seq = Row_Number() over(order by Val)
where ID in(2,4);
updTes2の更新結果
ID Val seq
-- --- ----
1 1 null
1 2 null
1 7 null
2 10 1
2 40 2
3 100 null
3 130 null
4 600 3
4 650 4
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
グレー線がwhere ID in(2,4)のイメージで
黄緑線と青色がRow_Number() over(order by Val)のイメージです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL Reference, Volume 2 (DB2SQLRefVol2-db2s2j971.pdf)の1330ページのサンプルのように、
分析関数を使用した、更新可能なビュー(UpdatableView)を使ってupdateすることもできます。
-- OLAPSample33
update (select ID,seq,
Row_Number() over(order by Val) as rn
from updTes3)
set seq = rn
where ID in(2,4);
updTes3の更新結果
ID Val seq
-- --- ----
1 1 null
1 2 null
1 7 null
2 10 4
2 40 5
3 100 null
3 130 null
4 600 8
4 650 9
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ちなみに、下記のように、MySQLのUpdate文でのLimit句指定を模倣することもできます。
-- OLAPSample34
update (select seq,
Row_Number() over(order by Val) as rn
from updTes4)
set seq = 999
where rn <= 2;
updTes4の更新結果
ID Val seq
-- --- ----
1 1 999
1 2 999
1 7 null
2 10 null
2 40 null
3 100 null
3 130 null
4 600 null
4 650 null
-- OLAPSample34のfetch firstを使った代替方法
update (select seq
from updTes4
order by seq fetch first 2 rows only)
set seq = 999;
20. delete文で重複行を削除
分析関数を使ったupdate文の次は、
分析関数を使ったdelete文を扱ってみます。
Oracle開発者のためのDB2 UDB SQLリファレンスの362ページのサンプルのように、
分析関数を使用した、削除可能なビュー(DeletableView)を使ってdeleteすることができます。
create table OLAPDeleteT(Val1 integer,Val2 integer);
insert into OLAPDeleteT values(1,1),
(1,2),
(1,2),
(1,2),
(1,2),
(2,1),
(2,1);
delete文で重複行を1行にしてみます。
-- OLAPSample35
delete from (select Row_Number() over(partition by Val1,Val2) as rn
from OLAPDeleteT)
where rn > 1;
削除結果
Val1 Val2
---- ----
1 1
1 2
2 1
Row_Number関数でorder byを省略すると、テキトーなソートで連番を付与しますが、
この場合は、テキトーなソートでも困りません。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition by Val1,Val2で赤線を引いてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ちなみに、下記のように、MySQLのDelete文でのLimit句指定を模倣することもできます。
create table ManyRows(Val integer);
insert into ManyRows
with rec(Val) as(
values(1)
union all
select Val+1 from rec
where Val+1 <= 100)
select Val from rec;
-- OLAPSample36
delete from (select Row_Number() over(order by Val) as rn
from ManyRows)
where rn <= 90;
削除結果
Val
---
91
92
93
94
95
96
97
98
99
100
-- OLAPSample36のfetch firstを使った代替方法
delete from (select 1
from ManyRows
order by Val fetch first 90 rows only);
第2部 Oracle11gR2の分析関数をDB2 V9.7で模倣
21. 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オプションが使えます。
DB2 V9.7で、下記の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
-- OLAPSample37
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;
-- OLAPSample37の相関サブクエリを使った代替方法
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 + 正順位 + 逆順位 になることをふまえてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition by IDで赤線を引いてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
下記のようにdense_rank関数の結果の最大値を取得してもいいです。
-- OLAPSample38
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)
order by ID,Val;
ちなみに、count(distinct Val) は、Valがnullだとカウントしませんので、
Valがnullの場合も考慮するなら、下記のように存在肯定命題を使う必要があります。
-- OLAPSample39
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;
22. count(distinct Val) over(order by sortKey)
Oracle11gR2でも文法エラーになるのですが、count(distinct Val) over(order by sortKey) を模倣してみます。
これは、US-OTNで結構見かける問題で、訪問者が、リピーターか新規かを見分ける時に使うSQLのようです。
create table VisiterT(sortKey integer,Visit char(4));
insert into VisiterT values(10,'AAAA'),
(20,'BBBB'),
(30,'AAAA'),
(40,'BBBB'),
(50,'CCCC'),
(60,'CCCC'),
(70,'DDDD'),
(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
-- OLAPSample40
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)
order by sortKey;
-- OLAPSample40の相関サブクエリを使った代替方法
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)に対応する黄緑線を引いてます。
23. sum(Val) Keep(Dense_Rank Last order by sortKey) over(partition by ID)
Oracleでは、集合関数や分析関数の、count関数やmax関数やsum関数などで、Keepが使えます。
DB2 V9.7で、同じ結果を取得してみます。
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
-- OLAPSample41
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)
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
-- OLAPSample42
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)
order by ID,sortKey1,sortKey2,Val;
order by sortKey1 asc ,sortKey2 asc の逆ソートである
order by sortKey1 desc,sortKey2 desc を使ってます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。partition by IDに対応する赤線を引いてます。
24. Range '10' minute Precedingなcount(*)
Oracleの分析関数では、date型やTimeStamp型をソートキーとしてのrange指定で、InterVal型での範囲指定ができます。
create table OracleRangeMinute(KindDay TimeStamp);
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:09:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:20:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:30:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:35:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:36:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:45:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 22:46:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 23:50:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-09 23:55:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-10 00:01:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-10 00:02:00');
insert into OracleRangeMinute values(TimeStamp '2010-01-10 00:12:00');
-- 模倣対象のOracleのSQL
select KindDay,
count(*) over(order by KindDay
range interVal '10' minute Preceding) as cnt
from OracleRangeMinute
order by KindDay;
KindDay cnt
-------------- ---
10-01-09 22:09 1 ← 22:09
10-01-09 22:20 1 ← 22:20
10-01-09 22:30 2 ← 22:20と22:30
10-01-09 22:35 2 ← 22:30と22:35
10-01-09 22:36 3 ← 22:30と22:35と22:36
10-01-09 22:45 3 ← 22:35と22:36と22:45
10-01-09 22:46 3 ← 22:36と22:45と22:46
10-01-09 23:50 1 ← 23:50
10-01-09 23:55 2 ← 23:50と23:55
10-01-10 00:01 2 ← 23:55と00:01
10-01-10 00:02 3 ← 23:55と00:01と00:02
10-01-10 00:12 2 ← 00:02と00:12
-- OLAPSample43
select KindDay,
count(*) over(order by days(KindDay)*24*60
+hour(KindDay)*60
+minute(KindDay)
range 10 Preceding) as cnt
from OracleRangeMinute
order by KindDay;
西暦1年1月1日からの経過分数に変換してrange指定を行ってます。
-- 参考select文
select KindDay,
days(KindDay)*24*60
+hour(KindDay)*60
+minute(KindDay) as totalMinute
from OracleRangeMinute
order by KindDay;
KindDay totalMinute
---------------- -----------
2010-01-09-22.09 1056645969
2010-01-09-22.20 1056645980
2010-01-09-22.30 1056645990
2010-01-09-22.35 1056645995
2010-01-09-22.36 1056645996
2010-01-09-22.45 1056646005
2010-01-09-22.46 1056646006
2010-01-09-23.50 1056646070
2010-01-09-23.55 1056646075
2010-01-10-00.01 1056646081
2010-01-10-00.02 1056646082
2010-01-10-00.12 1056646092
なお、秒も考慮するならbigInt型にキャストする必要があります。
Integer型の最大値は2147483647ですが、bigInt型の最大値は9223372036854775807なのです。
西暦1年1月1日からの経過秒数に変換してrange指定を行ってます。
-- OLAPSample44
select KindDay,
count(*) over(order by cast(days(KindDay) as bigInt)*24*60*60
+hour(KindDay)*60*60
+minute(KindDay)*60
+second(KindDay)
range 600 Preceding) as cnt
from OracleRangeMinute
order by KindDay;
25. Range '1' month Precedingなcount(*)
Oracleでは分析関数のrange指定で、interVal '1' monthといった使い方もできます。
末日を超えたら、自動的に末日に変換してくれます。
create table OracleRangeMonth(Val date);
insert into OracleRangeMonth values(date '2010-01-31');
insert into OracleRangeMonth values(date '2010-02-23');
insert into OracleRangeMonth values(date '2010-02-28');
insert into OracleRangeMonth values(date '2010-03-05');
insert into OracleRangeMonth values(date '2010-03-30');
insert into OracleRangeMonth values(date '2010-03-31');
insert into OracleRangeMonth values(date '2010-04-05');
insert into OracleRangeMonth values(date '2010-04-29');
insert into OracleRangeMonth values(date '2010-04-30');
insert into OracleRangeMonth values(date '2010-05-29');
insert into OracleRangeMonth values(date '2010-05-30');
insert into OracleRangeMonth values(date '2010-05-31');
-- 模倣対象のOracleのSQL
select Val,
count(*) over(order by Val
range interVal '1' month Preceding) as cnt
from OracleRangeMonth
order by Val;
Val cnt
-------- ---
10-01-31 1 ← 01-31
10-02-23 2 ← 01-31と02-23
10-02-28 3 ← 01-31と02-23と02-28
10-03-05 3 ← 02-23と02-28と03-05
10-03-30 3 ← 02-28と03-05と03-30
10-03-31 4 ← 02-28と03-05と03-30と03-31
10-04-05 4 ← 03-05と03-30と03-31と04-05
10-04-29 4 ← 03-30と03-31と04-05と04-29
10-04-30 5 ← 03-30と03-31と04-05と04-29と04-30
10-05-29 3 ← 04-29と04-30と05-29
10-05-30 3 ← 04-30と05-29と05-30
10-05-31 4 ← 04-30と05-29と05-30と05-31
-- 相関サブクエリで対応する方法
select Val,Val - 1 month as PrevMonthDay,
(select count(*)
from OracleRangeMonth b
where b.Val between a.Val - 1 month and a.Val) as cnt
from OracleRangeMonth a
order by Val;
Val PrevMonthDay cnt
---------- ------------ ---
2010-01-31 2009-12-31 1
2010-02-23 2010-01-23 2
2010-02-28 2010-01-28 3
2010-03-05 2010-02-05 3
2010-03-30 2010-02-28 3
2010-03-31 2010-02-28 4
2010-04-05 2010-03-05 4
2010-04-29 2010-03-29 4
2010-04-30 2010-03-30 5
2010-05-29 2010-04-29 3
2010-05-30 2010-04-30 3
2010-05-31 2010-04-30 4
中学受験の算数の日暦算の公式
5月10日から 6月10日までの日数は、5月の日数に等しい
6月10日から 7月10日までの日数は、6月の日数に等しい
7月10日から 8月10日までの日数は、7月の日数に等しい
8月10日から 9月10日までの日数は、8月の日数に等しい
9月10日から10月10日までの日数は、9月の日数に等しい
をふまえつつ、末日との大小比較を行いつつ、分析関数で同じ結果を取得することもできます。
DB2 V9.7では、range 計算式 precedingといった記述ができないので、
単純case式で、使用する分析関数を多分岐させてます。
-- OLAPSample45
select Val,
case greatest(day(Val),day(last_day(Val - 1 month)))
when 28 then count(*) over(order by days(Val) range 28 preceding)
when 29 then count(*) over(order by days(Val) range 29 preceding)
when 30 then count(*) over(order by days(Val) range 30 preceding)
when 31 then count(*) over(order by days(Val) range 31 preceding)
end as cnt
from OracleRangeMonth
order by Val;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracleのrange interVal '2' month Precedingなども、DB2で模倣することができますが、
少し複雑なロジックとなります。
-- 模倣対象のOracleのSQL
select Val,
count(*) over(order by Val
range interVal '2' month Preceding) as cnt
from OracleRangeMonth
order by Val;
Val cnt
-------- ---
10-01-31 1 ← 01-31
10-02-23 2 ← 01-31,02-23
10-02-28 3 ← 01-31,02-23,02-28
10-03-05 4 ← 01-31,02-23,02-28,03-05
10-03-30 5 ← 01-31,02-23,02-28,03-05,03-30
10-03-31 6 ← 01-31,02-23,02-28,03-05,03-30,03-31
10-04-05 6 ← 02-23,02-28,03-05,03-30,03-31,04-05
10-04-29 6 ← 02-28,03-05,03-30,03-31,04-05,04-29
10-04-30 7 ← 02-28,03-05,03-30,03-31,04-05,04-29,04-30
10-05-29 6 ← 03-30,03-31,04-05,04-29,04-30,05-29
10-05-30 7 ← 03-30,03-31,04-05,04-29,04-30,05-29,05-30
10-05-31 7 ← 03-31,04-05,04-29,04-30,05-29,05-30,05-31
-- OLAPSample46
select Val,
case day(Val) - day(last_day(Val - 2 month))
when 3 then count(*) over(order by 100*(12*year(Val)+month(Val))+Day(Val) range 203 preceding)
when 2 then count(*) over(order by 100*(12*year(Val)+month(Val))+Day(Val) range 202 preceding)
when 1 then count(*) over(order by 100*(12*year(Val)+month(Val))+Day(Val) range 201 preceding)
else count(*) over(order by 100*(12*year(Val)+month(Val))+Day(Val) range 200 preceding)
end as cnt
from OracleRangeMonth
order by Val;
26. nth_Value
Oracle11gR2やPostgreSQL8.4では、nth_Valueという分析関数が使えます。
分析関数のorder byをソートキーとして、最初の行の値を求めるのが、First_Value
分析関数のorder byをソートキーとして、最後の行の値を求めるのが、Last_Value
分析関数のorder byをソートキーとして、(Row_Numberな順位が)n番目の行の値を求めるのが、nth_Value
となります。
create table nthT(ID integer,SortKey integer,Val integer);
insert into nthT values(1,10,666);
insert into nthT values(1,30,333);
insert into nthT values(1,40,222);
insert into nthT values(1,50,444);
insert into nthT values(2,20,777);
insert into nthT values(2,25,111);
insert into nthT values(2,27,555);
insert into nthT values(3,60,999);
insert into nthT values(3,61,888);
-- 模倣対象のPostgreSQL8.4のSQL
select ID,SortKey,Val,
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 | SecondVal | thirdVal
----+---------+-----+-----------+----------
1 | 10 | 666 | 333 | 222
1 | 30 | 333 | 333 | 222
1 | 40 | 222 | 333 | 222
1 | 50 | 444 | 333 | 222
2 | 20 | 777 | 111 | 555
2 | 25 | 111 | 111 | 555
2 | 27 | 555 | 111 | 555
3 | 60 | 999 | 888 | null
3 | 61 | 888 | 888 | null
-- OLAPSample47
select ID,SortKey,Val,
max(case rn when 2 then Val end) over(partition by ID) as SecondVal,
max(case rn when 3 then Val end) over(partition by ID) as thirdVal
from (select ID,SortKey,Val,
Row_Number() over(partition by ID order by SortKey) as rn
from nthT)
order by ID,SortKey;
分析関数のorder byをソートキーとして、
(Row_Numberな順位が)n番目の行の値を求めるのが、nth_Valueであることをふまえて、
インラインビューでRow_Number関数で順位を求めておいてから、
Pivotクエリでよく使われるmax関数とcase式を組み合わせる手法を使ってます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
脳内のイメージは、このようになります。
partition byで脳内で赤線を引いて、Row_Number関数を黄緑線でイメージしてます。
27. ListAggとwmsys.wm_concat
create table ListAggT(
ID integer,
Val char(3));
insert into ListAggT Values(1,'aaa');
insert into ListAggT Values(1,'bbb');
insert into ListAggT Values(1,'ccc');
insert into ListAggT Values(2,'ddd');
insert into ListAggT Values(2,'eee');
Oracleには、MySQLのgroup_concat関数のような集約関数として、
wmsys.wm_concat関数やListAgg関数があって、分析関数としても使えます。
-- 模倣対象のOracleのSQL(order by指定)
select ID,Val,wmsys.wm_concat(Val) over(order by Val) as strAgg1
from ListAggT;
ID Val strAgg1
-- --- -------------------
1 aaa aaa
1 bbb aaa,bbb
1 ccc aaa,bbb,ccc
2 ddd aaa,bbb,ccc,ddd
2 eee aaa,bbb,ccc,ddd,eee
DB2では、XMLAgg関数も、XMLGroup関数も分析関数として使うことはできませんので、
共通表式を使う方法や、XML関数を相関サブクエリで使う方法が、代用案となります。
-- 共通表式を使う方法
with tmp(ID,Val,Rn) as(
select ID,Val,Row_Number() over(order by Val)
from ListAggT),
rec(ID,Val,strAgg1,Rn) as(
select ID,Val,cast(Val as varchar(20)),rn
from tmp
where rn=1
union all
select b.ID,b.Val,a.strAgg1 || ',' || b.Val,b.Rn
from rec a,tmp b
where b.rn = a.rn+1)
select ID,Val,strAgg1 from rec;
-- XML関数を使う方法
select ID,Val,
(select substr(XMLCast(XMLGroup(',' || b.Val as wk) as varchar(20)),2)
from ListAggT b
where b.Val <= a.Val) as strAgg1
from ListAggT a;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
-- 模倣対象のOracleのSQL(partition by指定)
select ID,Val,wmsys.wm_concat(Val) over(partition by ID) as strAgg2
from ListAggT;
ID Val strAgg2
-- --- -----------
1 aaa aaa,bbb,ccc
1 bbb aaa,bbb,ccc
1 ccc aaa,bbb,ccc
2 ddd ddd,eee
2 eee ddd,eee
-- XML関数を使う方法
select ID,Val,
(select substr(XMLCast(XMLGroup(',' || Val as wk) as varchar(20)),2)
from ListAggT b
where b.ID = a.ID) as strAgg2
from ListAggT a;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
なお、連結する文字列の連結順序を指定したい場合は、
下記のように、XMLGroup関数にorder byを指定します。
select ID,
substr(XMLCast(XMLGroup(',' || Val as wk ORDER BY Val desc) as varchar(20)),2) as strAgg3
from ListAggT
group by ID;
ID strAgg3
-- -----------
1 ccc,bbb,aaa
2 eee,ddd
28. 中央値(メジアン)を求める
create table MedianT(ID integer,Val integer);
insert into MedianT values(1, 10);
insert into MedianT values(1, 30);
insert into MedianT values(1,300);
insert into MedianT values(2,100);
insert into MedianT values(2,350);
insert into MedianT values(2,400);
insert into MedianT values(2,900);
insert into MedianT values(2,900);
insert into MedianT values(3,200);
insert into MedianT values(3,800);
Oracleには、メジアンを求める集約関数として、Median関数があって、分析関数としても使えます。
-- 模倣対象のOracleのSQL
select ID,Val,
Median(Val) over(partition by ID) as MedianVal
from MedianT
order by ID,Val;
ID Val MedianVal
-- --- ---------
1 10 30
1 30 30
1 300 30
2 100 400
2 350 400
2 400 400
2 900 400
2 900 400
3 200 500
3 800 500
-- OLAPSample48
select ID,Val,
avg(case when mod(RecCnt,2) = 0 and Rn in(RecCnt/2,RecCnt/2+1)
or mod(RecCnt,2) = 1 and Rn = Ceil(RecCnt/2.0)
then Val end) over(partition by ID) as MedianVal
from (select ID,Val,
count(*) over(partition by ID) as RecCnt,
Row_Number() over(partition by ID order by Val) as Rn
from MedianT)
order by ID,Val;
インラインビューで、分析関数のcount関数でレコード数を求めて、Row_Number関数で順位を求めて、
分析関数のavg関数の引数のcase式で、レコード数が偶数か奇数かで場合分けしてます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
下記の、データ件数が奇数個の場合と、偶数個の場合の
正順位と逆順位の差をふまえた下記のSQLでもメジアンを求めることができます。
データ件数が奇数個の場合
正順位 1 2 3 4 5
逆順位 5 4 3 2 1
差 4 2 0 2 4
偶数個の場合
正順位 1 2 3 4
逆順位 4 3 2 1
差 3 1 1 3
-- OLAPSample49
select ID,Val,
avg(case when Rn-RevRn in(-1,0,1)
then Val end) over(partition by ID) as MedianVal
from (select ID,Val,
Row_Number() over(partition by ID order by Val) as Rn,
Row_Number() over(partition by ID order by Val desc) as RevRn
from MedianT)
order by ID,Val;
第3部 分析関数の参考リソース