T-SQL Query返回最接近的值

I've got some data in a SQL Server table as followings:

我在SQL Server表中有一些数据如下：

DateTime                Bid       Ask 
02/10/2017 09:59.323    123.111   123.894 
02/10/2017 10:01.432    123.321   124.001  
02/10/2017 10:03.132    123.421   124.121
02/10/2017 10:03.983    123.121   123.721 
02/10/2017 10:04.342    123.587   124.200

What I'd like to query is what the Bid and Ask values were at each second time period. For example at:

我想询问的是每个第二时间段的买入价和卖出价。例如：

10:00.000 the Bid was 123.111 and the Ask was 123.894
10：00.000，出价为123.111，Ask为123.894
10:01.000 the Bid was 123.111 and the Ask was 123.894
10：01.000，出价为123.111，Ask为123.894
10:02.000 the Bid was 123.321 and the Ask was 124.001
10：02.000，出价为123.321，Ask为124.001
10:03.000 the Bid was 123.321 and the Ask was 124.001
10：03.000，买入价为123.321，卖出价为124.001
10:04.000 the Bid was 123.121 and the Ask was 123.721
10：04.000，出价为123.121，Ask为123.721

So the SQL needs to return the Bid and Ask values for the Date Time before each second value.

因此，SQL需要在每个第二个值之前返回Date Time的Bid和Ask值。

For example for:

例如：

10:00.000 use 09:59.323
10：00.000使用09：59.323
10:01.000 use 09:59.323
10：01.000使用09：59.323
10:02.000 use 10:01.432
10：02.000使用10：01.432
10:03.000 use 10:01.432
10：03.000使用10：01.432
10:04.000 use 10:03.983
10：04.000使用10：03.983

And my query will return values between a start and end date/time, so it'll return multiple rows.

我的查询将在开始和结束日期/时间之间返回值，因此它将返回多行。

6 个解决方案

#1

You can try this query:

您可以尝试以下查询：

if object_id('tempdb..#Table1') is not null
  drop table #Table1
go
create table #Table1(
DateTime datetime
,Bid float     
,Ask float)

insert into #Table1
select '02/10/2017 09:59.323', 123.111, 123.894 
union all select '02/10/2017 10:01.432', 123.321, 124.001  
union all select '02/10/2017 10:03.132', 123.421, 124.121
union all select '02/10/2017 10:03.983', 123.121, 123.721 
union all select '02/10/2017 10:04.342', 123.587, 124.200

declare @start_date datetime
      , @end_date datetime

select @start_date = dateadd(mi, datediff(mi, 0, min(DateTime)) + 1, 0)
  from #Table1

select @end_date = dateadd(mi, datediff(mi, 0, max(DateTime)), 0) 
  from #Table1

;with generates_dates as(
select @start_date as dt
union all
select dateadd(mi, 1, dt) as dt
  from generates_dates
  where dt < @end_date)

select t1.dt
     , t2.Bid
     , t2.Ask
  from generates_dates t1
    cross apply(select top 1 Bid, Ask 
                  from #Table1 t2 
                  where t2.DateTime < t1.dt 
                  order by t2.DateTime desc)t2(Bid, Ask)
  option (maxrecursion 0)

#2

Order the table by datetime decending, limit the return to 1 and then make sure that any values returned are lower than the supplied datetime.

按日期时间降序对表进行排序，将返回值限制为1，然后确保返回的任何值都低于提供的日期时间。

[edit]

[编辑]

select top 1 bid, ask
from datatable
where itsdatetime <= '02/10/2017 10:00.000'
order by itsdatetime desc

You can get more creative and put that inside of condition subquery.

您可以获得更多创意并将其置于条件子查询中。

select *
from requiretimes rt
join datatable dt on dt.itsdatetime = (select top 1 itsdatetime
                    from datatable
                    where itsdatetime <= rt.requireddatetime
                    order by itsdatetime desc)

#3

DECLARE @T TABLE 
(
    d   DateTime, 
    bid MONEY,
    ask MONEY
)
INSERT INTO @T VALUES
('02/10/2017 09:59.323',    123.111,   123.894),
('02/10/2017 10:01.432',    123.321,   124.001),  
('02/10/2017 10:03.132',    123.421,   124.121),
('02/10/2017 10:03.983',    123.121,   123.721), 
('02/10/2017 10:04.342',    123.587,   124.200),
('03/10/2017 10:04.342',    123.587,   124.200)

