返回按周MySQL分组的行数

I am trying to get the row count from a MySQL table where the data is grouped by WEEK.

我试图从MySQL表中获取行数，其中数据按WEEK分组。

So far the query I have is:

到目前为止，我的查询是：

 "SELECT count(*) as tweets, twitTimeExtracted as date 
  FROM scene.twitData 
  group by week(twitTimeExtracted)"

This query returns the data below:

此查询返回以下数据：

As you can see, the weeks are not correct, I'm expecting data for each week starting with Monday 7th January (7,14,21,28,4,11 etc...) and running through to this week.

正如你所看到的那样，周数不正确，我期待从1月7日（7,14,21,28,4,11等等）开始每周的数据并持续到本周。

I also tried a modified version of the orignal query:

我还尝试了orignal查询的修改版本：

SELECT count(*) as tweets, twitTimeExtracted as date 
FROM scene.twitData 
WHERE date(twitTimeExtracted) 
   BETWEEN '2013-01-07' and '2013-03-11' 
group by week(twitTimeExtracted)

This returns similar results as the first query.

这将返回与第一个查询类似的结果。

Maybe there is an inconsistency with some data stored in the DATETIME: twitTimeExtracted column on a few rows of data? I don't really know I'm not very experienced with MySQL.

也许与DATETIME中存储的一些数据不一致：twitTimeExtracted列在几行数据上？我真的不知道我对MySQL不是很有经验。

Any help would really be appreciated.

真的很感激任何帮助。

Thanks

谢谢

4 个解决方案

#1

This converts the datetime value to the appropriate Monday of the week

这会将datetime值转换为适当的星期一

select count(*) as tweets,
       str_to_date(concat(yearweek(twitTimeExtracted), ' monday'), '%X%V %W') as `date`
from twitData 
group by yearweek(twitTimeExtracted)

yearweek returns both week and year. Together with the string monday, str_to_date gives you the Monday's datetime value.

yearweek返回周和年。与字符串星期一一起，str_to_date为您提供星期一的日期时间值。

If your week starts on Monday, use yearweek(twitTimeExtracted, 1) instead.

如果您的周从星期一开始，请使用yearweek（twitTimeExtracted，1）。

#2

One option to always get the Monday of the week is to use adddate:

总是得到星期一的一个选择是使用adddate：

SELECT count(*) as tweets, adddate(twitTimeExtracted, INTERVAL 2-DAYOFWEEK(twitTimeExtracted) DAY)
FROM twitData 
WHERE date(twitTimeExtracted) 
   BETWEEN '2013-01-07' and '2013-03-11' 
GROUP BY adddate(twitTimeExtracted, INTERVAL 2-DAYOFWEEK(twitTimeExtracted) DAY)

SQL Fiddle Demo

SQL小提琴演示

#3

try this:

尝试这个：

 SELECT count(*) as tweets, 
     date_add('7 Jan 2012', day, datediff('7 Jan 2012', twitTimeExtracted)) % 7   
 FROM scene.twitData 
 group by date_add('7 Jan 2012', day, datediff('7 Jan 2012', twitTimeExtracted)) % 7

#4

An alternate solution:

另一种解决方案：

SELECT DATE(DATE_SUB(yourDate, INTERVAL WEEKDAY(yourDate) DAY));

Eg:

例如：

SELECT DATE(DATE_SUB(DATE("2016-04-11"), INTERVAL WEEKDAY(DATE("2016-04-11")) DAY));