suzu6MySQLに溜め込んだデータから月次のレポートを作成するためのSQLです。 月毎の売上や購入数など集計していきます。
テーブル
テーブルは購入履歴（purchase_history）とします。
CREATE TABLE purchase_history (
purchase_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
price INT,
purchase_datetime DATETIME
);
priceに購入金額、created_atに購入日があるとしておきます。 データは適当につくりました。
月毎の集計
上記のテーブルで毎月の購入数を出力してみます。
SELECT
DATE_FORMAT(purchase_datetime, '%Y-%m') as purchase_month,
COUNT(*) as count
FROM
purchase_history
GROUP BY
DATE_FORMAT(purchase_datetime, '%Y%m');
出力をMarkdownで整形したもの
| purchase_month | count |
|---|---|
| 2018-03 | 2,035 |
| 2018-04 | 2,369 |
| 2018-05 | 2,715 |
| 2018-06 | 2,994 |
| 2018-07 | 3,203 |
| 2018-08 | 3,338 |
| 2018-09 | 3,723 |
| 2018-10 | 4,571 |
| 2018-11 | 4,601 |
| 2018-12 | 4,975 |
| 2019-01 | 4,807 |
| 2019-02 | 5,375 |
DATETIME型だとGROUP BY DATE_FORMAT(purchase_datetime, '%Y%m')のようにして集計できる。
年、日時での集計
これを応用すれば年や日時での集計もできる。
--- 年単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y')
--- 月単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y%m')
--- 日単位
GROUP BY DATE_FORMAT(purchase_datetime, '%Y%m%d')
売上の合計
金額の合計も出してみる。
SELECT
DATE_FORMAT(purchase_datetime, '%Y-%m') as purchase_month,
COUNT(*) as count,
SUM(price) as price
FROM
purchase_history
GROUP BY
DATE_FORMAT(purchase_datetime, '%Y%m');
SUM()で金額を合計した。
| purchase_month | count | price |
|---|---|---|
| 2018-03 | 2,035 | 40,772 |
| 2018-04 | 2,369 | 66,054 |
| 2018-05 | 2,715 | 100,381 |
| 2018-06 | 2,994 | 163,641 |
| 2018-07 | 3,203 | 83,310 |
| 2018-08 | 3,338 | 114,945 |
| 2018-09 | 3,723 | 235,429 |
| 2018-10 | 4,571 | 312,217 |
| 2018-11 | 4,601 | 454,915 |
| 2018-12 | 4,975 | 417,500 |
| 2019-01 | 4,807 | 1,584,720 |
| 2019-02 | 5,375 | 138,980 |