如何高效地选择给定值的最接近且小于和大于该值的值？

Question

我有两个表格，一个存储数值，另一个存储位置，现在想对位置进行插值。以下是简化后的表格：

CREATE TABLE value(
    Timestamp DATETIME2,
    Value float NOT NULL,
    PRIMARY KEY(Timestamp)
);

CREATE TABLE location(
    Timestamp DATETIME2,
    Position INT NOT NULL,
    PRIMARY KEY(Timestamp)
); 

INSERT INTO value VALUES 
    ('2011/12/1 16:55:01', 1),
    ('2011/12/1 16:55:02', 5),
    ('2011/12/1 16:55:05', 10),
    ('2011/12/1 16:55:08', 6);

INSERT INTO location VALUES 
    ('2011/12/1 16:55:00', 0),
    ('2011/12/1 16:55:05', 10),
    ('2011/12/1 16:55:10', 5)

预期结果将是

TimeStamp, Value, LowerTime, LowerLocation, UpperTime, UpperLocation
2011-12-01 16:55:01,  1, 2011-12-01 16:55:00,  0, 2011-12-01 16:55:05, 10
2011-12-01 16:55:02,  5, 2011-12-01 16:55:00,  0, 2011-12-01 16:55:05, 10
2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10
2011-12-01 16:55:08,  6, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:10,  5

请注意，这是简化的样本数据，用于了解我试图执行的查询的基本思路。

为了进行插值，我需要确定在给定值的时间之前和之后的时间和位置。目前，我正在使用以下查询进行此操作：

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation
 FROM 
    dbo.value V 

现在这个代码能够工作，但显然它执行了很多操作。我在想，可能有一种查询简化的方法我还没有想到，但是我已经尝试了整个上午，没有得出实质性的结果。希望这里的某个人有更好的想法。

我正在探索是否有办法找出LowerTime和UpperTime并用它们确定位置。类似于：

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT Position FROM dbo.location WHERE Timestamp = LowerTime) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT Position FROM dbo.location WHERE Timestamp = UpperTime) as UpperLocation
 FROM 
    dbo.value V 

但这并不起作用。

编辑1：根据建议更新了查询。然而执行时间没有明显改变。

编辑2：添加了我目前尝试的方法的想法。

- brianestey

Answer 1

为了简单起见，您可以至少使用MAX()和MIN()函数查询timestamp字段，而不是使用TOP 1和ORDER BY。

完整的查询将是

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation
 FROM 
    dbo.value V 

- Sergey Kudriavtsev

---

谢谢你的建议！那是个好主意 - 我会试试看。不过我希望能够在某种程度上减少或者消除那四个查询。 - brianestey

谢谢。我已将您的答案标记为最佳答案，因为它是给出选项中最好的一个。这确实有些帮助。 - brianestey

Answer 2

这可能会起作用（尽管我认为连接看起来相当丑陋）：

;with OrderedLocations as (
    select
        v.Timestamp,
        v.Value,
        l.Timestamp as tsl,
        l.Position,
        ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp <= v.Timestamp THEN l.Timestamp ELSE '00010101' END desc) as PrevRN,
        ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp >= v.Timestamp THEN l.Timestamp ELSE '99991231' END asc) as NextRN
    from
        value v
            cross join
        location l
)
select
    ol1.Timestamp,
    ol1.Value,
    ol1.tsl,
    ol1.Position,
    ol2.tsl,
    ol2.Position
from
    OrderedLocations ol1
        inner join
    OrderedLocations ol2
        on
            ol1.Timestamp = ol2.Timestamp and
            ol1.Value = ol2.Value
where
    ol1.PrevRN = 1 and
    ol2.NextRN = 1

不幸的是，对于大多数效率/性能问题，答案往往是尝试使用您的实际表格和数据的许多不同组合，并测量每个组合的性能。

---

使用与上面相同的CTE的另一种替代方法（避免连接）是：

SELECT Timestamp,Value,
    MAX(CASE WHEN PrevRN=1 THEN tsl END),MAX(CASE WHEN PrevRN=1 then Position END),
    MAX(CASE WHEN NextRN=1 THEN tsl END),MAX(CASE WHEN NextRN=1 then Position END)
FROM
    OrderedLocations
where PrevRN=1 or NextRN=1
group by Timestamp,Value

---

CTE（OrderedLocations）试图构造一个行集，其中每个位置的每一行与value中的每一行都匹配。对于每个结果行，我们计算两个ROW_NUMBER - 在降序中为所有具有较低或相等时间戳的行编号（PrevRN），在升序中为所有具有较高或相等时间戳的行编号（NextRN）。然后，我们通过仅考虑其中一个行号为1的行来构造我们的最终结果。

- Damien_The_Unbeliever

---

感谢您的输入 - 我不确定这段代码具体在做什么。在简单的数据集上运行它没有产生任何结果。我会研究您的答案，看看是否有帮助。 - brianestey

@brianestey - 如果您有一些样本数据和期望的结果，请将它们添加到您的问题中 - 这对我们来说更容易编写工作查询，因为在将其发布为答案之前我们可以自己进行测试。 - Damien_The_Unbeliever

好主意，我已经更新了问题，并提供了一些我用于测试的数据点以及我所期望的输出。 - brianestey

@brianestey - 我现在已经在我的CTE中按照v.Timestamp进行了分区，并得到了您发布的预期结果。（最初我只在value表中测试了一行，糟糕） - Damien_The_Unbeliever

谢谢更新。我已经测试过了，它确实给出了预期的结果，但是我在使用时遇到了一些性能问题。例如，我向值表中插入了5000行，向位置表中插入了1250行，原始查询在0~1秒内完成，但是这个查询在相同的数据下需要几分钟（约5.5分钟）。你觉得这合理吗？ - brianestey