この記事は T-SQL のバグ、落とし穴、ベストプラクティスについてのシリーズの 4 回目です。 前回は、決定論、サブクエリ、および結合を取り上げました。 今月の記事の焦点は、ウィンドウ関数に関するバグ、落とし穴、ベストプラクティスです。 Erland Sommarskog、Aaron Bertrand、Alejandro Mesa、Umachandar Jayachandran（UC）、Fabiano Neves Amorim、Milos Radivojevic、Simon Sabin、Adam Machanic、Thomas Grohser、Chan Ming Man および Paul White、皆さんのアイデアを提供いただきありがとうございます！
私の例で TSQLV5 というサンプル データベースを使ってみましょう。 このデータベースを作成し、入力するスクリプトをここで、その ER 図をここで見つけることができます。 1つの落とし穴は、暗黙のRANGEオプションでウィンドウフレームを取得する実行合計の計算と関係があります。 もう 1 つの落とし穴は、FIRST_VALUE および LAST_VALUE 関数の暗黙のフレーム定義に関連し、多少関連しますが、より深刻な結果をもたらします。
Window frame with implicit RANGE option
最初の落とし穴は、集約ウィンドウ関数を使用する実行合計の計算があります。 rows unbounded precedingのように。 この落とし穴を示すために、貸方（正の値）と借方（負の値）を持つ 200 万の銀行口座取引を保持する Transactions というテーブルを使用することにします。 次のコードを実行して Transactions テーブルを作成し、サンプル データを入力します。
SET NOCOUNT ON; USE TSQLV5; -- http://tsql.solidq.com/SampleDatabases/TSQLV5.zip DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) -- creates POC index ); DECLARE @num_partitions AS INT = 100, @rows_per_partition AS INT = 20000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
この落とし穴には、論理的バグとパフォーマンスのペナルティがあります。 性能ペナルティは、ウィンドウ関数が行モード処理演算子で最適化される場合にのみ関連します。 SQL Server 2016 では、バッチモードの Window Aggregate 演算子が導入され、落とし穴のパフォーマンス ペナルティの部分が取り除かれましたが、SQL Server 2019 より前のこの演算子は、データ上に列ストア インデックスが存在する場合のみ使用されます。 SQL Server 2019 では、行ストア サポートにバッチ モードが導入されたので、データに列ストア インデックスが存在していなくても、バッチ モード処理ができるようになりました。 行モード処理によるパフォーマンスの低下を示すために、本記事のコード サンプルを SQL Server 2019 以降、または Azure SQL Database で実行する場合は、次のコードを使用してデータベース互換性レベルを 140 に設定し、行ストアでのバッチ モードをまだ有効にしないようにします。
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
セッションの時間と I/O 統計をオンにするには、次のコードを使用します。
SET STATISTICS TIME, IO ON;
SSMS で 200 万行を表示するために待つことを避けるために、このセクションのコード サンプルを、実行後に結果を破棄するオプションが有効な状態で実行すると良いでしょう（［クエリー オプション］［結果］＜グリッド］に進み、［実行後に結果を破棄］をチェックしてください）。
落とし穴に入る前に、次のクエリ (クエリ 1 と呼ぶ) を考えてみましょう。これは、明示的なフレーム指定でウィンドウ集約関数を使用して実行合計を適用し、各トランザクション後の銀行口座残高を計算します。
行モード処理によるこのクエリの計画は、図 1 で示されています。
Figure 1: Plan for Query 1, row-mode processing
この計画は、テーブルのクラスタ化インデックスからあらかじめ並べられたデータを引き出します。 次に、セグメントとシーケンスプロジェクト演算子を使用して行番号を計算し、どの行が現在の行のフレームに属しているかを把握します。 次に、Segment、Window Spool、Stream Aggregate の各オペレータを使用して、ウィンドウ集約関数を計算します。 Window Spool 演算子は、集約が必要なフレーム行をスプールするために使用されます。 特別な最適化がなければ、計画は、スプールに該当する全てのフレーム行を行ごとに書き込み、そして、それらを集約しなければならなかったことでしょう。 これは2次関数、つまり、N2の複雑さをもたらすでしょう。 この場合、SQL Serverは単純に前の行の実行時合計を取り、現在の行の値を追加して現在の行の実行時合計を計算し、線形スケーリングになります。 このファストトラックモードでは、計画は入力行ごとにスプールに2行だけ書き込みます。1つは集約で、もう1つは詳細です。 特にウィンドウ機能のために設計された高速なインメモリ・スプールとして、または、基本的にtempdbの一時テーブルである低速なオンディスク・スプールとしてです。 もし、基礎となる行ごとにスプールに書き込む必要がある行の数が1万を超える可能性がある場合、または、SQL Serverがその数を予測できない場合、より遅いディスク上のスプールを使用することになります。 このクエリプランでは、基礎となる行ごとにちょうど2行がスプールに書き込まれるので、SQL Serverはインメモリ・スプールを使用します。 残念ながら、どのようなスプールを使用しているかを計画から判断する方法はありません。 これを把握する方法は2つあります。 1つは、window_spool_ondisk_warningという拡張イベントを使用することです。 もう1つの方法は、STATISTICS IOを有効にし、Worktableというテーブルについて報告された論理読み込みの数を確認することです。 0より大きい数値は、ディスク上のスプールを取得したことを意味します。 ゼロは、インメモリ・スプールであることを意味します。 以下は、このクエリのI/O統計です：
見ての通り、インメモリ・スプールを使用することができました。 これは一般的に、ROWSウィンドウのフレーム単位で、最初の区切り文字としてUNBOUNDED PRECEDINGを使用した場合に起こります。
以下は、このクエリの時間統計です：
私のマシンでは、結果を破棄して、このクエリを完了するのに約4.5秒かかりました。 ROWSの代わりにRANGEオプションを使用し、同じ区切り文字を使用した場合、意味は微妙に異なるかもしれませんが、行モードでの性能には大きな違いがあります。 この意味の違いは、総順序でない場合、つまり、一意でないもので順序付けしている場合にのみ関係します。 ROWS UNBOUNDED PRECEDINGオプションは、現在の行で停止するため、同値の場合は計算が非決定的になります。 逆に、RANGE UNBOUNDED PRECEDINGオプションは、現在の行の前方を調べ、同値がある場合はそれを含めます。 これは、TOP WITH TIESオプションと同様のロジックを使用します。 総順序の場合、つまり、一意なものによる順序付けの場合、含めるべき同値はありません。したがって、ROWSとRANGEはこのような場合、論理的に等価となります。 問題は、RANGEを使用する場合、SQL Serverは常にディスク上のスプールを行モード処理で使用することです。
ROWS の代わりに RANGE オプションを使用した、クエリ 1 と同じ次のクエリ（クエリ 2 と呼ぶ）を考えてみましょう。
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
このクエリのプランは図 2 に示されます。
Figure 2: Plan for Query 2, row-mode processing
Query 2は総順序があるので論理的にはQuery 1と同じですが、RANGEを使っているのでディスク上のスプールにより最適化されています。 Query 2の計画では、Window SpoolはQuery 1の計画と同じように見え、推定コストも同じであることに注意してください。
Query 2の実行時間とI/O統計は以下のとおりです。 12044701. テーブル’Transactions’の論理読み込み。 6208.
Worktableに対する論理読み込み数が多いことに注目し、ディスク上のスプールを取得したことを表しています。 実行時間は、クエリ1の4倍以上です。
このような場合、本当にタイを含める必要がなければ、単にRANGEオプションを使用しないようにしようと考えるなら、それは良い考えです。 問題は、明示的なウィンドウ順序句を持つフレーム（集約、FIRST_VALUE、LAST_VALUE）をサポートするウィンドウ関数を使用しても、ウィンドウ フレーム単位とその関連範囲についての言及がない場合、デフォルトで RANGE UNBOUNDED PRECEDING が発生することです。 このデフォルトは標準SQLによって決められており、標準SQLは一般的にデフォルトとしてより決定論的なオプションを好むのでこれを選択しました。 次のクエリ（クエリ 3 と呼ぶ）は、この罠にはまった例です。
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
多くの場合、このように記述すると、デフォルトで ROWS UNBOUNDED PRECEDING が取得されていると思ってしまい、実際には RANGE UNBOUNDED PRECEDING を取得しているとは気付かないことがあります。 この関数は総順序を使うので、ROWSと同じ結果が得られるので、結果からは問題があるとはわからないということです。 しかし、得られるパフォーマンスの数値はQuery 2と同じです。 この問題を避けるためのベスト プラクティスは、フレームでウィンドウ関数を使用する場合、ウィンドウ フレームの単位とその範囲を明示し、一般に ROWS を使用することです。 RANGEは、順序が一意でなく、同値を含む必要がある場合にのみ使用してください。
ROWS と RANGE の間に概念の違いがある場合を示す次のクエリを考えてみましょう。
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS sumrows, SUM(val) OVER( ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS sumrange FROM Sales.OrderValues ORDER BY orderdate;
このクエリは次の出力を生成します。
orderdate orderid val sumrows sumrange ---------- -------- -------- -------- --------- 2017-07-04 10248 440.00 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2303.40 2017-07-08 10250 1552.60 3856.00 4510.06 2017-07-08 10251 654.06 4510.06 4510.06 2017-07-09 10252 3597.90 8107.96 8107.96 ...
2017年7月8日のように、同じ注文日が複数回現れる行の結果の違いを観察してください。 ROWS オプションは同値を含まないため非決定的であり、RANGE オプションは同値を含むため常に決定論的であることに注意してください。
実際には、ユニークでない何かで注文するケースがあり、計算を決定論的にするために同値を含める必要があるかどうかは疑問ですが。 おそらく実際には、2 つのうちの 1 つを実行する方がはるかに一般的でしょう。 1つは、ウィンドウの順序に何かを追加して一意にすることで同値を解消し、この方法で決定論的な計算を行います。
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
この問い合わせは次のような出力を生成します。
orderdate orderid val runningsum ---------- -------- --------- ----------- 2017-07-04 10248 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2017-07-08 10250 1552.60 3856.00 2017-07-08 10251 654.06 4510.06 2017-07-09 10252 3597.90 8107.96 ...
別のオプションは、予備的なグループ化、私たちの場合は注文日によって、次のように適用されることです。
SELECT orderdate, SUM(val) AS daytotal, SUM(SUM(val)) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues GROUP BY orderdate ORDER BY orderdate;
このクエリは、各注文日が一度だけ現れる以下の出力を生成します：
orderdate daytotal runningsum ---------- --------- ----------- 2017-07-04 440.00 440.00 2017-07-05 1863.40 2303.40 2017-07-08 2206.66 4510.06 2017-07-09 3597.90 8107.96 ...
とにかく、ここでベストプラクティスを覚えていることを確認してください!
良いニュースは、SQL Server 2016 以降で実行していて、データ上に列ストア インデックスが存在する場合 (それが偽のフィルター付き列ストア インデックスであっても)、または SQL Server 2019 以降、あるいは Azure SQL Database で実行している場合、列ストア インデックスの存在に関係なく、前述の 3 つのクエリーはすべてバッチモードの Window Aggregate オペレーターで最適化できる、ということです。 この演算子では、行モード処理の非効率性の多くが解消されます。 この演算子はスプールを全く使用しないので、メモリ内スプールとディスク内スプールの問題は発生しません。 これは、ROWSとRANGEの両方で、メモリ内の行のウィンドウに対して複数の並列パスを適用できる、より洗練された処理を使用します。
バッチモードの最適化を使用するデモを行うには、データベースの互換性レベルが 150 以上に設定されていることを確認します。
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Query 1 を再び実行します。
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
このクエリのプランは図3のようになります。
Figure 3: Plan for Query 1, batch-mode processing
Here are the performance statistics that I got for this query:
Table ‘Transactions’ logical reads.このクエリーで得たパフォーマンスの統計を以下に記載します。 6208.
実行時間は1秒になりました！
再び明示的RANGEオプションでクエリ2を実行：
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
このクエリのプランは図4で示される通りです。
Figure 2: Query 2、バッチ モード処理のプラン
このクエリで得たパフォーマンス統計は次のとおりです。
テーブル ‘Transactions’ 論理読み取り数: 6208.
パフォーマンスはクエリ1と同じです。
Query 3を暗黙のRANGEオプションで再度実行します：
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
プランとパフォーマンスの数値はもちろんクエリ2と同じです。
終了したら、次のコードを実行してパフォーマンス統計をオフにします：
SET STATISTICS TIME, IO OFF;
また、SSMSで［実行後に結果を破棄］オプションをオフにしておくことを忘れないでください。
FIRST_VALUEおよびLAST_VALUEによる暗黙のフレーム
FIRST_VALUEおよびLAST_VALUE関数は、それぞれウィンドウ フレームの最初または最後の行から数式を返すオフセット ウィンドウ関数です。 この関数の厄介なところは、初めて使用するときに、フレームをサポートしていることに気づかず、むしろパーティション全体に適用されると考えてしまうことです。
注文情報と顧客の最初と最後の注文の値を返そうとする次のような試みを考えてみてください。
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
もし、これらの関数がウィンドウ パーティション全体で動作すると誤って考えているなら、それはこれらの関数を初めて使用する多くの人の考えですが、FIRST_VALUE が顧客の最初の注文の注文値を返し、LAST_VALUE が顧客の最後の注文の注文値を返ると自然に予想されるでしょう。 しかし、実際には、これらの関数はフレームをサポートしています。 注意点として、フレームをサポートする関数では、ウィンドウ順序節を指定してもウィンドウフレーム単位と関連するエクステントを指定しない場合、デフォルトでRANGE UNBOUNDED PRECEDINGが取得されます。 FIRST_VALUE関数を使用すると、期待通りの結果を得ることができますが、もし問い合わせが行モード演算子で最適化されると、ディスク上のスプールを使用することによるペナルティを支払うことになります。 LAST_VALUE関数を使用すると、さらに悪いことが起こります。 ディスク上のスプールのペナルティを支払うだけでなく、パーティション内の最後の行から値を取得するのではなく、現在の行から値を取得します！
これが上記のクエリの出力です。
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 814.50 1 2018-10-03 10692 878.00 814.50 878.00 1 2018-10-13 10702 330.00 814.50 330.00 1 2019-01-15 10835 845.80 814.50 845.80 1 2019-03-16 10952 471.20 814.50 471.20 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 88.80 2 2018-08-08 10625 479.75 88.80 479.75 2 2018-11-28 10759 320.00 88.80 320.00 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 403.20 3 2018-04-15 10507 749.06 403.20 749.06 3 2018-05-13 10535 1940.85 403.20 1940.85 3 2018-06-19 10573 2082.00 403.20 2082.00 3 2018-09-22 10677 813.37 403.20 813.37 3 2018-09-25 10682 375.50 403.20 375.50 3 2019-01-28 10856 660.00 403.20 660.00 ...
こうした出力を初めて見た人は、SQL Server にバグがあると考えることがよくあります。 しかし、もちろん、そうではありません。これは単に標準SQLのデフォルトなのです。 クエリにバグがあるのです。 フレームがあることを認識した上で、フレーム指定を明示し、目的の行を捕捉する最小限のフレームを使用するようにしたいものです。 また、ROWSという単位を使うようにします。 つまり、パーティションの最初の行を取得するには、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWというフレームを使って、FIRST_VALUE関数を使用します。 パーティションの最後の行を得るには、LAST_VALUE関数をROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGのフレームで使用します。
バグを修正したクエリを以下に示します。
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
今度は正しい結果を得ました。 考えてみると、ほとんどの場合、パーティションの最初か最後の行から何かを取得するためにこれらを使用します。 例えば、現在の2行前の値が必要な場合、2 PRECEDINGで始まるフレームでFIRST_VALUEを使用する代わりに、以下のようにオフセットを2としてLAGを使用する方がずっと簡単ではないでしょうか。
SELECT custid, orderdate, orderid, val, LAG(val, 2) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
このクエリは次の出力を生成します：
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 NULL 1 2018-10-03 10692 878.00 NULL 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 NULL 2 2018-08-08 10625 479.75 NULL 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 NULL 3 2018-04-15 10507 749.06 NULL 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
明らかに、上記のLAG関数の使用と2 PRECEDINGから始まるフレームのFIRST_VALUEにはセマンティックな違いがあります。 前者では、目的のオフセットに行が存在しない場合、デフォルトで NULL が得られます。 後者では、存在する最初の行、つまりパーティション内の最初の行からの値を取得します。
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
このクエリは以下の出力を生成します。
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 814.50 1 2018-10-03 10692 878.00 814.50 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 88.80 2 2018-08-08 10625 479.75 88.80 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 403.20 3 2018-04-15 10507 749.06 403.20 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
今回は出力にNULLがないことに注意してください。 したがって、FIRST_VALUEとLAST_VALUEを持つフレームをサポートすることには、何らかの価値があると言えます。 これらの関数では常にフレーム指定について明示すること、そして、対象となる行を含む最小のフレームで ROWS オプションを使用することを忘れないでください。
結論
この記事では、ウィンドウ関数に関するバグ、落とし穴、ベスト プラクティスに焦点を当てました。 ウィンドウ集約関数と FIRST_VALUE および LAST_VALUE ウィンドウ オフセット関数の両方がフレームをサポートし、ウィンドウ順序節を指定してもウィンドウ フレーム単位とその関連する範囲を指定しないと、デフォルトで RANGE UNBOUNDED PRECEDING が得られることを忘れないでください。 これは、行モード演算子で最適化されたクエリでは、パフォーマンス上のペナルティとなります。 LAST_VALUE関数を使用すると、パーティション内の最後の行ではなく、現在の行から値を取得することになります。 フレームを明示することと、一般的にRANGEよりもROWSを選択することを忘れないでください。 バッチモードのWindow Aggregate演算子でパフォーマンスが向上するのは素晴らしいことです。 それが適用されると、少なくともパフォーマンスの落とし穴はなくなります。
。