MySQL - 일별통계, 주간통계, 월간통계, 기간별통계

sample 테이블 생성

CREATE TABLE `test_st` (
  `reg_date` date NOT NULL,
  `value` int(11) NOT NULL
);

INSERT INTO `test_st` (`reg_date`, `value`) VALUES
  ('2017-03-01', 4),
  ('2017-03-08', 2),
  ('2017-03-15', 4),
  ('2017-03-22', 4),
  ('2017-04-01', 9),
  ('2017-04-02', 1),
  ('2017-04-04', 2),
  ('2017-04-09', 4),
  ('2017-04-11', 2),
  ('2017-04-16', 4),
  ('2017-04-18', 5),
  ('2017-04-23', 3),
  ('2017-04-25', 4),
  ('2017-04-30', 4),
  ('2017-04-01', 5);

---

MySQL - 일별통계

SELECT DATE(`reg_date`) AS `date`, sum(`value`) FROM test_st GROUP BY `date`;

---

MySQL - 주간통계

SELECT 
  DATE_FORMAT(
    DATE_SUB(
      `reg_date`,
      INTERVAL (DAYOFWEEK(`reg_date`) - 1) DAY
    ),
    '%Y/%m/%d'
  ) AS START,
  DATE_FORMAT(
    DATE_SUB(
      `reg_date`,
      INTERVAL (DAYOFWEEK(`reg_date`) - 7) DAY
    ),
    '%Y/%m/%d'
  ) AS 
END,
DATE_FORMAT(`reg_date`, '%Y%U') AS `date`,
SUM(`value`) 
FROM
  test_st 
GROUP BY DATE ;

---

MySQL - 월간통계

SELECT MONTH(`reg_date`) AS `date`, sum(`value`) FROM test_st GROUP BY `date`;

---

MySQL - 기간별통계

SELECT 
  DATE(`reg_date`) AS `date`,
  SUM(`value`) 
FROM
  test_st 
WHERE DATE(`reg_date`) >= STR_TO_DATE('2017-04-01', '%Y-%m-%d') 
  AND DATE(`reg_date`) <= STR_TO_DATE('2017-04-10', '%Y-%m-%d') 
GROUP BY `date` ;