使用 SQL Server 2014 计算真实范围 [英] Calculate the true range using SQL Server 2014
本文介绍了使用 SQL Server 2014 计算真实范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试根据下表计算真实范围.
I am trying to calculate the true range from the table below.
TIME BID-OPEN BID-HIGH BID-LOW BID-CLOSE
1993-05-09 21:00:00.000 1.5786 1.5786 1.5311 1.5346
1993-05-10 21:00:00.000 1.5346 1.5551 1.5326 1.5391
1993-05-11 21:00:00.000 1.5391 1.5521 1.5299 1.5306
1993-05-12 21:00:00.000 1.5306 1.5451 1.5106 1.5256
1993-05-13 21:00:00.000 1.5256 1.5416 1.5211 1.5361
到目前为止,我已经做到了:
So far I have managed to do this:
SELECT
t.Time,
Round([BID-HIGH]-[BID-LOW],5)
AS [H-L],
Abs(Round([BID-HIGH]-[prev_BID-CLOSE],5))
AS [H-Cp],
Abs(Round([BID-LOW]-[prev_BID-CLOSE],5))
AS [L-Cp]
FROM (SELECT t.*,
(select top 1 [BID-CLOSE]
from [GBP-USD].[dbo].[tbl_GBP-USD_1-Day] t2
where t2.[TIME] < t.[TIME] order by t2.[TIME] desc)
AS [prev_BID-CLOSE]
FROM [GBP-USD].[dbo].[tbl_GBP-USD_1-Day] AS t) AS t;
这给了我:
Time H-L H-Cp L-Cp
1993-05-09 21:00:00.000 0.0475 NULL NULL
1993-05-10 21:00:00.000 0.0225 0.0205 0.002
1993-05-11 21:00:00.000 0.0222 0.013 0.0092
1993-05-12 21:00:00.000 0.0345 0.0145 0.02
1993-05-13 21:00:00.000 0.0205 0.016 0.0045
1993-05-16 21:00:00.000 0.0216 0.0173 0.0043
1993-05-17 21:00:00.000 0.0208 0.009 0.0118
我现在需要 [H-L] [H-Cp] [L-Cp] 字段中的 MAX 值,它会给出这样的查询表:
I now need the MAX value from [H-L] [H-Cp] [L-Cp] fields which would give a query table like this:
Time H-L H-Cp L-Cp TR
1993-05-09 21:00:00.000 0.0475 NULL NULL 0.0475
1993-05-10 21:00:00.000 0.0225 0.0205 0.002 0.0225
1993-05-11 21:00:00.000 0.0222 0.013 0.0092 0.0222
1993-05-12 21:00:00.000 0.0345 0.0145 0.02 0.0345
1993-05-13 21:00:00.000 0.0205 0.016 0.0045 0.0205
1993-05-16 21:00:00.000 0.0216 0.0173 0.0043 0.0216
1993-05-17 21:00:00.000 0.0208 0.009 0.0118 0.0208
上面的数据只是一个很小的片段,所以在这个例子中,MAX 值总是 [H-L] 字段,但是这确实会改变.
The data above is only a very small snipping, so in this example the MAX value is always the [H-L] field, however this does change.
我需要一种查询查询的方法,但在同一个查询中:)
I need a way of querying the query, but within the same query : )
(SELECT MAX(v)
FROM (VALUES([H-L]),([H-Cp]),([L-Cp])) AS value(v)) AS [TR]
推荐答案
您可以使用 交叉应用
:
You could use CROSS APPLY
:
SELECT
t.[Time]
,[H-L] = Round([BID-HIGH]-[BID-LOW],5)
,[H-Cp] = Abs(Round([BID-HIGH]-[prev_BID-CLOSE],5))
,[L-Cp] = Abs(Round([BID-LOW]-[prev_BID-CLOSE],5))
,[TR]
FROM (SELECT t.*,
(select top 1 [BID-CLOSE]
from [tbl_GBP-USD_1-Day] t2
where t2.[TIME] < t.[TIME] order by t2.[TIME] desc)
AS [prev_BID-CLOSE]
FROM [tbl_GBP-USD_1-Day] AS t) AS t
CROSS APPLY (
SELECT MAX(v) AS v
FROM ( VALUES (Round([BID-HIGH]-[BID-LOW],5))
,(Abs(Round([BID-HIGH]-[prev_BID-CLOSE],5)))
,(Abs(Round([BID-LOW]-[prev_BID-CLOSE],5)))
) AS value(v)
) AS sub([TR]);
LiveDemo
强>
(select top 1 [BID-CLOSE]
from [tbl_GBP-USD_1-Day] t2
where t2.[TIME] < t.[TIME] order by t2.[TIME] desc)
看起来很适合LAG
函数.
Looks like a great candidate for LAG
function.
WITH cte AS
(
SELECT
t.[Time]
,[H-L] = Round([BID-HIGH]-[BID-LOW],5)
,[H-Cp] = Abs(Round([BID-HIGH]-[prev_BID-CLOSE],5))
,[L-Cp] = Abs(Round([BID-LOW]-[prev_BID-CLOSE],5))
FROM (SELECT *,
[prev_BID-CLOSE] = LAG([BID-CLOSE]) OVER(ORDER BY [Time])
FROM [#tbl_GBP-USD_1-Day]) AS t
)
SELECT *
FROM cte
CROSS APPLY (SELECT MAX(v) AS v
FROM ( VALUES ([H-L]),([H-Cp]),([L-Cp])) AS value(v)
) AS sub([TR]);
输出:
╔═════════════════════╦════════╦════════╦════════╦════════╗
║ Time ║ H-L ║ H-C ║ L-C ║ TR ║
╠═════════════════════╬════════╬════════╬════════╬════════╣
║ 1993-05-09 21:00:00 ║ 0.0475 ║ ║ ║ 0.0475 ║
║ 1993-05-10 21:00:00 ║ 0.0225 ║ 0.0205 ║ 0.002 ║ 0.0225 ║
║ 1993-05-11 21:00:00 ║ 0.0222 ║ 0.013 ║ 0.0092 ║ 0.0222 ║
║ 1993-05-12 21:00:00 ║ 0.0345 ║ 0.0145 ║ 0.02 ║ 0.0345 ║
║ 1993-05-13 21:00:00 ║ 0.0205 ║ 0.016 ║ 0.0045 ║ 0.0205 ║
╚═════════════════════╩════════╩════════╩════════╩════════╝
这篇关于使用 SQL Server 2014 计算真实范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文