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_monthcount
2018-032,035
2018-042,369
2018-052,715
2018-062,994
2018-073,203
2018-083,338
2018-093,723
2018-104,571
2018-114,601
2018-124,975
2019-014,807
2019-025,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_monthcountprice
2018-032,03540,772
2018-042,36966,054
2018-052,715100,381
2018-062,994163,641
2018-073,20383,310
2018-083,338114,945
2018-093,723235,429
2018-104,571312,217
2018-114,601454,915
2018-124,975417,500
2019-014,8071,584,720
2019-025,375138,980

参考

Placeholder image

すずろく/suzu6

札幌にいるWEBアプリのエンジニア。最近はテストをもっと書きたい。

@suzu6_py Amazon ほしいものリスト

 関連記事

 新着 or 更新記事