大宗交易資料挖掘（三）

計算次元

資料集字段如下：

1. [CurClose]，目前收盤價；

2. [TradePrice]，大宗交易成交價；

3. [FluctRange3]，近3日波動幅度（中途發現波動幅度太大，止損點不好定，是以把最近3天波動幅度超過15%的都去掉了）；

4. [Volume]，大宗交易成交量

5. [Amount]，大宗交易金額

6. [Discount]，折扣率 = [TradePrice]/[CurClose] - 1

7. [BuyerCount]，買家席位數；

8. [SellerCount]，賣家席位數；

9. [TradeToSelf]，是否存在同席位交易；

10. [Capital]，流通股本；

11. [VolumeToCapital]，交易量占流通股本比值 = [Volume]/[VolumeToCapital]

12. [LastDZJYDays]，上一次大宗交易距今有多少個交易日，停盤時間不計；

13. [ClosedDays]，累計停盤時長（交易日）；

14. [ZSCloseToMA5]，上證指數（大盤）目前收盤價相對于5日線的的漲幅 = 目前收盤價/MA5 - 1；

15. [ZSCloseToMA20]，上證指數（大盤）目前收盤價相對于20日線的的漲幅 = 目前收盤價/MA20 - 1；

16. [CloseToMA5]，股票目前收盤價相對于其5日線的的漲幅 = 目前收盤價/MA5 - 1；

17. [CloseToMA20]，股票目前收盤價相對于其20日線的的漲幅 = 目前收盤價/MA20 - 1；

18. [BuyRK]，股價低于大宗交易價時，所處的天數。例如大宗交易發生在2018-1-1，目前是2018-1-3，則該字段為2，表示大宗交易這個事件發生後的第2天，這種情況視為有買入的機會；

19. [SellRK]，如果買入後，股價相對于買入價（或大宗交易價）上漲了3%，則記錄當天所處的天數為SellRK。

主要代碼如下：

if exists(select * from sysobjects where id=object_id('DZJY_TradeByDay') and OBJECTPROPERTY(id, 'IsTable')=) 
drop table DZJY_TradeByDay
go
if object_id('tempdb..#BaseTradeInfo') is not null drop table tempdb..#BaseTradeInfo
go
if object_id('tempdb..#bsrk') is not null drop table tempdb..#bsrk 
go
if exists(select * from sysobjects where id=object_id('MultiDimDataSet') and OBJECTPROPERTY(id, 'IsTable')=) 
drop table MultiDimDataSet
go

--基礎交易資訊，每天隻有一條大宗交易資料
select distinct TradeDate, StockCode into DZJY_TradeByDay from DZJY

--TradeToSelf用來記錄是否存在同席位交易，有則為，無則為
select (StockCode+convert(varchar(),TradeDate, ))as TradeKey,d.TradeDate, d.StockCode, 
        (select PriceClose from TradeByDay where TradeDate=d.TradeDate and StockCode = d.StockCode) as CurClose,
        round(sum(d.TradePrice * d.Volume)/sum(d.Volume),) as TradePrice, 
        sum(d.Volume) as Volume, 
        round(sum(d.TradePrice * d.Volume),) as Amount,
        count(distinct d.Buyer) as BuyerCount,
        count(distinct d.Seller) as SellerCount,
        max(TradeToSelf) as TradeToSelf
into #BaseTradeInfo
from (
    select StockCode, TradeDate, TradePrice, Volume, Buyer, Seller,
        TradeToSelf = case when Buyer = Seller then  else  end
    from DZJY

)d
group by d.TradeDate, d.StockCode

--買賣天數
select b1.TradeKey, b1.StockCode, b1.TradePrice, b1.CurClose, b1.FluctRange3, b1.RK, b1.BuyRK,
        --未來天内最高價大于交易價的倍，确定賣出的天數
        SellRK = case when b1.BuyRK is NULL then NULL
                      else (select TOP  RK from StockMA 
                            where StockCode = b1.StockCode and 
                                    RK between b1.BuyRK+ and b1.BuyRK+ and 
                                    PriceMax>(*b1.TradePrice) order by TradeKey)
                      end,
        round(b1.CurClose/s1.MA20-,) as CloseToMA20,
        round(b1.CurClose/s1.MA5-,) as CloseToMA5
