?
快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

銀河唯一官網1331:探討SQL Server 2005的評價函數評價函數

?

一、 簡介

在2005年11月份,微軟發行了三種新產品系列:Visual Studio 2005,SQL Server 2005和.NET框架2.0(它包括ASP.NET 2.0)。SQL Server 2005是微軟自從其上一個主要發行版本SQL Server 2000以來最新版本的數據庫平臺。在以前五年的成長中,SQL Server中加入了大年夜量的新特性,所有這些新內容都被總結到微軟網站的一篇文章《What's New in SQL Server 2005?》中。應用SQL Server 2005作為后端數據庫構建基于web利用法度榜樣的開拓者很可能會對這些新特性抱有濃厚的興趣,這些新特性包括新的T-SQL改進,更好的Visual Studio集成,與CLR/.NET框架的集成,以及SQL Server 2005 Management Studio利用法度榜樣(它是SQL Server 2000的企業治理器的一個更為"平滑"的版本)。

與曩昔的SQL Server 2000比擬, 2005中的T-SQL改進使得編寫某些類型的查詢極為輕易。在SQL Server 2005中,T-SQL語法更為精練、可讀和易于理解。

在本文中,我們將專門探究SQL Server 2005的評價函數,它們大年夜大年夜簡化了對查詢結果進行評價的歷程。

二、 數據模型和評價結果根基

在我們闡發若何應用通俗查詢模式之前,讓我們起開創建一個能夠運行這些查詢的數據模型。在本文中,我應用SQL Server 2005 Express版原先實現我的演示,并且包括了一個數據庫和一個ASP.NET 2.0網站(請參考本文響應的完備源碼。就象Visual Studio一樣,SQL Server發行中也一同加雜了其它一些不合的版本。此中,Express版本是一個針對業余喜歡者、門生等群體的免費版本。假如你下載和安裝Visual Web Developer(Visual Studio針對web開拓者的Express版本),那么你可以選擇一同安裝SQL Server 2005 Express版本)。

對付本文中的示例,我們將應用一個含有產品、販賣職員(雇員)、顧客和訂單信息的數據庫。我們應用五個表來建模:Customers,Employees,Products,Orders和OrderItems。此中,Customers,Employees和Products表分手包孕每一個顧客,雇員和產品信息的行記錄數據。每當一個顧客進行購買活動,一條新記錄被添加到Orders表中,此中的信息唆使該顧客實現了購買、該雇員進行的這一販賣活動及訂單的日期。此中,OrderItems映射訂單中的每一件產品,產品的數量和價格總值(假定較大年夜的購買量可以打折)。下圖展示了這銀河唯一官網1331些表(及字段)以及它們之間的關系。

如圖所展示的,這個OrderItems在Orders和Products表之間建立一個對多對的連接。

當構建申報或闡發數據時,用戶或治理員常常盼望看到以某種要領對數據的評價信息。例如,你的老板可能想要一個申報來顯示賣路最好的前十項,或在第三個季度販賣部中實現最大年夜收入的前三名販賣職員。更繁雜的環境可能是僅返回第3到第5個評價排名的販賣職員。在SQL Server 2000中,返回最高排名項的查詢可以經由過程應用TOP或ROWCOUNT關鍵字來實現。為了檢索一個特定評價子集,你必要應用一種"派生表"(或者是一種基于視圖的手段)。

SQL Server 2005中引入了四個新的評價函數:ROW_NUMBER,RANK,DENSE_RANK和NTILE。只管這銀河唯一官網1331些與SQL Server 2000所供給的函數比擬是一個顯著的進步,然則這些函數的應用仍舊存在一些限定(要求應用派生表或視圖來實現功能更為強大年夜的利用法度榜樣)。下面讓我們闡發一下每一個函數。

三、 應用ROW_NUMBER函數謀略行數

這個ROW_NUMBER函數把一個序數值賦給每一個返回的記錄,該序數值依附于一個特定的與這個函數一路應用的ORDER BY語句。函數ROW_NUMBER的語法是:ROW_NUMBER() OVER([partition] ORDER BY子句)。例如,下列查詢將返回從最貴的到最便宜的產品,對每一種產品按價格進行評價:

SELECT ProductID,Name,Price, ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products

這個語句的履行結果如下表所示:

ProductID

Name

Price

PriceRank

8

Desk

495.0000

1

10

Executi銀河唯一官網1331ve Chair

295.0000

2

9

Chair

125.0000

3

5

Mouse

14.9500

4

6

Mousepad

9.9900

5

11

Scissors

8.5000

6

4

Stapler

7.9500

7

3

Binder

1.9500

8

...

默認環境下,這個ROW_NUMBER函數把一個增量值(逐次加1)賦給結果集中的每一個記錄。借助于可選的partition參數,無論何時分區(partitioning)列值發生變更,你都可以讓ROW_NUMBER函數從新謀略行數。為了闡明這個問題,我應用如下查詢語法創建了一個視圖vwTotalAmountBilledPerOrder,它將返回每一個OrderID和該訂購的總訂單數:

ProductID

Name

Price

PriceRank

8

Desk

495.0000

1

10

Executive Chair

295.0000

2

9

Chair

125.0000

3

5

Mouse

14.9500

4

6

Mousepad

9.9900

5

11

Scissors

