php - MYSQL:按周间隔分组，大于星期一

Question

假设我们有一个 posts 表，其中包含以下列:id、title、expires_at。我们希望显示每年每周有多少帖子未“过期”。

也许更简单的表达方式是:“按一年中的几周分组的帖子计数，其中 expires_at 日期比每周的开始日期更好”

例如:

-------------------------------------------------
|   Year    |   Week    |   posts_not_expired   |
------------|-----------|-----------------------|
|   2017    |   01      |   22                  |
|   2017    |   02      |   103                 |
|   2017    |   03      |   7                   |
|   ...     |   ...     |   ...                 |
|   2009    |   52      |   63                  |
|-----------|-----------|-----------------------|   

到目前为止我们所拥有的:

SELECT
    COUNT(id) as posts_not_expired,
    YEAR(expires_at) * 100 as Year,
    YEARWEEK(expires_at) as Week,
FROM posts
GROUP BY Year, Week 

Answer 1

您可以使用DAYOFWEEK计算给定一周内未过期的帖子。 (其中 1 = 星期日，2=星期一，..7=星期六)

SELECT
    YEAR(expires_at) as `Year`,
    WEEKOFYEAR(expires_at) as `Week`,
    SUM(DAYOFWEEK(expires_at) > 2) as `posts_not_expired`
FROM posts
GROUP BY YEAR(expires_at), WEEKOFYEAR(expires_at)