Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

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

ダウンロード SourceCode (3.1 KB)

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

目次

はじめに

 本稿では、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のイメージ

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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