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

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

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:

我目前正在探索是否有辦法找出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 

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:添加了我對目前正在嘗試的方法的看法。

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.

為簡單起見,您至少可以使用MAX()和MIN()函數來查詢時間戳字段,而不是TOP 1和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条回复