从累积数据中获取单个值的 SQL 查询

Question

以下示例数据具有每个条目的累积大小。我正在尝试编写一个查询来更新大小，大小与先前时间戳的差异(例如 - 第 2 行应该更新为 (4478.74 - 4476) = 2.74)相同的项目名称。请帮我解决这个问题。

Row Item     Size       Time
-----------------------------------------------
1   ItemA    4476       7/01/2012 11:15
2   ItemA    4478.74    7/01/2012 11:20
3   ItemA    4478.82    7/01/2012 11:21
4   ItemA    4487.51    7/01/2012 11:29
5   ItemB    1.53       7/01/2012 11:29
6   ItemB    1.67       7/01/2012 11:20
7   ItemB    1.84       7/01/2012 11:21
8   ItemB    2.18       7/01/2012 11:15

Answer 1

您可以按 Item 对数据进行分区，然后在前一个行号上将其外部连接到自身:

    create table ItemTotal (id uniqueidentifier, item varchar(10), size numeric(10,2), entry datetime)

    insert into ItemTotal values(NEWID(), 'ItemA', 4476, '7/01/2012 11:15');
    insert into ItemTotal values(NEWID(), 'ItemA', 4478.74, '7/01/2012 11:20');
    insert into ItemTotal values(NEWID(), 'ItemA', 4478.82, '7/01/2012 11:21');
    insert into ItemTotal values(NEWID(), 'ItemA', 4487.51, '7/01/2012 11:29');
    insert into ItemTotal values(NEWID(), 'ItemB', 1.53, '7/01/2012 11:29');
    insert into ItemTotal values(NEWID(), 'ItemB', 1.67, '7/01/2012 11:20');
    insert into ItemTotal values(NEWID(), 'ItemB', 1.84, '7/01/2012 11:21');
    insert into ItemTotal values(NEWID(), 'ItemB', 2.18, '7/01/2012 11:15');

    with items(id, item, size, entry, rn) as (
    select id, item, size, entry, ROW_NUMBER () OVER (Partition By item order by entry) as rn From ItemTotal t)
    select i.item, i.size, i.entry, i.size - coalesce(o.size, 0) as difference
    from items i
    left outer join items o on o.item = i.item and o.rn = i.rn-1
    order by i.item, i.entry desc

结果输出:

item    size    entry   difference
ItemA   4487.51 2012-07-01 11:29:00.000 8.69
ItemA   4478.82 2012-07-01 11:21:00.000 0.08
ItemA   4478.74 2012-07-01 11:20:00.000 2.74
ItemA   4476.00 2012-07-01 11:15:00.000 4476.00
ItemB   1.53    2012-07-01 11:29:00.000 -0.31
ItemB   1.84    2012-07-01 11:21:00.000 0.17
ItemB   1.67    2012-07-01 11:20:00.000 -0.51
ItemB   2.18    2012-07-01 11:15:00.000 2.18