15
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Posted at

注文テーブル

> 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/

15
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
15
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?