如何GROUP BY连续的数据（在这种情况下是日期） [英] How to GROUP BY consecutive data (date in this case)

问题描述

我有一个产品表和一个销售表，用于记录给定产品在销售过程中销售的产品数量每个日期。当然，并非所有的产品每天都有销售。

I have a products table and a sales table that keeps record of how many items a given product sold during each date. Of course, not all products have sales everyday.

我需要生成一份报告，告诉我产品销售的时间有多少连续 （从最新日期到过去）以及它在那些日子里卖出的物品数量。

I need to generate a report that tells me how many consecutive days a product has had sales (from the latest date to the past) and how many items it sold during those days only.

我想告诉你我已经尝试了多少东西很远，但唯一成功（和缓慢的递归）是我的应用程序内部的解决方案，而不是内部SQL，这是我想要的。

I'd like to tell you how many things I've tried so far, but the only succesful (and slow, recursive) ones are solutions inside my application and not inside SQL, which is what I want.

我也浏览了几个类似但是我还没有找到让我清楚自己真正需要什么的一个。

I also have browsed several similar questions on SO but I haven't found one that lets me have a clear idea of what I really need.

我已经设置了一个 SQLFiddle here 向你展示我在说什么。在那里你会看到我能想到的唯一的问题，这并不能给我我需要的结果。我还在那里添加了注释，显示查询的结果应该是什么。

I've setup a SQLFiddle here to show you what I'm talking about. There you will see the only query I can think of, which doesn't give me the result I need. I also added comments there showing what the result of the query should be.

我希望这里有人知道如何实现这一点。感谢您的任何意见！

I hope someone here knows how to accomplish that. Thanks in advance for any comments!

Francisco

Francisco

推荐答案

http://sqlfiddle.com/#!2/20108/1

这是一个执行这项工作的存储过程

Here is a store procedure that do the job

CREATE PROCEDURE myProc()
BEGIN
    -- Drop and create the temp table
    DROP TABLE IF EXISTS reached;
    CREATE TABLE reached (
    sku CHAR(32) PRIMARY KEY,
    record_date date,
    nb int,
    total int)
   ENGINE=HEAP;

-- Initial insert, the starting point is the MAX sales record_date of each product
INSERT INTO reached 
SELECT products.sku, max(sales.record_date), 0, 0
FROM products
join sales on sales.sku = products.sku
group by products.sku;

-- loop until there is no more updated rows
iterloop: LOOP
    -- Update the temptable with the values of the date - 1 row if found
    update reached
    join sales on sales.sku=reached.sku and sales.record_date=reached.record_date
    set reached.record_date = reached.record_date - INTERVAL 1 day, 
        reached.nb=reached.nb+1, 
        reached.total=reached.total + sales.items;

    -- If no more rows are updated it means we hit the most longest days_sold
    IF ROW_COUNT() = 0 THEN
        LEAVE iterloop;
    END IF;
END LOOP iterloop;

-- select the results of the temp table
SELECT products.sku, products.title, products.price, reached.total as sales, reached.nb as days_sold 
from reached
join products on products.sku=reached.sku;

END//

然后你只需要做

call myProc()

这篇关于如何GROUP BY连续的数据（在这种情况下是日期）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！