SQL ゼロから始めるデータベース操作 第３章 集約と並び替え その2
今回は、前回に引き続き集約と並び替えについて。
個人的に最もよく使うのはORDER BY。
金額でのソート、IDでのソート、日付のソートなど思いつくだけでも色々な使い方があります。
今回のキーワード
GROUP BY HAVING ORDER BY
CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2010/06/29
- メディア: 大型本
- 購入: 39人 クリック: 484回
- この商品を含むブログ (16件) を見る
例としてこのようなテーブルを使います。
| id | Name | VALUE | Date | Category |
|---|---|---|---|---|
| 0001 | 車 | 2000000 | 2009-11-25 | 乗り物 |
| 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 |
| 0003 | キャビネット | 12500 | 2009-11-27 | 家具 |
| 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 |
| 0005 | バイク | 195000 | 2009-12-21 | 乗り物 |
| 0006 | 椅子 | 8500 | 2010-05-12 | 家具 |
| 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 |
| 0008 | クレヨン | 820 | 2010-06-15 | 文房具 |
| 0009 | ガレージ | 5282000 | 2010-09-01 | NULL |
| 0010 | MacBookPro | NULL | 2011-02-03 | NULL |
趣味がモロバレ？気にしない！
書いてて気づいたが12500円の消しゴムってなんだ
文法
GROUP BY
指定したキー（フィールド）の値でグループ分けします。 以下の例の場合、各カテゴリごとに分けて、それぞれのカテゴリの個数を求めています。
mysql> SELECT Category, COUNT(*) FROM sample GROUP BY Category; +-----------+----------+ | Category | COUNT(*) | +-----------+----------+ | NULL | 2 | | 乗り物 | 2 | | 家具 | 2 | | 文房具 | 4 | +-----------+----------+
HAVING
GROUP分けしたもののWHERE文に相当するもの。
以下の例ではカテゴリごとにグループ分けを行い、グループの合計VALUEが100000以上のものを抽出しています。
mysql> SELECT Category, Sum(VALUE) FROM Sample GROUP BY Category HAVING Sum(VALUE) >= 100000; +-----------+------------+ | Category | Sum(VALUE) | +-----------+------------+ | NULL | 5282000 | | 乗り物 | 2195000 | +-----------+------------+
グループ分けしないで使おうとすると怒られます。
こういう時はWHEREを使うべし！
グループ化前はWHERE,グループ化したらHAVING!コレがSQLの約束だ！
mysql> SELECT * FROM Sample HAVING Sum(VALUE) > 1000; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'shop.Sample.id'; this is incompatible with sql_mode=only_full_group_by
ORDER BY
指定したフィールド及び条件で並び替えを行う。
非常に使用頻度が高い。
mysql> SELECT * FROM Sample ORDER BY VALUE; +------+--------------------+---------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+--------------------+---------+------------+-----------+ | 0010 | MacBookPro | NULL | 2011-02-03 | NULL | | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | | 0006 | 椅子 | 8500 | 2010-05-12 | 家具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0003 | キャビネット | 12500 | 2009-11-27 | 家具 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0005 | バイク | 195000 | 2009-12-21 | 乗り物 | | 0001 | 車 | 2000000 | 2009-11-25 | 乗り物 | | 0009 | ガレージ | 5282000 | 2010-09-01 | NULL | +------+--------------------+---------+------------+-----------+
ORDER BY VALUEの後に何も指定しなかった場合、もしくは'ASC' と入力した場合、VALUEフィールドの値で昇順で並び替えを行う。
'DESC'と入力した場合、降順で並び替えを行う。
mysql> SELECT * FROM Sample ORDER BY VALUE DESC; +------+--------------------+---------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+--------------------+---------+------------+-----------+ | 0009 | ガレージ | 5282000 | 2010-09-01 | NULL | | 0001 | 車 | 2000000 | 2009-11-25 | 乗り物 | | 0005 | バイク | 195000 | 2009-12-21 | 乗り物 | | 0003 | キャビネット | 12500 | 2009-11-27 | 家具 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0006 | 椅子 | 8500 | 2010-05-12 | 家具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0010 | MacBookPro | NULL | 2011-02-03 | NULL | +------+--------------------+---------+------------+-----------+
個人的にはDESCをよく使う。
実例では、ブログの記事の一覧。 日付を降順ソートすると、
最も新しい日付が先頭になって、順番に古い日付に向かっていく形になるんですよね。
そこから先頭から何個か取り出してるんですよ。
実例
GROUPと集約関数
ここまで、いくつかGROUPと集約関数を降りませた例をあげましたが、
より深く見ていきます。
GROUPを使わない場合、集約関数を使うと単一の結果だけになります。
mysql> SELECT AVG(VALUE) FROM Sample; +-------------+ | AVG(VALUE) | +-------------+ | 835546.6667 | +-------------+
イメージにしてみると、こんな感じです。
すべてのレコードが１つのグループになっているイメージです。
ここでカテゴリごとにグループ化して平均値を集計すれば、
というイメージになります。
実際にやってみると、下の結果になります。
mysql> SELECT AVG(VALUE) FROM Sample GROUP BY Category; +--------------+ | AVG(VALUE) | +--------------+ | 5282000.0000 | | 1097500.0000 | | 10500.0000 | | 5480.0000 | +--------------+
GROUP BYを使った時のSELECTの制限
GROUP BYを使う場合、SELECTで指定できるフィールドに制限がかかります。
GROUPに指定したフィールドか、集約関数,もしくは定数しか指定できません。
何故かと言うと、「表示できるものがない」からです。
例えば、以下のようなSQL文があったとします。
SELECT Name, Category FROM Sample GROUP BY Category;
この実行結果はエラーです。
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.Sample.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
このSQL文のイメージを考えてみると、
Categoryでグループ分けされているので、Categoryは表示できます。
でも、NAMEは、どれを表示すればいいんでしょう？複数ありますよね。
なので、何を表示すればわからないからできませんよ！というイメージです。
もし、なんでもいいから１つだけ表示！という場合、MAX,MINなどを使えばいいでしょう。
mysql> SELECT MAX(Name), Category FROM Sample GROUP BY Category; +--------------+-----------+ | MAX(Name) | Category | +--------------+-----------+ | ガレージ | NULL | | 車 | 乗り物 | | 椅子 | 家具 | | 消しゴム | 文房具 | +--------------+-----------+
これで１つだけ表示させることができます。
これを利用すると、グループ内で並び替えて先頭の要素を表示！ということができます。
WHEREとHAVINGの関係
WHERE, HAVINGは似たような性質を持っています。
どちらも結果の抽出を行うのが目的です。
例えば、以下のような結果が欲しいときは、
+-----------+----------+------------+ | Category | Count(*) | Sum(VALUE) | +-----------+----------+------------+ | 乗り物 | 2 | 2195000 | +-----------+----------+------------+
以下の２つのアプローチを取ることができます。
mysql> select Category, Count(*), Sum(VALUE) FROM sample WHERE Category='乗り物' GROUP BY Category ;
mysql> select Category, Count(*), Sum(VALUE) FROM sample GROUP BY Category HAVING Category='乗り物' ;
では、どう使い分けていくか。
本書の解説を引用します。
私は、こういう
集約キー(※ここではCategoryのこと)に対する条件はWHEREに書くべきだと思います。
私がこのように考える理由は２つあります。
まず１つは、WHERE句とHAVING句の役割の違いという、根本的なものです。
<中略>HAVING句というのはグループに対する条件を指定するものです。
したがって、単なる「行」に対する条件は、WHERE句で書くようにしたほうが
お互いの機能をはっきりさせて、読みやすいコードになります。
もう１つは、実行速度に違いが出てくる事があるためです。
まとめると、
WHERE句= 各レコードに対する条件指定
HAVING句=各グループに対する条件指定
ソースコードでも、同じような使い方ができるからといって
本来の目的ではないことをやらせてしまうと、読みにくいと思ってしまうのと同じですね！
複数のORDER KEY
ORDER BYに指定するキーとなるフィールドは、複数指定できます。
複数指定した場合、左側から優先されます。
例えば、日付ごとに降順ソートして、同じ日付だったら値段を降順ソートする、という処理をしたい場合、
以下の様なSQL文になります。
mysql> SELECT * FROM Sample ORDER BY Date DESC, Value DESC; +------+--------------------+---------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+--------------------+---------+------------+-----------+ | 0010 | MacBookPro | NULL | 2011-02-03 | NULL | | 0009 | ガレージ | 5282000 | 2010-09-01 | NULL | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0006 | 椅子 | 8500 | 2010-05-12 | 家具 | | 0005 | バイク | 195000 | 2009-12-21 | 乗り物 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0003 | キャビネット | 12500 | 2009-11-27 | 家具 | | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0001 | 車 | 2000000 | 2009-11-25 | 乗り物 | +------+--------------------+---------+------------+-----------+
Dateが同じキャビネット、えんぴつはより高いキャビネットが上になっています。
もし、Valueを昇順ソートした場合、
mysql> SELECT * FROM Sample ORDER BY Date DESC, Value ASC; +------+--------------------+---------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+--------------------+---------+------------+-----------+ | 0010 | MacBookPro | NULL | 2011-02-03 | NULL | | 0009 | ガレージ | 5282000 | 2010-09-01 | NULL | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0006 | 椅子 | 8500 | 2010-05-12 | 家具 | | 0005 | バイク | 195000 | 2009-12-21 | 乗り物 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0003 | キャビネット | 12500 | 2009-11-27 | 家具 | | 0001 | 車 | 2000000 | 2009-11-25 | 乗り物 | +------+--------------------+---------+------------+-----------+
という結果になります。こちらではより安いえんぴつが上になっています。
まとめ
- 集約、並び替えは様々な条件で使うことができる。
- WHEREとHAVINGの関係を意識する。
