Redgate Hub (日本語)

私は、SQL saturdayやその他のイベントでT-SQLウィンドウ関数についてデータベースの専門家に伝えるために、米国を旅 私は少数の人々がそれらを使用しているこれらの機能および更に少数について聞いたか驚かせられる。 各プレゼンテーションの最後に、一つ以上の人々は、彼らが非常に多くのクエリのために有益であった可能性があるため、彼らは年前にこれらの機能につ,

これらの関数は、他のより伝統的な方法よりもパフォーマンスを向上させるために促進されました。 私は部分的に同意します。 多くのクエリを書きやすくし、パフォーマンスを向上させることもあります。

Windows OSとは関係ありません

これらの関数はANSI SQL2003標準の一部であり、SQL Serverの場合はクエリの書き込みに使用されるT-SQL関数です。 WindowsオペレーティングシステムやAPI呼び出しとは何の関係もありません。 Oracleなどの他のデータベースシステムも、これらを独自のSQL言語の一部として含めています。,

ウィンドウ関数（ウィンドウ関数またはウィンドウ関数）は、行のセットに対して計算を実行します。 私は、返されている行を”窓から見る”ことと、計算を実行する最後の機会を持つことを考えるのが好きです。 ウィンドウが定義されるの条項が付されているかどうかを判定します行が仕切られた小さなセットされます。 実際、ウィンドウ関数を使用する場合は、常にOVER句を使用します。 OVER句は、sequenceオブジェクトに必要な構文のNEXT VALUEの一部でもありますが、それ以外の場合はウィンドウ関数で使用されます。,

OVER句にはPARTITION BYオプションを含めることができます。 この列となります。 これはGROUP BYと同じだと思うかもしれませんが、そうではありません。 グループ化すると、一意のグループごとに一つの行が返さ PARTITION BYを使用すると、すべての詳細行が計算とともに返されます。 ホームにウィンドウがあり、ウィンドウに分割されている場合、各ウィンドウはウィンドウになります。 を考える際の窓関数は、全体の結果は、パーティションが、使用時のパーティションでは、各仕切りもできると考えられます。, PARTITION BYは、すべてのウィンドウ機能でサポートされ、オプションです。

OVER句にはORDER BYオプションも含めることができます。 これは、クエリのORDER BY句とは独立しています。 いくつかの関数はORDER BYを必要とし、他の関数ではサポートされていません。 計算を適用するときに行の順序が重要な場合は、ORDER BYが必要です。

ウィンドウ関数は、クエリのSELECT句およびORDER BY句でのみ使用できます。 結合、フィルタリング、またはグループ化の後に適用されます。,

ランキング関数

最も一般的に使用されるウィンドウ関数であるランキング関数は、2005年から利用できるようになりました。 これは、MICROSOFTがROW_NUMBER、RANK、DENSE_RANK、およびNTILEを導入したときです。 ROW_NUMBERは、パーティションまたは結果セット全体に一意の行番号を追加するために非常に頻繁に使用されます。 行番号、または他のランク付け関数のいずれかを追加することは、通常は目標ではありませんが、ソリューションへの道のりに沿ったステップです。

このグループ内のROW_NUMBERおよびその他の関数を使用する場合は、OVER句でORDER BYが必要です。, これにより、数値が適用される順序がデータベースエンジンに指示されます。 ORDER BYで使用される列または式の値が一意でない場合、RANKとDENSE_RANKはタイを処理しますが、ROW_NUMBERはタイを気にしません。 NTILEは、ORDER BYに基づいて行をバケットに分割するために使用されます。

ROW_NUMBERの利点の一つは、一意でない行を一意の行に変換できることです。 これは、たとえば、重複する行を排除するために使用できます。

これがどのように機能するかを示すには、重複する行を含む一時テーブルから始めます。, 最初のステップは、テーブルを作成して移入することです。

ROW_NUMBERを追加し、各列によってパーティショニングすると、一意の行セットごとに行番号が再起動されます。 一意の行を識別するには、行番号が等しい行を見つけます。,

ここで、行番号が大きい行を削除するだけです。, 問題は、WHERE句にウィンドウ関数を追加できないことです。,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., その後、CTEから直接行を削除できます。

