代码之家 › 专栏 › 技术社区 › Gabriël

如何从SQL Server查询打开-高-低-关闭(OHLC)数据

stocks finance group-by sql-server

Gabriël · 技术社区 · 15 年前

我正试图直接从数据库中检索一个开盘-盘高-盘低-收盘图(ohlc)的数据,这就是你看到的股票图表。这是可能的吗?如果,怎么可能?

我有一张这样的桌子(简化):

日期价格价格类型

每天都会创建一个记录,我将每月/每年报告,而不是每天用于股票。

我想问这样的问题:

选择pricetype,max(price)为high,min(price)为low,[price of first item of month]为open,[price of last item of month]为close group by pricetype,year(date),month(date)

要访问SQL Server,我使用llblgen,因此基于该技术的anwser将非常棒,通用SQL Server也将如此!

它是SQL 2005,但2008也是一个选项。

谢谢。

2 回复 | 直到 12 年前

Ed Harper 15 年前

这似乎奏效了。很可能会有一种不那么冗长的方法。

--create test data
CREATE TABLE #t
(priceDate DATETIME
,price MONEY
,priceType CHAR(1)
)

INSERT #t
      SELECT '20090101',100,'A'
UNION SELECT '20090102',500,'A'
UNION SELECT '20090103',20 ,'A'
UNION SELECT '20090104',25 ,'A'
UNION SELECT '20090105',28 ,'A'
UNION SELECT '20090131',150,'A'


UNION SELECT '20090201',501,'A'
UNION SELECT '20090203',21 ,'A'
UNION SELECT '20090204',26 ,'A'
UNION SELECT '20090205',29 ,'A'
UNION SELECT '20090228',151,'A'


UNION SELECT '20090101',100,'B'
UNION SELECT '20090102',500,'B'
UNION SELECT '20090103',20 ,'B'
UNION SELECT '20090104',25 ,'B'
UNION SELECT '20090105',28 ,'B'
UNION SELECT '20090131',150,'B'


UNION SELECT '20090201',501,'B'
UNION SELECT '20090203',21 ,'B'
UNION SELECT '20090204',26 ,'B'
UNION SELECT '20090205',29 ,'B'
UNION SELECT '20090228',151,'B'

--query
;WITH rangeCTE
AS
(
        SELECT  MIN(priceDate) minDate
                ,MAX(priceDate) maxDate
        FROM #t
)
,datelistCTE
AS
(
        SELECT CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME) AS monthStart
               ,DATEADD(mm,1,CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME)) -1 AS monthEnd
               ,1 AS monthID
        FROM rangeCTE

        UNION ALL

        SELECT DATEADD(mm,1,monthStart)
               ,DATEADD(mm,2,monthStart) - 1
               ,monthID + 1
        FROM datelistCTE
        WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
,priceOrderCTE
AS
(
        SELECT * 
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY priceDate
                                   ) AS rn1
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY priceDate DESC
                                   ) AS rn2
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY price DESC
                                   ) AS rn3                                   
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY price 
                                   ) AS rn4
        FROM datelistCTE AS d
        JOIN #t          AS t
        ON t.priceDate BETWEEN d.monthStart AND d.monthEnd
        WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
SELECT o.MonthStart
       ,o.priceType
       ,o.Price AS opening
       ,c.price AS closing
       ,h.price AS high
       ,l.price AS low
FROM priceOrderCTE AS o
JOIN priceOrderCTE AS c
ON   c.priceType = o.PriceType 
AND  c.monthID   = o.MonthID
JOIN priceOrderCTE AS h
ON   h.priceType = o.PriceType 
AND  h.monthID   = o.MonthID
JOIN priceOrderCTE AS l
ON   l.priceType = o.PriceType 
AND  l.monthID   = o.MonthID
WHERE o.rn1 = 1
AND   c.rn2 = 1
AND   h.rn3 = 1
AND   l.rn4 = 1

MarcZ2121 12 年前

这是我写的一个小查询,一次只能工作一个时间跨度。您只需对所选日期部分进行注释,即可到达您要查找的时间跨度。或者您可以为不同的时间跨度创建多个视图。此外,基础数据表使用bid-ask-tick样式的数据。如果您使用的是中间价或最后一个价格,您可以从选择中删除案例陈述。

Select 
tmp.num,
rf.CurveName, 
rf.Period as Period,
CASE WHEN (tmp2.Bid is null or tmp2.Ask is null) then isnull(tmp2.Bid,0)+isnull(tmp2.Ask,0) else (tmp2.Bid+tmp2.Ask)/2 end as [Open],
tmp.Hi,
tmp.Lo,
CASE WHEN (rf.Bid is null or Rf.Ask is null) then isnull(rf.Bid,0)+isnull(rf.Ask,0) else (rf.Bid+rf.Ask)/2 end as [Close],
tmp.OpenDate,
tmp.CloseDate,
tmp.yr,
tmp.mth,
tmp.wk,
tmp.dy,
tmp.hr
from BidAsk rf inner join 
(SELECT count(CurveName)as num,CurveName,
Period,
max(CASE WHEN (Bid is null or Ask is null) then isnull(Bid,0)+isnull(Ask,0) else (Bid+Ask)/2 end) as Hi,
min(CASE WHEN (Bid is null or Ask is null) then isnull(Bid,0)+isnull(Ask,0) else (Bid+Ask)/2 end) as Lo, 
max(CurveDateTime) as CloseDate, min(CurveDateTime) as OpenDate,
    DATEPART(year, CurveDateTime) As yr,  
    DATEPART(month, CurveDateTime) As mth,  
    DATEPART(week, CurveDateTime) As wk,  
    DATEPART(Day, CurveDateTime) as dy,
    DATEPART(Hour, CurveDateTime) as hr  
    --DATEPART(minute, CurveDateTime) as mnt 
FROM  
    BidAsk 
GROUP BY  
CurveName,Period,
    DATEPART(year, CurveDateTime),  
    DATEPART(month, CurveDateTime),  
    DATEPART(week, CurveDateTime),
    DATEPART(Day, CurveDateTime) ,
    DATEPART(Hour, CurveDateTime)
    --DATEPART(minute, CurveDateTime) 
) tmp on 
tmp.CurveName=rf.CurveName and 
tmp.CloseDate=rf.CurveDateTime and 
tmp.Period=rf.Period

inner join BidAsk tmp2 on 
tmp2.CurveName=rf.CurveName and 
tmp2.CurveDateTime=tmp.Opendate and 
tmp2.Period=rf.Period

ORDER BY  
CurveName,Period,tmp.yr,tmp.mth
    --DATEPART(year, CurveDateTime), 
    --DATEPART(month, CurveDateTime)  
    --DATEPART(day, CurveDateTime),  
    --DATEPART(Hour, CurveDateTime), 
    --DATEPART(minute, CurveDateTime) )