概要
インラインビューを使って、各レコードのある列の値のシェアを計算できる。
以下、実行例に沿ってその方法を整理する。
実行例
以下のデータを使う。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM components; +-----------+----------+ | component | quantity | +-----------+----------+ | 成分A | 60 | | 成分B | 35 | | 成分C | 15 | +-----------+----------+ 3 rows in set (0.00 sec) |
ここでは各成分の量quantityのシェアを計算する。
|
1 2 3 4 5 6 7 8 9 |
SELECT component, quantity, total, quantity / total AS rate FROM components, (SELECT SUM(quantity) AS total FROM components) AS view ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT -> component, -> quantity, -> total, -> quantity / total AS rate -> FROM -> components, -> (SELECT SUM(quantity) AS total FROM components) AS view -> ; +-----------+----------+-------+--------+ | component | quantity | total | rate | +-----------+----------+-------+--------+ | 成分A | 60 | 110 | 0.5455 | | 成分B | 35 | 110 | 0.3182 | | 成分C | 15 | 110 | 0.1364 | +-----------+----------+-------+--------+ 3 rows in set (0.00 sec) |
手順
合計値の計算
合計値はSUM()関数で計算。
|
1 |
SELECT SUM(quantity) FROM components; |
|
1 2 3 4 5 6 7 |
mysql> SELECT SUM(quantity) FROM components; +---------------+ | SUM(quantity) | +---------------+ | 110 | +---------------+ 1 row in set (0.00 sec) |
インラインビューによる合計列の追加
インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな合計値の列が追加される。
|
1 2 3 4 5 |
SELECT component, quantity, total FROM components, (SELECT SUM(quantity) AS total FROM components) AS view ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT component, quantity, total -> FROM -> components, -> (SELECT SUM(quantity) AS total FROM components) AS view -> ; +-----------+----------+-------+ | component | quantity | total | +-----------+----------+-------+ | 成分A | 60 | 110 | | 成分B | 35 | 110 | | 成分C | 15 | 110 | +-----------+----------+-------+ 3 rows in set (0.00 sec) |
各レコードのシェアの計算
各レコードのquantityを合計値で割ってシェアを計算する。
|
1 2 3 4 5 6 7 8 9 |
SELECT component, quantity, total, quantity / total AS rate FROM components, (SELECT SUM(quantity) AS total FROM components) AS view ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT -> component, -> quantity, -> total, -> quantity / total AS rate -> FROM -> components, -> (SELECT SUM(quantity) AS total FROM components) AS view -> ; +-----------+----------+-------+--------+ | component | quantity | total | rate | +-----------+----------+-------+--------+ | 成分A | 60 | 110 | 0.5455 | | 成分B | 35 | 110 | 0.3182 | | 成分C | 15 | 110 | 0.1364 | +-----------+----------+-------+--------+ 3 rows in set (0.00 sec) |