分析関数の衝撃

MySQLで分析関数を模倣2（中編）

MySQLで、Oracleの分析関数と同じ結果を取得する2

山岸賢治[著]

2008/11/18 14:00

ポスト

ダウンロード SourceCode (3.1 KB)

4. 同じIDでSeqの昇順で最初にValが100か200になる行のValを求める

　最後は、同じIDでSeqの昇順で最初にValが100か200になる行のValを求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

IDTable

ID	Seq	Val
AA	1	100
AA	2	100
AA	3	500
AA	4	200
AA	5	200
AA	6	50
BB	1	200
BB	2	400
BB	3	800
BB	4	900
CC	1	100
CC	2	800
CC	3	700
DD	1	400
EE	1	50
FF	1	10
FF	3	20
FF	5	40
FF	6	80

　同じIDでSeqの昇順で最初にValが100か200になる行のValを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Seq,Val,
First_Value(case when Val in(100,200)
                 then Val end ignore nulls)
over(partition by ID
order by Seq
Rows between Current Row
         and Unbounded Following) as FirstVal
  from IDTable
order by ID,Seq;

出力結果

ID	Seq	Val	FirstVal
AA	1	100	100
AA	2	100	100
AA	3	500	200
AA	4	200	200
AA	5	200	200
AA	6	50	null
BB	1	200	200
BB	2	400	null
BB	3	800	null
BB	4	900	null
CC	1	100	100
CC	2	800	null
CC	3	700	null
DD	1	400	null
EE	1	50	null
FF	1	10	null
FF	3	20	null
FF	5	40	null
FF	6	80	null

　前問と同じ考え方を使って、答えは、下記となります。

相関サブクエリを使う方法

select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq = (select min(c.Seq)
                   from IDTable c
                  where c.ID=a.ID
                    and c.Val in(100,200)
                    and c.Seq >= a.Seq)) as FirstVal
  from IDTable a
order by ID,Seq;

　解説すると、最初に下記によって、同じIDで、Seqが自分以上で、Valが100か200の行の中での最小のSeqを求めています。

同じIDで、Seqが自分以上で、Valが100か200の行の中での最小のSeqを求める

select min(c.Seq)
  from IDTable c
 where c.ID=a.ID
   and c.Val in(100,200)
   and c.Seq >= a.Seq

　続いて下記により、同じIDでSeqの昇順で最初にValが100か200になる行のValを取得しています。

同じIDでSeqの昇順で最初にValが100か200になる行のValを取得

select b.Val
  from IDTable b
 where b.ID=a.ID
   and b.Seq = (select min(c.Seq)
                  from IDTable c
                 where c.ID=a.ID
                   and c.Val in(100,200)
                   and c.Seq >= a.Seq)

　下記のLimit句を使った別解もあり、こっちのほうがシンプルでしょう。

Limit句を使った別解

select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Val in(100,200)
    and b.Seq >= a.Seq
 order by b.Seq Limit 1) as FirstVal
  from IDTable a
order by ID,Seq;

　SQLのイメージは下記となります。

SQLのイメージ

最後に

　今回は、First_Value関数やLast_Value関数と同じ結果を取得するSQLを扱いました。次回の後編では、Lag関数およびLead関数と同じ結果を取得するSQLを扱う予定です。

参考資料

ONLamp.com 『Emulating Analytic （AKA Ranking） Functions with MySQL』 Stephane Faroultt著、2007年3月

本連載の原案となった記事です。
ONLamp.com 『Emulating Analytic （AKA Ranking） Functions with MySQL: Part 2』 Stephane Faroult著、2007年4月
本連載の原案となった記事です。