神魂顛倒論壇logo

icon
首頁Microsoft 應用技術區資料庫 MS SQL → 探討SQL Server 2005的評價函數

雷射溶脂 | 瘦小腹 | 植髮 | 眼袋 | 玻尿酸 | 電波拉皮 | Flash | 購物車 | Flash Player 11.2 | 豐胸 |

下一主題 上一主題


探討SQL Server 2005的評價函數


簡版






探討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映射訂單中的每一件產品,產品的數量和價格總值(假定較大的購買量可以打折)。下圖展示了這些表(及字串)以及它們之間的關係。
在新視窗檢視
 
如圖所展示的,這個OrderItems在Orders和Products表之間建立一個對多對的連結。
  當構建報告或分析資料時,使用者或管理員經常希望看到以某種方式對資料的評價訊息。例如,你的老闆可能想要一個報告來顯示賣路最好的前十項,或在第三個季度銷售部中實現最大收入的前三名銷售人員。更複雜的情況可能是僅返回第3到第5個評價排名的銷售人員。在SQL Server 2000中,返回最高排名項的查詢可以通過使用TOP或ROWCOUNT關鍵字來實現。為了檢索一個特定評價子集,你需要使用一種"派生表"(或者是一種基於視圖的手段)。
  SQL Server 2005中引入了四個新的評價函數:ROW_NUMBER,RANK,DENSE_RANK和NTILE。儘管這些與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
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
...
  預設情況下,這個ROW_NUMBER函數把一個增量值(逐次加1)賦給結果集中的每一個記錄。借助於可選的partition參數,無論何時分區(partitioning)列值發生變化,你都可以讓ROW_NUMBER函數重新計算行數。為了說明這個問題,我使用如下查詢語法建立了一個視圖vwTotalAmountBilledPerOrder,它將返回每一個OrderID和該訂購的總訂單數:
SELECT OrderID,SUM(AmountBilled) AS TotalOrderAmountFROM OrderItemsGROUP BY OrderID
  這條語法將返回OrderItems表中每一個唯一的訂單,還有相應於該訂單的AmountBilled值的和。借助於這個視圖,我們可以使用ROW_NUMBER方法來按最大花錢數來評價這些訂單,如下所示:
SELECT c.Name,o.DateOrdered,tab.TotalOrderAmount, ROW_NUMBER() 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
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
...
  注意,某些顧客多次出現在這個列表中(如Bob,Tito和Bruce)。也許有時,我們不是想觀看以銷售量排序的所有訂單,而更想看到每一個顧客的最高訂單量。為此,我們可以通過使用ROW_NUMBER函數中的PARTITION BY子句達到這一目的,如下所示:
SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, ROW_NUMBER() OVER (PARTITION 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
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
...
  注意,儘管這些結果非常不錯;但是,你卻不能在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
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
...
  注意,具有相同數量的兩個訂單是怎樣都被評價為#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
  這個語法將返回如下表所示的結果:
ProductID
Name
Price
Quartile
8
Desk
495.0000
1
10
Executive Chair
295.0000
1
9
Chair
125.0000
2
5
Mouse
14.9500
2
6
Mousepad
9.9900
3
11
Scissors
8.5000
3
4
Stapler
7.9500
4
3
Binder
1.9500
4
  在隨本文下載的演示程式中,我提供了一個範例,它使用了一個具有NTILE函數的視圖來允許使用者有選擇地檢視各種分組的最大訂單量。
  六、 結論
  在本文中,我們探討了SQL Server 2005中的四個新函數的用法。這四個新函數分別是:ROW_NUMBER,RANK,DENSE_RANK和NTILE。與SQL Server 2000使用的舊技術相比,它們使得返回評價結果更為容易。然而,這些函數僅僅是SQL Server 2005中所提供的新的T-SQL特徵中的極少的一部分。



自體脂肪隆乳 | 回到頂部
Icon
網路的事情,讓網路解決

[ 逛網路就像是在探險 ]

    神 魂 顛 倒 T W G

http://bbs.flash2u.com.tw

http://tw.myblog.yahoo.com/flash2u-twg

http://flash-silverlight.blogspot.com/

http://flash2u.spaces.live.com/?_c02_owner=1

簡版






探討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映射訂單中的每一件產品,產品的數量和價格總值(假定較大的購買量可以打折)。下圖展示了這些表(及字串)以及它們之間的關係。
在新視窗檢視
 
如圖所展示的,這個OrderItems在Orders和Products表之間建立一個對多對的連結。
  當構建報告或分析資料時,使用者或管理員經常希望看到以某種方式對資料的評價訊息。例如,你的老闆可能想要一個報告來顯示賣路最好的前十項,或在第三個季度銷售部中實現最大收入的前三名銷售人員。更複雜的情況可能是僅返回第3到第5個評價排名的銷售人員。在SQL Server 2000中,返回最高排名項的查詢可以通過使用TOP或ROWCOUNT關鍵字來實現。為了檢索一個特定評價子集,你需要使用一種"派生表"(或者是一種基於視圖的手段)。
  SQL Server 2005中引入了四個新的評價函數:ROW_NUMBER,RANK,DENSE_RANK和NTILE。儘管這些與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
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
...
  預設情況下,這個ROW_NUMBER函數把一個增量值(逐次加1)賦給結果集中的每一個記錄。借助於可選的partition參數,無論何時分區(partitioning)列值發生變化,你都可以讓ROW_NUMBER函數重新計算行數。為了說明這個問題,我使用如下查詢語法建立了一個視圖vwTotalAmountBilledPerOrder,它將返回每一個OrderID和該訂購的總訂單數:
SELECT OrderID,SUM(AmountBilled) AS TotalOrderAmountFROM OrderItemsGROUP BY OrderID
  這條語法將返回OrderItems表中每一個唯一的訂單,還有相應於該訂單的AmountBilled值的和。借助於這個視圖,我們可以使用ROW_NUMBER方法來按最大花錢數來評價這些訂單,如下所示:
SELECT c.Name,o.DateOrdered,tab.TotalOrderAmount, ROW_NUMBER() 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
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
...
  注意,某些顧客多次出現在這個列表中(如Bob,Tito和Bruce)。也許有時,我們不是想觀看以銷售量排序的所有訂單,而更想看到每一個顧客的最高訂單量。為此,我們可以通過使用ROW_NUMBER函數中的PARTITION BY子句達到這一目的,如下所示:
SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, ROW_NUMBER() OVER (PARTITION 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
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
...
  注意,儘管這些結果非常不錯;但是,你卻不能在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
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
...
  注意,具有相同數量的兩個訂單是怎樣都被評價為#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
  這個語法將返回如下表所示的結果:
ProductID
Name
Price
Quartile
8
Desk
495.0000
1
10
Executive Chair
295.0000
1
9
Chair
125.0000
2
5
Mouse
14.9500
2
6
Mousepad
9.9900
3
11
Scissors
8.5000
3
4
Stapler
7.9500
4
3
Binder
1.9500
4
  在隨本文下載的演示程式中,我提供了一個範例,它使用了一個具有NTILE函數的視圖來允許使用者有選擇地檢視各種分組的最大訂單量。
  六、 結論
  在本文中,我們探討了SQL Server 2005中的四個新函數的用法。這四個新函數分別是:ROW_NUMBER,RANK,DENSE_RANK和NTILE。與SQL Server 2000使用的舊技術相比,它們使得返回評價結果更為容易。然而,這些函數僅僅是SQL Server 2005中所提供的新的T-SQL特徵中的極少的一部分。



自體脂肪隆乳 | 回到頂部
Icon
網路的事情,讓網路解決

[ 逛網路就像是在探險 ]

    神 魂 顛 倒 T W G

http://bbs.flash2u.com.tw

http://tw.myblog.yahoo.com/flash2u-twg

http://flash-silverlight.blogspot.com/

http://flash2u.spaces.live.com/?_c02_owner=1

簡版






請問可有sql2005函數電子書
初學sql
想先認識sql函數來入手
目前可透過obdc連線至sql讀取資料
但sql相關函數還是不太會用
尤其是要套入條件約束或預設程序
以上敬請協助
謝謝



雷射溶脂 | 回到頂部

簡版






請問可有sql2005函數電子書
初學sql
想先認識sql函數來入手
目前可透過obdc連線至sql讀取資料
但sql相關函數還是不太會用
尤其是要套入條件約束或預設程序
以上敬請協助
謝謝



瘦小腹 | 回到頂部
下一主題 上一主題