MySQL:按日期范围分组

通过计算周的开始日期和结束日期来尝试下面的查询

select week(expires_at),count(id) as active_products
from product
where expires_at>SUBDATE(expires_at, weekday(expires_at)) and expires_at<DATE(expires_at + INTERVAL (6 - WEEKDAY(expires_at)) DAY)
group by week(expires_at)

*created_at* >= week-start AND expires_at < week-end .

如果在所有周内都创建了产品,则可以使用相关子查询:

select week_of_year,
       (select count(*)
        from products t2
        where yearweek(t2.created_at) >= w.week_of_year and
              yearweek(t2.expires_at) < w.week_of_year
       ) as num_actives
from (select distinct yearweek(created_at) as week_of_year
      from products
     ) w;

YEARWEEK(date) ( https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek 包括当年

SELECT 
count(id) as active_products, YEARWEEK(expires_at)
FROM products
GROUP BY YEARWEEK(expires_at)

使用 WEEK() 不会影响同一周内的年份。