schedule2019-03-01

MySQLで月次レポートのための集計をする

MySQLに溜め込んだデータから月次のレポートを作成するための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

参考