A database question for storing and computing with historical data.

Question

I have an application that polls different bins at different times and computes the number of widgets in each bin based on the weight. Polling is done every few minutes and the result is timestamped and added to a MySQL table. The table contains the 3 columns below. The example shows 3 bins (A,B and C) but there could be anywhere from 1 to 10 bins for the same widget. (Tiny widgets could be just in 1 or 2 bins and larger widgets may take up more bins)

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

The application needs to generate a 'stock history' report - this would involve computing at each timestep what is the total no. of items from all bins at that timestep. For this example, the report will contain only the timestamp column and the CountHistory column (last column) below (the other columns are shown just to show the computations)

At time 1, A has been polled and has 8 widgets. B and C have not been polled. So the total is 8.

At time 2, B has been polled and has 7 widgets. So the total is 17

At time 3, C has been polled and has 4 widgets. So the total is 19

At time 4, A polled again and has only 1 widget. So the total is now 1+4+7=12

..and so on.

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

I would appreciate any help on how best to go about this. I tried to create a temporary table, scroll through each record using a cursor but could not get the correct queries.

(I had asked a related question before but I did not frame the question correctly and goofed up the example as well. http://stackoverflow.com/questions/1160729/need-help-with-queries-views-in-microsoft-access)

Answer 1

Without an actual model (DDL) and data (including expected output), I'm not sure I would attempt to provide actual, syntax-checked sample SQL. And although I do think this would be easy to do in application code or with a cursor, I think it could be done in a single query.

That said, it would be something like this--consider this 100% pseudocode:

SELECT
 timestamp,
 bin,
 widget_count,
 (SELECT A that is most recent and <= current row's timestamp) A,
 (SELECT B that is most recent and <= current row's timestamp) B,
 (SELECT C that is most recent and <= current row's timestamp) C,
 (SELECT sum of last three columns) CountHistory
FROM
 widget_bin_table

It could probably be done with joins instead of subqueries, and joins would liekly be more efficient. But you get the idea.

---

Edit: ok, the question got the brain blood flowing, and I couldn't rest until I solved it to my satisfaction. Then I came back to post an update and Paul had already answwered with a more elegent solution. :)

Here is my solution with working SQL (using SQL Server) in case anyone cares:

SELECT
    timestamp, bin, widget_count, A, B, C, A + B + C CountHistory
FROM
    (
     SELECT
        wb.timestamp,
        wb.bin,
        wb.widget_count,
        ISNULL((SELECT TOP 1 widget_count FROM widget_bin wb1 WHERE wb1.bin = 'A' 
                AND wb1.timestamp <= wb.timestamp ORDER BY wb1.timestamp DESC), 0) A,
        ISNULL((SELECT TOP 1 widget_count FROM widget_bin wb1 WHERE wb1.bin = 'B' 
                AND wb1.timestamp <= wb.timestamp ORDER BY wb1.timestamp DESC), 0) B,
        ISNULL((SELECT TOP 1 widget_count FROM widget_bin wb1 WHERE wb1.bin = 'C' 
                AND wb1.timestamp <= wb.timestamp ORDER BY wb1.timestamp DESC), 0) C
     FROM
        widget_bin wb
    ) sub

Answer 2

I think this will do what you need. The inner query finds the most recent timestamp at which each bin was changed for each timestamp.

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