sql - 从交易列表中确定谨慎时期的价格

Question

我有一份来自商品交易所的历史交易的谨慎列表，其中包含日期时间和值，例如

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

现在我希望能够每小时创建一个价格列表。所以三种逻辑可能性是:

1. 该期间有 1 笔交易 - 太好了，这就是价格(开盘价和收盘价)
2. 有多个交易 - 第一个是开盘价，最后一个是收盘价
3. 没有交易 - 开盘价和收盘价应为该期间之前的最新交易。

因此，对于上面的示例，查看 10 分钟的增量，输出应该类似于:

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

可能用MIN()、MAX()作为日期来选择价格？我不确定这一切如何与

Answer 1

尝试这个查询:

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)

演示: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 |
...
...
...