MySQL中如何按周分组？

发布于 2024-08-11 01:17:20 字数 368 浏览 4 评论 0原文

Oracle 的表服务器提供了一个内置函数 TRUNC(timestamp,'DY')。此函数将任何时间戳转换为上周日的午夜。在 MySQL 中执行此操作的最佳方法是什么？

Oracle 还提供 TRUNC(timestamp,'MM') 将时间戳转换为其发生月份第一天的午夜。在 MySQL 中，这很简单：

TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01'))

但是这个 DATE_FORMAT 技巧在数周内都不起作用。我知道 WEEK(timestamp) 函数，但我真的不想要一年中的周数；这个东西适合多年的工作。

原文

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 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:

TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01'))

But this DATE_FORMAT trick 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾听心声的旋律 2024-08-18 01:17:20

您可以使用 YEAR(时间戳） 和 WEEK(timestamp)，并在 SELECT 和 GROUP BY 子句中使用这两个表达式。

不是过于优雅，但功能齐全......

当然，您也可以将这两个日期部分组合在一个表达式中，即类似

SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp))

编辑：As Martin 指出您还可以使用YEARWEEK(mysqldatefield) 函数，尽管它的输出不如上面较长的公式那么直观。

编辑 2 [3 1/2 年后！]：
YEARWEEK(mysqldatefield)带有可选的第二个参数 (mode）设置为 0 或 2 可能是按 complete< 进行聚合的最佳方式/em> 周（即包括跨越 1 月 1 日的周），如果这是所期望的。本答案中最初提出的 YEAR() / WEEK() 方法具有将此类“跨越”周的聚合数据分成两部分的效果：一个与前一年，与新的一年同在。每年进行一次干净的划分，代价是最多有两个部分周，两端各一个，在会计等领域通常需要，为此，YEAR() / WEEK() 方法是更好的。

You can use both YEAR(timestamp) and WEEK(timestamp), and use both of the these expressions in the SELECT and the GROUP BY clause.

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 out you can also use the YEARWEEK(mysqldatefield) function, although its output is not as eye friendly as the longer formula above.

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 complete weeks (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.

回复收藏 0 原文

凡尘雨 2024-08-18 01:17:20

想通了...有点麻烦，但就是这样。

FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))

而且，如果您的业务规则规定每周从星期一开始，请将 -1 更改为 -2。

编辑

几年过去了，我终于抽出时间来写这篇文章。
https://www.plumislandmedia.net/mysql/sql-reporting-time-间隔/

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 -1 to -2.

Edit

Years have gone by and I've finally gotten around to writing this up.
https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

回复收藏 0 原文

情感失落者 2024-08-18 01:17:20

上面接受的答案对我不起作用，因为它按字母顺序而不是时间顺序对周进行排序：

这是我按周计数和分组的解决方案：

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

生成：

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

The accepted answer above did not work for me, because it ordered the weeks by alphabetical order, not chronological order:

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

回复收藏 0 原文

羁拥 2024-08-18 01:17:20

您可以使用 YEARWEEK() 函数。如果我正确理解您的目标，那么您应该能够对多年数据进行分组。

如果您需要一周开始的实际时间戳，那就不太好：

DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )

对于每月订购，您可以考虑 LAST_DAY() 函数 - 排序将按该月的最后一天排序，但这应该等同于按该月的第一天排序……不应该吗？

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.

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?

回复收藏 0 原文

秋意浓 2024-08-18 01:17:20

只需在选择中添加此内容：

DATE_FORMAT($yourDate, \'%X %V\') as week

并且

group_by(week);

Just ad this in the select :

DATE_FORMAT($yourDate, \'%X %V\') as week

And

group_by(week);

回复收藏 0 原文

貪欢 2024-08-18 01:17:20

如果您需要“周结束”日期，这也可以。这将计算每周的记录数。示例：如果在 2010 年 1 月 2 日（含）和 2010 年 1 月 8 日（含）之间创建了 3 个工作订单，并且在 2010 年 1 月 9 日（含）和 2010 年 1 月 16 日（含）之间创建了 5 个工作订单，则将返回：

3 1/8 /2010
5 1/16/2010

我必须使用额外的 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;

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:

3 1/8/2010
5 1/16/2010

I had to use the extra DATE() function to truncate my datetime field.

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;

回复收藏 0 原文

蓝眸 2024-08-18 01:17:20

上周日：

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,2),'0'),'%X%V%w')

上周一：

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,3),'1'),'%x%v%w')

