分析関数の衝撃

MySQLで分析関数を模倣3（後編）

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

山岸賢治[著]

2008/11/26 14:00

ポスト

ダウンロード SourceCode (3.0 KB)

2. IDごとのSeqの昇順での3行前と3行後の行のValを求める

　最後は、IDごとのSeqの昇順で、3行前と3行後の行の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の昇順での3行前と3行後の行のValを求めます。3行前の行がなければ、その値は333とします。同様に、3行後の行がなければ、その値は666とします。なお、Valは非nullとします。

　言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Seq,Val,
Lag (Val,3,333) over(partition by ID order by Seq) as LagVal,
Lead(Val,3,666) over(partition by ID order by Seq) as LeadVal
  from IDTable
order by ID,Seq;

出力結果

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

　前問と似たような考えを使います。

　Lag (Val,3,333) over(partition by ID order by Seq)について考えてみると、Seqの昇順でソートした時の3行前というのは、ソートキーであるSeqの値が対象行より小さい中で、Seqの降順にソートしての3番目の行だと考えることができます。

　Lead(Val,3,666) over(partition by ID order by Seq)について考えてみると、Seqの昇順でソートした時の3行後というのは、ソートキーであるSeqの値が対象行より大きい中で、Seqの昇順にソートしての3番目の行だと考えることができます。

　また、3行前および3行後の行がない場合は、case式などで対処すればよさそうです。

　以上をふまえて、答えは下記となります。

Limit句を使うSQL

select ID,Seq,Val,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq < a.Seq
 order by b.Seq desc Limit 2,1),333) as LagVal,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq > a.Seq
 order by b.Seq Limit 2,1),666) as LeadVal
  from IDTable a
order by ID,Seq;

　LagValを求める相関サブクエリでは、where句で自分よりSeqが小さいことを条件として、order by句でSeqの降順にソートして、Limit句でoffsetに2を指定しrow_countに1を指定しています。

offset=0の行がソートキーの降順で1つ先の行
offset=1の行がソートキーの降順で2つ先の行
offset=2の行がソートキーの降順で3つ先の行

　ですので、相関サブクエリでソートキーの降順で3つ先の行の値を取得しているのです。

　LeadValを求める相関サブクエリでは、where句で自分よりSeqが大きいことを条件として、order by句でSeqの昇順にソートして、Limit句でoffsetに2を指定しrow_countに1を指定しています。

offset=0の行がソートキーの昇順で1つ先の行
offset=1の行がソートキーの昇順で2つ先の行
offset=2の行がソートキーの昇順で3つ先の行

　ですので、相関サブクエリでソートキーの昇順で3つ先の行の値を取得しているのです。

　そして、3行前および3行後の行がない場合に相関サブクエリの値は、nullになりますので、coalesce関数で対処してます。なお、MySQLであればifnull関数をcoalesce関数の代わりに使えますし、Oracleであればnvl関数をcoalesce関数の代わりに使えます。

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

SQLのイメージ

　ちなみに、Limit句を使わないSQLは、下記となります。

Limit句を使わないSQL 1（exists述語を使用）

select ID,Seq,Val,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and exists(select 1 from IDTable c
                where c.ID=a.ID
                  and c.Seq between b.Seq and a.Seq
               having count(*) = 3+1)),333) as LagVal,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and exists(select 1 from IDTable c
                where c.ID=a.ID
                  and c.Seq between a.Seq and b.Seq
               having count(*) = 3+1)),666) as LeadVal
  from IDTable a
order by ID,Seq;

　前問と同じ考え方を使ってます。前問と同じく、上記のSQLは、having句での条件指定が単純な等価条件で、かつ、group by句がないので、下記に書き換え可能です。

Limit句を使わないSQL 2

select ID,Seq,Val,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and (select count(*)
           from IDTable c
          where c.ID=a.ID
            and c.Seq between b.Seq and a.Seq) = 3+1),333) as LagVal,
coalesce(
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and (select count(*)
           from IDTable c
          where c.ID=a.ID
            and c.Seq between a.Seq and b.Seq) = 3+1),666) as LeadVal
  from IDTable a
order by ID,Seq;

最後に

　今回は、Lag関数とLead関数と同じ結果を取得するSQLを扱いました。次回の完結編では、Range指定およびRows指定のSum関数と同じ結果を取得するSQLを扱います。

参考資料

MySQL 5.1 リファレンスマニュアル『12.2.7 SELECT 構文』
MySQLのマニュアルです。本稿で多用したLimit句について記述されています。
PostgreSQL 8.3.4文書『LIMITとOFFSET』
PostgreSQLのマニュアルです。本稿のソースコードで多用したLimit句について記述されています。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL』 Stephane Faroultt著、2007年3月
本連載の原案となった記事です。
ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL: Part 2』 Stephane Faroult著、2007年4月
本連載の原案となった記事です。