成功！ 余分な行は削除され、一意の行セットが残ります。

ROW_NUMBER、RANK、およびDENSE_RANKの違いを確認するには、次のクエリを実行します。

各OVER句のORDER BYはOrderDateであり、一意ではありません。 この顧客は2013年10月24日に二つの注文を行った。 ROW_NUMBERは数字の割り当てを続け、重複した日付があっても何も違うことはしませんでした。, ランクは両方の行に6を割り当て、次にROW_NUMBERに追いつき、次の行に8を割り当てました。 DENSE_RANKはまた、二つの行に6を割り当てましたが、次の行に7を割り当てました。

二つの違いを説明し、位置としてROW_NUMBERを考えます。 ランクは位置と論理の両方です。 これら二つの行は論理的に同じランク付けされますが、次の行はセット内の位置によってランク付けされます。 DENSE_RANKはそれらを論理的にランク付けします。 注文2013-11-04は7番目のユニークな日付です。

このグループの最後の関数はNTILEと呼ばれます。 行番号またはランクの代わりに行にバケット番号を割り当てます。, ここに例があります：

NTILEにはパラメーターがあります（この場合は4）。 ORDER BYは売上の合計に適用されます。 最も低い25％の行には1が割り当てられ、最も高い25％の行には4が割り当てられます。 最後に、NTILEの結果に1000を掛けてボーナス額を考え出します。 14は4で均等に分割することはできないので、余分な行は最初の二つのバケットのそれぞれに入ります。

ウィンドウ集計

ウィンドウ集計は、SQL Server2005でも導入されました。, これらは、いくつかのトリッキーなクエリを簡単に書くことができますが、多く これにより、お気に入りの集計関数を非集計クエリに追加することができます。 たとえば、すべての顧客注文を各顧客の小計とともに表示したいとします。, OVER句を使用して合計を追加することで、これを非常に簡単に達成できます。

パーティションBYを追加することにより、各顧客の小計が計算されます。 任意の集計関数を使用でき、OVER句のORDER BYはサポートされていません。

2012年のウィンドウ集計の機能強化

2012年からは、ウィンドウ集計のOVER句にORDER BYを追加して、実行中の合計や移動平均などを生成できます。 同時に、Microsoftはフレーミングの概念を導入しました。 パーティションを追加することは、ウィンドウをペインに分割するよ, 追加枠組みをはじめ、ステンドグラスウインドウです。 各行には、式が適用される個々のウィンドウがあります。

この機能拡張では、フレーム構文を追加しなくても、実行中の合計を作成できます。, Here is an example that returns a running total by customer:

フレームが指定されていない場合に使用されるデフォルトのフレームは、無制限の前行と現在行の間の範囲です。 残念ながら、代わりにこのフレームを指定した場合と同様に、これはうまく機能しません：無制限の先行行と現在行の間の行。 違いは単語ROWSです。 RANGEはこの時点で部分的にしか実装されておらず、ROWSは位置ですが、一定期間を処理するためのものです。, フレームは、UNBOUNDED PRECEDINGとCURRENT ROWの間の行であり、ウィンドウはパーティションの最初の行と現在の行までのすべての行で構成されていることを意味します。 各計算は、異なる行のセットに対して行われます。 たとえば、行4の計算を実行するときは、行1から4が使用されます。

行5の計算を実行すると、行は1から5になります。 ある行から次の行に移動するにつれて、ウィンドウが大きくなります。,

構文ROWS BETWEEN N先行と現在の行または現在の行とN次の間の行を使用することもできます。 これは、例えば、三ヶ月移動平均を計算するために有用である可能性があります。 次の図は、2つの前の行と現在の行の間の行を表しています。

5が現在の行の場合、ウィンドウは移動します。,

フレーミングオプションを書くときに知っておく必要がある用語のリストは次のとおりです。

この構文は少し混乱しますが、SQLプロンプトを使用すると、フレーミングオプションを書くのが簡単になります！

オフセット関数

