SHOEISHA iD

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

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

分析関数の衝撃

MySQLで分析関数を模倣5(応用編)

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

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

ダウンロード SourceCode (4.9 KB)

3. 最大値の合計と最小値の合計

 次に、最大値の合計と最小値の合計を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

FirstLast
ID Seq Val
AA 1 1
AA 1 2
AA 2 4
AA 3 8
AA 3 16
AA 3 32
BB 1 64
BB 2 128
BB 3 256
CC 1 512
DD 6 10
DD 6 20
DD 7 40
DD 7 80

 IDごとのSeqが最大値の行のValの合計と、IDごとのSeqが最小値の行のValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数でのFirst指定とLast指定
select ID,Seq,Val,
sum(Val) Keep(Dense_Rank First order by Seq) over(partition by ID) as FirstSum,
sum(Val) Keep(Dense_Rank Last  order by Seq) over(partition by ID) as LastSum
  from FirstLast
order by ID,Seq;
出力結果
ID Seq Val FirstSum LastSum
AA 1 1 3 56
AA 1 2 3 56
AA 2 4 3 56
AA 3 8 3 56
AA 3 16 3 56
AA 3 32 3 56
BB 1 64 64 256
BB 2 128 64 256
BB 3 256 64 256
CC 1 512 512 512
DD 6 10 30 120
DD 6 20 30 120
DD 7 40 30 120
DD 7 80 30 120

 集計条件は、Seqが最大値であることと、Seqが最小値であることです。このように集計条件が2つ以上ある場合は、case式の使いどころです。このことをふまえて、答えは下記となります。

インラインビューを使うSQL
select d.ID,d.Seq,d.Val,c.FirstSum,c.LastSum
from (select a.ID,
      sum(case when a.Seq = b.minSeq then a.Val end) as FirstSum,
      sum(case when a.Seq = b.maxSeq then a.Val end) as LastSum
        from FirstLast a,
             (select ID,
              min(Seq) as minSeq,
              max(Seq) as maxSeq
                from FirstLast
               group by ID) b
       where a.ID=b.ID
      group by a.ID) c,FirstLast d
 where c.ID=d.ID
order by d.ID,d.Seq,d.Val;

 まずは、最も内側にあるクエリの実行結果について考えてみましょう。

最も内側にあるクエリ
select ID,
min(Seq) as minSeq,
max(Seq) as maxSeq
  from FirstLast
 group by ID;
出力結果
ID minSeq maxSeq
AA 1 3
BB 1 3
CC 1 1
DD 6 7

 IDでグループ化して、IDごとのSeqの最大値と最小値を求めています。後に、case式で使うことになります。

 続きまして、次に内側にあるクエリの実行結果について考えてみましょう。

最も内側にあるクエリ
select a.ID,
sum(case when a.Seq = b.minSeq then a.Val end) as FirstSum,
sum(case when a.Seq = b.maxSeq then a.Val end) as LastSum
  from FirstLast a,
       (select ID,
        min(Seq) as minSeq,
        max(Seq) as maxSeq
          from FirstLast
         group by ID) b
 where a.ID=b.ID
group by a.ID;
出力結果
ID FirstSum LastSum
AA 3 56
BB 64 256
CC 512 512
DD 30 120

 表別名がaの表と、表別名がbの表を、IDが等しいことを条件として内部結合しています。表別名がbの表で、IDでグループ化して、IDごとのSeqの最大値と最小値を求めておいて、sum関数の引数となるcase式で使用し、集計条件として使っています。

 後は、FirstLastテーブルとIDが等しいことを条件として内部結合して、欲しい出力結果としています。

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

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

 後は、FirstLastテーブルとIDが等しいことを条件として内部結合して、欲しい出力結果としています。

 余談ですが、Oracleでは下記のような、集合関数でのFirst指定とLast指定というのも存在します。こちらのほうが使用頻度は高いでしょう。

集合関数でのFirst指定とLast指定
select ID,
sum(Val) Keep(Dense_Rank First order by Seq) as FirstSum,
sum(Val) Keep(Dense_Rank Last  order by Seq) as LastSum
  from FirstLast
group by ID
order by ID;
出力結果
ID FirstSum LastSum
AA 3 56
BB 64 256
CC 512 512
DD 30 120

次のページ
4. その行までの文字列を連結

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング