取得したデータの平均を求める関数の使い方～AVG集約関数 select～

SQLにもExcelのようにデータを操作する関数があります。今回はデータの平均を計算するAVG集約関数について紹介していきます。

前提条件

商品の売り上げを管理しているテーブルから指定した月の平均単価を計算する

使用するテーブル：ordersテーブル

+------+---------+--------+---------------------+
| id   | user_id | amount | order_time          |
+------+---------+--------+---------------------+
|    1 |       2 |   6000 | 2030-12-31 11:00:00 |
|    2 |       6 |   2000 | 2030-12-25 18:30:00 |
|    3 |       1 |   3000 | 2030-12-10 10:00:00 |
|    4 |       4 |   2500 | 2030-11-01 20:15:00 |
|    5 |       3 |   4000 | 2030-11-30 09:45:00 |
|    6 |       5 |   3600 | 2030-10-15 23:30:00 |
+------+---------+--------+---------------------+
6 rows in set (0.000 sec)

条件

• order_timeカラムの日付が11月のものを取得する
• 11月の商品の売り上げを平均する

11月のデータ一覧を取得するSQL

select * from orders 
where 
order_time >= '2030-11-01 00:00:00' 
and 
order_time < '2030-12-01 00:00:00';

コードの見方

出力結果

+------+---------+--------+---------------------+
| id   | user_id | amount | order_time          |
+------+---------+--------+---------------------+
|    4 |       4 |   2500 | 2030-11-01 20:15:00 |
|    5 |       3 |   4000 | 2030-11-30 09:45:00 |
+------+---------+--------+---------------------+
2 rows in set (0.000 sec)

二つのレコードが取得できたので売り上げの平均を計算していきます。

取得したデータの平均を計算する

構文

select avg(平均を出すカラム名) from orders where 条件;

使用するSQL文

select avg(amount) from orders 
where 
order_time >= '2030-11-01 00:00:00' 
and 
order_time < '2030-12-01 00:00:00';

今回は平均金額を出すのでカラム名はamountを指定します。

条件は11月を指定しています。

出力結果

+-------------+
| avg(amount) |
+-------------+
|   3250.0000 |
+-------------+
1 row in set (0.007 sec)

11月の売り上げの平均客単価が表示されました。

Nullに注意！

カラムの値がNullとなっていた場合には計算されません。

例えば、

12月の売り上げが3つあったとします。

+------+---------+--------+---------------------+
| id   | user_id | amount | order_time          |
+------+---------+--------+---------------------+
|    1 |       2 |   6000 | 2030-12-31 11:00:00 |
|    2 |       6 |        | 2030-12-25 18:30:00 |
|    3 |       1 |   3000 | 2030-12-10 10:00:00 |
+------+---------+--------+---------------------+
3 rows in set (0.001 sec)

idが1と３の売り上げには値が入っていますが、idが２の売り上げには値が入っていません。

この場合の計算式は以下になります。

〇 (6000 + 3000) ÷ 2 = 4500

以下のような計算式にはならない

× (6000 + 3000) ÷ 3 = 3000

計算するカラムに値の入っていないレコードがあると客数に対して計算結果が合わないなどのトラブルが発生します。

これはほかの集約関数を利用した場合にも同様です。

Nullは0を表しているのではなく、値そのものがないので計算にも反映されません。

データベースの設計時点からNullを許容しないなどの制約をしておくことが大事です。