SQL QnA

2018年1月21日星期日

[ORACLE] wmsys.wm_concat 字串彙總合併函數

在此介紹10g 未公佈的指令wmsys.wm_concat，可於彙總運算中將同一群組之字串進行合併串連(CSV String)，類似MySQL所提供的GROUP_CONCAT函數。

也一併列出ORACLE 11g R2提供一組新的分析函數/彙總函數『LISTAGG』，同樣可用於彙總運算中使用。為分析重複資料對兩個指令影響及處理，因此額外造出重複資料。如下:

---測試資料

CREATE TABLE Calendar

SELECT TO_CHAR(DATE'2010-01-01' + (LEVEL-1), 'YYYY-MM-DD') DT

FROM DUAL

CONNECT BY DATE'2010-01-01' + (LEVEL-1) < DATE'2014-01-01'

UNION ALL --額外造出重複資料

SELECT TO_CHAR(DATE'2010-01-01' + (LEVEL-1), 'YYYY-MM-DD') DT

FROM DUAL

CONNECT BY DATE'2010-01-01' + (LEVEL-1) < DATE'2014-01-01';

SQL及執行結果如下。請注意由於wmsys.wm_concat輸出資料型態為CLOB，因此額外使dbms_lob.substr指令將CLOB轉換為VARCHAR2(4000)，也請特別注意ORACLE的VARCHAR2長度限制問題，而LISTAGG函數無剔除重複值之參數及設定，請注意使用。

--顥示結果

SELECT SUBSTR(DT, 1, 7) YrMs

, COUNT(*) RowCnt

, wmsys.wm_concat(DT) --輸出為CLOB

, dbms_lob.substr(wmsys.wm_concat(DT), 4000, 1) --未排序

, dbms_lob.substr(wmsys.wm_concat(distinct DT), 4000, 1) --使用DISTINCT即可排序

, LISTAGG(DT, ',') WITHIN GROUP(ORDER BY DT) --LISTAGG無法搭配DISTINCT

FROM Calendar

GROUP BY SUBSTR(DT, 1, 7)

由於wmsys.wm_concat命令除指定合併字串外，並無任何其他參數可設定，因此使用上非常簡便，且可直接搭配DISTINCT關鍵字即可剔除重複值，此二項為重要優點，但非為官方公開指令，因此無法確保未來是否繼續支援，另外，無法指定分隔符號及指定排序為其缺點，但可使用其他方法解決。

	wmsys.wm_concat	LISTAGG
版本	10g 非正式指令	11g R2
輸出型態	CLOB	VARHCAR2
分隔符號參數	無內部設定為逗號(,)	有
DISTINCT (剔除重複)	可使用	不可需使用子查詢並配合其他函數
排序參數	無可搭配DISTINCT參數	有

2018年1月18日星期四

[MSSQL] 字串彙總(CSV String)

許多實務應用時常需將群組特定字串組合成格式化字串(如CSV字串)，如下表列出部門員工列表。

Dept	Emp_List
HR	Jane
IT	Andy,Kevin
MA	James,May

許多資料庫較新版本已提供字串彙總函數，如MySQLGROUP_CONCAT指令、ORACLE 11g之LISTAGG或MSSQL 2017之STRING_AGG函數。

資料庫	指令
MySQL	GROUP_CONCAT
ORACLE 11g	LISTAGG
MSSQL 2017	STRING_AGG

MSSQL2017以下版本，可採用ML PATH進行字串組合，由於指令稍稍繁雜且使用上並不簡便，但為內建功能而常被應用，但強烈建議以CLR建立User-Defined Aggregate Functions。

	SQL
方法1	SELECT DISTINCT E.Dept , STUFF( ( SELECT ',' + A.EmpName FROM #Emp A WHERE E.Dept = A.Dept ORDER BY A.EmpName FOR XML PATH('') ) , 1, 1, '' ) AS Emp_List FROM #Emp E
方法2	SELECT DISTINCT E.Dept , SUBSTRING(EmpList, 1, LEN(EmpList)-1) EmpList FROM #Emp E CROSS APPLY ( SELECT COALESCE(A.EmpName, '') + ',' FROM #Emp A WHERE E.Dept = A.Dept ORDER BY A.EmpName FOR XML PATH('') ) X (EmpList) *提醒: 此語法在2014版本中建立為VIEW時，發生1033錯誤，方法1則可。2016卻正常。

建立資料:

SELECT * INTO #Emp

FROM (VALUES('HR', 'Jane')

,('IT', 'Andy')

,('IT', 'Kevin')

,('MA', 'May')

,('MA', 'James')

) AS Emp(Dept, EmpName)

2018年1月17日星期三

[MSSQL] JSON資料交換

JavaScript Object Notation (JSON) 為一種以文字為基礎的輕量級資料交換語言，常應用於網站資料呈現、傳輸等，將資料從伺服器送至用戶端，並可由網頁直接呈現。MSSQL2016版本內建json功能，可簡化前端應用程式於資料編組json字串，或者拆解傳輸（交換）資料等應用。

步驟	SQL
產生Json	SELECT * FROM (VALUES('2330', 240.50, '20180116') , ('0050', 85.00, '20180116') , ('3008', 4000, '20180116') ) PRI(StkId, Cls_Pri, DT) FOR JSON AUTO
匯入Json	DECLARE @json varchar(max) SET @json='[{"StkId":"2330","Cls_Pri":240.50,"DT":"20180116"} ,{"StkId":"0050","Cls_Pri":85.00,"DT":"20180116"} ,{"StkId":"3008","Cls_Pri":4000.00,"DT":"20180116"}]' SELECT * FROM OPENJSON (@json) WITH ( StkId varchar(10), Cls_Pri NUMERIC(10,4), DT datetime )

參考資料:

https://docs.microsoft.com/zh-tw/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

https://docs.microsoft.com/zh-tw/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

2018年1月21日 星期日