MySQL 日次、週次、月次の集計 #MySQL

注文テーブル

> describe `order`;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| buyer_id    | bigint(20)   | NO   | MUL | NULL    |                |
| product_id  | bigint(20)   | YES  | UNI | NULL    |                |
| pay         | bigint(20)   | NO   |     | 0       |                |
| ...                                                                |
+-------------+--------------+------+-----+---------+----------------+

集計用のカレンダーを作成する

CREATE TABLE `calendar` (
  `dt` datetime NOT NULL,
  PRIMARY KEY (`dt`)
)

カレンダーテーブルに日付を入れる

DELIMITER |
CREATE PROCEDURE fill_calendar(start_date DATE, end_date DATE)
BEGIN
  DECLARE crt_date DATE;
  SET crt_date=start_date;
  WHILE crt_date < end_date DO
    INSERT INTO calendar VALUES(crt_date);
    SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
  END WHILE;
END |
DELIMITER ;

CALL fill_calendar('2000-01-01', '2099-12-31');

日次の取得

SELECT
    DATE_FORMAT(c.dt,'%Y-%m-%d')
    , COUNT(o.id) order_count
    , COUNT(DISTINCT o.product_id) product_count
    , COUNT(DISTINCT o.buyer_id) buyer_count
    , IFNULL(SUM(o.pay), 0) total_pay
FROM
    calendar c
LEFT JOIN 
    `order` o
ON
    TO_DAYS(c.dt) = TO_DAYS(o.created_dt)
WHERE
    c.dt >= '2015-06-01 00:00:00'
    AND c.dt < '2015-07-01 00:00:00'
GROUP BY
    c.dt
ORDER BY 
    c.dt;

週次の取得

SELECT
    STR_TO_DATE(CONCAT(YEARWEEK(c.dt, 1),' 1'), '%x%v %w') week_start_date
    , STR_TO_DATE(CONCAT(YEARWEEK(c.dt, 1),' 0'), '%x%v %w') week_end_date
    , COUNT(o.id) order_count
    , COUNT(DISTINCT o.product_id) product_count
    , COUNT(DISTINCT o.buyer_id) buyer_count
    , IFNULL(SUM(o.pay), 0) total_pay
FROM
    calendar c
LEFT JOIN 
    `order` o
ON
    YEARWEEK(c.dt,1) = YEARWEEK(o.created_dt,1)
WHERE
    YEARWEEK(c.dt,1) >= YEARWEEK('2015-06-01 00:00:00',1)
    AND YEARWEEK(c.dt) <= YEARWEEK('2016-07-01 00:00:00',1)
GROUP BY
    YEARWEEK(c.dt,1)
ORDER BY 
    YEARWEEK(c.dt,1);

月次の取得

SELECT
    DATE_FORMAT(c.dt,'%Y-%m')
    , COUNT(o.id) order_count
    , COUNT(DISTINCT o.product_id) product_count
    , COUNT(DISTINCT o.buyer_id) buyer_count
    , IFNULL(SUM(o.pay), 0) total_pay
FROM
    calendar c
LEFT JOIN 
    `order` o
ON
    DATE_FORMAT(c.dt,'%Y%m') = DATE_FORMAT(o.created_dt,'%Y%m')
WHERE
    DATE_FORMAT(c.dt,'%Y%m') >= DATE_FORMAT('2015-06-01 00:00:00','%Y%m')
    AND DATE_FORMAT(c.dt,'%Y%m') <= DATE_FORMAT('2015-07-01 00:00:00','%Y%m')
GROUP BY
    DATE_FORMAT(c.dt,'%Y%m')
ORDER BY 
    DATE_FORMAT(c.dt,'%Y%m');

参考: http://www.media-division.com/using-mysql-generate-daily-sales-reports-filled-gaps/