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

SQLにおけるナンバリング

本日はナンバリングに関して。 MySQLを使っていますと各テーブルにはid int not null auto_increment primary keyなどと打って主キーを打つことがほぼ習慣になって …

no image

Cakeでのリレーションについて

いまさらながらCakeのリレーションについての復習。 基本から。 Contents1 基本的なリレーション1.1 1対N1.2 N対11.3 動的な紐づけ 基本的なリレーション 下記のようなテーブル構 …

no image

アンチパターン バインド変数の未使用+直積組み合わせ+データ量爆発+インデックス関連

本日はSQLコーディングに関して。 ここら辺は実際にプログラムを書く際に重要になってくるネタ。 Contents1 バインド変数1.1 デメリット1.2 対策2 直積により組み合わせが爆発する2.1 …

no image

joinとeager loading

フレームワークでデータをORMがらみでjoinするときのネタ。 自分の場合はLaravel。他のフレームワークでも考え方は通じるものあるかと・・ Contents1 通常のjoin2 ループの中で取得 …