按周分组，如何获取空白周？

Question

我有下面的代码，它将一些事件（YYYY-MM-DD HH:MM:SS）按周进行分组，并显示为“Y:2010 - Week: 50”这样的格式。

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(*) as number 

它工作得很好，但我想返回所有的周数，即使没有活动安排在其中。

如果第三周中没有注册事件，目前我会得到：

week 1: 10
week 2: 1
week 4: 2

我想要获得：

week 1: 10
week 2: 1
week 3: 0
week 4: 2

- Danilo

---

你想要从什么时候开始的所有周？从时间的开始吗？ - thkala

所有在第一次事件发生的周数，或者最近的N个月都可以。它应该只是一个“连续”的时间段。 - Danilo

Answer 1

SQL 无法返回某个表中不存在的行。为了获得所需的效果，您需要创建一个名为 Weeks（WeekNo INT）的表，每年有一行，表示可能的周数（如果我没记错的话，根据计算方法，可能是 53 或 54 周）。

然后，使用 OUTER JOIN 将此表与您的常规结果连接，以添加额外的周数。

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(date) as number 
    FROM YourTable RIGHT OUTER JOIN Weeks ON WEEK(YourTable.date) = Weeks.WeekNo

<更新>：请注意使用COUNT(date)而不是COUNT(*)。在累加COUNT时，SQL将不包括日期列中的NULL值。由于缺失的周没有任何日期，因此这将正确地为这些周提供0个事件。

- Larry Lustig

---

谢谢你的建议。我正在尝试按照这种方式操作，但是右连接会返回数字1，即使在那一周没有事件，对吗？ - Danilo

你必须将COUNT(*)更改为COUNT(date)。由于日期在“缺失”的周中将为NULL，因此不会包括在计数中。我会更新我的答案。 - Larry Lustig

太棒了！如果我没错的话，你的答案还需要一个“按周分组（Weeks.WeekNo）”才能正常工作。 - Danilo

是的，没错。我只是从原始问题中获取了你的SQL片段并添加了JOIN，你仍然需要应用之前使用的过滤、分组和排序（当然要使用WeekNo而不是regweek）。 - Larry Lustig

Answer 2

最终，我决定按照以下方式解决问题，为周创建一个临时表，并使用在上面找到的答案中发现的代码。

CREATE TEMPORARY TABLE weeks (
         id INT
       );
INSERT INTO weeks (id) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54);
SELECT 
w.id, COUNT(c.issuedate) as numberPerWeek
FROM tableName c RIGHT OUTER JOIN weeks w ON WEEK(c.issuedate) = w.id group by w.id;

- Danilo

---

你实际上是用这种方法创建了一个临时数字表来生成结果 :-) 这是一种非常好的通用技术，值得深入学习。 - eftpotrm

Answer 3

这是数字/计数表所制作的问题类型 - 只是一个简单的表格，从0开始计数到任何数字。它们通常很有用，我总是建议将它们添加到您的数据库中。

通过这种方式，您可以相对容易地动态生成一周列表，而无需预先计算一张特定的周表（因此您既不需要一个巨大的表格，也不必担心周数用尽），然后将其与您的约会列表左连接以显示所有包含约会的周。

快速入门：

declare @min    datetime
set     @min    ='2010-01-01'

select
    dateadd(wk, number, @min) as weekDate
from
    numbers
where
    number between 0 and datediff(wk,@min,getdate())

使用

- eftpotrm