SQLウィンドウ関数の概要

あなたが嫌うのが大好きな非常に強力な機能（しかし知る必要があります）

SQLウィンドウ関数は、いくつかの非常に強力で便利な機能を提供します。 しかし、多くの人にとって、それらは標準SQLに対して非常に異質であるため、習得および理解が難しく、奇妙な構文を持ち、非常に頻繁に回避されます。

ウィンドウ関数は、集計と同様の計算関数として簡単に説明できますが、 GROUP BY句を介した通常の集計が組み合わされて、集計される個々の行が非表示になる場合、ウィンドウ関数は個々の行にアクセスでき、それらの行の属性の一部をに追加できます。結果セット。

このSQLウィンドウ関数のチュートリアルでは、ウィンドウ関数の使用を開始し、利点とそれらをいつ使用するかを説明し、概念に役立つ実際の例を示します。

データへの窓

SQLで最も使用されている重要な機能の1つは、特定の方法でデータの行を集約またはグループ化する機能です。 ただし、必要なものによっては、グループ化が非常に複雑になる場合があります。

クエリの結果をループして、ランキングや上位xリストなどを取得したいと思ったことはありませんか。 視覚化ツールにぴったりのデータを準備したいが、それがほぼ不可能または非常に複雑で価値がないことに気付いた分析プロジェクトはありますか？

ウィンドウ関数は物事を簡単にすることができます。 クエリの結果を取得した後、つまりWHERE句と標準の集計の後、ウィンドウ関数は残りの行（データのウィンドウ）に作用し、必要なものを取得します。

これから説明するウィンドウ関数には、次のものがあります。

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

簡単すぎる

OVER句はウィンドウ関数を指定するものであり、常にステートメントに含める必要があります。 OVER句のデフォルトは、行セット全体です。 例として、会社のデータベースの従業員テーブルを見て、各行の従業員の総数と、会社を始めたときを含む各従業員の情報を表示してみましょう。

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

上記は、多くのウィンドウ関数と同様に、より馴染みのあるウィンドウ化されていない方法で記述することもできます。この単純な例では、それほど悪くはありません。

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

しかし、ここで、同じ月に開始した従業員の数を列の従業員と表示したいとします。 各行のカウントをその月だけに絞り込むか制限する必要があります。 それはどのように行われますか？ 次のように、ウィンドウのPARTITION句を使用します。

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

パーティションを使用すると、ウィンドウを特定の値でセクションにフィルタリングできます。 各セクションは、多くの場合、ウィンドウフレームと呼ばれます。

さらに言えば、同じ月に何人の従業員が開始したかを知りたいだけでなく、その月に開始した順序を示したいとしましょう。 そのために、おなじみのORDER BY句を使用できます。 ただし、ウィンドウ関数内では、 ORDER BYの動作はクエリの終了時とは少し異なります。

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

この場合、 ORDER BYはウィンドウを変更して、パーティションの開始（この場合は従業員が開始した月と年）から現在の行に移動します。 したがって、カウントは各パーティションで再開されます。

ランク付け

ウィンドウ関数は、ランク付けの目的で非常に役立ちます。 以前、 COUNT集計関数を使用すると、従業員が会社に参加した順序を確認できることを確認しました。 ROW_NUMBER（ ROW_NUMBER() 、 RANK() 、 DENSE_RANK()などのウィンドウランキング関数を使用することもできます。

翌月に新しい従業員を追加し、パーティションを削除すると、違いがわかります。

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

あなたは違いを見ることができます。 ROW_NUMBER()は、特定のパーティション内のシーケンシャルカウントを提供します（ただし、パーティションがない場合は、すべての行を通過します）。 RANK()は、 ORDER BY句に基づいて各行のランクを示します。 同点を示し、次のランキングをスキップします。 DENSE_RANKも同点を示しますが、同点がなかったかのように次の連続値を続行します。

その他のランキング機能は次のとおりです。

• CUME_DIST –パーティション内の現在の行の相対ランクを計算します
• NTILE –各ウィンドウパーティションの行を可能な限り均等に分割します
• PERCENT_RANK –現在の行のパーセントランク

この例では、1つのクエリに複数のウィンドウ関数を含めることができ、パーティションと順序の両方がそれぞれ異なる可能性があることにも注意してください。

行と範囲とフレーム、オーマイ

OVER()句内でウィンドウフレームをさらに定義または制限するには、 ROWSおよびRANGEを使用できます。 ROWS句を使用すると、パーティションに含まれる行を、現在の行の前後の行として指定できます。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

この例では、ウィンドウフレームは最初の行から現在の行から1を引いたものになり、ウィンドウサイズは行ごとに増加し続けます。

範囲の動作は少し異なり、異なる結果が得られる可能性があります。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

範囲には、現在の行と同じORDER BY値を持つウィンドウフレーム内の行が含まれます。 したがって、 ORDER BYが一意でない場合、 RANGEで重複が発生する可能性があります。

ROWSを物理演算子として説明する人もいれば、 RANGEを論理演算子として説明する人もいます。 いずれの場合も、 ROWSとRANGEのデフォルト値は、常にUNBOUNDED PRECEDING AND CURRENT ROWです。

ほかに何か？

ほとんどの標準的な集計関数は、ウィンドウ関数で機能します。 すでに例でCOUNTを見てきました。 その他には、 SUM 、 AVG 、 MIN 、 MAXなどがあります。

ウィンドウ関数を使用すると、 LAGとLEAD 、およびFIRST_VALUEとLAST_VALUEを使用して、前のレコードと後続のレコードの両方にアクセスすることもできます。 たとえば、各行に今月の売上高と先月の売上高の差を表示するとします。 あなたはこのようなことをするかもしれません：

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

基本的に、SQLウィンドウ関数は非常に強力です

これはSQLウィンドウ関数の簡単な紹介ですが、うまくいけば、SQLウィンドウ関数で実行できるすべてのことを確認することに興味を持ってもらうことができます。 ウィンドウ関数は集計関数と同様の計算を実行することを学びましたが、個々の行内のデータにアクセスできるという利点があり、非常に強力です。 これらには常にOVER句が含まれ、 PARTITION BY 、 ORDER BY 、および多数の集計（ SUM 、 COUNTなど）およびその他の位置関数（ LEAD 、 LAG ）が含まれる場合があります。 また、ウィンドウフレームと、それらがデータのセクションをカプセル化する方法についても学びました。

SQLのフレーバーが異なれば、ウィンドウ関数の実装も異なる場合があり、すべてのウィンドウ関数または句が実装されていない場合もあることに注意してください。 使用しているプラ​​ットフォームのドキュメントを必ず確認してください。

SQL開発者として、SQLデータベースのパフォーマンスの調整に関心がある場合は、 SQL Database Performance TuningforDevelopersを確認してください。

ハッピーウィンドウ！

特定の実装の詳細については、以下を参照してください。

• PostgreSQL実装に関するPostgreSQLのウィンドウ関数のドキュメント。
• SELECT-MicrosoftによるOVER句（Transact-SQL）のドキュメント。
• SQL Serverの実装とそのパート2の概要については、SQLServerのウィンドウ関数を参照してください。