mysql 按周统计数据
在做数据统计的时候,经常会有按周统计的要求,刚好mysql 有一个week的函数,可以使用,比原来的 通过 DATE_FORMAT进行转换简单方便多了。
实例:按周统计新增客户数
SELECT week(c.CREATED_DATE),count(0) from customer c GROUP BY WEEK(c.CREATED_DATE)
周一为一周的第一天
SELECT week(c.CREATED_DATE,1),count(0) from customer c GROUP BY WEEK(c.CREATED_DATE,1)
其中会出现周的开始时间不一致,导致数据出错的问题。是因为mysql 默认已周日为一周的第一天,国内喜欢用周一作为一周的第一天。这时候可以通过week方法的model参数来调整
Mode First day of week Range Week 1 is the first week …
0 Sunday 0-53 with a Sunday in this year
1 Monday 0-53 with 4 or more days this year
2 Sunday 1-53 with a Sunday in this year
3 Monday 1-53 with 4 or more days this year
4 Sunday 0-53 with 4 or more days this year
5 Monday 0-53 with a Monday in this year
6 Sunday 1-53 with 4 or more days this year
7 Monday 1-53 with a Monday in this year