php - 每 30 天范围内的列计数

Question

所以我有一个如下所示的表格:

Person     Product     Date         Quantity
1            A       1/11/2014        1
2            A       1/11/2014        2
1            A       1/20/2014        2
3            A       1/21/2014        1
3            B       1/21/2014        1
1            A       1/25/2014        1

我想查找产品为 A 且人员数量 > 1 在任何 30 天滑动范围内的数量。另一个关键是，一旦两条记录满足标准，它们就不应该再次添加到计数中。例如，人员 1 对于 1/11 和 1/20 的计数为 3，但对于 1/20 和 1/25 的计数不为 3。人员 2 的计数为 2。人员 3 将不会显示在结果中，因为第二个产品是 B。此查询也将在特定日期范围内运行(例如，2014 年 1 月 1 日 - 2014 年 10 月 27 日) )。

我的产品是用 MySQL 和 PHP 编写的，我更愿意只在 MySQL 中执行此操作，但这看起来更像是一个 OLAP 问题。我非常感谢任何指导。

Answer 1

Another key is that once two records meet the criteria, they should not add to the count again.

这不是关系性的。为了使其有意义，我们必须定义记录的评估顺序。虽然 SQL 确实有 ORDER BY，但这仅用于显示目的。它不影响查询的计算顺序。评估顺序并不重要。

我根本不相信这可以表示为 SELECT 查询。如果我是对的，那么您就只剩下 plSQL 或非 SQL 语言了。

如果您愿意放弃此要求(并且可能在后处理中实现它，请参见下文)，那么这是可行的。首先查看所有相关日期范围:

CREATE VIEW date_ranges(
    start_date, -- DATE
    end_date -- DATE
) AS
SELECT DISTINCT date, DATE_ADD(date, INTERVAL 30 day)
FROM your_table;

现在，创建相关计数的 View :

CREATE VIEW product_counts(
    person, -- INTEGER REFERENCES your_table(person)
    count, -- INTEGER
    start_date, -- DATE
    end_date -- DATE
) AS
SELECT y.person,
    sum(y.quantity),
    r.start_date,
    r.end_date
FROM date_ranges r
JOIN your_table y
ON y.date BETWEEN r.start_date AND r.end_date
GROUP BY y.person
HAVING sum(y.quantity) > 1;

对于后处理，您需要查看 product_counts View 中的每一行，并查找与其对应的采购订单(your_table 的行)。检查您之前是否见过这些订单(使用哈希集)，如果是，则将它们排除在考虑范围之外，减少当前项目的数量，并可能完全消除它。最好使用 SQL 以外的过程语言来完成此操作。