8.5000

6

4

Stapler

7.9500

7

3

Binder

1.9500

8

...

留意,某些顧客多次呈現在這個列表中(如Bob,Tito和Bruce)。大概無意偶爾,我們不是想不雅看以販賣量排序的所有訂單,而更想看到每一個顧客的最高訂單量。為此,我們可以經由過程應用ROW_NUMBER函數中的PARTITION BY子句達到這一目的,如下所示:

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, ROW_NUMBER() OVER (P銀河唯一官網1331ARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID

這個語句將返回如下表所示的結果:

Name

DateOrdered

TotalOrderAmount

BestCustomer

Bob

12/1/2005

12649.9900

1

Darren

1/2/2006

620.0000

2

Bob

12/19/2005

265.8500

3

Tito

12/22/2005

14.9500

4

Bruce

1/5/2006

14.9500

5

Tito

12/18/2005

12.4400

6

Bruce

1/4/2006

9.9900

7

Lee Ann

1/3/2006

8.5000

8

...

留意,只管這些結果異常不錯;然則,你卻不能在WHERE語句中應用ROW_NUMBER()函數(或任何其它的評價函數)。也便是說,你可能想要說,"把按價格評價第5到第8名的產品列出"。為此,你必要應用一個派生的表或視圖。例如,你可以把上面的查詢放到一個視圖vwPriceRankedProducts中,然后應用如下查詢返回第5到第8個排名的產品:

SELECT ProductID,Name,Price,PriceRankFROM vwPriceRankedProductsWHERE PriceRank BETWEEN 5 AND 8

四、 應用RANK和DENSE_RANK處置懲罰同級問題

基于可選的partition子句和要求的order by子句,ROW_NUMBER函數默認地遞增(加1)每一個返回結果的值。然而,無意偶爾你可能想以不合要領處置懲罰相同級別,而不是把相同的值賦給相同的級別。例如,前面顯示的總訂單列表中,Tito在2005年12月22日實現的訂單數與Bruce在2006年1月5日實現的訂單數相同;然而,ROW_NUMBER函數卻把這兩行評價為#4和#5,而不是都評價為#4。

RANK和DENSE_RANK函數都能夠應用相同的評價計數級。例如,應用下列查詢:

SELECT c.Name,o.DateOrdered,tab.TotalOrderAmount, RANK() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID

這個語句將返回如下表所示的結果:

Name

DateOrdered

TotalOrderAmount

BestCustomer

Bob

12/1/2005

12649.9900

1

Bob

12/19/2005

265.8500

2

Tito

12/22/2005

14.9500

1

Tito

12/18/2005

12.4400

2

Darren

1/2/2006

620.0000

1

Bruce

1/5/2006

14.9500

1

Bruce

1/4/2006

9.9900

2

Lee Ann

1/3/2006

8.5000

1

...

留意,具有相同數量的兩個訂單是如何都被評價為#4的。RANK和DENSE_RANK之間的不合之處在于,在平級的結果后他們是若何從新開始計數的。RANK繞過盡可能多的平級的結果。在我們上面的示例中,由于有兩個結果綁定在#4上,是以跟隨其后的#5被跳過而評價等級以#6繼承。另一方面,DENSE_RANK從下一個整數繼承。假如我們在上面的查詢中應用函數名DENSE_RANK代替RANK,那么Tito在2005年12月18日響應于單價$12.44的訂單評價將是#5。

類似于ROW_NUMBER函數,RANK和DENSE_RANK函數都能應用可選的PARTITION BY語句。

五、 應用NTILE函數對評價結果進行分組

SQL Server 2005新引入的著末一個與T-SQL函數相關的評價函數是NTILE(int)。NTILE象其它的評價函數一樣操作,然則它能夠把結果分成組,每組中響應相同評價結果的記錄。你可以應用NTILE函數把結果分化成兩組、三組或四組等,如下例所示:

SELECT ProductID,Name,Price,NTILE(4) OVER (ORDER BY Price DESC) as QuartileFROM Produts

這個語句將返回如下表所示的結果:

Name

DateOrdered

TotalOrderAmount

BestCustomer

Bob

12/1/2005

12649.9900

1

Darren

1/2/2006

620.0000

2

Bob

12/19/2005

265.8500

3

Tito

12/22/2005

14.9500

4

Bruce

1/5/2006

14.9500

5

Tito

12/18/2005

12.4400

6

Bruce

1/4/2006

9.9900

7

Lee Ann

1/3/2006

8.5000

8

...

在隨本文下載的演示法度榜樣中,我供給了一個例子,它應用了一個具有NTILE函數的視圖來容許用戶有選擇地查看各類分組的最大年夜訂單量。

六、 結論

在本文中,我們探究了SQL Server 2005中的四個新函數的用法。這四個新函數分手是:ROW_NUMBER,RANK,DENSE_RANK和NTILE。與SQL Server 2000應用的舊技巧比擬,它們使得返回評價結果更為輕易。然而,這些函數僅僅是SQL Server 2005中所供給的新的T銀河唯一官網1331-SQL特性中的極少的一部分。

免責聲明:以上內容源自網絡,版權歸原作者所有,如有侵犯您的原創版權請告知,我們將盡快刪除相關內容。

您可能還會對下面的文章感興趣:

快三平台开户