インクレイブ[仙台・渋谷] - ITプロダクト・ホームページ制作・Webシステム開発・デザイン開発

こんにちは。仲沢です。
久しぶりのSQL Tipsとなりますが、前回行ったHAVING句の応用編ということで、以下のような例題を挙げてみました。

この表はある小学校のテストの結果となります。
この中から、「算数の点数が80点以上かつ国語の点数が50点以上」の生徒IDを抽出します。
抽出のイメージは以下の通りです。

一見簡単そうに見えますが、行毎に科目が別れている表の中から条件が異なる行を抽出しますので、かなりの難問です。
通常のWHERE句を使うと「算数の点数が80点以上」や「国語の点数が50点以上」という単一の条件や「算数の点数が80点以上もしくは国語の点数が50点以上」というOR条件での取得は可能ですが、今回のようにAND条件での取得となると一工夫が必要となります。

まずはCASE文を使って「科目が算数の場合80点以上であれば1、科目が国語の場合50点以上であれば1、それ以外は0」を返す列（判定）を作成してみます。

SELECT
    生徒ID,
    科目,
    点数,
    CASE WHEN (科目='算数' AND 点数>=80) THEN 1
    WHEN (科目='国語' AND 点数>=50) THEN 1
    ELSE 0 END AS 判定
FROM
    テスト結果

実行結果は以下の通りです。

ここで、算数が80点以上、国語が50点以上の行に1がつく列ができました。
求めたい行はどちらにも1がついている行、つまり「この判定の合計が2の生徒ID」ということになります。
行の合計を求めるにはGROUP BY句でグループ化した上で集計する必要があり、その結果からの条件抽出となるためHAVING句を使用します。

SELECT
    生徒ID
FROM
    テスト結果
GROUP BY
    生徒ID
HAVING
SUM(CASE WHEN (科目='算数' AND 点数>=80) THEN 1
    WHEN (科目='国語' AND 点数>=50) THEN 1
    ELSE 0 END)=2

となります。
いかがでしょうか。このような抽出をしたいときには、プログラム内での処理で解決することが多いですが、意外とスッキリしたSQLで解決できることが分かりました。
是非、ご活用ください。

こんにちは、仲沢です。
今回のSQLTipsは「最頻値」です。
とある求人広告に社員の平均月収が「66万円」と書いてある企業Ａがあったとします。
これだけ見ると、かなりの高待遇だなという印象をうけますが、実際の社員月収内訳を見てみると左の表のような結果でした。

たしかに間違ってはいませんが、この会社に入社した社員はちょっと詐欺に遭ったような感覚を受けるかもしれません。
極端に偏ったデータを持つ場合、単純平均は「外れ値」に影響を受けやすいという欠点があります。
このようなときに集団の傾向をもっと正確に示す指標として、「最頻値」が利用されます。
最頻値とは、母集団の中で最も数の多かった値のことであり、その意味で「流行値」という呼び名もあります。
今回の場合は「15万円」となりますが、これを求める方法を考えてみます。

まずは月収を基準としたグループ化により、該当人数を求めてみます。

SELECT
    月収,
    count(*) AS 回数
FROM
    月収リスト
GROUP BY
    月収

結果は以下の通りです。

つまり、今回は最も該当人数が多い「15万円」という値が欲しいので、このSQLから「最も該当人数が多いデータ」という条件を追加します。
この場合、GROUP BYで求めた結果から条件を追加するため、WHERE句ではなくHAVING句を利用します。

SELECT
    月収,
    count(*) AS 回数
FROM
    月収リスト
GROUP BY
    月収
HAVING 回数 >= ALL(SELECT count(*) FROM 月収リスト GROUP BY 月収 )

いかがでしょうか？
HAVING句は応用次第で、このような便利な利用方法もありますので、是非ご活用ください。

こんにちは。仲沢です。

本日はHAVING句について触れていきたいと思います。

HAVING句はGROUP BYで集計した結果行に対して条件を設定し、その条件に合致するものを抽出する用途で利用されるため、HAVING句はGROUP BY句と併用しなければならないと思われることが多いですが、現在の標準SQLではHAVING句を単独で使えます。

例えば、以前ご紹介した順位をつけるときのSQL

SELECT
    A.氏名,
    A.売上,
    (SELECT count(*) +1 FROM 社員リスト WHERE 売上 > A.売上) AS 順位
FROM
    A.社員リスト

この時に「順位が3位以上」という条件を設定したい場合、WHERE句を使うのであれば、

SELECT
    A.氏名,
    A.売上,
    (SELECT count(*) +1 FROM 社員リスト WHERE 売上 > A.売上) AS 順位
FROM
    A.社員リスト
WHERE
    (SELECT count(*) +1 FROM 社員リスト WHERE 売上 > A.売上) >= 3

となります。
間違いではないですが、見た目として冗長であり、可読性もよくありません。

ここでHAVING句を利用すると

SELECT
    A.氏名,
    A.売上,
    (SELECT count(*) +1 FROM 社員リスト WHERE 売上 > A.売上) AS 順位
FROM
    A.社員リスト
HAVING
    順位 >= 3

となります。
可読性がよくなっただけでなく、例えば順位を求めるサブクエリが変わった場合も、SELECT句のサブクエリだけを修正すればよくなります。

今回はほんの一部分でしたが、HAVING句には便利な使い方がまだありますので、次回以降ご紹介していきたいと思います。