集計系画面の集計、結合、検索条件、ソートの組み合わせについて
集計系画面でも検索機能を組み込んだり、他のテーブルと結合させてその値を表示させたり、
並べ替えを行いたい場合がある。
それが１つのSQLで実現可能か検証した。
サンプルテーブルは以下の2種類。
animalsテーブル
| id |
name |
price |
| 1 |
ヘラクレスオオカブト |
7000 |
| 2 |
タイワンカブト |
1000 |
| 3 |
リュウキュウカブト |
1200 |
| 4 |
カブトムシ |
400 |
| 5 |
ヒラタクワガタ |
2000 |
| 6 |
オオヒラタクワガタ |
3500 |
| 7 |
リュウキュウノコギリクワガタ |
800 |
| 8 |
タテヅノマルバネクワガタ |
10000 |
salesテーブル
| id |
animal_id |
shop_id |
| 1 |
1 |
1 |
| 2 |
1 |
2 |
| 3 |
1 |
2 |
| 4 |
2 |
2 |
| 5 |
3 |
1 |
| 6 |
4 |
3 |
| 7 |
4 |
1 |
| 8 |
5 |
2 |
| 9 |
6 |
2 |
| 10 |
6 |
3 |
まずは普通に結合をしてみる。
SELECT sales.id as sale_id,sales.animal_id,sales.shop_id,
animals.id as animal_id2,animals.name,animals.price
FROM sales
LEFT JOIN
animals ON(sales.animal_id=animals.id)
| sale_id |
animal_id |
shop_id |
animal_id2 |
name |
price |
| 1 |
1 |
1 |
1 |
ヘラクレスオオカブト |
7000 |
| 2 |
1 |
2 |
1 |
ヘラクレスオオカブト |
7000 |
| 3 |
1 |
2 |
1 |
ヘラクレスオオカブト |
7000 |
| 4 |
2 |
2 |
2 |
タイワンカブト |
1000 |
| 5 |
3 |
1 |
3 |
リュウキュウカブト |
1200 |
| 6 |
4 |
3 |
4 |
カブトムシ |
400 |
| 7 |
4 |
1 |
4 |
カブトムシ |
400 |
| 8 |
5 |
2 |
5 |
ヒラタクワガタ |
2000 |
| 9 |
6 |
2 |
6 |
オオヒラタクワガタ |
3500 |
| 10 |
6 |
3 |
6 |
オオヒラタクワガタ |
3500 |
結合状態でグルーピングによる集計を行ってみる。
下記の例では店舗毎に価格を集計。
SELECT COUNT(sales.id) AS cnt ,sales.shop_id,
SUM(animals.price) AS SUM_PRICE
FROM sales
LEFT JOIN
animals ON(sales.animal_id=animals.id)
GROUP BY sales.shop_id
| cnt |
shop_id店舗ID |
SUM_PRICE |
| 3 |
1 |
8600 |
| 5 |
2 |
20500 |
| 2 |
3 |
3900 |
集計の関係上、SELECTには含めることができない項目で検索ができる。
下記の例ではSELECTに含めることのできない動物名で部分一致検索。
SELECT COUNT(sales.id) AS cnt ,sales.shop_id,
SUM(animals.price) AS SUM_PRICE
FROM sales
LEFT JOIN
animals ON(sales.animal_id=animals.id)
WHERE
animals.name LIKE '%カブト%'
GROUP BY sales.shop_id
| cnt |
shop_id |
SUM_PRICE |
| 3 |
1 |
8600 |
| 3 |
2 |
15000 |
| 1 |
3 |
400 |
※注意
集計値で検索する場合はHAVING句を使わねばならない。
上記の例ではcntとSUM_PRICEで検索する場合。
上記の注意書きでも書いたが、集計値で検索する例を以下に示す。
cntに検索条件をかける。
SELECT COUNT( sales.id ) AS cnt, sales.shop_id,
SUM( animals.price ) AS SUM_PRICE
FROM sales
LEFT JOIN animals ON ( sales.animal_id = animals.id )
GROUP BY sales.shop_id
HAVING cnt >2
| cnt |
shop_id |
SUM_PRICE |
| 3 |
1 |
8600 |
| 5 |
2 |
20500 |
※注意
HAVINGは集計値以外の通常フィールド（shop_id)を指定することも可能のようである。
ただ処理に無駄に時間がかかるので、WHEREをなるべく使うこと。
WHEREを使った場合、絞込を行ってから集計するが、
HAVINGは集計してから絞込を行うため、時間がかかるようである。（集計は時間がかかる処理なので）
さらに集計値でソートできる。
SELECT COUNT(sales.id) AS cnt ,sales.shop_id,
SUM(animals.price) AS SUM_PRICE
FROM sales
LEFT JOIN
animals ON(sales.animal_id=animals.id)
WHERE
animals.name LIKE '%カブト%'
GROUP BY sales.shop_id
ORDER BY SUM_PRICE DESC
| cnt |
shop_id |
SUM_PRICE |
| 3 |
2 |
15000 |
| 3 |
1 |
8600 |
| 1 |
3 |
400 |
集計と結合、検索条件、ソートの組み合わせについての結論
集計系の画面でも、他のテーブルと結合、検索機能、ソート機能を１つのSQLで実現できる。
そしてSQLには処理順番がある。そして処理順番は以下の通りである。
- WHEREによる通常フィールドでの絞込
- GROUP BYによる集計処理
- HAVINGによる集計値への絞込
- ODER BYによる並び替え
集計系の画面を作るときには上記のことを念頭に置いておこうと思う。