在此將以台灣50（0050）及台積電（2330）之股票五日均線（MA5: Moving Average）計算為例，說明分析函數之基本概念，SQL2012及ORACLE 8i/9i起已陸續提供許多相關析函數。
5日均線即逐日計算出最近5日(前4天及當天)收盤價之平均價格，再將每日數值連成一條曲線，以下圖0050為例，01/08之MA5為01/02、03、04、05及當日等5日收盤價平均值（82.60、83.35、83.50、83.75及84.10等5日收盤價平均值為83.46），如下圖。
一般常用計算5日均線之SQL，如下所示:
WITH Stk AS
(
SELECT *
, ROW_NUMBER() OVER(PARTITION BY StkId ORDER BY Dt) SEQ
FROM #STK
)
SELECT C.StkId, C.Dt, C.Pri, C.SEQ
, (
SELECT COUNT(*)
FROM Stk P
WHERE 1=1
AND C.StkId = P.StkId
AND P.SEQ BETWEEN C.SEQ-4 --PRECEDING
AND C.SEQ --CURRENT ROW
) RowCnt
, (--彙總運算(子查詢): 每5筆(前4筆及目前)計算出平均值
SELECT SUM(P.Pri) / COUNT(*) --3.計算平均值可換為AVG(P.Pri)
FROM Stk P
WHERE 1=1
AND C.StkId = P.StkId --1.股票限制:限同檔股票資料
AND P.SEQ BETWEEN C.SEQ-4 --2.日期區間:(起)前4日收盤價PRECEDING
AND C.SEQ --2.日期區間:(迄)當日收盤價CURRENT ROW
) MA5
FROM Stk C--原始資料(主查詢)，0050、2330各6筆
WHERE StkId IN ('0050', '2330') --0.查詢限制: 0050、2330兩檔
|
將區分主查詢（2018年初兩檔股票收盤價）及子查詢（計算5日均線）進行說明:
1. 主查詢（2018年初兩檔股票收盤價）
主查詢為2018年初兩檔股票收盤價原始資料，並未包含無任何彙總運算。但為提供子查詢計算5日均線(前4天及當天)相對位置之參考依據，需使用ROW_NUMBER()次序函數根據每檔股票收盤價日期分別產生流水序號。
WITH Stk AS
(
SELECT *
, ROW_NUMBER() OVER(PARTITION BY StkId --1.股票限制: 限同檔股票資料
ORDER BY Dt --2’.推導出日期前後次序關係，子查詢將參考使用
) SEQ
FROM #STK
)
SELECT C.StkId, C.Dt, C.Pri, C.SEQ
FROM Stk C--原始資料(主查詢)
WHERE C.StkId IN ('0050', '2330') --0.查詢限制: 0050、2330兩檔
|
2. 子查詢（計算5日均線）
子查詢與主查詢以股票代碼進行內部連結(INNER JOIN)，限制子查詢與主查詢處理同一檔股票，再以流水序號限制此股票前4日及當日之資料，最後計算此5筆收盤價之平均值（MA5），如SQL中所列3個步驟。
(--彙總運算(子查詢): 每5筆(前4筆及目前)計算出平均值
SELECT SUM(P.Pri) / COUNT(*) --3.計算平均值可換為AVG(P.Pri)
FROM Stk P
WHERE 1=1
AND C.StkId = P.StkId --1.股票限制: :限同檔股票資料
AND P.SEQ BETWEEN C.SEQ-4 --2.日期區間: (起)前4日收盤價PRECEDING
AND C.SEQ --2.日期區間: (迄)當日收盤價CURRENT ROW
) MA5
|
由前述查詢 （尤其為子查詢）之設計想法，即可導出分析函數基本概念。改以AVG()分析函數重新撰寫，SQL及說明如下:
SELECT C.StkId, C.Dt, C.Pri--, C.SEQ
, AVG(Pri) --3.計算平均值
OVER(PARTITION BY StkId --1.股票限制: 限同檔股票資料
ORDER BY Dt --2’.推導出日期前後次序關係，
----------(以上與ROW_NUMBER概念相同)----------------
ROWS BETWEEN 4 PRECEDING --2.日期區間: (起)前4日
AND CURRENT ROW --2.日期區間: (迄)當日
) MA5
--, MA5
FROM #Stk C --原始資料(主查詢)
WHERE StkId IN ('0050', '2330') --0.查詢限制: 0050、2330兩檔
|
將子查詢及分析函數之視窗框架（WINDOW）條件式兩者對照整理如下，由此可得知分析函數重要概念『視窗框架（WINDOW）』。
日期區間（WINDOW）
|
子查詢-查詢條件式
|
分析函數-窗口條件式
|
2.日期區間: (起)前4日收盤價
2.日期區間: (迄)當日收盤價
|
P.SEQ BETWEEN C.SEQ-4 --上界
AND C.SEQ --下界
|
ROWS BETWEEN 4 PRECEDING--上界
AND CURRENT ROW--下界
|
WINDOW子句可指定資料區間之起始點和結束點，用以再次限縮 計算資料集。語法如下:
[ROWS|RANGE] BETWEEN <Start Expr> AND <End Expr>
|
ROWS: 指定目前資料列（Row）前或後之固定資料筆數。
RANGE: 以邏輯方式指定與目前資料列的值相關的值範圍 。
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
|
另外，為符合計算5日均線所需，查詢條件之起始條件（上界）為前4天（4 PRECEDING），結束條件（下界）為當天（CURRENT ROW），請注意天數之計算基準是以營業日而非日曆日，因此將使用ROWS關鍵字指定前後資料列，如同子查詢範例，ROW_NUMBER()次序函數於ORDER BY子句中指定收盤日為排序依據並產生前後對應次序關係，即是以動態/滑動相對座標之概念。
ROWS BETWEEN 4 PRECEDING--上界
AND CURRENT ROW--下界
|
如本文計算MA為例，欲同時呈現股票每日之收盤價（主查詢，原始資料）及5日均價（子查詢，彙總運算結果），使用分析函數可簡化（取代）過去許多需使用多組自我連結複雜查詢語法（通常為子查詢），如圖示中需一併計算MA10（10日均價），也可直指定上界為前9天（9 PRECEDING）即可，在此嘗試以對照說明方式分析函數導入之概念，使用細節、參數未多做著墨，應用時建議參考原廠文件、其他技術文章等。範例所用AVG()函數為分析函數與並非為彙總函數，兩者差別比較整理如下，以供應用參考。
彙總函數(Aggegate)
|
分析函數(Analytic)
|
|
語法
|
常搭配GROUP BY子句
|
OVER關鍵字
|
輸出
|
將多筆彙總成一筆
|
仍為原資料集
|
沒有留言:
張貼留言