データベース GROUP BYとCASE、条件を絞る順序

ichikawaです。
今日はDBについて書きます。

まずは今日習った書き方で、少し複雑であるけれどもこういう書き方もある、というのを書きます。

GROUP BY とCASE文を合わせた書き方

さて例です。
架空のカレーのリストがあるとします。
今回もカレーですが、カレーはただ好きなだけです。

そのcurrys(カレーのリスト)はprice(値段)、spice(辛さレベル)があるとします。
ではこれを使い、安い、普通、高いの3ランクの値段を付けて、辛さごとにそれぞれ各ランクがいくつ存在するか表示するというものをやってみると、以下の様になります。

SELECT
  spice AS 辛さ
, SUM(
     CASE
       WHEN price < 500 THEN 1
     ELSE 0
     END
     ) AS "安い"
, SUM(
     CASE
       WHEN price < 800 THEN 1
     ELSE 0
     END
     ) AS "普通"
, SUM(
     CASE
       WHEN price < 1100 THEN 1
     ELSE 0
     END
     ) AS "高い"FROM
  currys
GROUP BY
  spice
;

これが最初見た時とても複雑のように見えて、「ついにDB勉強が追いつかなくなってきたかな。」
と思いましたが、
よく見るととても簡単で面白いものでした。
条件式を一つにしてどうなっているか考えてみると

SELECT
 SUM(
    CASE
      WHEN 条件 THEN 別名
      ELSE 別名
    END
    ) AS カラム名
FROM
テーブル名
GROUP BY
グループ化列名
;

こんな感じになっています。
一瞬みた時SUM関数がなんか複雑のような気にさせていたものでした。
それとCASE文の 1 や 0 が意味が分かりませんでした。

あの部分は、それぞれに値段のランク分けしたときに値を一つ持たせていて、それをSUM関数で合計しているのです。

前回CASE文を使った時はTHEN の後は何か文字列を記入したりしましたが、今回のような使い方もありなんだなと勉強になりました。

そして読み方も、書き方と同じ順番に沿って読むとスピードも速くなることに気がつきました。
というのは、書く時は上から書かず、まずFROMから下を書いてから最後にSELECT内容を書くと習いましたが、これはデータから取ってきて、表示するまでの順序立った流れになっているので、読む場合も同じようにすれば流れを読みやすくなります。なので読むときはまずはFROM以降!それからSELECT!
これからそう読むように心がけてみようと思います。

順序

今回習ったこととは少し離れるのですが、気になっていたので書いてみます。
というのはWHEREとHAVINGで条件が絞れますが、
複雑になっていくうちにこれがどう違うのか混乱してきたのでどうすればいいのか考えてみました。

例えばカレーの辛さが1〜10まであって、その辛さごとカレーを分けて表示したいのだが、
辛さ3以上のものだけでいい、という場合。

SELECT
  spice
, count(*)
FROM
  currys
WHERE
  spice >= 3
GROUP BY
  spice
;

これと

SELECT
  spice
, count(*)
FROM
  currys
GROUP BY
  spice
HAVING
  spice >= 3
;

これは同じことです。同じことがあるので、何が違うのか混乱してしまいました。
しかし、これは条件として絞った位置が変わってきます。
もしこれがカレーの値段の平均が500円以上だけ、という条件を作ると、

GROUP BY文の前にWHERE文で絞ると、カレーの値段全部の平均を取って絞りますが、
GROUP BY文の後のHAVING分で絞ると、その絞ったものの中でそれぞれ平均の値段を取ります。

こう考えると、同じ条件を絞ることでも場所によってずいぶん意味が変わってきてしまいます。
なので、SQL文を書くときは
条件を絞る順序に注意しないといけないことが分かりました。

SQL文の問題集などで、既にできてある例文を見ると、条件を絞る順序はどちらが先かは明らかに分かりますが、何もない状態から「〜の条件で絞る」ということにWHEREを使うかHAVINGを使うか、すぐに判断するのは今の自分にはまだ難しいです。

今日はこの記事をまとめるのに随分時間がかかりましたが、気になってしょうがなかったのでどうしても書きたかったです。

(2008/07/07 irisawa追記)

お待たせしました。動画上がりました。

part.1

part.2

コメントをどうぞ

名前: (Required)

eMail: (Required)

Website:

Comment:

Spam Protection by WP-SpamFree