Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

MySQLで分析関数を模倣3(後編)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/11/26 14:00

ダウンロード SourceCode (3.0 KB)

 連載「分析関数の衝撃」では、Oracleの分析関数を主に扱いました。「分析関数の衝撃」の外伝となるこの連載では、 MySQLで、Oracleの分析関数と同じ結果を取得するSQLの考え方と、処理のイメージを解説します。今回は、Lag関数とLead関数と同じ結果を取得するSQLを扱います。

目次

はじめに

 本稿では、Oracleの分析関数の中で、Lag関数とLead関数と同じ結果を取得するSQLを扱います。

対象読者

  • MySQLやPostgreSQLで、分析関数と同じ結果を取得するSQLを書きたい方
  • OracleやDB2やSQLServerの、分析関数の理解を深めたい方

 本稿では、相関サブクエリを多用しますので、『相関サブクエリで行と行を比較する』を先に読んでおくと理解が進むと思います。

必要な環境

 本稿で扱うSQLは、MySQL 5.1.28で動作確認しました。ソースコードはPostgreSQL 8.3とOracle 10.2.0.1.0でも動作確認しました。その他、次の環境でも応用が可能です。

  • SQLServer
  • DB2

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

 最初は、IDごとのSeqの昇順で、1行前と1行後の行の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の昇順での1行前と1行後の行のValを求めます。1行前の行がなければ、その値はnullとします。同様に、1行後の行がなければ、その値はnullとします。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL
select ID,Seq,Val,
Lag(Val)  over(partition by ID order by Seq) as LagVal,
Lead(Val) over(partition by ID order by Seq) as LeadVal
  from IDTable
order by ID,Seq;
出力結果
ID Seq Val LagVal LeadVal
AA 1 100 null 100
AA 2 100 100 500
AA 3 500 100 200
AA 4 200 500 200
AA 5 200 200 50
AA 6 50 200 null
BB 1 200 null 400
BB 2 400 200 800
BB 3 800 400 900
BB 4 900 800 null
CC 1 100 null 800
CC 2 800 100 700
CC 3 700 800 null
DD 1 400 null null
EE 1 50 null null
FF 1 10 null 20
FF 3 20 10 40
FF 5 40 20 80
FF 6 80 40 null

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

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

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

Limit句を使うSQL
select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq < a.Seq
 order by b.Seq desc Limit 1) as LagVal
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq > a.Seq
 order by b.Seq Limit 1) as LeadVal
  from IDTable a
order by ID,Seq;

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

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

 SQLのイメージは下記です。

SQLのイメージ
SQLのイメージ

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

Limit句を使わないSQL 1(exists述語を使用)
select ID,Seq,Val,
(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(*) = 1+1)) as LagVal,
(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(*) = 1+1)) as LeadVal
  from IDTable a
order by ID,Seq;

 from句でのネストされたインラインビューなどはネストの深い方から読みますが、上記のようなselect句でのネストされた相関サブクエリはネストの浅い方から読むと分かりやすいです。

 select句での相関サブクエリの中で、exists述語を使用し、where句で同じIDを指定し、between述語でSeqの大小条件を指定して、having句でその行の数を指定しています。

 LagValでは、条件式

Valを取得する行のソートキー <= ソートキー <= 自分の行のソートキー

 を満たす行数が2であることをhaving句で指定しています。

 LeadValでは、条件式

自分の行のソートキー <= ソートキー <= Valを取得する行のソートキー

 を満たす行数が2であることをhaving句で指定しています。

 上記のSQLは、having句での条件指定が単純な等価条件で、かつ、group by句がないので、下記に書き換え可能です。

Limit句を使わないSQL 2
select ID,Seq,Val,
(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) = 1+1) as LagVal,
(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) = 1+1) as LeadVal
  from IDTable a
order by ID,Seq;

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5