SQL Server2012のリリースには、自己結合を行わずに他の行の値を含めることができる四つの関数も含まれています。 Microsoft呼これら分析機能、常に参照することとしてオフセット機能が発表。, 二つの関数を使用すると、現在の行の前（LAG）または後（LEAD）の行から列または式をプルできます。 他の二つの関数を使用すると、パーティションの最初の行(FIRST_VALUE)またはパーティションの最後の行(LAST_VALUE)から値を返すことができます。 FIRST_VALUEとLAST_VALUEもフレーミングが必要なので、これらの関数を使用するときはフレームを含めるようにしてください。 これらの関数はすべて、OVER句のORDER BYオプションを必要とします。 データベースエンジンは、返す値を含む行を把握するために行の順序を知っている必要があるため、これは理にかなっています。,

好きなバンドを持っている人もいれば、好きな映画を持っている人もいます。 私は好きな機能を持っています–LAG。 それは使いやすいです（フレームなし！）と素晴らしいを実行します。, Here is an example:

ラグとリードには、返す列または式の引数が必要です。 デフォルトでは、LAGは前の行の値を返し、LEADは次の行の値を返します。 これを変更するには、OFFSETパラメーターの値(既定では1)を指定します。 パーティションの最初の行はNULLを返します。 Nullをオーバーライドする場合は、デフォルト値を指定できます。, Here is a similar query that goes back two rows and has a default value:

FIRST_VALUEとLAST_VALUEは、パーティションの最初の行または最後の行から値を見つけるために使用できます。 パフォーマンス上の理由だけでなく、デフォルトのフレームはLAST_VALUEで期待どおりに機能しないため、フレームを指定してください。 デフォルトのフレームRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは、現在の行にのみ上がります。 パーティションの最後の行は含まれていません。, 期待される結果を得るには、LAST_VALUEを使用するときに、CURRENT ROWとUNBOUNDED FOLLOWINGの間の行を指定してください。, FIRST_VALUEを使用した例は次のとおりです。

統計関数

Microsoftは、PERCENT_RANK、CUME_DIST、PERCENTILE_DISC、PERCENTILE_CONTの四つの関数を、すべての分析関数を呼び出すオフセット関数とともにグループ化します。 私はこれらをオフセット関数と区別したいので、これらを統計的に呼び出します。

PERCENT_RANKおよびCUME_DISTは、パーティション上の各行のランク付けを提供します。 彼らは少し異なります。 PERCENT_RANKは、現在の行よりも低いランク付けされた行の割合を返します。, “私のスコアはスコアの90％よりも高いです。”CUME_DIST、または累積分布は、正確なランクを返します。 “私のスコアはスコアの90％です。”ここでは、毎月のセントルイスの平均高温を使用した例です。 ランクは華氏温度によって決定されたことに注意してください。

ランクは相対値ではなく、行の位置によって決定されます。 月と月は同じ平均高温を持っているので、彼らは同じランク付けされたことに注意してくださ

あなたはPERCENT_RANKとCUME_DISTを計算する方法が不思議に思うかもしれません。, 以下の式は次のとおりです。

percentile_discとpercentile_contは逆の方法で機能します。 与えられ％位の値です。, それらは、PERCENTILE_DISCがセットに存在する値を返すのに対し、PERCENTILE_CONTはセット内の値のどれもそのランクに正確に収まらない場合に正確な値を計算するという点で異なります。 PERCENTILE_CONTを使用して、0.5をパーセントランクとして指定することにより、中央値を計算できます。 例えば、温度の上位50セントルイス?

PERCENTILE_CONT関数は、中間に最も近い二つの値67と69の平均を取り、それらを平均化します。 PERCENTILE_DISCは、正確な値67を返します。, また、これら二つの関数には、OVER句内ではなくORDER BYを含むGROUP内の他の関数には見られない余分な句があることにも注意してください。

概要

この記事は、T-SQLウィンドウ関数の非常に簡単な概要です。 SQL Server2005では、ランキング関数とウィンドウ集計の二つのタイプの関数がリリースされました。 2012年には、フレーミング機能と分析機能を備えたウィンドウ集計が強化されました。 私は分析関数を二つのグループ、オフセット関数と統計関数に分けるのが好きです。, ウィンドウ関数は多くのクエリを書きやすくし、それが主な利点だと思います。 場合によっては、クエリのパフォーマンスも向上しますが、それは別の日の議論です。

私はこの記事がこれらの素晴らしい機能についての詳細を学ぶためにあなたに影響を与えた願ってい