into #bsrk
from (
    select b.TradeKey, b.StockCode, b.TradePrice, b.CurClose, s.FluctRange3, s.RK,
            --未來天内最低價小于大宗交易價，确定買入的天數
            (select TOP  RK from StockMA 
             where StockCode = b.StockCode and 
                    RK between s.RK+ and s.RK+ and 
                    PriceMin<b.TradePrice order by TradeKey) as BuyRK
    from #BaseTradeInfo b left outer join StockMA s on b.TradeKey = s.TradeKey
)b1 left outer join StockMA s1 on b1.TradeKey = s1.TradeKey

--抽取次元
select t.TradeKey, t.TradeDate, t.StockCode, t.CurClose, t.TradePrice, rk.FluctRange3, t.Volume, t.Amount, 
        round(t.TradePrice/t.CurClose-,) as Discount, t.BuyerCount, t.SellerCount,t.TradeToSelf,
        s.Capital, round(t.Volume/s.Capital,) as VolumeToCapital,
        df.LastDZJYDays, (df.LastTradeDays-df.LastDZJYDays) as ClosedDays,
        round(zs.CurSHClose/zs.SHMA20-,) as ZSCloseToMA20, 
        round(zs.CurSHClose/zs.SHMA5-,) as ZSCloseToMA5,
        rk.RK, isnull(rk.BuyRK,) as BuyRK, isnull(rk.SellRK,) as SellRK, rk.CloseToMA20, rk.CloseToMA5
into MultiDimDataSet
from #BaseTradeInfo as t left outer join Stockinfo s on t.StockCode = s.StockCode
    left outer join DZJY_TradeFreq df on t.TradeKey = df.TradeKey
    left outer join SHZSMA zs on t.TradeDate = zs.TradeDate
    left outer join #bsrk rk on t.TradeKey = rk.TradeKey
           

查詢的優化

計算買賣天數時，對于上百萬行的資料，檢索速度實在比較拙計。是以通過了一些手段來提高查詢速度，譬如索引。這裡展示部分表在計算時加的索引：

IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('gupiao.dbo.StockRanK') and OBJECTPROPERTY(id, 'IsTable')=)
DROP TABLE gupiao.dbo.StockRanK
GO
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('gupiao.dbo.StockMA') and OBJECTPROPERTY(id, 'IsTable')=)
DROP TABLE gupiao.dbo.StockMA
GO

SELECT (StockCode+CONVERT(VARCHAR(),TradeDate,)) AS TradeKey, 
        TradeDate, StockCode, PriceOpen, PriceClose, PriceMin, PriceMax, Volume, Amount,
        RANK() OVER(PARTITION BY StockCode ORDER BY TradeDate) AS RK
INTO gupiao.dbo.StockRanK
FROM TradeByDay
GO


--增加索引
CREATE CLUSTERED INDEX [ClusteredIndex-] ON gupiao.dbo.StockRanK
(
    [StockCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-] ON gupiao.dbo.StockRanK
(
    [TradeDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--FluctRange3最近個交易日的波動幅度
SELECT s.TradeKey, s.StockCode, s.TradeDate,PriceOpen, PriceClose, PriceMin, PriceMax, RK,
    MA5 = ROUND((SELECT CASE WHEN SUM(Volume)= THEN NULL ELSE SUM(Amount)/SUM(Volume) END
                    FROM StockRanK WHERE StockCode = s.StockCode AND (RK BETWEEN (s.RK-) AND s.RK)),),
    MA20 = ROUND((SELECT CASE WHEN SUM(Volume)= THEN NULL ELSE SUM(Amount)/SUM(Volume) END
                    FROM StockRanK WHERE StockCode = s.StockCode AND (RK BETWEEN (s.RK-) AND s.RK)),),
    FluctRange3 = ROUND((SELECT CASE WHEN Min(PriceMin)= THEN NULL ELSE Max(PriceMax)/Min(PriceMin)- END
                    FROM StockRanK WHERE StockCode = s.StockCode AND (RK BETWEEN (s.RK-) AND s.RK)),)
INTO StockMA
FROM StockRanK AS s

CREATE CLUSTERED INDEX [ClusteredIndex-] ON gupiao.dbo.StockMA
(
    [StockCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-] ON gupiao.dbo.StockMA
(
    [TradeDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO