2017年12月25日 星期一

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

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

Bloomberg
代碼(APPLE為例: AAPL US EQUITY)會額外註記商品類型,如EQUITYCOMDTYINDEX...等等;當資料儲存或顯示同類型商品時則有重覆現象,而欲將代尾端之商品類型剔除,當然可使用多組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的起始位置可為負值,但並非代表方向(由右至左)
  

執行結果如下圖所示:

2017年4月16日 星期日

[ORACLE] Model指令應用簡介

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

跨列(Row)參照類型問題,如使用EXCEL則相當容易且方便即可達成,本範例,可於新欄位(Column)使用IF函數,以EXCELE3欄位運式為例,首先判斷匯率欄位是否為空值(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).運算公式,當同一筆ROWRATE為空值時,則採用前一筆運算欄位值替代