mysql 일별, 주별, 월별, 기간별 통계 쿼리

출처

http://bluexmas.tistory.com/626

# 일별

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

# 주별

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;

# 월별

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

# 기간별

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