mysql - 历史数据存储和计算的数据库问题

Question

我有一个应用程序，可以在不同时间轮询不同的垃圾箱，并根据重量计算每个垃圾箱中的小部件数量。每隔几分钟进行一次轮询，结果带有时间戳并添加到 MySQL 表中。该表包含以下 3 列。该示例显示了 3 个容器(A、B 和 C)，但同一小部件​​可能有 1 到 10 个容器。 (小型小部件可能仅位于 1 或 2 个垃圾箱中，较大的小部件可能会占用更多垃圾箱)

timestamp   bin Widget_Count
--------------------------
1           A       8
2           B       7
3           C       4
4           A       1
5           B       3
6           C       5
7           A       6
8           B       7
9           C       2

应用程序需要生成“库存历史”报告 - 这将涉及在每个时间步计算总数量是多少。该时间步长中所有垃圾箱中的元素数量。对于此示例，报告将仅包含下面的时间戳列和 CountHistory 列(最后一列)(显示其他列只是为了显示计算结果)

在时间 1，A 已被轮询并拥有 8 个小部件。 B 和 C 尚未进行民意调查。所以总数是 8。

在时间 2，B 已被轮询并拥有 7 个小部件。所以总数是 17

在时间 3，C 已被轮询并拥有 4 个小部件。所以总数是 19

在时间 4，A 再次轮询，并且只有 1 个小部件。所以现在总数是 1+4+7=12

..等等。

timestamp   bin Widget_     A   B   C   CountHistory
                Count                    (stock flow)
--------------------------------------------------------
1           A       8       8   0   0       8
2           B       7       8   7   0       15
3           C       4       8   7   4       19
4           A       1       1   7   4       12
5           B       3       1   3   4       8
6           C       5       1   3   5       9
7           A       6       6   3   5       14
8           B       7       6   7   5       18
9           C       2       6   7   2       15

如果您能提供有关如何最好地解决此问题的帮助，我将不胜感激。我尝试创建一个临时表，使用游标滚动浏览每条记录，但无法获得正确的查询。

(我之前曾问过一个相关问题，但我没有正确地提出问题，并且也搞砸了示例。Need Help with queries (views) in Microsoft Access)

Answer 1

我认为这将满足您的需求。内部查询查找每个时间戳的每个 bin 更改的最新时间戳。

SELECT m.timestamp, SUM(w.Widget_Count) AS CountHistory
FROM (
    SELECT a.timestamp, b.bin, MAX(b.timestamp) AS intMostRecentTimestamp
    FROM WidgetTable a
        CROSS JOIN WidgetTable b
    WHERE a.timestamp >= b.timestamp
    GROUP BY a.timestamp, b.bin, a.Widget_Count
) m 
    INNER JOIN WidgetTable w ON m.intMostRecentTimestamp = w.timestamp
GROUP BY m.timestamp
ORDER BY m.timestamp