OracleSQLパズル   明智重蔵のブログ   執筆記事   DB2 SQLパズル   @IT ClubDB2   ClubDB2 Cafe   Unofficial-DB2-BLOG

DB2の分析関数の使用例


概要

Club DB2の「第98回 【SQL上級編】 DB2の分析関数の使用例」で、
私が講師をさせていただきまして、そのまとめページです。

分析関数とmodel句や、PostgreSQL8.4新機能 window関数や、分析関数の衝撃の内容をふまえつつ、
Oracleコミュニティでよく見かけるSQLの問題を、DB2 V9.7で分析関数を使用して解いたり、
Oracle11gR2の分析関数をDB2 V9.7で模倣するSQLを紹介しながら、私のSQLの思考法と脳内のイメージを解説しました。

単純なサンプル中心の勉強会にしました。


プログラム

第1部 分析関数の使用例
 1. 分析関数とは
 2. select文の件数取得
 3. except allとcount(*) over()
 4. 最大値の行の取得
 5. 順位を付ける
 6. 最大値の行の取得(ソートキーが複数)
 7. 前後の値
 8. 累計
 9. 移動累計
10. First_ValueとLast_Value
11. First_ValueとLast_Value(IGNORE NULLS)
12. 全称肯定,全称否定,存在肯定,存在否定
13. 最頻値(モード)
14. 連続範囲の最小値と最大値 (2人旅人算)
15. 連続範囲の最小値と最大値 (3人旅人算)
16. 次の入社日を求める
17. 次の次の入社日を求める
18. 2日前の値を求める
19. update文で分析関数の値に更新
20. delete文で重複行を削除

第2部 Oracle11gR2の分析関数をDB2 V9.7で模倣
21. count(distinct Val) over(partition by ID)
22. count(distinct Val) over(order by sortKey)
23. sum(Val) Keep(Dense_Rank Last order by sortKey) over(partition by ID)
24. Range '10' minute Precedingなcount(*)
25. Range '1' month Precedingなcount(*)
26. nth_Value
27. ListAggとwmsys.wm_concat
28. 中央値(メジアン)を求める

第3部 分析関数の参考リソース


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文は、
1 from句
2 where句
3 group by句
4 having句
5 select句
6 distinct
7 order by句
8 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)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う脳内のイメージは、下のようになります。



中学受験の算数の旅人算の感覚を取得するには、
図表付きで解説している、これらのサイトの問題を解くのがオススメです。
(方程式や連立方程式を使って解いてもいいです)
Hello School 算数 旅人算
今月は旅人算 - 学びの場.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部 分析関数の参考リソース

書籍
達人に学ぶ SQL徹底指南書
SQLクックブック

Web
Oracle開発者のためのDB2 UDB SQLリファレンス
SQL Reference, Volume 1