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
16
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
0
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小提琴演示
#3
0
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
0
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));
#1
16
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
0
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小提琴演示
#3
0
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
0
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));