All geek questions in one place

Determining prices for restrained periods from the trading list

I have a discreet list of historical transactions from a commodity exchange with a date and value, for example.

tradeTime | price ----------------------------------- 2014-06-11T00:21:12+02:00 | 647.24 2014-06-11T00:23:12+02:00 | 700.18 2014-06-11T00:28:12+02:00 | 750.23 2014-06-11T00:40:00+02:00 | 767.81 2014-06-11T00:51:12+02:00 | 711.46

Now I want to make a list of prices every day. So, there are three logical possibilities:

  • At that time there was 1 deal - excellent, that price (opening and closing)
  • There were several deals: the first is the opening price, and the last is the closing
  • There were no transactions - the opening and closing prices should be the most recent trade before the period.

Thus, with the above example, looking at 10-minute increments, the result should look something like this:

 period | open | close -------------------------------- 00:20 -> 00:30 | 648.24 | 750.23 00:30 -> 00:40 | 750.23 | 767.81 00:40 -> 00:50 | 767.81 | 711.46

-

 SELECT * FROM sales BETWEEN From_date AND To_date

maybe with MIN() , MAX() for dates for price selection? I’m not sure how it will be with

+5
source share
4 answers

Try this query:

 WITH qq AS ( SELECT cast('2014-06-11T00:00:00' as datetime) as startTime, dateadd( minute, 10, cast('2014-06-11T00:00:00' as datetime)) as endTime UNION ALL SELECT endTime, dateadd( minute, 10, endTime ) FROM qq WHERE endTime < cast('2014-06-12T00:00:00' as datetime) ) SELECT startTime, endTime, coalesce( opening, opening1 ) as opening, coalesce( closing, opening, opening1 ) as closing FROM ( SELECT qq.startTime, qq.endTime, (SELECT TOP 1 price FROM table1 t1 WHERE t1.tradeTime >= qq.startTime AND t1.tradeTime < qq.endTime ORDER BY t1.tradeTime ) As opening, (SELECT TOP 1 price FROM table1 t1 WHERE t1.tradeTime >= qq.startTime AND t1.tradeTime < qq.endTime ORDER BY t1.tradeTime DESC ) As closing, (SELECT TOP 1 price FROM table1 t1 WHERE t1.tradeTime < qq.startTime ORDER BY t1.tradeTime DESC ) as opening1 FROM qq ) x option (maxrecursion 0)

Demo: http://sqlfiddle.com/#!18/b9363/6

 | startTime | endTime | opening | closing | |----------------------|----------------------|---------|---------| | 2014-06-11T00:00:00Z | 2014-06-11T00:10:00Z | (null) | (null) | | 2014-06-11T00:10:00Z | 2014-06-11T00:20:00Z | (null) | (null) | | 2014-06-11T00:20:00Z | 2014-06-11T00:30:00Z | 647.24 | 750.23 | | 2014-06-11T00:30:00Z | 2014-06-11T00:40:00Z | 750.23 | 750.23 | | 2014-06-11T00:40:00Z | 2014-06-11T00:50:00Z | 767.81 | 767.81 | | 2014-06-11T00:50:00Z | 2014-06-11T01:00:00Z | 711.46 | 711.46 | | 2014-06-11T01:00:00Z | 2014-06-11T01:10:00Z | 711.46 | 711.46 | | 2014-06-11T01:10:00Z | 2014-06-11T01:20:00Z | 711.46 | 711.46 | | 2014-06-11T01:20:00Z | 2014-06-11T01:30:00Z | 711.46 | 711.46 | | 2014-06-11T01:30:00Z | 2014-06-11T01:40:00Z | 711.46 | 711.46 | | 2014-06-11T01:40:00Z | 2014-06-11T01:50:00Z | 711.46 | 711.46 | | 2014-06-11T01:50:00Z | 2014-06-11T02:00:00Z | 711.46 | 711.46 | ... ... ...
+1
source

You need to start with a list of watches. Since you have no deals, you need to generate this. Assuming you only need a few hours - say, all the hours in one day - a recursive CTE is enough:

 with hours as ( select cast('2014-06-11' as datetime) as yyyymmddhh union all select dateadd(hour, 1, yyyymmddhh) from hours where dateadd(hour, 1, yyyymmddhh) < '2014-06-12' )

Then you can do what you want using apply :

 with hours as ( select cast('2014-06-11' as datetime) as yyyymmddhh union all select dateadd(hour, 1, yyyymmddhh) from cte where dateadd(hour, 1, yyyymmddhh) < '2014-06-12' ) select h.*, opening.price, closing.price from hours h outer apply (select top 1 t.* from t where tradetime < dateadd(hour, 1, yyyymmddhh) order by (case when cast(tradetime as date) = cast(yyyymmddhh as date) and datepart(hour, tradetime) = datepart(hour, yyyymddhh) then 1 else 0 end), (case when cast(tradetime as date) = cast(yyyymmddhh as date) and datepart(hour, tradetime) = datepart(hour, yyyymddhh) then tradetime end) asc, tradetime desc ) opening outer apply (select top 1 t.* from t where tradetime < dateadd(hour, 1, yyyymmddhh) order by (case when cast(tradetime as date) = cast(yyyymmddhh as date) and datepart(hour, tradetime) = datepart(hour, yyyymddhh) then 1 else 0 end), tradetime desc ) closing;
