SHOEISHA iD

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

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

分析関数の衝撃

分析関数の衝撃6(応用編)

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

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

ダウンロード SourceCode (3.0 KB)

 SQLを使う業務では分析関数を使いこなすと、生産性とSQLの可読性とパフォーマンスを、大きく向上させることができます。分析関数を使う際の考え方と、処理のイメージを解説します。今回の応用編では、分析関数の変わった使い方を紹介します。

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

はじめに

 今回の応用編では、分析関数の変わった使い方を紹介します。

対象読者

  • SQLの可読性を向上させたい方
  • SQLのパフォーマンスを向上させたい方

 2問目で3値倫理に関連する問題を、4問目でcase式に関連する問題を取り上げますので、3値論理とNULLCASE式のススメ(前編)を先に読んでおくと理解が進むと思います。

必要な環境

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

  • Oracle9i以降
  • DB2
  • SQL Server 2005以降

1. 複数列のdistinctなcount

 最初は、複数列のdistinctcountを求めるSQLです。まずは、テーブルのデータと、出力結果を考えます。

IDTable
ID ColA ColB
1 ab c
2 a bc
3 a b
4 a b
5 a b

 ColA,ColBの組み合わせのdistinctcountを求めます。(ColA,ColB) = (ab,c),(a,bc),(a,b)で3通りの組み合わせがあるので、これをdiscntとして下記の形で出力します。

出力結果
ID ColA ColB discnt
1 ab c 3
2 a bc 3
3 a b 3
4 a b 3
5 a b 3

 下記は、残念ながら文法エラーとなります。count関数で2つ以上の引数の指定は認められていないのです。

文法エラー
select ID,ColA,ColB,
count(distinct ColA,ColB) over() as discnt
  from IDTable;

 どうにかして引数を1つにまとめようということで、文字データとしてのカンマが存在しないならば、カンマを区切り文字に使って文字を連結させた下記のSQLでもいいでしょう。

カンマを区切り文字に使う方法
select ID,ColA,ColB,
count(distinct ColA || ',' || ColB) over() as discnt
  from IDTable
order by ID;

 しかし、上記のSQLではColAとColBが数値型や日付型だった場合に、文字型にキャストしないと暗黙キャストが発生します。

 そこで、別解として下記の逆ソートを使うSQLがあります。count関数で2つ以上の引数の指定はできませんが、dense_rank関数であれば、2つ以上の引数を指定できるのです。

逆ソートを使う方法
select ID,ColA,ColB,
-1+dense_rank() over(order by ColA asc ,ColB asc )
  +dense_rank() over(order by ColA desc,ColB desc) as discnt
  from IDTable;

 order by ColA asc,ColB ascでのascは省略可能ですが、descと対比させたほうが分かりやすいと考え、省略していません。

 下記の数式をふまえてdense_rank関数を使っています。-1を加算しているのは、自分を2回数えているからです。

-1 + 重複を除いた自分以上の組み合わせの数
   + 重複を除いた自分以下の組み合わせの数
= 重複を除いた組み合わせの数

 下記SQLの結果を考えると理解しやすいでしょう。

select ID,ColA,ColB,
dense_rank() over(order by ColA asc ,ColB asc ) as Rn,
dense_rank() over(order by ColA desc,ColB desc) as RevRn,
-1+dense_rank() over(order by ColA asc ,ColB asc )
  +dense_rank() over(order by ColA desc,ColB desc) as "-1+Rn+RevRn"
  from IDTable
order by ID;
SQLの結果
ID ColA ColB Rn RevRn -1+Rn+RevRn
1 ab c 3 1 3
2 a bc 2 2 3
3 a b 1 3 3
4 a b 1 3 3
5 a b 1 3 3

次のページ
2. range指定のcount関数

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング