SQL 通过保留填补空白

Question

我遇到了一个我的知识无法解决的问题，我还没有找到任何我理解得足够好的解决方案来解决我的问题。

这就是我试图实现的目标。 我有一个具有以下结构的数据库：

node_id, source_time, value
1      , 10:13:15   ,  1
2      , 10:13:15   ,  1
2      , 10:13:16   ,  2
1      , 10:13:19   ,  2
1      , 10:13:25   ,  3
2      , 10:13:28   ,  3

我想有一个sql查询来获取以下输出

time , value1, value2
10:13:15,    1  ,    1
10:13:16,    1  ,    2
10:13:19,    2  ,    2
10:13:25,    3  ,    2
10:13:28,    3  ,    3 

你看，时间是从两个节点发生的所有时间。 但是这些值必须填补空白，因为节点 1 在时间 ：16 和 ：28 上没有值。 我把它弄到了从一个表中得到 2 列的地步。这不是最难的部分。

SELECT T1.[value], T2.[value] 
FROM [db1].[t_value_history] T1, [db1].[t_value_history] T2 
WHERE ( T1.node_id = 1 AND T2.node_id = 2)

但结果看起来并不像我想要的那样。

我在 COALESCE 和另一个保存先前值的表中找到了一些东西。但对于这样简单的事情来说，这看起来很安静。 我想有一个简单的sql解决方案，但我没有太多时间进入材料。

我很乐意知道使用哪个功能。

到目前为止谢谢。

编辑：更改了数据库，在最后一行出错。

编辑2：我正在使用SQL Server。很抱歉没有澄清这一点。此外，这些值不一定会增加。我只是在这个例子中使用了递增的数字。

Answer 1

这适用于SQL Server。如果您确定两个节点在最短时间内都有一个值，则可以将外部应用更改为交叉应用，这将执行得更好。

WITH    times
AS ( SELECT DISTINCT
source_time
FROM     dbo.t_value_history
)
SELECT  t.source_time ,
n1.value ,
n2.value
FROM    times AS t
OUTER APPLY ( SELECT TOP 1
h.value
FROM      dbo.t_value_history AS h
WHERE     h.node_id = 1
AND h.source_time <= t.source_time
ORDER BY  h.source_time DESC
) AS n1
OUTER APPLY ( SELECT TOP 1
h.value
FROM      dbo.t_value_history AS h
WHERE     h.node_id = 2
AND h.source_time <= t.source_time
ORDER BY  h.source_time DESC
) AS n2;

Answer 2

您可以使用条件聚合来获取正确的行集：

select vh.source_time,
max(case when vh.node_id = 1 then value end) as value_1,
max(case when vh.node_id = 2 then value end) as value_2
from db1.t_value_history vh
group by vh.source_time;

如果要填写值，则最佳解决方案是lag()ignore nulls. 由ANSI支持，但不受SQL Server支持(我猜你正在使用(。 你的价值观似乎在增加。 如果是这种情况，您可以使用累积最大值：

select vh.source_time,
max(max(case when vh.node_id = 1 then value end)) over (order by vh.source_time) as value_1,
max(max(case when vh.node_id = 2 then value end) over (order by vh.source_time) as value_2
from db1.t_value_history vh
group by vh.source_time;

在数据中，value正在增加，因此这适用于示例中的数据。 如果不是这种情况，则需要更复杂的查询来填补空白。

Answer 3

这将在SQL Server中执行此操作。不过，这并不"好"：

SELECT DISTINCT
T1.source_time,
CASE WHEN T1.node_id = 1 THEN T1.[value] ELSE ISNULL(T2.[value], T3.[value]) END,
CASE WHEN T1.node_id = 1 THEN ISNULL(T2.[value], T3.[Value]) ELSE T1.[value] END
FROM
[db1].[t_value_history] T1
LEFT OUTER JOIN [db1].[t_value_history] T2 ON T2.source_time = T1.source_time
AND T2.node_id <> T1.node_id -- This join looks for a value for the other node at the same time.
LEFT OUTER JOIN [db1].[t_value_history] T3 ON T3.source_time < T1.source_time
AND T3.node_id <> T1.node_id -- If the previous join is empty, this looks for values for the other node at previous times
LEFT OUTER JOIN [db1].[t_value_history] T4 ON T4.source_time > T3.source_time
AND T4.source_time < T1.source_time
AND T4.node_id <> T1.node_id -- This join makes sure there aren't any more recent values
WHERE
T4.node_id IS NULL