SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

MySQLで分析関数を模倣2(中編)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (3.1 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 本稿では、Oracleの分析関数のFirst_Value関数およびLast_Value関数と、同じ結果を取得する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が最小の行のValを求める

 最初は、IDごとのSeqが最小の行の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を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

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

 partition by IDという指定をして、同じIDの中でSeqが最小の行のValを求めていますので、答えは相関サブクエリを使った、下記となります。

相関サブクエリを使う方法
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)) as FirstVal
  from IDTable a
order by ID,Seq;

 サブクエリのネストのあるSQLは、ネストの深いほうから先に読んでいくと理解しやすいと思います。最初に下記によって、同じIDの中で、最小のSeqを求めています。

同じIDの中で、最小のSeqを求める
select min(c.Seq)
  from IDTable c
 where c.ID=a.ID

 続いて下記によって、同じIDでSeqが最小の行のValを取得しています。

同じ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)

 下記のLimit句を使った別解もあり、下記のほうがシンプルでしょうし、 ソートキーがSeqのみでない(複数ソートキー)場合に、order by句にソートキーを追加するだけで容易に対応できます。

 ちなみに、複数ソートキーの場合にLimit句を使わないSQLはnot exists述語を使って、最小値の行は、自分よりキーの大きい行が存在しない行、と考える必要があります。

Limit句を使った別解
select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
 order by b.Seq Limit 1) as FirstVal
  from IDTable a
order by ID,Seq;

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

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

次のページ
2. IDごとのSeqが最大の行のValを求める

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/3106 2008/11/18 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング