MySQL 查询按周计算当前 52 周的项目？

Question

我想更改一个查询，以便它为我提供当前 52 周的计数。此查询使用我制作的日历表，其中包含固定范围内的日期列表。目前的查询是选择最大和最小日期，不一定是最近 52 周。

我想知道如何使我的日历表保持最新，以便我可以获得最近 52 周(即从现在到一年前)。或者是否有另一种方法使查询独立于使用日历表？

这是查询:

SELECT calendar.datefield AS date, IFNULL(SUM(purchaseyesno),0) AS item_sales
FROM items_purchased join items on items_purchased.item_id=items.item_id 
RIGHT JOIN calendar ON (DATE(items_purchased.purchase_date) = calendar.datefield)
WHERE (calendar.datefield BETWEEN (SELECT MIN(DATE(purchase_date)) 
FROM items_purchased) AND (SELECT MAX(DATE(purchase_date)) FROM items_purchased)) 
GROUP BY week(date)

想法？

Answer 1

有些人不喜欢这种方法，但我倾向于使用包含 0 - 1000 值的虚拟表，然后使用派生表来生成所需的范围 -

CREATE TABLE dummy (`num` INT NOT NULL);
INSERT INTO dummy VALUES (0), (1), (2), (3), (4), (5), .... (999), (1000);

如果您有一个带有自动递增 ID 和大量行的表，您可以从中生成它 -

CREATE TABLE `dummy`
SELECT id AS `num` FROM `some_table` WHERE `id` <= 1000;

请记住插入 0 值。

SELECT CURRENT_DATE - INTERVAL num DAY
FROM dummy
WHERE num < 365

因此，将这种方法应用于您的查询，您可以执行如下操作 -

SELECT WEEK(calendar.datefield) AS `week`, IFNULL(SUM(purchaseyesno),0) AS item_sales
FROM items_purchased join items on items_purchased.item_id=items.item_id 
RIGHT JOIN (
    SELECT (CURRENT_DATE - INTERVAL num DAY) AS datefield
    FROM dummy
    WHERE num < 365
) AS calendar ON (DATE(items_purchased.purchase_date) = calendar.datefield)
WHERE calendar.datefield >= (CURRENT_DATE - INTERVAL 1 YEAR)
GROUP BY week(datefield) -- shouldn't this be datefield instead of date?