mysql 按周统计数据

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参数来调整

WEEK(date[,mode])

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