SHOEISHA iD

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

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

分析関数の衝撃

分析関数の衝撃(前編)

CodeZineに掲載されたSQLを分析関数で記述する


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

ダウンロード ソースコード (795.0 B)

2. 最頻値(モード)を求める

 次に最頻値(モード)を求めるSQLです。「HAVING句の力」では、以下の最頻値(モード)を求めるSQLが提示されています。

--最頻値を求めるSQL その2:極値関数の利用
SELECT income
  FROM Graduates
 GROUP BY income
HAVING COUNT(*) >=  ( SELECT MAX(cnt)
                        FROM ( SELECT COUNT(*) AS cnt
                                 FROM Graduates
                             GROUP BY income) );

 これを分析関数で書き換えるのですが、このような複雑なselect文を書き換える際にはselect文の評価順序を理解していることが重要です。select文は、

  1. from句
  2. where句
  3. group by句
  4. having句
  5. select句
  6. order by句

 の順に評価されます。

 分析関数が使用できるのは5番目のselect句と6番目のorder by句なので、分析関数の結果をwhere句で使うにはインラインビューを使う必要があります。

 まず、Graduates(卒業生テーブル)に対する、以下のselect文を考えます。

Graduates(卒業生テーブル)
nameincome
サンプソン400,000
マイク30,000
ホワイト20,000
アーノルド20,000
スミス20,000
ロレンス15,000
ハドソン15,000
ケント10,000
ベッカー10,000
スコット10,000
SQL文
select income, count(*) as cnt
  from Graduates
 group by income;
出力結果
income    cnt
-------   ---
400,000     1
 30,000     1
 20,000     3
 15,000     2
 10,000     3

 この結果を見ると、「cntが最大であること」を条件とすればいいと分かりますよね。cntの最大値を分析関数のmax関数を使って求めたselect文を考えます。

分析関数で書き換えたSQL
select income,count(*) as cnt,
max(count(*)) over() as maxCnt
  from Graduates
 group by income;
出力結果
income    cnt  maxCnt
-------   ---  ------
400,000     1       3
 30,000     1       3
 20,000     3       3
 15,000     2       3
 10,000     3       3

 少し分かりにくいかもしれませんが、分析関数のmax関数の引数に集合関数のcount関数を使用してます。

 そして、分析関数の結果をwhere句で使うためのインラインビューを考慮すると、以下のSQL文ができあがります。

select income,cnt
  from (select income, count(*) as cnt,
        max(count(*)) over() as maxCnt
        from Graduates
        group by income)
where cnt = maxCnt;
出力結果
income   cnt
------   ---
20,000     3
10,000     3

3. 中央値(メジアン)を求める

 今度は中央値(メジアン)を求めます。「HAVING句の力」で提示されている以下のSQLを、分析関数を使って書き換えてみます。

--メジアンを求めるSQL:自己非等値結合をHAVING句で使う
SELECT AVG(DISTINCT income)
  FROM (SELECT T1.income
          FROM Graduates T1, Graduates T2
      GROUP BY T1.income
               --S1の条件
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
                   >= COUNT(*) / 2
               --S2の条件
           AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
                   >= COUNT(*) / 2 );

 まずは、データの具体的な例を挙げて考えます。なお、incomeの順位を持つRank列も付与して考えます。

case1           case2
income   Rank   income   Rank
-------  ----   -------  ----
 10,000     1    10,000     1
 10,000     2    10,000     2
 10,000     3    10,000     3
 15,000     4    15,000     4
 15,000     5    15,000     5
 20,000     6
 20,000     7
 20,000     8
 30,000     9
400,000    10
  • case1が、データ件数が偶数の場合で、中央値は、(15000+20000) / 2 = 17500
  • case2が、データ件数が奇数の場合で、中央値は、10000

 手続き型の言語であれば、

  1. 処理1 昇順にソート
  2. 処理2 データ件数を求める
  3. 処理3 データ件数が偶数なら、(データ件数 / 2)番目と(データ件数 / 2 +1)番目の、平均が中央値
  4. 処理4 データ件数が奇数なら、(データ件数 / 2 + 0.5)番目が中央値

 といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えたSQL
select avg(income)
  from (select income,
               Row_Number() over(order by income) as Rank,
               count(*) over() as DataCount
          from Graduates)
where (mod(DataCount,2) = 0 and Rank in(DataCount/2, DataCount/2+1))
   or (mod(DataCount,2) = 1 and Rank = Ceil(DataCount/2));

 インラインビューの中で、Row_Number関数で順位、count関数でデータ件数を求めてます。そして、データ件数が偶数か奇数かでwhere句の条件を分岐させてます。

最後に

 分析関数で書き換えたSQLを3つ挙げました。同じテーブルに2回以上アクセスするSQLのほとんどは分析関数で書き換えることが可能です。興味を持った方はどんどん分析関数を使ってみてください。

 partition byRowsRangeを使う分析関数は、分析関数の衝撃の中編と後編で扱います。

参考資料

  1. CodeZine 『HAVING句の力
  2. こちらで記載されたSQLを題材とさせていただきました。
  1. OTN Japan『Oracle8i データウェアハウス
  2. Oracleの分析関数についての公式資料です。分析関数を使うことによる、可読性とパフォーマンスの向上例が紹介されてます。
  1. OTN Japan『分析ファンクション
  2. 分析関数のOracleの公式マニュアルです。(日本語)
  1. US-OTN『Analytic Functions
  2. 分析関数のOracleの公式マニュアルです。(英語)
  1. @IT 『SQLクリニック(8) 極めよう! 分析関数によるSQL高速化計画
  2. Oracleの分析関数(RANK関数とDENSE_RANK関数)について、分かりやすく解説されてます。
  1. @IT 『SQLクリニック(9) SQL分析関数をさらに深く追求してみよう
  2. Oracleの分析関数(SUM関数とRATIO_TO_REPORT関数)について、分かりやすく解説されてます。
  1. @IT 『SQLクリニック(10) まだまだあるぞ! 分析関数の究極テクニック
  2. Oracleの分析関数(LAG関数とLEAD関数)について、分かりやすく解説されてます。
  1. OracleSQLパズル『select文の評価順序
  2. 本稿で挙げたselect文の評価順序の詳細を置いてます。
  1. OracleSQLパズル『モード(最頻値)を取得
  2. 本稿で挙げたモード(最頻値)を取得するSQLと別解を置いてます。
  1. OracleSQLパズル『メジアン(中央値)を取得
  2. 本稿で挙げたメジアン(中央値)を取得するSQLと別解を置いてます。
  1. OracleSQLパズル『最小の空き番号を取得その1
  2. 本稿で挙げた最小の空き番号を取得するSQLと別解を置いてます(データに欠番がないパターンを、考慮してないSQLです)。
  1. OracleSQLパズル『最小の空き番号を取得その2
  2. 本稿で挙げた最小の空き番号を取得するSQLと別解を置いてます(データに欠番がないパターンを、考慮したSQLです)。

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/1269 2007/06/13 15:31

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング