By Kathi Kellenberger

私は何年も T-SQL コードで ROW_NUMBER を使用してきましたが、それがウィンドウ関数と呼ばれる非常に便利な関数群の 1 つであることに気付きませんでした。 SQL Server 2012 リリースの新しい T-SQL 機能に関するプレゼンテーションを作成したときに初めて、話すべきことがあまりに多いことに気づき、新しいウィンドウ関数に関する別のプレゼンテーションを作成したのです。 最終的には、この1つのプレゼンテーションが2つのプレゼンテーションになり、アプレスから出版された私の本と、Pluralsightのコースになりました。 私は、この素晴らしい関数について、聞く人なら誰にでも話してきましたし、私自身、仕事で日常的に使っています。 これらは、集約関数によるグループ化と操作は似ていますが、重要な違いを可能にします。 集約クエリはグループごとに1つの行を返し、結果において詳細は排除されます。 窓関数を含むクエリは、窓関数式の結果とともに、すべての詳細行を返します。

私がSQL Serverや開発者のイベントでウィンドウ関数について話すと、通常、会場の約半数はROW_NUMBERについて知っています。 それ以上について知っている人はほんの一握りです。 これらの便利な関数は何年も前から利用可能であるため、これは残念なことです。 LAG は、結果セット内の別の行から任意の列を取得することができます。 他の方法でも同じことを実現できますが、パフォーマンスはずっと悪くなります。 以下は、AdventureWorksデータベースに対する2つのクエリです。 どちらのクエリも同じ結果になります。

–注文日と前の注文からの日数を持つ顧客をリストします

SELECT CustomerID, OrderDate,

DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder

FROM Sales.PrevOrder,OrderDate,

LAGを利用した外側結合によりそれを行ないます。SOH

OUTER APPLY (

SELECT TOP(1) OrderDate AS PrevOrder

FROM Sales.SalesOrderHeader AS OA

WHERE OA.CustomerID = SOH.CustomerID

AND OA.OrderDate < SOH.OrderDate

ORDER BY OrderDate) AS OAPPLY

ORDER BY CustomerID, OrderDate;

–LAG

SELECT CustomerID, OrderDate,

DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) で同じ結果が出ています。 AS DaysSinceLastOrder

FROM Sales.SalesOrderHeader

ORDER BY CustomerID, OrderDate;

LAG 関数を使用した2番目のクエリはより簡単に記述でき、パフォーマンスも良くなりました。 実際の実行計画を見ると、最初のクエリはバッチ内のリソースの99%を消費しています。 2番目のクエリは、リソースのわずか1%を消費するだけです。 以下は、2 つの実行計画です。

New Content Item

ウィンドウ関数の構文に慣れていないと、LAG クエリは少しわかりにくいかもしれません。 すべてのウィンドウ関数は、計算のためのウィンドウ、または行のセットを定義する OVER 句を持ちます。 私は顧客間で注文日を比較したくないので、PARTITION BY オプションを使用して、行を CustomerID によるグループに分割しました：

LAG(OrderDate) OVER(PARTITION BY CustomerID …

OVER句には、ORDER BYオプションもあります。 これはLAGの場合に必要で、エンジンが正しい行からそれぞれの値を取得するために、行を正しい順序で並べます。 私の最終的な LAG 呼び出しは次のようになります。

LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)

LAG は多くのウィンドウ関数のうちの 1 つの関数に過ぎないのです。 これらの関数がなければ不可能なことを実際にできるようにするわけではありませんが、コーディングを容易にすることは間違いありません。 また、これらの新しい関数を使用して定式化すると、多くの場合、クエリがより速く実行されることがわかります。 これらは、私の生活を容易にし、クエリのパフォーマンスを向上させます。

著者について

新しいコンテンツ項目Kathi Kellenbergerは、Linchpin Peopleのコンサルタントである。 SQL Serverに関するトピックの執筆や講演を好み、これまでに20本以上の記事を執筆し、4冊の書籍に寄稿し、多くのSQL Serverイベントで講演を行いました。 余暇には、家族や友人と過ごす時間、歌、サイクリングを楽しんでいます。

Kathi Kellenbergerの著書『Expert T-SQL Window Functions in SQL Server』（ISBN 9781484211045）では、ウィンドウ関数とそのパワーによる難しいクエリー問題の解決についてさらに詳しく解説しています。 Kathiの他の著書には、Beginning SQL Server Reporting Services (2016)、Beginning T-SQL (2014)があります

。