sql-server-2005 - 查询缺失元素

Question

我有一个具有以下结构的表:

timestamp | name | value
0         | john | 5
1         | NULL | 3
8         | NULL | 12
12        | john | 3
33        | NULL | 4
54        | pete | 1
180       | NULL | 4
400       | john | 3
401       | NULL | 4
592       | anna | 2

现在我正在寻找一个查询，它将给出每个名称的值的总和，并将之间的空值(按时间戳排序)视为列表中的第一个非空名称，就好像表格如下:

timestamp | name | value
0         | john | 5
1         | john | 3
8         | john | 12
12        | john | 3
33        | pete | 4
54        | pete | 1
180       | john | 4
400       | john | 3
401       | anna | 4
592       | anna | 2

我会查询SUM(value), name from this table group by name。我思考过，也尝试过，但想不出一个合适的解决办法。我研究过递归公用表表达式，并认为答案可能就在那里，但我无法正确理解它们。

这些表只是示例，我事先并不知道时间戳值。

有人可以帮我吗？非常感谢您的帮助。

Answer 1

With Inputs As
    (
    Select 0 As [timestamp], 'john' As Name, 5 As value
    Union All Select 1, NULL, 3
    Union All Select 8, NULL, 12
    Union All Select 12, 'john', 3
    Union All Select 33, NULL, 4
    Union All Select 54, 'pete', 1
    Union All Select 180, NULL, 4
    Union All Select 400, 'john', 3
    Union All Select 401, NULL, 4
    Union All Select 592, 'anna', 2
    )
    , NamedInputs As
    (
    Select I.timestamp
        , Coalesce (I.Name
            ,   (
                Select I3.Name
                From Inputs As I3
                Where I3.timestamp = (
                                    Select Max(I2.timestamp)
                                    From Inputs As I2
                                    Where I2.timestamp < I.timestamp
                                        And I2.Name Is not Null
                                    )
                )) As name
        , I.value
    From Inputs As I
    )
Select NI.name, Sum(NI.Value) As Total
From NamedInputs As NI
Group By NI.name

顺便说一句，首先纠正数据比任何查询都要快几个数量级。即，更新名称列以具有正确的值，使其不可为空，然后运行一个简单的 Group By 来获取总计。

其他解决方案

Select Coalesce(I.Name, I2.Name), Sum(I.value) As Total
From Inputs As I
    Left Join   (
                Select I1.timestamp, MAX(I2.Timestamp) As LastNameTimestamp
                From Inputs As I1
                    Left Join Inputs As I2
                        On I2.timestamp < I1.timestamp
                            And I2.Name Is Not Null   
                Group By I1.timestamp
                ) As Z
        On Z.timestamp = I.timestamp        
    Left Join Inputs As I2
        On I2.timestamp = Z.LastNameTimestamp
Group By Coalesce(I.Name, I2.Name)