# 如何有效地選擇最接近的值小於和大於給定值？ - How can I select the nearest value less-than and greater-than a given value efficiently? -开发者知识库

I have two tables, one for values one for location and am trying to interpolate location. The tables have been simplified to the following:

``````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)
``````

The expected results would be

``````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
``````

(Keep in mind this is simplified sample data to get the idea of the query I am trying to perform across.)

（請記住，這是簡化的示例數據，以便了解我正在嘗試執行的查詢。）

To do the interpolation, I need to figure out the time and locations before and after a given values time. I am currently doing this with a query that looks like:

``````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
``````

Now this works, but this obviously is doing a lot of work. I'm thinking there must be a query simplification that I'm missing but I've been playing with it all morning and haven't come up with anything concrete. Hoping someone here has a better idea.

I am currently exploring if there is a way to figure out the LowerTime and UpperTime and use those in determining the Locations. Something like:

``````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
``````

but this doesn't work.

EDIT1: Updated query as suggested. However no visible change in execution time.

EDIT1：按建議更新了查詢。但是執行時間沒有明顯變化。

EDIT2: Added my thoughts of the approach I am currently trying.

## 2 个解决方案

### #1

11

For simplicity you may at least use `MAX()` and `MIN()` functions for querying `timestamp` field instead of `TOP 1` and `ORDER BY`.

Full query will be

``````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
``````

0条回复