skillup

技術ブログ

Database

HAVING句がらみの計算式

投稿日:

「達人に学ぶSQL徹底指南書」読んでますが、かなり難しいものがでてきましたね。

テーブルは前回のものと同じ以下のものを使います。

name | income
————+——–
サンプソン | 400000
マイク | 30000
ホワイト | 20000
アーノルド | 20000
スミス | 20000
ロレンス | 15000
ハドソン | 15000
ケント | 10000
ベッカー | 10000
スコット | 10000

ここで中央値を求めるとします。(この例でいうと20000と15000の間なので17500になります。)

最初自力で考えようと思い、順番を作って中央値を求めようとしました。

これでまあランクは出せるんですが、

  • 順番に重複がある
  • そもそもrankと全体数の比較だけではある値が半分より上かがわからない(←ここがクリティカル)

となり、頓挫しました。仕方なく模範解答を見ましたが、発想が全然違ってびっくりしました。

模範解答は下記になりますが、最初は説明がちょっと少ないこともあり(?)さっぱりわかりませんでした。

今は何とか理解できたので順をおって解説します。

ランキング系の処理を考える場合自己結合が有効です。この場合、まず以前説明したように総組み合わせ(10行*10行=100行)になることを理解しておきましょう。

ここからですが、下記SQLでincomeでグルーピングし、列数をカウントします。

income | count
——–+——-
30000 | 10
400000 | 10
10000 | 30
20000 | 30
15000 | 20

すると上記のような列がでてるかと思います。これは400000や30000は1行だけなのでグルーピングすれば1行*(自己結合相手の)10行=10になりますが、20000ならば、3列ありますので、3列分になるため30となります。図で書くとわかりやすいでしょうが・・・

これが何に使えるとかというと、ある列から見て大きい列の数を数えます。

例えば30000から見ると自己結合相手のうち自分以上の列は2行ですが,20000は5行(40000,30000,200003つ分)になります。このように自分以上の自己結合相手を考えると重複などを考えなくてすみます。

上記の場合、自分より大きい結合相手の累積をCASE式を使って書くと下記のようになります。

income | point1
——–+——–
30000 | 2
400000 | 1
10000 | 30
20000 | 15
15000 | 14

20000が5ではなく15になっているのは3行分あるためです。(5*3)。

こうしてみていくとpoint1が全体数分の半分+1より上であれば、平均以上になっていると言えます。(下記SQL)

これを高い順に並べた場合と低い順から考え共通したものをとりますとターゲットの列が取得できます。この例でいえば15000と20000です。

あとは抽出されたものをもとに平均をとってあげればOKです。ちなみに列表示部分のpoint1,point2はあるとわかりやすいですが、本来は不要なので消してあります。

またHAVINGのなかでpoint1,point2はダイレクトには使えないのでめんどいですが、もう一度書いてあげましょう。

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

MySQL safe mode

MySQLに関してしっかりパスワードをチェックしていれば問題ありませんが、中にはrootパスワードをわすれた!なんてこともあるでしょう。 そんなときはsafe modeで実行することでrootのパスワ …

no image

JPAでのリレーションに関して

JPAではテーブルをクラスで定義します。もちろん例外とかはいろいろあるのですが、1テーブル1クラスというつくりで、これをエンティティと呼びます。 もともとクラスを作ってからDBを作成したり、JTAの規 …

no image

O/Rマッパー iciqlについて

以前、このブログでも紹介したO/Rマッパーのiciqlについて、使い方や問題点がある程度わかったので書いておきます。 Contents1 インストール2 自動生成3 注意点3.1 Date型のインポー …

no image

dbUnitの使い方

えーJavaで有名なテストツールDBUnitについて。 DbUtilではありませんので間違えないように。(私は最初間違えました・・・) まだ全然使い込んでるわけではありませんがどんなことができるかとい …

no image

サブクエリの使い方

以前も少し学習しましたが、今回からはサブクエリの扱い方です。特に異なった行の比較(年度計算など)に関して行われる自己結合をつかったものが非常に強力です。 例によってここを学習しています。 下記のような …