如何在MySQL 5.7中按周分组

Question

当我想按月分组时，我使用这个sql在MySQL 5.7中执行组：

select
    max(statistic_time) as statistic_time
    from home_overview_daily
    group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m')
    order by statistic_time desc

但当我每周分组时：

select
        max(statistic_time) as statistic_time
        from home_overview_daily
        group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%w')
        order by statistic_time desc

结果数据如下所示：

1640789999999   2021-12-3
1640703599999   2021-12-2
1640617199999   2021-12-1
1640530799999   2021-12-0
1640444399999   2021-12-6
1640357999999   2021-12-5
1640271599999   2021-12-4
1638284399999   2021-11-2
1638197999999   2021-11-1
1638111599999   2021-11-0
1638025199999   2021-11-6
1637938799999   2021-11-5
1637852399999   2021-11-4
1637765999999   2021-11-3

为什么12月份有7周？我哪里做错了？我使用的格式如下%Y-%m-%u：

select
        max(statistic_time) as statistic_time,
        date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%u')
        from home_overview_daily
        group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%u')
        order by statistic_time desc

但结果是：

1640789999999   2021-12-52
1640530799999   2021-12-51
1639925999999   2021-12-50
1639321199999   2021-12-49
1638716399999   2021-12-48
1638284399999   2021-11-48
1638111599999   2021-11-47
1637506799999   2021-11-46
1636901999999   2021-11-45
1636297199999   2021-11-44

每个月有5周？

Answer 1

一种方法是忽略年份和月份的界限，查看几个星期：

w = FLOOR((TO_DAYS(date_col) - n) / 7)

n是一个介于0到6之间的数字，基于这一周的那一天是“第一天”。

w是一个星期数。

然后向后工作，找出该周的开始日期：

FROM_DAYS(7 * w + n)

示例：

mysql> SET @n := 2;  -- for Monday being the "start of a week"
mysql> SELECT @w := FLOOR((TO_DAYS(CURDATE()) - @n) / 7);
+--------------------------------------------+
| @w := FLOOR((TO_DAYS(CURDATE()) - @n) / 7) |
+--------------------------------------------+
|                                     105502 |
+--------------------------------------------+
mysql> SELECT FROM_DAYS(7 * @w + @n), curdate();
+------------------------+------------+
| FROM_DAYS(7 * @w + @n) | curdate()  |
+------------------------+------------+
| 2021-12-27             | 2021-12-30 |
+------------------------+------------+

(不要使用@变量，这里使用它们是为了便于演示。)