Denali T-SQL 新增的函數

Programmability Enhancements (Database Engine)

Conversion functions

  • Parse:將字串轉為指令資料類型

select parse(‘NT$ 3’ as money using ‘zh-TW’)  — 就 CTP 3 而言,線上叢書的範例用逗號是錯的,要用 using

image

select parse(‘NT$ 3’ as money using ‘en-US’)

訊息 9819,層級 16,狀態 1,行 1
使用 ‘en-US’ 文化特性將字串值 ‘NT$ 3’ 轉換成資料類型 money 時發生錯誤。

線上叢書關於 Parse 函數的這段說明需要注意,很多新增的函數好像都跟 CLR 有關

PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). If the server does not have the CLR installed, then the statement with the call to the PARSE function fails. If the user has disabled the CLR by using sp_configure, the PARSE function continues to work, since it is a system function and not a user-defined function.

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

  • Try_Convert:將資料已指定型別傳回,轉型失敗,回傳 NULL

with v(c1)as(select c1 from (values(‘a’),(‘123’),(NULL),(‘12.3’)) t(c1))
select isnull(try_convert(int, c1),0) from v

image

  • Try_Parse:將字串內容嘗試轉成目的資料類型,若無法轉成目的類型,則傳回 null

with v(c1)as(select c1 from (values(‘$3’),(‘NT$ 3’),(NULL),(‘12.3’)) t(c1))
select try_parse(c1 as money using ‘en-US’) from v  — 就 CTP 3 而言,線上叢書的範例用逗號是錯的,要用 using

image

 

Date and time functions

with v(Year,Month,Day)as(select * from (values(1,1,1),(null,1,1),(9999,12,31),(1,-1,1)) t(year,month,day))
select DateFromParts(Year,Month,Day) from v
image

最後一筆會造成錯誤:

訊息 289,層級 16,狀態 1,行 1
無法建構資料類型 date,部分引數擁有無效的值。

  • Datetime2FromParts:給予 year, month, day, hour, minute, seconds, fractions, precision,precision 指定小數位數,要是常數,且要大於等於代表 fraction 的字元長度量

with v(Y,M,D,h,mm,s,f)as(select * from (values(1,1,1,1,1,1,12345),(9999,12,31,23,59,59,1234567)) t(Y,M,D,h,mm,s,f))
select Datetime2FromParts(Y,M,D,h,mm,s,f,7) from v

image

 

尚有 DatetimeFromPartsDatetimeOffsetFromPartsSmallDatetimeFromPartsTimeFromParts 功能近似的新增函數。

  • EOMonth:傳回指定月份的最後一天,傳回的資料類型會根據第一個參數的資料類型

with v(n)as(select * from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) t(n))
select EOMonth(cast(getdate() as date),n) from v

image

 

Logical functions

  • 提供與 RS 功能近似的 Choose 函數

SELECT CHOOSE ( 4, ‘1’, 2, 3.0, convert(char(1),0x34) ) AS Result; — 4.0,會轉型

with v(c1) as (select c1 from (values(1),(2),(3)) t(c1))
select choose(c1, ‘1’, 2, 3.0, convert(char(1),0x34) ) from v
image

  • IIF 與各處的 IIF 相同🙂

with v(c1,c2)as(select * from (values(1,2),(2,1),(2,2)) t(c1,c2))
select iif(c1>c2,c1,c2) from v

image

 

String functions

  • Concat:銜接字串,NULL 會被視為空字串而非 NULL

SELECT concat(‘a’,’b’)
UNION ALL
SELECT concat(‘a’,null,’b’)
UNION ALL
SELECT concat(0,null,1.23,’a’)

image

  • format:傳回指定格式的字串

select format(123,’c4′,’en-US’)
union all
select format(123,’yy/mm/dd’,’zh-TW’)
union all
select format(getdate(),’yyyy/MM/dd’,’zh-TW’)
union all
select format(getdate(),’y-M-d h:m:s’)

image

 

Analytic Functions

  • CUME_DIST:Calculates the cumulative distribution of a value in a group of values in Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3). That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.
  • PERCENT_RANK:Calculates the relative rank of a row within a group of rows

with v(Part,ID) as (select * from (values(1,1),(1,1),(1,3),
(2,1),(2,2),(2,3),(2,4)) t(Part,ID))
SELECT Part,ID,
CUME_DIST () OVER (PARTITION BY Part ORDER BY ID) AS CumeDist,
PERCENT_RANK() OVER (PARTITION BY Part ORDER BY ID ) AS PctRank
FROM v

image

  • First_Value:群組內,依排序值的第一個,傳回指定欄位的值
  • Last_Value:似乎是群組內,依排序值若有相同值,取最後一個,傳回指定欄位的值

with v(Part,ID,Val) as (select * from (values(1,’a’,1),(1,’b’,1),(1,’c’,3),
(2,’a’,1),(2,’b’,2),(2,’c’,2),(2,’d’,4)) t(Part,ID,Val))
SELECT Part,ID,val,
First_Value (ID) OVER (PARTITION BY Part ORDER BY val DESC) AS FirstVal,
Last_Value(ID) OVER (PARTITION BY Part ORDER BY val) AS LastVal,
Last_Value(val) OVER (PARTITION BY Part ORDER BY val) AS LastVal2
FROM v

image

  • lag:群組內,依照排序取當下成員的前一個成員,傳回指定欄位的值或是預設值
  • lead:群組內,依照排序取當下成員的後一個成員,傳回指定欄位的值或是預設值

with v(Part,ID) as (select * from (values(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),(2,4)) t(Part,ID))
SELECT Part,ID,
lag (id,1,-99) OVER (PARTITION BY Part ORDER BY ID) AS Lag1,
lead(id,1,-99) OVER (PARTITION BY Part ORDER BY ID ) AS Lead1
FROM v

image

  • percentile_cont:依照群組與排序,取百分率與內差的值,在紀錄中不一定實際有該值
  • percentile_disc:依照群組與排序,取百分率對應的紀錄值

with v(Part,ID) as (select * from (values(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),(2,4)) t(Part,ID))
SELECT Part,ID,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ID)
                            OVER (PARTITION BY Part) AS MedianCont,
PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY ID)
                            OVER (PARTITION BY Part) AS MedianDisc,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ID)
                            OVER (PARTITION BY Part) AS MedianDisc2
FROM v

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: