SELECT文の処理の仕組みを説明してみた。
SQLのSELECT文は非常に強力です。SQLを使うと複雑な処理も短い命令で行えることがあります。
ですが、複雑な処理を行うSELECT文を書くためには、SELECT文の処理の仕組みをきちんと理解しておく必要があります。
そこで、今回はSELECT文の処理の仕組みを、内部的な処理手順を仮定して説明してみます。*1
なお、今回は一つのテーブルに対する処理のみを説明します。複数テーブルの結合は説明しません。
目次
- SELECT文の処理の基本。
- SELECT文の処理は二つに分けられる。
- テーブルを一行ずつ処理する。
- 条件に当てはまる行を出力する。
- 出力項目を編集する
- 複雑な条件を指定する
- 出力結果を並べ替える。
- テーブル内の複数の行を集約する。
- すべての行を集約する。
- グループ毎に集約する。
SELECT文の処理の基本。
SELECT文の内部的な処理の基本は、テーブルの各行に対する繰り返し処理です。
ここでいう繰り返し処理は、他のプログラミング言語での記述するような繰り返し処理のことです。以下は他のプログラミングで記述した繰り返し処理の例です。
<> // すべての行について繰り返す。 for ( int i = 0; i < rows.length; ++i ) { // 対象の行を処理する。 someProcess( rows[i] ); } < > // すべての行について繰り返す。 for ( $i = 0; $i < count( $rows ); ++$i ) { // 対象の行を処理する。 some_process( $rows[$i] ); }
以降では、この繰り返し処理を念頭に置いて説明していきます。
SELECT文の処理は二つに分けられる。
SELECT文の処理は大まかに以下の二つに分けられます。この二つの処理は大きく異なるため、区別することは非常に重要です。
- テーブルを一行ずつ処理する。
- テーブル内の複数の行を集約する。
二つのうち、どちらの処理が行われるかは以下で判断できます。
- SELECT句で集約関数を使わない場合、テーブルは一行ずつ処理されます。
- SELECT句で集約関数を一つでも使った場合、テーブル内の複数の行を集約が行われます。
ここで、主要な5つの集約関数を挙げます。
- AVG - 平均値を求める
- COUNT - 個数を求める
- MAX - 最大値を求める
- MIN - 最小値を求める
- SUM - 合計を求める
テーブルを一行ずつ処理する。
テーブルの一行ずつの処理を説明します。SELECT句で集約関数を使わない場合、この処理が行われます。
条件に当てはまる行を出力する。
SELECT句、FROM句、WHERE句を使うと、テーブルから条件に当てはまる行だけを取得できます。
SELECT
date, product, price /* (3) */
FROM
sales /* (1) */
WHERE
price > 100 /* (2) */
このSELECT文の処理は、以下のような手順で行われます。
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「price > 100」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行の「date」、「product」そして「price」の値を出力する。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
3. 2行目が条件「price > 100」を満たすことを確認する。 … (2)
4. 2行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
5. 3行目が条件「price > 100」を満たすことを確認する。 … (2)
6. 3行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
7. 4行目が条件「price > 100」を満たすことを確認する。 … (2)
8. 4行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
9. 5行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。<<手順終了>>
最終的に、以下が出力されます。
+----------+--------------+-------+ | date | product | price | +----------+--------------+-------+ | 20090316 | 消しゴム | 150 | | 20090313 | 消しゴム | 150 | | 20090316 | 鉛筆削り | 1000 | +----------+--------------+-------+
出力項目を編集する
SELECT句で演算子や関数を使うと、出力項目の編集を行えます。さらに、asを使って処理結果の列名を指定できます。
SELECT /* (3) */
concat(date, "の", product, "の売上" ) as description, /* (3)-1. */
price * quantity as amount /* (3)-2. */
FROM
sales /* (1) */
WHERE
price > 100 /* (2) */このSELECT文の処理は、以下のような手順で行われます。
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「price > 100」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行をもとに「description」と「amount」を出力する。
(3)-1. 「description」は、「date」の値、「の」、「product」の値、「の売上」を連結したものとする。
(3)-2. 「amount」は、「price」の値×「quantity」の値とする。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
3. 2行目が条件「price > 100」を満たすことを確認する。 … (2)
4. 2行目をもとに「description」と「amount」を出力する。 … (3)
4-1. 「description」は、「date」の値、「の」、「product」の値、「の売上」を連結したものとする。 … (3)-1
⇒ 「20090316の消しゴムの売上」
4-2. 「amount」は、「price」の値×「quantity」の値とする。 … (3)-2
⇒ 「45000」
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
5. 3行目が条件「price > 100」を満たすことを確認する。 … (2)
6. 3行目をもとに「description」と「amount」を出力する。 … (3)
6-1. 「description」は、「date」の値、「の」、「product」の値、「の売上」を連結したものとする。 … (3)-1
⇒ 「20090313の消しゴムの売上」
6-2. 「amount」は、「price」の値×「quantity」の値とする。 … (3)-2
⇒ 「15000」
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
7. 4行目が条件「price > 100」を満たすことを確認する。 … (2)
8. 3行目をもとに「description」と「amount」を出力する。 … (3)
8-1. 「description」は、「date」の値、「の」、「product」の値、「の売上」を連結したものとする。 … (3)-1
⇒ 「20090316の鉛筆削りの売上」
8-2. 「amount」は、「price」の値×「quantity」の値とする。 … (3)-2
⇒ 「20000」
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
9. 5行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。<<手順終了>>
最終的に、以下が出力されます。
+----------------------------------+--------+ | description | amount | +----------------------------------+--------+ | 20090316の消しゴムの売上 | 45000 | | 20090313の消しゴムの売上 | 15000 | | 20090316の鉛筆削りの売上 | 20000 | +----------------------------------+--------+
複雑な条件を指定する
WHERE句で演算子や関数を組み合わせると、複雑な条件判定を行えます。
SELECT
date, product, price /* (3) */
FROM
sales /* (1) */
WHERE
price * quantity = 20000 and substring(date, 5, 4) = "0316" /* (2) */
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行の「date」、「product」そして「price」の値を出力する。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。 … (2)
3. 1行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
4. 2行目が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
5. 3行目が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
6. 4行目が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。 … (2)
7. 4行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
8. 5行目が条件「price * quantity = 20000 and substring(date, 5, 4) = "0316"」を満たすことを確認する。 … (2)
9. 5行目の「date」、「product」そして「price」を出力する。 … (3)<<手順終了>>
最終的に、以下が出力されます。
+----------+-----------------+-------+ | date | product | price | +----------+-----------------+-------+ | 20090316 | ボールペン | 100 | | 20090316 | 鉛筆削り | 1000 | +----------+-----------------+-------+
出力結果を並べ替える。
ORDER BY句を使うと、SELECT句の出力結果を並べ替えることができます。
SELECT
date, product, price /* (3) */
FROM
sales /* (1) */
WHERE
price > 100 /* (2) */
ORDER BY
date /* (4)と(5) */
このSELECT文の処理は、以下のような手順で行われます。
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「price > 100」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行の「date」、「product」そして「price」の値を一時テーブルに出力する。
(4) 一時テーブルを「date」をキーにして並べ替える。
(5) 一時テーブルの内容を出力する。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
3. 2行目が条件「price > 100」を満たすことを確認する。 … (2)
4. 2行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
5. 3行目が条件「price > 100」を満たすことを確認する。 … (2)
6. 3行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
7. 4行目が条件「price > 100」を満たすことを確認する。 … (2)
8. 4行目の「date」、「product」そして「price」を出力する。 … (3)
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
9. 5行目が条件「price > 100」を満たすことを確認する。 … (2)
⇒条件満たさないので、次の行の処理に進む。
これまでのところ、一時テーブルには以下が出力されています。
<<一時テーブル>> +----------+--------------+-------+ | date | product | price | +----------+--------------+-------+ | 20090316 | 消しゴム | 150 | | 20090313 | 消しゴム | 150 | | 20090316 | 鉛筆削り | 1000 | +----------+--------------+-------+
10. 一時テーブルを「date」をキーとして並べ替える。 … (4)
これまでのところ、一時テーブルには以下が出力されています。
<<一時テーブル>> +----------+--------------+-------+ | date | product | price | +----------+--------------+-------+ | 20090313 | 消しゴム | 150 | | 20090316 | 消しゴム | 150 | | 20090316 | 鉛筆削り | 1000 | +----------+--------------+-------+
11. 一時テーブルの内容を出力する。 … (5)<<手順終了>>
最終的に、以下が出力されます。
+----------+--------------+-------+ | date | product | price | +----------+--------------+-------+ | 20090313 | 消しゴム | 150 | | 20090316 | 消しゴム | 150 | | 20090316 | 鉛筆削り | 1000 | +----------+--------------+-------+
テーブル内の複数の行を集約する。
テーブル内の複数の行を集約する処理を説明します。SELECT句で集約関数を一つでも使った場合、この処理が行われます。
すべての行を集約する。
GROUP BY句を指定せずに集約関数を使うと、テーブルのすべての行が集約されます。
SELECT /* (7) */
SUM(price*quantity) /* (4)〜(6) */
as sum_of_amount /* (7)-1. */
FROM
sales /* (1) */
WHERE
product = "消しゴム" /* (2) */このSELECT文の処理は、以下のような手順で行われます。
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「product = "消しゴム"」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行を一時テーブルに出力する。
(4) SUM(price*quantity)用の変数を用意する。
(5) 一時テーブルのすべての行について(6)を繰り返す。
(6) SUM(price*quantity)用の変数に、対象の行の「price」の値×「quantity」の値を加算する。
(7) 「sum_of_amount」を出力する。
(7)-1. 「sum_of_amount」は、SUM(price*quantity)用の変数の内容とする。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)<<対象行:1行目>>
+------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「product = "消しゴム"」を満たすことを確認する。 … (2)
⇒条件を満たさないので、次の行の処理に進む。
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
3. 2行目が条件「product = "消しゴム"」を満たすことを確認する。 … (2)
4. 2行目を一時テーブルに出力する。 … (3)
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
5. 3行目が条件「product = "消しゴム"」を満たすことを確認する。 … (2)
6. 3行目を一時テーブルに出力する。 … (3)
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
7. 4行目が条件「product = "消しゴム"」を満たすことを確認する。 … (2)
⇒条件を満たさないので、次の行の処理に進む。
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
9. 5行目が条件「product = "消しゴム"」を満たすことを確認する。 … (2)
⇒条件を満たさないので、次の行の処理に進む。
このとき、一時テーブルには以下が出力されています。
<<一時テーブル>> +------------+----------+--------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+--------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150 | 300 | | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+--------------+-------+----------+
10. SUM(price*quantity)用の変数を用意する。 … (4)
⇒SUM(price*quantity)用の変数 ＝ 0
11. 一時テーブルのすべての行について(6)を繰り返す。 … (5)
<<対象行:1行目>> +------------+----------+--------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+--------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150 | 300 | +------------+----------+--------------+-------+----------+
12. SUM(price*quantity)用の変数に、対象の行の「price」の値×「quantity」の値を加算する。 … (6)
⇒SUM(price*quantity)用の変数 ＝ 0 ＋ 150 × 300 ＝ 45000
<<対象行:2行目>> +------------+----------+--------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+--------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+--------------+-------+----------+
13. SUM(price*quantity)用の変数に、対象の行の「price」の値×「quantity」の値を加算する。 … (6)
⇒SUM(price*quantity)用の変数 ＝ 45000 ＋ 150 × 100 ＝ 60000
14. 「sum_of_amount」を出力する。 … (7)
14-1. 「sum_of_amount」は、SUM(price*quantity)用の変数の内容とする。 … (7)-1.<<手順終了>>
最終的に、以下が出力されます。
+---------------+ | sum_of_amount | +---------------+ | 60000 | +---------------+
グループ毎に集約する。
GROUP BY句を指定して集約関数を使うと、テーブルの行がグループに分けられ、グループ毎の集約が行われます。
SELECT /* (10) */
product, /* (10)-1. */
SUM(quantity) /* (7)〜(9) */
as sum_of_qunatity /* (10)-2. */
FROM
sales /* (1) */
WHERE
product <> "鉛筆削り" /* (2) */
GROUP BY
product /* (3)〜(6) */このSELECT文の処理は、以下のような手順で行われます。
(1) 「sales」テーブルのすべての行について(2)と(3)を繰り返す。
(2) 対象の行が条件「product <> "鉛筆削り"」を満たすことを確認する。
⇒条件を満たさない場合、次の行の処理に進む。
(3) 対象の行を一時テーブルに出力する。
(4) 一時テーブルを「product」をキーとして並べ替える。
(5) 一時テーブルの行をキー「product」の値でグループに分ける。
(6) 一時テーブルの全てのグループについて(7)〜(10)を繰り返す。
(7) SUM(quantity)用の変数を用意する。
(8) 対象のグループのすべての行について(9)を繰り返す。
(9) SUM(quantity)用の変数に、対象の行の「quantity」の値を加算する。
(10) 「product」と「sum_of_quantity」を出力する。
(10)-1. 「product」は、グループのキー「product」の値とする。
(10)-2. 「sum_of_quantity」は、SUM(quantity)用の変数の内容とする。
例として、以下の「sales」テーブルにこの手順をあてはめてみます。
<<「sales」テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | | 0000000005 | 20090316 | 消しゴム | 150| 300 | | 0000000002 | 20090313 | 消しゴム | 150| 100 | | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
<<手順開始>>
1. 「sales」テーブルのすべての行について(2)と(3)を繰り返す。 … (1)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
2. 1行目が条件「product <> "鉛筆削り"」を満たすことを確認する。 … (2)
3. 1行目を一時テーブルに出力する。 … (3)
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150| 300 | +------------+----------+-----------------+-------+----------+
4. 2行目が条件「product <> "鉛筆削り"」を満たすことを確認する。 … (2)
5. 2行目を一時テーブルに出力する。 … (3)
<<対象行:3行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150| 100 | +------------+----------+-----------------+-------+----------+
6. 3行目が条件「product <> "鉛筆削り"」を満たすことを確認する。 … (2)
7. 3行目を一時テーブルに出力する。 … (3)
<<対象行:4行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000004 | 20090316 | 鉛筆削り | 1000| 20 | +------------+----------+-----------------+-------+----------+
8. 4行目が条件「product <> "鉛筆削り"」を満たすことを確認する。 … (2)
⇒条件を満たさないので、次の行の処理に進む。
<<対象行:5行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100| 200 | +------------+----------+-----------------+-------+----------+
9. 5行目が条件「product <> "鉛筆削り"」を満たすことを確認する。 … (2)
10. 5行目を一時テーブルに出力する。 … (3)
このとき、一時テーブルには以下が出力されています。
<<一時テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100 | 200 | | 0000000005 | 20090316 | 消しゴム | 150 | 300 | | 0000000002 | 20090313 | 消しゴム | 150 | 100 | | 0000000001 | 20090313 | ボールペン | 100 | 200 | +------------+----------+-----------------+-------+----------+
11. 一時テーブルを「product」をキーとして並べ替える。 … (4)
このとき、一時テーブルには以下が出力されています。
<<一時テーブル>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100 | 200 | | 0000000001 | 20090313 | ボールペン | 100 | 200 | | 0000000005 | 20090316 | 消しゴム | 150 | 300 | | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+-----------------+-------+----------+
12. 一時テーブルの行をキー「product」の値でグループに分ける。 … (5)
このとき、一時テーブルは以下のようにグループ分けされます。
<<グループ1>> キー「product」＝「ボールペン」 +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100 | 200 | | 0000000001 | 20090313 | ボールペン | 100 | 200 | +------------+----------+-----------------+-------+----------+ <<グループ2>> キー「product」＝「消しゴム」 +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150 | 300 | | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+-----------------+-------+----------+
13. 一時テーブルの全てのグループについて(7)〜(10)を繰り返す。 … (6)
<<対象グループ:グループ1>> キー「product」＝「ボールペン」 +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100 | 200 | | 0000000001 | 20090313 | ボールペン | 100 | 200 | +------------+----------+-----------------+-------+----------+
14. SUM(quantity)用の変数を用意する。 … (7)
⇒SUM(quantity)用の変数 ＝ 0
15. グループ1のすべての行について(9)を繰り返す。 … (8)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000003 | 20090316 | ボールペン | 100 | 200 | +------------+----------+-----------------+-------+----------+
16. SUM(quantity)用の変数に、1行目の「quantity」の値を加算する。 … (9)
⇒SUM(quantity)用の変数 ＝ 0 ＋ 200 ＝ 200
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000001 | 20090313 | ボールペン | 100 | 200 | +------------+----------+-----------------+-------+----------+
17. SUM(quantity)用の変数に、2行目の「quantity」の値を加算する。 … (9)
⇒SUM(quantity)用の変数 ＝ 200 ＋ 200 ＝ 400
18. 「product」と「sum_of_quantity」を出力する。 … (10)
18-1. 「product」は、グループのキー「product」の値とする。 … (10)-1.
18-2. 「sum_of_quantity」は、SUM(quantity)用の変数の内容とする。 … (10)-2.
<<対象グループ:グループ2>> キー「product」＝「消しゴム」 +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150 | 300 | | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+-----------------+-------+----------+
19. SUM(quantity)用の変数を用意する。 … (7)
⇒SUM(quantity)用の変数 ＝ 0
20. グループ2のすべての行について(9)を繰り返す。 … (8)
<<対象行:1行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000005 | 20090316 | 消しゴム | 150 | 300 | +------------+----------+-----------------+-------+----------+
21. SUM(quantity)用の変数に、1行目の「quantity」の値を加算する。 … (9)
⇒SUM(quantity)用の変数 ＝ 0 ＋ 300 ＝ 300
<<対象行:2行目>> +------------+----------+-----------------+-------+----------+ | no | date | product | price | quantity | +------------+----------+-----------------+-------+----------+ | 0000000002 | 20090313 | 消しゴム | 150 | 100 | +------------+----------+-----------------+-------+----------+
22. SUM(quantity)用の変数に、2行目の「quantity」の値を加算する。 … (9)
⇒SUM(quantity)用の変数 ＝ 300 ＋ 100 ＝ 400
23. 「product」と「sum_of_quantity」を出力する。 … (10)
23-1. 「product」は、グループのキー「product」の値とする。 … (10)-1.
23-2. 「sum_of_quantity」は、SUM(quantity)用の変数の内容とする。 … (10)-2.<<手順終了>>
最終的に、以下が出力されます。
+-----------------+-----------------+ | product | sum_of_qunatity | +-----------------+-----------------+ | ボールペン | 400 | | 消しゴム | 400 | +-----------------+-----------------+
