Blank?=False

「呉下の阿蒙にあらず」をモットーにしたITエンジニアの日々

SQL ゼロから始めるデータベース操作 第3章 集約と並び替え その2

今回は、前回に引き続き集約と並び替えについて。
個人的に最もよく使うのはORDER BY。
金額でのソート、IDでのソート、日付のソートなど思いつくだけでも色々な使い方があります。

今回のキーワード
GROUP BY HAVING ORDER BY

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)


例としてこのようなテーブルを使います。

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 |
+-------------+

イメージにしてみると、こんな感じです。
f:id:stonebeach-dakar:20160423084156p:plain
すべてのレコードが1つのグループになっているイメージです。

ここでカテゴリごとにグループ化して平均値を集計すれば、
f:id:stonebeach-dakar:20160423084919p:plain
というイメージになります。

実際にやってみると、下の結果になります。

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は、どれを表示すればいいんでしょう?複数ありますよね。 f:id:stonebeach-dakar:20160423090340p:plain

なので、何を表示すればわからないからできませんよ!というイメージです。

もし、なんでもいいから1つだけ表示!という場合、MAX,MINなどを使えばいいでしょう。

mysql> SELECT MAX(Name), Category FROM Sample GROUP BY Category;
+--------------+-----------+
| MAX(Name)    | Category  |
+--------------+-----------+
| ガレージ     | NULL      |
| 車           | 乗り物    |
| 椅子         | 家具      |
| 消しゴム     | 文房具    |
+--------------+-----------+

これで1つだけ表示させることができます。
これを利用すると、グループ内で並び替えて先頭の要素を表示!ということができます。

WHEREとHAVINGの関係

WHERE, HAVINGは似たような性質を持っています。 どちらも結果の抽出を行うのが目的です。

例えば、以下のような結果が欲しいときは、

+-----------+----------+------------+
| Category  | Count(*) | Sum(VALUE) |
+-----------+----------+------------+
| 乗り物    |        2 |    2195000 |
+-----------+----------+------------+

以下の2つのアプローチを取ることができます。

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に書くべきだと思います。
私がこのように考える理由は2つあります。
まず1つは、WHERE句とHAVING句の役割の違いという、根本的なものです。
<中略>HAVING句というのはグループに対する条件を指定するものです。
したがって、単なる「行」に対する条件は、WHERE句で書くようにしたほうが
お互いの機能をはっきりさせて、読みやすいコードになります。
もう1つは、実行速度に違いが出てくる事があるためです。

まとめると、
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の関係を意識する。