こんにちは。仲沢です。
久しぶりの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句は応用次第で、このような便利な利用方法もありますので、是非ご活用ください。
こんにちは、仲沢です。
久々のSQLTipsとなりますが、今回はSQLのテクニックでも業務効率化のためのテクニックをご紹介したいと思います。
一般的なマスタデータを格納する際に、WEBページ上の表データをテーブルに取り込みたいと思ったことはないでしょうか？
例えば、左図のようなWEBページ上にある携帯端末リスト（ケータイ・オール）をテーブルに取り込みたい場合に、一つ一つ手入力していくと、とても大変な作業になります。
このような場合には、一度このリストをタブ区切り等のテキストデータを作成し、そのテキストデータからINSERT文を作ることで簡単に取り込むことができます。
タブ区切りのテキストデータを作成する際に便利なのが、Table2ClipboardというFireFoxのアドオンです。
このアドオンを利用すると、ページ上の表で選択した行や列を、配置を保ったままクリップボードにコピーできるので、
Excelなどの表計算ソフトや、CSV 形式でのテキストエディタへの貼り付けが可能となります。
このアドオンを利用して、表の上で右クリックするとショートカットメニューが表示されますので、ここから「表全体をコピー」を選択します。
コピーしたデータを貼り付けると、下図のようなタブ区切りのテキストデータが取得できます。
あとはINSERT文の書式に従って、テキストエディタの置換機能やブロック選択機能を利用して値の部分を変更することによって、INSERT文が完成します。
該当ページにCSV機能やXML機能があればこのような作業自体も発生しませんが、そのような機能が無い場合には、上記のテクニックを使うことで大量のデータもテーブルデータとして短時間で取り込むことができますので、お試しください。
このブログを購読する
2011.10.04 
