返回按周分组的行数MySQL

Question

我正在尝试从MySQL表中获取行数，该表中的数据是按周分组的。

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

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

此查询返回以下数据：

​

​

如你所见，周数不正确，我期望从1月7日星期一开始的每周的数据(7,14,21,28,4,11等)一直持续到这周。

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

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)

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

可能与存储在DATETIME: twitTimeExtracted列中的几行数据不一致？我真的不知道我对MySQL不是很有经验。

任何帮助都将不胜感激。

谢谢

Answer 1

这会将datetime值转换为一周中相应的星期一

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

yearweek同时返回week和year。str_to_date和字符串monday一起提供星期一的datetime值。

如果你的一周是从Monday开始的，那就改用yearweek(twitTimeExtracted, 1)。

Answer 2

可以使用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

Answer 3

试试这个：

 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