Perlデータ分析入門

SQLで月毎の売り上げを集計してみよう

SQLで月毎の売り上げを集計してみましょう。テーブル作成、データを挿入をし、SQLで月毎の売り上げを集計します。

※データベースは、こちらで作成したものを使用します。

テーブルの作成

テーブル名はsaleです。IDと販売日時と売り上げ価格のデータです。 

MariaDB [kimotosystem]>create table kimotosystem.sale
    -> (
    -> id int AUTO_INCREMENT PRIMARY KEY,
    -> saled_at datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    -> book_id int NOT NULL DEFAULT 0,
    -> price int NOT NULL DEFAULT 0,
    -> quantity int NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [kimotosystem]>
MariaDB [kimotosystem]>desc kimotosystem.sale;
+----------+----------+------+-----+---------------------+----------------+
| Field    | Type     | Null | Key | Default             | Extra          |
+----------+----------+------+-----+---------------------+----------------+
| id       | int      | NO   | PRI | NULL                | auto_increment |
| saled_at | datetime | NO   |     | 0001-01-01 00:00:00 |                |
| book_id  | int      | NO   |     | 0                   |                |
| price    | int      | NO   |     | 0                   |                |
| quantity | int      | NO   |     | 0                   |                |
+----------+----------+------+-----+---------------------+----------------+
5 rows in set (0.01 sec)

データの挿入

売り上げのデータをSQLのinsert文を使って挿入します。 

MariaDB [kimotosystem]>insert into kimotosystem.sale (saled_at, book_id, price, quantity) values 
    -> ('2020-05-03 09:20:00', 1, 800, 3),
    -> ('2020-05-08 10:00:00', 1, 800, 1),
    -> ('2020-05-17 16:00:00', 5, 2800, 1),
    -> ('2020-05-29 17:00:00', 6, 3500, 1),
    -> ('2020-06-02 09:00:00', 2, 1000, 1),
    -> ('2020-06-07 11:00:00', 2, 1000, 3),
    -> ('2020-06-16 15:00:00', 5, 2800, 1),
    -> ('2020-06-28 18:00:00', 7, 4000, 1),
    -> ('2020-07-02 10:00:00', 2, 1000, 2),
    -> ('2020-07-07 12:00:00', 2, 1000, 3),
    -> ('2020-07-16 15:00:00', 5, 2800, 1),
    -> ('2020-07-28 17:00:00', 7, 4000, 1),
    -> ('2020-08-01 09:00:00', 1, 800, 2),
    -> ('2020-08-05 11:00:00', 2, 1000, 1),
    -> ('2020-08-09 14:00:00', 3, 1500, 3),
    -> ('2020-08-24 16:00:00', 4, 2500, 2),
    -> ('2020-09-02 10:00:00', 5, 2800, 2),
    -> ('2020-09-07 11:30:00', 6, 3500, 2),
    -> ('2020-09-11 15:00:00', 3, 1500, 2),
    -> ('2020-09-26 16:30:00', 2, 1000, 1);
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

MariaDB [kimotosystem]>
MariaDB [kimotosystem]>select * from kimotosystem.sale;
+----+---------------------+---------+-------+----------+
| id | saled_at            | book_id | price | quantity |
+----+---------------------+---------+-------+----------+
|  1 | 2020-05-03 09:20:00 |       1 |   800 |        3 |
|  2 | 2020-05-08 10:00:00 |       1 |   800 |        1 |
|  3 | 2020-05-17 16:00:00 |       5 |  2800 |        1 |
|  4 | 2020-05-29 17:00:00 |       6 |  3500 |        1 |
|  5 | 2020-06-02 09:00:00 |       2 |  1000 |        1 |
|  6 | 2020-06-07 11:00:00 |       2 |  1000 |        3 |
|  7 | 2020-06-16 15:00:00 |       5 |  2800 |        1 |
|  8 | 2020-06-28 18:00:00 |       7 |  4000 |        1 |
|  9 | 2020-07-02 10:00:00 |       2 |  1000 |        2 |
| 10 | 2020-07-07 12:00:00 |       2 |  1000 |        3 |
| 11 | 2020-07-16 15:00:00 |       5 |  2800 |        1 |
| 12 | 2020-07-28 17:00:00 |       7 |  4000 |        1 |
| 13 | 2020-08-01 09:00:00 |       1 |   800 |        2 |
| 14 | 2020-08-05 11:00:00 |       2 |  1000 |        1 |
| 15 | 2020-08-09 14:00:00 |       3 |  1500 |        3 |
| 16 | 2020-08-24 16:00:00 |       4 |  2500 |        2 |
| 17 | 2020-09-02 10:00:00 |       5 |  2800 |        2 |
| 18 | 2020-09-07 11:30:00 |       6 |  3500 |        2 |
| 19 | 2020-09-11 15:00:00 |       3 |  1500 |        2 |
| 20 | 2020-09-26 16:30:00 |       2 |  1000 |        1 |
+----+---------------------+---------+-------+----------+
20 rows in set (0.00 sec)

SQLで月毎の売り上げを集計する

select文とGROUP BY句と集計関数を用いて月毎の売り上げを求めます。

sales_datetimeはdatetime型の列のため、date_format関数を利用し'YYYY-MM'の形式にしてgroup byによる集計を行います。 

MariaDB [kimotosystem]>select
    ->   date_format(saled_at, '%Y-%m') as saled_at, sum(price*quantity) as price
    -> from
    ->   kimotosystem.sale
    -> group by date_format(saled_at, '%Y-%m');
+----------+-------+
| saled_at | price |
+----------+-------+
| 2020-05  |  9500 |
| 2020-06  | 10800 |
| 2020-07  | 11800 |
| 2020-08  | 12100 |
| 2020-09  | 16600 |
+----------+-------+
5 rows in set (0.00 sec)

関連情報

Perlクラブ