ON, WHERE, HAVING などの SQL の古典的なフィルターは、述語に基づいています。 TOPやOFFSET-FETCHは異なる概念に基づくフィルタです：順序を示し、その順序に基づいて何行をフィルタリングするかということです。 多くのフィルタリングタスクは、順序と必要な行数に基づいて定義されています。 タスクについて考える方法に近い方法で要求を言い表すことができる T-SQL の言語サポートがあることは確かに良いことです。
この章では、フィルターの論理設計の側面から説明します。 そして、ページングシナリオを使用して、その最適化を実証します。 この章では、TOP with modificationステートメントの使用もカバーしています。 最後に、この章ではTOPとOFFSET-FETCHを使用して、グループごとのトップNや中央値などの実用的な問題を解決することを示します。
TOPとOFFSET-FETCHフィルタ
直感的な方法でクエリのフィルタリング要件を実装するには、TOPとOFFSET-FETCHフィルタを使用します。 TOPフィルターはT-SQLの独自機能であるのに対し、OFFSET-FETCHフィルターは標準機能です。 T-SQL は Microsoft SQL Server 2012 で OFFSET-FETCH のサポートを開始しました。 SQL Server 2014の時点では、T-SQLのOFFSET-FETCHの実装には、まだいくつかの標準的な要素が欠けています。 現在の実装では、それぞれのフィルターに、他ではサポートされていない機能があります。
TOP の論理設計の側面から説明し、次に OFFSET-FETCH の側面について説明します。 その人気はおそらく、その設計が多くのフィルタリング要件が表現される方法と非常によく一致しているという事実に起因しています-例えば、”直近の3つの注文を返す”。 この要求では、フィルタの順序はorderdate、降順に基づいており、この順序に基づいてフィルタしたい行の数は3です。
フィルタしたい行の数を示すBIGINTとして入力された値でSELECTリスト内のTOPオプションを指定するのです。 順序指定は古典的なORDER BY句で行います。 たとえば、次のクエリを使用して、最新の 3 つの注文を取得します。
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
このクエリから次の出力が得られました。 そのためには、0 から 100 の範囲の値 (FLOAT と入力) を指定し、PERCENT キーワードを追加します。 例えば、次のクエリでは、行の 1 パーセントをフィルタリングするよう要求します。
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server は、入力したパーセントに基づいて計算した行の数を切り上げます。 たとえば、Orders テーブルの 830 行に 1 パーセントを適用した結果は 8.3 です。 この数字を切り上げると、9になります。 以下は、このクエリの出力です。
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
入力パーセントを行数に変換するために、SQL Server はまずクエリ結果の行数を計算しなければならず、これは通常余分な作業を必要とすることに注意しましょう。 たとえば、次のクエリを考えてみましょう。
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
このクエリから次の出力を得ました。
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
どの 3 行を返すかの選択は、非決定的です。 つまり、基礎となるデータを変更せずに再度クエリを実行した場合、理論的には異なる3行のセットを得ることができます。 実際には、行の選択は、最適化の選択、ストレージエンジンの選択、データレイアウトなどの物理的な条件に依存します。 実際にクエリを複数回実行しても、物理的条件が変わらない限り、同じ結果が得られる可能性があります。 しかし、リレーショナルモデルから「物理データの独立性」の原則を理解し、論理レベルでは再現性のある結果が保証されないことを忘れないようにすることが重要です。 順序指定がない場合、順序は任意であり、結果として非決定的な行の選択になると考えるべきです。
順序指定を行った場合でも、クエリが決定論的であるというわけではありません。 たとえば、以前の TOP クエリでは、順序指定として orderdate, DESC を使用しました。 orderdate列は一意ではないので、同じorderdateを持つ行の選択は非決定論的である。 では、決定性を保証しなければならない場合、どうすればいいのでしょうか？ WITH TIESを使うか、一意な順序付けをするかです。
WITH TIESオプションは、結果に同値を含ませるようにします。
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
このクエリから得た結果は次のとおりです。
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server は、最新の注文日を持つ 3 行をフィルターし、さらに最後の行と同じ注文日を持つ他のすべての行を含めます。 その結果、指定した数よりも多くの行を取得することができます。 このクエリでは、3 行をフィルタリングするように指定しましたが、最終的には 4 行を取得することになりました。 ここで興味深いのは、行の選択は決定論的ですが、同じ注文日を持つ行の間の表示順序は非決定論的であるということです。
決定論的な結果を保証する 2 つ目の方法は、タイブレーカーを追加して順序指定をユニークにすることです。 例えば、この例ではタイブレーカーとして orderid, DESC を追加することができます。 これは、注文日の値が同値の場合、注文IDの値が大きい行が小さい行よりも優先されることを意味します。
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
このクエリは次の出力を生成します。
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
一意な順序付けを使用すると、行の選択と表示順序の両方が決定的になります。 結果セットと行の表示順序は、基礎となるデータが変更されない限り、再現可能であることが保証されます。
ある数の行をフィルターする必要があるが、本当に順序を気にしない場合、次のように ORDER BY (SELECT NULL) を指定することは良い考えです：
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
この方法で、任意の順序の選択が意図的であると全員に知らせることにより、混乱や疑いを回避することができます。
第1章「論理的なクエリ処理」でTOPとOFFSET-FETCHフィルタについて説明したことの覚え書きとして、表示順は外側のクエリがORDER BY句を持つ場合のみ保証されます。 例えば、次のようなクエリ表示では、順序は保証されません。
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
表示の順序を保証するには、次のように外部クエリでORDER BY句を指定する必要があります。 つまり、完全な結果セットが長すぎて1つの画面またはWebページに収まらない場合に、要求に応じてユーザーに一度に1つのチャンクを返すことです。
OFFSET-FETCH フィルターは ORDER BY 句の存在を必要とし、そのすぐ後に指定されます。 まずOFFSET句でスキップする行数を示し、次にFETCH句でフィルタリングする行数を示します。 たとえば、指定された順序に基づき、次のクエリは最初の50行をスキップし、次の25行をフィルタリングします：
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
言い換えると、このクエリは51から75行をフィルタリングします。 ページング用語では、25行のページサイズを仮定すると、このクエリは3ページ目を返します。
自然な宣言的言語を可能にするために、意味は同じですが、必要に応じてNEXTの代わりにFIRSTキーワードを使用することができます。 行をスキップしないのであれば、FIRSTを使用する方がより直感的でしょう。 行をスキップしない場合でも、T-SQLでは、解析のあいまいさを避けるために、OFFSET句の指定（0 ROWSの指定）が必須となっています。 同様に、キーワードROWSの複数形を使用する代わりに、OFFSET句とFETCH句の両方で単数形のROWを使用することができます。 キーワード ONLY の目的が何であるかを知りたければ、それは同値を含めないということです。 標準SQLでは、WITH TIESという代替手段を定義していますが、T-SQLはまだこれをサポートしていません。 同様に、標準SQLではPERCENTオプションが定義されていますが、T-SQLはまだこれをサポートしていません。 これら 2 つの欠けているオプションは TOP フィルターで使用できます。
言及したように、OFFSET-FETCH フィルターは ORDER BY 句を必要とします。 ORDER BY句なしでTOPのような任意の順序を使用したい場合は、次のようにORDER BY (SELECT NULL) を使ったトリックを使用できます：
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
FETCH句は省略可能です。 もし、ある行数をスキップしたいが、返す行数は制限しないのであれば、単にFETCH句を示さないようにすればよい。 つまり、OFFSET-FETCHでも、最も外側のクエリにORDER BY句がある場合にのみ、表示順序が保証されます。