如何在 MySQL 中按周分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1736010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to group by week in MySQL?
提问by O. Jones
Oracle's table server offers a built-in function, TRUNC(timestamp,'DY'). This function converts any timestamp to midnight on the previous Sunday. What's the best way to do this in MySQL?
Oracle 的表服务器提供了一个内置函数TRUNC(timestamp,'DY'). 此函数将任何时间戳转换为上一个星期日的午夜。在 MySQL 中执行此操作的最佳方法是什么？
Oracle also offers TRUNC(timestamp,'MM')to convert a timestamp to midnight on the first day of the month in which it occurs. In MySQL, this one is straightforward:
Oracle 还提供TRUNC(timestamp,'MM')将时间戳转换为它发生的月份的第一天的午夜。在 MySQL 中，这很简单：
TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01'))
But this DATE_FORMATtrick won't work for weeks. I'm aware of the WEEK(timestamp)function, but I really don't want week number within the year; this stuff is for multiyear work.
但是这个DATE_FORMAT技巧在几周内都不会奏效。我知道这个WEEK(timestamp)功能，但我真的不想要一年中的周数；这些东西适合多年工作。
采纳答案by O. Jones
Figured it out... it's a little cumbersome, but here it is.
想通了……这有点麻烦，但就是这样。
FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))
And, if your business rules say your weeks start on Mondays, change the -1to -2.
而且，如果你的业务规则说你周在星期一开始，改-1到-2。
Edit
编辑
Years have gone by and I've finally gotten around to writing this up. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
几年过去了，我终于有时间写这篇文章了。 http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
回答by mjv
You can use both YEAR(timestamp)and WEEK(timestamp), and use both of the these expressions in the SELECTand the GROUP BYclause.
您可以同时使用YEAR(timestamp)and WEEK(timestamp)，并在SELECTandGROUP BY子句中使用这两个表达式。
Not overly elegant, but functional...
不太优雅，但功能齐全......
And of course you can combine these two date parts in a single expression as well, i.e. something like
当然，您也可以将这两个日期部分组合在一个表达式中，即类似于
SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp))
Edit: As Martin points outyou can also use the YEARWEEK(mysqldatefield)function, although its output is not as eye friendly as the longer formula above.
编辑：正如马丁指出的那样，您也可以使用该YEARWEEK(mysqldatefield)函数，尽管它的输出不像上面更长的公式那样对眼睛友好。
Edit 2[3 1/2 years later!]: YEARWEEK(mysqldatefield)with the optional second argument (mode) set to either 0 or 2 is probably the best way to aggregate by completeweeks (i.e. including for weeks which straddle over January 1st), if that is what is desired. The YEAR() / WEEK()approach initially proposed in this answer has the effect of splitting the aggregated data for such "straddling"weeks in two: one with the former year, one with the new year.
A clean-cut every year, at the cost of having up to two partial weeks, one at either end, is often desired in accounting etc. and for that the YEAR() / WEEK()approach is better.
编辑 2[3 1/2 年后！]：YEARWEEK(mysqldatefield)将可选的第二个参数 ( mode) 设置为 0 或 2 可能是按完整周进行聚合的最佳方式（即包括跨越 1 月 1 日的周），如果是这样的话想要什么。YEAR() / WEEK()此答案中最初提出的方法具有将此类“跨越”周的汇总数据分成两部分的效果：一个是前一年，一个是新年。
每年进行一次清理，代价是最多有两个部分周，两端各一个，这在会计等中通常是需要的，因此这种YEAR() / WEEK()方法更好。
回答by B Seven
The accepted answer above did not work for me, because it ordered the weeks by alphabetical order, not chronological order:
上面接受的答案对我不起作用，因为它按字母顺序而不是时间顺序对周进行了排序：
2012/1
2012/10
2012/11
...
2012/19
2012/2
Here's my solution to count and group by week:
这是我按周计数和分组的解决方案：
SELECT CONCAT(YEAR(date), '/', WEEK(date)) AS week_name,
YEAR(date), WEEK(date), COUNT(*)
FROM column_name
GROUP BY week_name
ORDER BY YEAR(DATE) ASC, WEEK(date) ASC
Generates:
产生：
YEAR/WEEK YEAR WEEK COUNT
2011/51 2011 51 15
2011/52 2011 52 14
2012/1 2012 1 20
2012/2 2012 2 14
2012/3 2012 3 19
2012/4 2012 4 19
回答by martin clayton
You can get the concatenated year and week number (200945) using the YEARWEEK()function. If I understand your goal correctly, that should enable you to group your multi-year data.
您可以使用YEARWEEK()函数获取连接的年和周数 (200945) 。如果我正确理解您的目标，那么您应该能够对多年数据进行分组。
If you need the actual timestamp for the start of the week, it's less nice:
如果您需要一周开始的实际时间戳，那就不太好：
DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )
For monthly ordering, you might consider the LAST_DAY()function - sort would be by last day of the month, but that should be equivalent to sorting by first day of the month ... shouldn't it?
对于每月排序，您可能会考虑LAST_DAY()函数 - 将按月的最后一天排序，但这应该等同于按月的第一天排序......不是吗？
回答by Xavier
Just ad this in the select :
只需在选择中添加此广告：
DATE_FORMAT($yourDate, \'%X %V\') as week
And
和
group_by(week);
回答by Randy
If you need the "week ending" date this will work as well. This will count the number of records for each week. Example: If three work orders were created between (inclusive) 1/2/2010 and 1/8/2010 and 5 were created between (inclusive) 1/9/2010 and 1/16/2010 this would return:
如果您需要“周结束”日期，这也将起作用。这将计算每周的记录数。示例：如果在（包括）1/2/2010 和 1/8/2010 之间创建了三个工作订单，并且在（包括）1/9/2010 和 1/16/2010 之间创建了 5 个，这将返回：
3 1/8/2010
5 1/16/2010
3 1/8/2010
5 1/16/2010
I had to use the extra DATE() function to truncate my datetime field.
我不得不使用额外的 DATE() 函数来截断我的日期时间字段。
SELECT COUNT(*), DATE_ADD( DATE(wo.date_created), INTERVAL (7 - DAYOFWEEK( wo.date_created )) DAY) week_ending
FROM work_order wo
GROUP BY week_ending;
