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

データベースのテスト環境作成

現在作っているシステムのリリースが近づいており、本番に近い環境を作成しお客様に見てもらうことに。 こういった手順はマニュアル化しておいたほうが楽だろうと思い、自分的にメモ 1 現状運用されているデータ …

no image

SQL結合 サブクエリに関して

今回はサブクエリに関してです。 もちろんSQL実践入門を読み進めています。 SQL上ではテーブル、ビュー、サブクエリというのは機能的にはそれほど変わらず主にパフォーマンスの点で違いがでてきます。 以下 …

no image

jQuery modalダイアログについて&重複時間処理

Contents1 jqueryモーダルダイアログ1.1 あらかじめ読み込むライブラリ1.2 ソース本体1.2.1 Html側1.2.2 Javascript側1.2.3 参考リンク2 重複時間につい …

no image

オブジェクト指向 データベース層

本日も引き続き「現場で役立つシステム設計の原則」を読み進めてます。 本日は主にデータベース層の考え方について。 Contents1 データべース層1.1 要点1.1.1 典型的なダメテーブル設計1.1 …

no image

JPAでのデータベースとの同期

このブログでも何回か書いてきたJPAですが、新規レコードをインサートさせた際IDを取得し、そのIDをもとに何らかのキーを作る、そういう処理があったので紹介させていただきます。 何回か書いてますが、JP …