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

SQLのチューニングに関して

ここ2か月ぐらいはSQLの本でがりがり勉強してきましたね。当然復習も必要かと思いますが、だいぶいろんなことを覚えたなあという気がします。 一番勉強になった本はもちろん「達人に学ぶ SQL徹底指南書」と …

no image

CASE+HAVING句

今回はCASEとHAVINGを組み合わせて、そこそこの難問を解いていきます。 まず下記のようなテーブル(testresults)があるとします。 student | class | sex | sco …

no image

MySQLのSETとFIND_IN_SETについて

MySQLの方ですが、単純な文字列や、数字、日付の他に配列に近いSET型、また5.7以降ではJSONなどを入れることができます。 Contents1 SET型2 FIND_IN_SET SET型 いわ …

no image

laravelのmigrationに関して

DBのカラムの保守などをする場合のmigrationに関して。 ポツポツ使っていたのですが、しっかりまとめてなかったのでここでまとめて見ようかと思います。 Contents1 カラム定義2 実際の実行 …

no image

外部結合について 行列変換

今回から外部結合について学習します。 これはCASE式でもやった集合の考え方が大事になってきます。 例えば下記のようなテーブルCoursesとがあるとします。 name | course &#8212 …

アーカイブ