查询缺少的元素
我有以下结构的表:查询缺少的元素
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
现在我所寻找的是一个查询,这将使我的每一个名字值的总和,并设为空的之间(通过时间戳orderd)作为第一个非空的名字在列表中向下,仿佛表如下:
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
。我曾经想过并尝试过,但我无法想出一个合适的解决方案。我研究过递归公用表表达式,并认为答案可能存在于那里,但我无法正确理解这些表达式。
这些表仅仅是一些例子,我不知道预先设置的时间戳值。
有人能帮我一个忙吗?非常感谢帮助。
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
顺便说一句,比任何查询首先更正数据要快几个数量级。即,更新名称列以使其具有适当的值,使其不可空,然后运行简单的分组依据以获得总计。
其他解决方案
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)
嗨托马斯,谢谢你的回答!我想我应该事先说明这是一个例子,它实际上是一个非常大的表格,并且我不知道时间戳记的值。感谢您让我走上正轨。 – Martijn 2011-02-15 18:01:06
@Martijn - 在我的例子中,它应该没有关系。我的`Inputs` CTE仅仅用于测试目的。你只需要`NamedInputs` CTE(调用任何你喜欢的)来确定它应该分配一个NULL的名字。我会说如果你要这样做,更快的方法是更新数据,使列不可为空,然后运行你的总和。 – Thomas 2011-02-15 18:03:13
啊,我误解了。我将给出这个运行并尝试一下我的实际数据,看看它是如何工作的。如果符合我的需求,蛋糕将被发送;) – Martijn 2011-02-15 18:09:37
你不需要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
我希望我不会被为您提供我的这个小递归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 */
+1用于提供一些样本输入和预期输出(尽管您可能还包括您对总和值的期望)。 – Thomas 2011-02-15 18:07:22