查询缺失元素

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)

Answer 2

您不需要 CTE，只需要一个简单的子查询。

select t.timestamp, ISNULL(t.name, (
    select top(1) i.name
    from inputs i
    where i.timestamp < t.timestamp
    and i.name is not null
    order by i.timestamp desc
    )), t.value
from inputs t

并从这里总结

select name, SUM(value) as totalValue
from
(
    select t.timestamp, ISNULL(t.name, (
        select top(1) i.name
        from inputs i
        where i.timestamp < t.timestamp
        and i.name is not null
        order by i.timestamp desc
        )) as name, t.value
    from inputs t
) N
group by name

Answer 3

我希望我不会因为向您提供这个小小的递归 CTE 查询来解决您的问题而感到尴尬。

;WITH
numbered_table AS (
  SELECT
    timestamp, name, value,
    rownum = ROW_NUMBER() OVER (ORDER BY timestamp)
  FROM your_table
),
filled_table AS (
  SELECT
    timestamp,
    name,
    value
  FROM numbered_table
  WHERE rownum = 1

  UNION ALL

  SELECT
    nt.timestamp,
    name = ISNULL(nt.name, ft.name),
    nt.value
  FROM numbered_table nt
    INNER JOIN filled_table ft ON nt.rownum = ft.rownum + 1
)
SELECT *
FROM filled_table
/* or go ahead aggregating instead */