找出欄位值中最小(大)值

ORACLE直接使用LEAST函數(最小)或GREATEST函數(最大)即可，但MSSQL目前版本尚未提供類似函數，但可用CROSS APPLY/VALUES等組合出所需功能。

	ORACLE	MSSQL
資料	CREATE TABLE T ( ID INT , Col1  INT, COL2  INT, COL3  INT )  INSERT INTO T VALUES(1, 1, 2, 3); INSERT INTO T VALUES(2, 5, 8, 4); INSERT INTO T VALUES(3, 7, 2, 6);	CREATE TABLE #T ( ID INT IDENTITY(1, 1), Col1  INT, COL2  INT, COL3  INT )   INSERT INTO #T VALUES(1, 2, 3) INSERT INTO #T VALUES(5, 8, 4) INSERT INTO #T VALUES(7, 2, 6)
SQL	SELECT ID, Col1, Col2, Col3   , LEAST(COL1, COL2, COL3) MinVal   , GREATEST(COL1, COL2, COL3) MaxVal FROM T	SELECT ID, Col1, Col2, Col3      , MinVal      , MaxVal FROM #T CROSS APPLY   (    SELECT MIN(d) MinVal         , MAX(d) MaxVal    FROM (VALUES (Col1),(Col2),(Col3)) AS a(d)   ) A (1). 以VALUES函數，逐筆將Col1~3轉成僅具1個欄位      (d)的虛擬Table(a)，原3個欄位轉成3筆資料。 (2). 再以MIN/MAX函數找出3筆中最小(大)值      (即原資料中3個欄位中最小(大)值)。

如何取得本月第三個週三 (期貨轉倉日)

每月的第三個週三為期貨轉倉日，以下範例將進行說明，將使用以下二個重要概念：

1.   取得下一個週三

請注意下週三與下一個週三意義不同，如今天為週二，則下週三為第8天，而下一個週三則為次日。ORACLE可直接使用NEXT_DAY函數，MSSQL無對應函數，但可使用計算方式求得。

2.   使用上個月底日為基準日

本月第三個週三，如使用本月初為基準日，需額外判斷月初是否符合第一個週三等煩雜運算邏輯，如使用上個月月底日，即可完全省略此判斷式（重點）。

DB	SQL
ORACLE	SELECT NEXT_DAY(TRUNC(DATE'2018-01-02', 'MM')-1, 4) + 2*7 "第三個週三" FROM DUAL 上個月月底: TRUNC(DATE'2018-01-02', 'MM')-1 下一個週三: NEXT_DAY({DATE}, 4)
MSSQL	SELECT Tgt   , CONVERT(CHAR(10)       , Tgt + (7- DATEPART(DW, Tgt) + 3)%7+1       , 120) "下一個週三"   , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')  "上個月底日"      , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')               + (7          - DATEPART(DW             , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')) + 3)%7 +1     + 2*7 "第三個週三" FROM    (    SELECT CAST('2018-01-02' AS DATETIME) Tgt    --UNION ALL    --SELECT CAST('2018-03-30' AS DATETIME)    ) A 上個月月底: DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01') 下一個週三: Tgt + (7- DATEPART(DW, Tgt) + 3)%7 +1

如何捨棄最右方空白後之字串

在此說明如何直接捨棄右方空白後之字串，由於可能會有1或2個空白，需由左至右找到1st空白，然後去尾。

Bloomberg代碼(以APPLE為例: AAPL US EQUITY)會額外註記商品類型，如EQUITY、COMDTY、INDEX...等等；當資料儲存或顯示同類型商品時則有重覆現象，而欲將代尾端之商品類型剔除，當然可使用多組REPLACE函數執行置換成空白，但會造成SQL極為繁雜，多種商品類型時不建議使用，SQL如下:

DB	SQL
ORACLE	SELECT COLUMN_VALUE      , SUBSTR(COLUMN_VALUE, 1, INSTR(COLUMN_VALUE, ' ',-1, 1)-1) BBG FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('AAPL US EQUITY'              , 'Z M3 INDEX', 'FVH4 COMDTY')); 可使用INSTR使用負值，代表由右至左，即可。
MSSQL	SELECT Ticker      , LEFT(Ticker            , LEN(Ticker)-CHARINDEX(' ', REVERSE(Ticker))            ) BBG    FROM (VALUES('AAPL US EQUITY'), ('Z M3 INDEX'), ('FVH4 COMDTY')) BBG(Ticker) 將字串反轉，找到最後一個空白。MSSQL的起始位置可為負值，但並非代表方向(由右至左)。

  

執行結果如下圖所示:

[ORACLE] Model指令應用簡介

MODEL子句是10g起所提供之新功能，提供類似EXCEL中之功能，與傳統的一般語法之最大差別，在於提供跨列（Row）引用、多單元格引用以及單元格聚合等，以下以匯率市場為例，當股滙市休市時，由於無法當時取得當日（即時）匯率，則以前一營日之匯率替代，如下圖，由於03/18（六）及03/19（日）為例假休市，因此將以前一營業日03/17（五）匯率替代此兩日之匯率。

跨列(Row)參照類型問題，如使用EXCEL則相當容易且方便即可達成，本範例，可於新欄位（Column）使用IF函數，以EXCEL的E3欄位運式為例，首先判斷匯率欄位是否為空值（D3<>""），若不為空值則取直接取用（D3）；反之，則取得同欄位中前一筆資料（E2），判斷運算式如下。

=IF(D3<>"", D3, E2)

SELECT *

FROM

    (

    SELECT 'USD' CRNCY, DATE'2017-03-17' CDATE, 30.5 Rate

    FROM DUAL     

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-18' DT, NULL

    FROM DUAL     

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-19' DT, NULL

    FROM DUAL 

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-20' DT, 30.2

    FROM DUAL

     )

MODEL

--(1).以此資料進行分群，幣別

PARTITION BY (CRNCY) 

--(2).創造虛擬列(ROW)順序流水號

DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY CRNCY ORDER BY CDATE) SEQ)

--(3)輸出欄位(RATE, CDATE)及新增之匯率運算欄位（RATE_Adj，預設為RATE欄位值）

MEASURES (RATE, CDATE, RATE RATE_Adj)

RULES

(

  RATE_Adj[ANY] = COALESCE(RATE[CV()], RATE_Adj[CV()-1], 0)  

--(3).運算公式，當同一筆ROW的RATE為空值時，則採用前一筆運算欄位值替代

)