0
source

Good afternoon,

I'm not sure if my solution really helps you, since I'm not in a situation where I can check if the code works or why it doesn’t, I would try something in this direction:

 SELECT prices FROM trades WHERE(SELECT MIN(DATEPART(MINUTE, trades.dates)), MAX(DATEPART(MINUTE, trades.dates)) WHERE(SELECT DISTINCT DATEPART(HOUR, trades.dates));

The idea is to query the minimum and maximum minutes for each individual hour, and then request the associated prices for minute 1 and minute 60 to get both opening and closing prices.

I hope this helps you move forward on your issue, or at least gives you a hint about a possible solution.

Best wishes,

MS.

0
source

The following query should give you a start.

  SELECT DISTINCT Disp, ( CASE WHEN [Open] IS NOT NULL THEN [Open] ELSE (SELECT TOP 1 PRICE FROM @Table WHERE Datepart(MINUTE, TradeTime) / 10 < O.Diff ) END ) AS [Open] ,( CASE WHEN O.[Open] IS NOT NULL THEN [Open] ELSE (SELECT TOP 1 PRICE FROM @Table WHERE Datepart(MINUTE, TradeTime) / 10 < O.Diff ) END ) AS [Close] FROM ( SELECT D.Disp ,D.Diff ,FIRST_VALUE(Price) OVER (PARTITION BY GRP ORDER BY TradeTime ASC) AS [Open] ,FIRST_VALUE(Price) OVER (PARTITION BY GRP ORDER BY TradeTime DESC) AS [Close], TradeTime FROM (VALUES (0,'00:01->00:10'), (1,'00:10->00:20'), (2,'00:20->00:30'), (3,'00:30->00:40'), (4,'00:40->00:50'), (5,'00:50->00:59') ) D(Diff,Disp) LEFT JOIN (SELECT Datepart(MINUTE, TradeTime) / 10 AS GRP, TradeTime, Price FROM @Table GROUP BY Datepart(MINUTE, TradeTime) / 10, TradeTime, Price) T ON T.GRP = D.Diff ) O

Full example:

 DECLARE @Table TABLE(TradeTime DATETIME, Price DECIMAL(15,2)) INSERT INTO @Table SELECT * FROM (VALUES (CAST('2018-02-18 13:04:46.920' AS DATETIME) , 647.24) ,(CAST('2018-02-18 13:05:46.920' AS DATETIME) , 700.18) ,(CAST('2018-02-18 13:15:46.920' AS DATETIME) , 750.23) ,(CAST('2018-02-18 13:24:46.920' AS DATETIME) , 767.81) ,(CAST('2018-02-18 14:26:46.920' AS DATETIME) , 711.46) ) TAB(TradeTime, Price) SELECT DISTINCT Disp, ( CASE WHEN [Open] IS NOT NULL THEN [Open] ELSE (SELECT TOP 1 PRICE FROM @Table WHERE Datepart(MINUTE, TradeTime) / 10 < O.Diff ) END ) AS [Open] ,( CASE WHEN O.[Open] IS NOT NULL THEN [Open] ELSE (SELECT TOP 1 PRICE FROM @Table WHERE Datepart(MINUTE, TradeTime) / 10 < O.Diff ) END ) AS [Close] FROM ( SELECT D.Disp ,D.Diff ,FIRST_VALUE(Price) OVER (PARTITION BY GRP ORDER BY TradeTime ASC) AS [Open] ,FIRST_VALUE(Price) OVER (PARTITION BY GRP ORDER BY TradeTime DESC) AS [Close], TradeTime FROM (VALUES (0,'00:00->00:10'), (1,'00:10->00:20'), (2,'00:20->00:30'), (3,'00:30->00:40'), (4,'00:40->00:50'), (5,'00:50->00:59') ) D(Diff,Disp) LEFT JOIN (SELECT Datepart(MINUTE, TradeTime) / 10 AS GRP, TradeTime, Price FROM @Table GROUP BY Datepart(MINUTE, TradeTime) / 10, TradeTime, Price) T ON T.GRP = D.Diff ) O

Output result

 Disp Open Close -------------------------------------- 00:00->00:10 647.24 647.24 00:10->00:20 750.23 750.23 00:20->00:30 767.81 767.81 00:30->00:40 647.24 47.24 00:40->00:50 647.24 647.24 00:50->00:59 647.24 647.24
0
source

Source: https://habr.com/ru/post/1275431/

More articles:


All Articles