;WITH sec AS 
(
    SELECT TOP (SELECT 60*60*24) ROW_NUMBER() OVER (ORDER BY 1/0) as s
    FROM master..spt_values a,master..spt_values m
), dd as
(
    SELECT DISTINCT CAST(d as date ) as d
    FROM @t
), Tbl as 
(
    SELECT
        DATEADD(ss,b.s,CAST(a.d as datetime)) as dat
    FROM     
        dd  a
    CROSS JOIN 
        sec b   
)
SELECT
    dat 
    ,c.*
FROM     tbl
CROSS APPLY 
(
    SELECT TOP 1 *
    FROM @t a
    WHERE 
        a.d >= tbl.dat
    ORDER BY 
        a.d ASC
) as c
WHERE 
    c.d >= dat  AND 
ORDER BY dat

#4

There are two parts to this:

这有两个部分：

Create a projection holding your timestamps: 10:00.000, 10:01.000, 10:02.000 etc. This is hard to show in an answer here, because we don't know what criteria you're using to determine your start and end ranges, and because your question asks about seconds, but your timestamp values are actually showing minutes. If you need help with this, there are lots of results in Google and here on Stack Overflow for creating projections, number tables, or sequences.

创建一个包含时间戳的投影：10：00.000,10：01.000,10：02.000等。这里的答案很难显示，因为我们不知道您使用什么标准来确定您的开始和结束范围，并且因为您的问题询问秒数，但您的时间戳值实际上显示分钟数。如果您需要帮助，Google和Stack Overflow上有很多结果可用于创建投影，数字表或序列。
Use the OUTER APPLY operator to connect the projection back to your original data. OUTER APPLY makes it easy to show exactly the one right record for each item from your projection.

使用OUTER APPLY运算符将投影连接回原始数据。外部应用可以轻松显示投影中每个项目的正确记录。

。

WITH times As (
    SELECT cast('2017-02-10 10:00.000' as datetime) "DateTime"
    UNION
    SELECT cast('2017-02-10 10:01.00' as datetime)
    UNION
    SELECT cast('2017-02-10 10:02.000' as datetime) 
    UNION
    SELECT cast('2017-02-10 10:03.000' as datetime)
    UNION
    SELECT cast('2017-02-10 10:04.000' as datetime)
    UNION
    SELECT cast('2017-02-10 10:05.000' as datetime)
)
SELECT t.[DateTime], u.[DateTime], u.Bid, u.Ask
FROM times t
CROSS APPLY ( 
      SELECT TOP 1 * 
      FROM [MyTable] 
      WHERE [DateTime] < t.[DateTime] 
      ORDER BY [DateTime] DESC
) u
ORDER BY t.[DateTime]

SQLFiddle

SQLFiddle

#5

You'd generate the seconds with a recursive query. (As SQL Server doesn't support ANSI timestamp literals, you'll need CONVERT for this.) Then join via CROSS APPLY to get the last entry from the table per second.

您将使用递归查询生成秒数。（由于SQL Server不支持ANSI时间戳文字，因此您需要使用CONVERT。）然后通过CROSS APPLY连接以每秒从表中获取最后一个条目。

with secs(sec) as
(
  select convert(datetime, '2017-10-02 10:00:00', 20) as sec
  union all
  select dateadd(second, 1, sec) as sec 
  from secs
  where sec <= convert(datetime, '2017-10-02 10:00:04', 20)
)
select secs.sec, data.bid, data.ask
from secs
cross apply
(
  select top(1) *
  from mytable
  where mytable.datetime <= secs.sec
  order by datetime desc
) data;

I am using seconds here as per your description, while your sample uses minutes instead. Decide which you actually need.

我根据你的描述在这里使用秒，而你的样本使用分钟代替。决定你真正需要的是什么。

#6

Try this:

尝试这个：

declare @idate datetime
declare @fdate datetime

select @idate = min(gendate) from BidAsk
select @fdate = max(gendate) from BidAsk

create table #temp (bid float, ask float, Gendate datetime)

while (@idate <= @fdate)
begin
   insert into #temp
   select top 1 Bid, Ask, @idate from BidAsk where @idate > GenDate
   order by GenDate desc

   set @idate = DATEADD(second,1,@idate)
end

select * from #temp

#1