MySQL查询当前52周按周计数的项目? [英] MySQL query to count items by week for the current 52-weeks?

问题描述

我有一个要更改的查询，以便为我提供当前 52周的计数.该查询利用了我制作的日历表，其中包含固定范围内的日期列表.目前的查询是选择最大日期和最小日期，而不一定是最近52周.

I have a query that I'd like to change so that it gives me the counts for the current 52 weeks. This query makes use of a calendar table I've made which contains a list of dates in a fixed range. The query as it stands is selecting max and min dates and not necessarily the last 52 weeks.

我想知道如何使我的日历表保持最新状态，这样我才能获得最近52周的时间(即从现在到一年前).还是有另一种方法使查询独立于日历表?

I'm wondering how to keep my calendar table current such that I can get the last 52-weeks (i.e, from right now to one year ago). Or is there another way to make the query independent of using a calendar table?

以下是查询:

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)

有什么想法?

推荐答案

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

Some people dislike this approach but I tend to use a dummy table that contains values from 0 - 1000 and then use a derived table to produce the ranges that are needed -

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

如果您的表具有自动递增的ID，并且有很多行，则可以从中生成它-

If you have a table with an auto-incrementing id and plenty of rows you could generate it from that -

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

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

So, applying this approach to your query you could do something like this -

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?

这篇关于MySQL查询当前52周按周计数的项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！