HAVING句がらみの計算式

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

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

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

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

SELECT
    name,
    income,
    (
        SELECT
            COUNT(*)+1
        FROM
            graduates g2
        WHERE
            g1.income < g2.income
    ) AS rank
FROM
    graduates g1

SELECT

name,

income,

(

SELECT

COUNT(*)+1

FROM

graduates g2

WHERE

g1.income < g2.income

) AS rank

FROM

graduates g1

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

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

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

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

SELECT
    AVG(income)
FROM
    (
        SELECT
            g1.income
        FROM
            graduates g1,
            graduates g2
        GROUP BY
            g1.income
        HAVING SUM(
            CASE
                WHEN g1.income <= g2.income THEN 1
                ELSE 0
            END
        ) >= COUNT(*) / 2
    AND SUM(
            CASE
                WHEN g1.income >= g2.income THEN 1
                ELSE 0
            END
        ) >= COUNT(*) / 2
    ) AS T1

SELECT

AVG(income)

FROM

(

SELECT

g1.income

FROM

graduates g1,

graduates g2

GROUP BY

g1.income

HAVING SUM(

CASE

WHEN g1.income <= g2.income THEN 1

ELSE 0

END

) >= COUNT(*) / 2

AND SUM(

CASE

WHEN g1.income >= g2.income THEN 1

ELSE 0

END

) >= COUNT(*) / 2

) AS T1

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

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

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

SELECT
    g1.income,
    COUNT(*)
FROM
    graduates g1,
    graduates g2
GROUP BY
    g1.income

SELECT

g1.income,

COUNT(*)

FROM

graduates g1,

graduates g2

GROUP BY

g1.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式を使って書くと下記のようになります。

SELECT 
  g1.income,
  SUM( CASE WHEN g1.income <= g2.income  THEN 1  ELSE 0 END ) as point1
FROM 
  graduates g1, graduates g2
GROUP BY
  g1.income

SELECT

g1.income,

SUM( CASE WHEN g1.income <= g2.income THEN 1 ELSE 0 END ) as point1

FROM

graduates g1, graduates g2

GROUP BY

g1.income

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

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

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

SELECT 
  g1.income,
  SUM( CASE WHEN g1.income <= g2.income  THEN 1  ELSE 0 END ) as point1
FROM 
  graduates g1, graduates g2
GROUP BY
  g1.income
HAVING SUM( CASE WHEN g1.income <= g2.income  THEN 1  ELSE 0 END ) >= COUNT (*) / 2

SELECT

g1.income,

SUM( CASE WHEN g1.income <= g2.income THEN 1 ELSE 0 END ) as point1

FROM

graduates g1, graduates g2

GROUP BY

g1.income

HAVING SUM( CASE WHEN g1.income <= g2.income THEN 1 ELSE 0 END ) >= COUNT (*) / 2

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

SELECT
    g1.income,
    SUM(
        CASE
            WHEN g1.income <= g2.income THEN 1
            ELSE 0
        END
    ) as point1,
    SUM(
        CASE
            WHEN g1.income >= g2.income THEN 1
            ELSE 0
        END
    ) as point2
FROM
    graduates g1,
    graduates g2
GROUP BY
    g1.income
HAVING SUM(
    CASE
        WHEN g1.income <= g2.income THEN 1
        ELSE 0
    END
) >= COUNT(*) / 2
AND SUM(
        CASE
            WHEN g1.income >= g2.income THEN 1
            ELSE 0
        END
    ) >= COUNT(*) / 2

SELECT

g1.income,

SUM(

CASE

WHEN g1.income <= g2.income THEN 1

ELSE 0

END

) as point1,

SUM(

CASE

WHEN g1.income >= g2.income THEN 1

ELSE 0

END

) as point2

FROM

graduates g1,

graduates g2

GROUP BY

g1.income

HAVING SUM(

CASE

WHEN g1.income <= g2.income THEN 1

ELSE 0

END

) >= COUNT(*) / 2

AND SUM(

CASE

WHEN g1.income >= g2.income THEN 1

ELSE 0

END

) >= COUNT(*) / 2

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

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