DATE_FORMAT(date,format) 参考：

%V - 周 (01..53)，其中星期日是一周的第一天； WEEK() 模式 2;与 %X 一起使用
%v - 周 (01..53)，其中星期一是一周的第一天； WEEK() 模式 3;与 %x 一起使用
%w - 一周中的某一天 (0=星期日..6=星期六)
%X - 所在周的年份Sunday是一周的第一天，数字，四位数字；与 %V 一起使用
%x - 一周的年份，其中星期一是一周的第一天，数字，四位数字；与 %v 一起使用

Previous Sunday:

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,2),'0'),'%X%V%w')

Previous Monday:

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,3),'1'),'%x%v%w')

DATE_FORMAT(date,format) reference:

%V - Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v - Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%w - Day of the week (0=Sunday..6=Saturday)
%X - Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x - Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

回复收藏 0 原文

我也只是我 2024-08-18 01:17:20

我喜欢 MySQL 中的 week 函数，但在我的情况下，我想知道一行位于该月的哪一周。我使用了这个解决方案：

其中 run_date 是类似于 2021-02-25 00:00:00 的时间戳

concat ( 
        date_format(run_date, '%Y-%m'), 
        ' wk ', 
        (week(run_date,1) - ( week(date_format(run_date, '%Y-%m-01')) - 1))
        ) as formatted_date

此输出：

2021-02-23 --->    2021-02 wk 4
2021-02-25 --->    2021-02 wk 4
2021-02-11 --->    2021-02 wk 2
2021-03-02 --->    2021-03 wk 1

这背后的想法是我想知道（相对确定地）相关月份的哪一周日期发生了吗？

因此，我们连接：

date_format(run_date, '%Y-%m') 以获得2021-02

，然后添加文字文本字符串wk

然后我们使用：
week(run_date, 1) 获取此记录的周（1 从星期一开始），（这将是 7 因为 02/21 /2021 位于一年中的第 7 周，我们减去该月第一天的周数 - 2021-02-01 的 week() > 是 5，因为它是一年中的第 5 周：

(week(date_format(run_date, '%Y-%m-01'))

不幸的是，这将从 0 开始计数，人们不喜欢这一点，因此我们从串联结果的最后部分减去 1，以便“周”从 1 开始。

I like the week function in MySQL, but in my situation, I wanted to know which week of the month a row was in. I utlized this solution:

where run_date is a timestamp like 2021-02-25 00:00:00

concat ( 
        date_format(run_date, '%Y-%m'), 
        ' wk ', 
        (week(run_date,1) - ( week(date_format(run_date, '%Y-%m-01')) - 1))
        ) as formatted_date

This outputs:

2021-02-23 --->    2021-02 wk 4
2021-02-25 --->    2021-02 wk 4
2021-02-11 --->    2021-02 wk 2
2021-03-02 --->    2021-03 wk 1

The idea behind this is that I want to know (with relative certainty) which week of the month in question did the date occur?

So we concatenate:

date_format(run_date, '%Y-%m') to get 2021-02

then we add the literal text string wk

then we use:
week(run_date, 1) to get the week (1 to start Monday) of this record, (which would be 7 because 02/21/2021 is in the 7th week of the year, and we subtract whatever the week is on the 1st day of this same month - the week() for 2021-02-01 is 5, because it is in the 5th week of the year:

(week(date_format(run_date, '%Y-%m-01'))

Unfortunately, this will start out the counting at 0, which people don't like, so we subtract 1 from the last part of the concatenation result so that the "week" start at 1.

回复收藏 0 原文

无声情话 2024-08-18 01:17:20

这可能是一个不错的选择：

SELECT
 year(datetime_field) as year_date, week(datetime_field) as week_date
FROM
 bd.table
GROUP BY
 year_date, week_date;

它看起来像这样：

'2020', '14'
'2020', '15'
'2020', '16'
'2020', '17'
'2020', '18'

This may be a good option:

SELECT
 year(datetime_field) as year_date, week(datetime_field) as week_date
FROM
 bd.table
GROUP BY
 year_date, week_date;

It would look like this:

'2020', '14'
'2020', '15'
'2020', '16'
'2020', '17'
'2020', '18'

回复收藏 0 原文

~没有更多了~

关于作者

尸血腥色

暂无简介

0 文章

0 评论

22 人气

关注发私信

友情链接

文江博客

MySQL中如何按周分组？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（9）

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

MySQL中如何按周分组？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（9）

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。