注文テーブル
> 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/