ヒント10：SQL Server 2005でのTOP N
SQLのTOP NクエリでNが可変となるケースを扱ったことがある人はご存知と思いますが、SQL 2000ではその場合、動的SQLを使用する方法か、ROWCOUNTを設定して後でリセットするという方法でしか対応できませんでした。その理由は、SQL 2000ではNはリテラルとして扱われるからです。
-- Dynamic SQL to implement variable TOP N DECLARE @nTop int SET @nTop = 5 DECLARE @cSQL nvarchar(100) SET @cSQL = N'SELECT TOP ' + CAST(@nTop AS VARCHAR(4)) + ' * FROM ORDERS ORDER BY Freight DESC' EXECUTE SP_EXECUTESQL @cSQL -- Setting ROWCOUNT to implement variable TOP N DECLARE @nTop int SET @nTop = 5 SET ROWCOUNT @nTop -- Get the top 5 with the most Freight SELECT * FROM Orders ORDER BY Freight DESC -- Set back to 0 SET ROWCOUNT 0
幸い、SQL Server 2005では、TOP Nの実装が拡張されており、Nを変数として扱って、SQLステートメントの中で直接使用できます。
-- New implementation in SQL Server 2005 DECLARE @nTop int SET @nTop = 5 SELECT TOP (@nTop) * FROM Orders ORDER BY Freight DESC -- The N can even be the result -- of another function SELECT TOP( SELECT COUNT(*) FROM SHIPPERS) * FROM ORDERS
また、可変のTOP Nは、UPDATE、INSERT、DELETEの各ステートメントでも使用できます。
DECLARE @nTop int SET @nTop = 100 UPDATE TOP (@nTOP) Orders SET Freight = Freight * 1.1
ヒント11：SQL Server 2005でのPIVOT
私は、『CoDe Magazine』誌の2005年3月／4月号で、T-SQL 2000でのデータベース開発に役立つヒント集を、「The Baker's Dozen」シリーズの記事として執筆しました。その中で取り上げた例の一つに、口座年齢表の結果セットを作成するというものがあり、CASEステートメントを使用して、日付範囲に基づく日付区分列に延滞請求書の行を配置するという方法を用いました。その方法を簡単に言えば、一定の条件（日付範囲）に基づいて、行を列に変換した、ということになります。そのような処理を「ピボット（pivot）」と呼びます。CASEステートメントを使用する方法は現在でも有効ですが、SQL Server 2005には、PIVOT機能が新たに搭載されており、簡単に処理できるようになりました。
リスト1は新しいPIVOT機能の使用例です。この例では、各請求書間の経過日数と、何日付けかという日付を計算したうえで、PIVOTステートメントを使用して、対応する区分に請求額を配置します。
-- First, create some test data. DECLARE @tInv TABLE (CustomerID char(15), InvoiceNo Char(20), InvoiceDate DateTime, InvoiceAmount decimal(14,2), ReceivedAmount decimal(14,2)) INSERT INTO @tInv VALUES ('Cust 1','ABC','09-01-2005', 1000, 0) INSERT INTO @tInv VALUES ('Cust 1', 'DEF','10-01-2005', 2000, 100) INSERT INTO @tInv VALUES ('Cust 1', 'GHI','11-01-2005', 3000, 3000) INSERT INTO @tInv VALUES ('Cust 1', 'JKL','12-01-2005', 4000, 175) INSERT INTO @tInv VALUES ('Cust 1', 'MNO','12-18-2005', 4000, 175) INSERT INTO @tInv VALUES ('Cust 2', 'PQR','05-01-2005', 500, 250) INSERT INTO @tInv VALUES ('Cust 2', 'STU','08-01-2005', 12000, 0) INSERT INTO @tInv VALUES ('Cust 2', 'WYX','10-01-2005', 7000, 70) INSERT INTO @tInv VALUES ('Cust 2', 'YYZ','12-01-2005', 3200, 1750) -- Second, every aging report has an "as of" date. DECLARE @dAgingDate DATETIME SET @dAgingDate = '12-15-2005' -- Third, create a table of aging brackets and their ranges. -- This demonstrates how you can have configurable date ranges -- (i.e. 1-45 days, 46-90, etc.). DECLARE @tAgingBrackets TABLE ( StartDay int, EndDay int, BracketNum int, BracketLabel char(20)) INSERT INTO @tAgingBrackets VALUES (0, 30,1, '1-30 Days') INSERT INTO @tAgingBrackets VALUES (31, 60,2, '31-60 Days') INSERT INTO @tAgingBrackets VALUES (61, 90,3, '61-90 Days') INSERT INTO @tAgingBrackets VALUES (91, 120,4, '91-120 Days') INSERT INTO @tAgingBrackets VALUES (121,99999,5, '> 120 Days') -- Note: The BracketNum column is especially critical. -- When we match up each amount due with the date range, -- we'll place the amount due into that bracket. -- Fourth, create a table variable to hold the result set. DECLARE @tAgingDetails TABLE (CustomerID char(15), InvoiceNo char(20), InvoiceDate DateTime, Bracket1 decimal(14,2), Bracket2 decimal(14,2), Bracket3 decimal(14,2), Bracket4 decimal(14,2), Bracket5 decimal(14,2)) -- Fifth, run the query. -- In the WHERE clause, use DateParts to determine the # of days -- between the invoice date and the "as of date", grab the -- corresponding bracket number…and at the end, PIVOT on -- the sum of AmountOwed for the BracketNumber being in -- one of the five brackets. INSERT INTO @tAgingDetails SELECT * FROM (SELECT CustomerID,InvoiceNo, Invoicedate, InvoiceAmount - ReceivedAmount AS AmountOwed, BR.BracketNum FROM @tInvoices TI, @tAgingBrackets BR WHERE InvoiceAmount - ReceivedAmount <> 0 AND DATEDIFF(dd,Invoicedate,@dAgingDate) BETWEEN BR.StartDay AND BR.EndDay ) as Temp PIVOT ( SUM(AmountOwed) FOR BracketNumber In ( [1],[2],[3],[4],[5])) As X SELECT * FROM @tAgingBrackets SELECT * FROM @tAgingDetails ORDER BY CustomerID, InvoiceNo
ヒント12：SQL Server 2005の再帰クエリと共通テーブル式
SQL Server 2005で目を引く新機能の1つが、再帰クエリを作成できる機能です。再帰クエリとは、その名が示すとおり、そのクエリ自身の結果に対してクエリを行うことで処理全体を構成するというものです。例えば、階層型のデータに対するクエリを行って、可変個の親子関係を抽出することができます。
再帰クエリと共通テーブル式の簡単な例を紹介します。リスト2は、階層を上と下にクエリする2つの簡単な例です。処理は2つの部分で構成されます。メインとなる「アンカー」クエリでは、最初の結果セットを共通テーブル式に取り込みます。共通テーブル式はさまざまな点で派生テーブルに似ています。2番目の部分では、共通テーブル式を再帰的にクエリします。そして、クエリの作成方法に応じて、親または子のいずれかを取得します。SQL Server 2005の既定では、100レベルまでの再帰が可能ですが、この値は設定により変更できます。
-- Let's create some hierarchical data. -- A hierarchy of music information -- music genres, instruments, and musicians. -- This could be a hierarchy of company product lines, -- or a Bill of Material structure, or -- even a company's organizational database. -- Each row contains a PK and a reference to it's parent. DECLARE @tMusicData TABLE (MainPK int, ParentPK int, Name char(50)) INSERT INTO @tMusicData VALUES (1,NULL,'Musicians') INSERT INTO @tMusicData VALUES (2,1,'Jazz') INSERT INTO @tMusicData VALUES (3,1, 'Rock') INSERT INTO @tMusicData VALUES (4,1, 'Classical') INSERT INTO @tMusicData VALUES (5,2,'Saxophone') INSERT INTO @tMusicData VALUES (6,2,'Trumpet') INSERT INTO @tMusicData VALUES (7,3,'Guitar') INSERT INTO @tMusicData VALUES (8,4,'Piano') INSERT INTO @tMusicData VALUES (9, 5,'Charlie Parker') INSERT INTO @tMusicData VALUES (10,5,'John Coltrane') INSERT INTO @tMusicData VALUES (11, 6,'Miles Davis') INSERT INTO @tMusicData VALUES (12, 7,'Eddie Van Halen') INSERT INTO @tMusicData VALUES (13, 6,'Franz Liszt') DECLARE @cSearch char(50) SET @cSearch = 'Charlie Parker' -- You want to query on a single row, and get every parent -- to which it belongs. -- MusicTree is the CTE (similar to a derived table). WITH MusicTree (ResultName, PKValue) -- First, the main, or "anchor" query AS (SELECT Name, ParentPK FROM @tMusicData WHERE Name = @cSearch -- This pulls out Charlie UNION ALL -- Second, the recursive query. -- Note that it queries from the CTE, MusicTree, for -- all the parents. SELECT Name, parentPK FROM @tMusicData INNER JOIN MusicTree ON PKValue = MainPK ) SELECT * FROM MusicTree -- Results: -- Charlie Parker -- Saxophone -- Jazz -- Musicians -- Let's try again, but this time, query for all the children. SET @cSearch = 'Saxophone' WITH MusicTree (ResultName, PKValue) -- This time, we reverse the searches on MainPK and ParentPK. AS (SELECT Name, MainPK FROM @tMusicData WHERE Name = @cSearch UNION ALL SELECT Name, MainPK FROM @tMusicData INNER JOIN MusicTree ON PKValue = ParentPK) -- Results: -- Saxophone -- Charlie Parker -- John Coltrane
ヒント13：SQL Server 2005でのテーブル値ユーザー定義関数の結果の適用
正直言って、この機能は、T-SQL 2005の新機能の中でも特に気に入っています。APPLY（適用）という名が示すとおり、この機能は、テーブル値ユーザー定義関数の結果を、一時テーブルを介さずに、SQLのSELECTステートメントに直接適用できるというものです。
T-SQLは、プログラムのモジュール性のレベルという点では、C#やVisual Basicでの開発にはかないませんが、新たに導入されたこのAPPLY演算子によって、再利用可能なテーブル値ユーザー定義関数をさまざまなストアドプロシージャから呼び出す場合の連係がしやすくなります。
次のテーブル値ユーザー定義関数で考えてみましょう。特定の顧客に対応する注文について、注文数に基づくTOP Nをテーブル変数で返す関数です。
CREATE FUNCTION [dbo].[GetTopNOrders] (@CustomerID AS varchar(10), @nTOP AS INT) RETURNS TABLE AS RETURN SELECT TOP(@N) OH.OrderID, CustomerID, OrderDate, (UnitPrice * Quantity) as Orderamount FROM Orders OH JOIN [dbo].[Order Details] OD ON OH.OrderID = OD.OrderID WHERE CustomerID = @CustomerID ORDER BY ORDERAMOUNT DESC GO
ここで、顧客データベース全体に対するクエリを行い、各顧客について上記のユーザー定義関数を実行して、TOP Nの注文を取得したいとしましょう。SQL Server 2005では、顧客データのクエリに対してユーザー定義関数の結果をAPPLYで直接適用できます。
DECLARE @nTopCount int SET @nTopCount = 5 SELECT TOPOrd.CustomerID, TOPOrd.OrderID, TOPOrd.OrderDate, TOPOrd.OrderAmount FROM Customers CROSS APPLY DBO.TopNOrders(Customers.CustomerID, @nTopCount) AS TOPOrd ORDER BY TOPOrd.CustomerID,TOPOrd.OrderAmount DESC
次回予告
冒頭でも述べたように、今回の記事は、データ処理機能について取り上げる全2回の記事の前編です。次回の後編では、.NETのジェネリックや、ASP.NETの新しいObjectDataSource機能、T-SQL 2005のその他の機能について解説します。そちらもお楽しみに。
最後に
私のWebサイトに、すべてのソースコードが掲載してあります。最新情報や補足情報については、私のブログでご確認ください。
文章、書類、コードなどを提出したときに、その後になって、もっと良いアイデアが浮かんだという経験はありませんか。実のところ、私は後知恵の帝王です。幸い、そうした場合の対応にはブログがうってつけです。「The Baker's Dozen」に関連する補足情報や追加のヒントについては、私のブログをぜひチェックしてください。他にも、お得な情報が載っているかもしれませんよ。
