年度でまとめる MySQL

2016年7月1日

3月締めの年度でまとめてほしいということで、やり方を調べました。
3ヵ月間ずらしてまとめる方法です。

テストデータです。

CREATE TABLE `days` (
  `create_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `days` (`create_date`) VALUES
('2014-12-31 00:00:00'),
('2015-01-01 00:00:00'),
('2015-01-15 00:00:00'),
('2015-01-31 00:00:00'),
('2015-02-01 00:00:00'),
('2015-02-15 00:00:00'),
('2015-02-28 00:00:00'),
('2015-03-01 00:00:00'),
('2015-03-15 00:00:00'),
('2015-03-31 00:00:00'),
('2015-04-01 00:00:00'),
('2015-04-15 00:00:00'),
('2015-04-30 00:00:00'),
('2015-05-01 00:00:00'),
('2015-05-15 00:00:00'),
('2015-05-31 00:00:00'),
('2015-06-01 00:00:00'),
('2015-06-15 00:00:00'),
('2015-06-30 00:00:00'),
('2015-07-01 00:00:00'),
('2015-07-15 00:00:00'),
('2015-07-31 00:00:00'),
('2015-08-01 00:00:00'),
('2015-08-15 00:00:00'),
('2015-08-31 00:00:00'),
('2015-09-01 00:00:00'),
('2015-09-15 00:00:00'),
('2015-09-30 00:00:00'),
('2015-10-01 00:00:00'),
('2015-10-15 00:00:00'),
('2015-10-31 00:00:00'),
('2015-11-01 00:00:00'),
('2015-11-15 00:00:00'),
('2015-11-30 00:00:00'),
('2015-12-01 00:00:00'),
('2015-12-15 00:00:00'),
('2015-12-31 00:00:00'),
('2016-01-01 00:00:00'),
('2016-01-15 00:00:00'),
('2016-01-31 00:00:00'),
('2016-02-01 00:00:00'),
('2016-02-15 00:00:00'),
('2016-02-29 00:00:00'),
('2016-03-01 00:00:00'),
('2016-03-15 00:00:00'),
('2016-03-31 00:00:00'),
('2016-04-01 00:00:00'),
('2016-04-15 00:00:00'),
('2016-04-30 00:00:00'),
('2016-05-01 00:00:00'),
('2016-05-15 00:00:00'),
('2016-05-31 00:00:00'),
('2016-06-01 00:00:00'),
('2016-06-15 00:00:00'),
('2016-06-30 00:00:00'),
('2016-07-01 00:00:00'),
('2016-07-15 00:00:00'),
('2016-07-31 00:00:00'),
('2016-08-01 00:00:00'),
('2016-08-15 00:00:00'),
('2016-08-31 00:00:00'),
('2016-09-01 00:00:00'),
('2016-09-15 00:00:00'),
('2016-09-30 00:00:00'),
('2016-10-01 00:00:00'),
('2016-10-15 00:00:00'),
('2016-10-31 00:00:00'),
('2016-11-01 00:00:00'),
('2016-11-15 00:00:00'),
('2016-11-30 00:00:00'),
('2016-12-01 00:00:00'),
('2016-12-15 00:00:00'),
('2016-12-31 00:00:00'),
('2017-01-01 00:00:00'),
('2017-01-15 00:00:00'),
('2017-01-31 00:00:00'),
('2017-02-01 00:00:00'),
('2017-02-15 00:00:00'),
('2017-02-28 00:00:00'),
('2017-03-01 00:00:00'),
('2017-03-15 00:00:00'),
('2017-03-31 00:00:00'),
('2017-04-01 00:00:00');

DATE_SUB関数を使用して、3ヵ月ずらしているのがポイントです。

SELECT create_date, YEAR(DATE_SUB(create_date, INTERVAL 3 MONTH)) AS nendo
FROM `days`
ORDER BY create_date ASC

SELECT COUNT(create_date) AS num, YEAR(DATE_SUB(create_date, INTERVAL 3 MONTH)) AS nendo
FROM `days`
GROUP BY nendo
-- GROUP BY YEAR(DATE_SUB(create_date, INTERVAL 3 MONTH))
ORDER BY nendo ASC

1. DATE_ADD, DATE_SUB - MySQL 5.6 リファレンスマニュアル

• Tweet