SQLにおいて、ウィンドウ関数または分析関数[ 1 ]は、1行または複数行の値を使用して各行の値を返す関数です。（これは、複数行に対して単一の値を返す集計関数とは対照的です。）ウィンドウ関数にはOVER句があります。OVER句のない関数はウィンドウ関数ではなく、集計関数または単一行（スカラー）関数です。[ 2 ]
例
例として、ウィンドウ関数を使って各従業員の給与をその部門の平均給与と比較するクエリを次に示します（PostgreSQLのドキュメントからの例）: [ 3 ]
SELECT depname 、empno 、salary 、avg ( salary ) OVER ( PARTITION BY depname ) FROM empsalary ;出力：
部署名 | 従業員番号 | 給与 | 平均 ----------+-------+--------+---------------------- 開発 | 11 | 5200 | 5020.00000000000000000 開発 | 7 | 4200 | 5020.00000000000000000 開発 | 9 | 4500 | 5020.00000000000000000 開発 | 8 | 6000 | 5020.00000000000000000 開発 | 10 | 5200 | 5020.00000000000000000 人員 | 5 | 3500 | 3700.00000000000000000 人員 | 2 | 3900 | 3700.00000000000000000 売上 | 3 | 4800 | 4866.6666666666666667 売上 | 1 | 5000 | 4866.6666666666666667 売上 | 4 | 4800 | 4866.6666666666666667 （10行）
このPARTITION BY句は行をパーティションにグループ化し、関数は各パーティションに個別に適用されます。このPARTITION BY句が省略された場合（空の句など）、結果セットOVER()全体が単一のパーティションとして扱われます。[ 4 ]このクエリでは、報告される平均給与はすべての行の平均値になります。
構文
PostgreSQLのドキュメントによると、ウィンドウ関数の構文は次のいずれかになります。[ 4 ]
function_name ([式[,式... ]]) OVER window_name function_name ([式[,式... ]]) OVER (ウィンドウ定義) function_name ( * ) OVER window_name function_name ( * ) OVER (ウィンドウ定義)window_definition構文は次のとおりです。
[ existing_window_name ] [ PARTITION BY式[, ... ] ] [ ORDER BY式[ ASC | DESC | USING演算子] [ NULLS { FIRST | LAST } ] [, ... ] ] [ frame_clause ]frame_clause構文は次のいずれかになります。
{範囲|行|グループ} frame_start [ frame_exclusion ] {範囲|行|グループ} frame_startとframe_endの間[ frame_exclusion ]frame_startおよび、、、、またはframe_endになります。、、 、、またはになります。 UNBOUNDED PRECEDINGoffset PRECEDINGCURRENT ROWoffset FOLLOWINGUNBOUNDED FOLLOWINGframe_exclusionEXCLUDE CURRENT ROWEXCLUDE GROUPEXCLUDE TIESEXCLUDE NO OTHERS
expressionウィンドウ関数の呼び出しを含まない任意の式を指します。
表記:
- 括弧[]はオプションの節を示します
- 中括弧 {} は、さまざまなオプションのセットを示します。各オプションは縦棒 | で区切られます。
例
ウィンドウ関数は、現在のレコードの前後のレコードのデータにアクセスすることを可能にする。[ 5 ] [ 6 ] [ 7 ] [ 8 ]ウィンドウ関数は、現在の行の周囲に指定された長さの行のフレームまたはウィンドウを定義し、ウィンドウ内のデータセット全体にわたって計算を実行する。[ 9 ] [ 10 ]
名前 | ------------ Aaron| <-- 先行（無制限） アンドリュー| アメリア| ジェームズ| ジル| ジョニー| <-- 前の行1 マイケル| <-- 現在の行 Nick| <-- 次の行の1行目 オフィーリア| Zach| <-- フォロー中（無制限）
上記の表では、次のクエリは各行に対して、前の行と次の行が 1 つずつあるウィンドウの値を抽出します。
SELECT LAG ( name , 1 ) OVER ( ORDER BY name ) "prev" , name , LEAD ( name , 1 ) OVER ( ORDER BY name ) "next" FROM people ORDER BY name結果クエリには次の値が含まれます。
| 前へ | 名前 | 次へ | |----------|----------|----------| | (null)| アーロン| アンドリュー| | アーロン| アンドリュー| アメリア| | アンドリュー| アメリア| ジェームズ| | アメリア| ジェームズ| ジル| | ジェームズ| ジル| ジョニー| | ジル| ジョニー| マイケル| | ジョニー| マイケル| ニック| | マイケル| ニック| オフィーリア| | ニック| オフィーリア| ザック| | オフィーリア| ザック| (null)|
歴史
ウィンドウ関数はSQL:2003標準に組み込まれ、その後の仕様で機能が拡張されました。[ 11 ]
特定のデータベース実装のサポートが次のように追加されました。
参照
参考文献
- ^ a b「標準SQLの分析関数のコンセプト | BigQuery」。Google Cloud 。 2021年3月23日閲覧。
- ^ 「ウィンドウ関数」 . sqlite.org . 2021年3月23日閲覧。
- ^ 「3.5. ウィンドウ関数」 . PostgreSQLドキュメント. 2021年2月11日. 2021年3月23日閲覧。
- ^ a b「4.2. 値式」 . PostgreSQLドキュメント. 2021年2月11日. 2021年3月23日閲覧。
- ^ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (2015年6月). 「分析SQLクエリにおけるウィンドウ関数の効率的な処理」Proc. VLDB Endow . 8 (10): 1058– 1069. doi : 10.14778/2794367.2794375 . ISSN 2150-8097 .
- ^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (2012年7月). 「解析ウィンドウ関数の最適化」. Proc. VLDB Endow . 5 (11): 1244– 1255. arXiv : 1208.0086 . doi : 10.14778/2350229.2350243 . ISSN 2150-8097 .
- ^ 「おそらく最もクールなSQL機能：ウィンドウ関数」 . Java、SQL、jOOQ . 2013年11月3日. 2017年9月26日閲覧。
- ^ 「SQLのウィンドウ関数 - Simple Talk」 . Simple Talk . 2013年10月31日. 2017年9月26日閲覧。
- ^ 「SQLウィンドウ関数の紹介」Apache Drill 。
- ^ 「PostgreSQL: ドキュメント: ウィンドウ関数」 . www.postgresql.org . 2020年4月4日閲覧。
- ^ 「ウィンドウ関数の概要」 . MariaDB KnowledgeBase . 2021年3月23日閲覧。
- ^ 「Oracle 8iリリース2（8.1.6）の新機能」 www.oracle.com . 2025年1月23日閲覧。
- ^ 「Oracle 8iの分析関数」（PDF） www.stanford.edu 2025年1月23日閲覧。
- ^ "PostgreSQL Release 8.4" . www.postgresql.org . 2014年7月24日. 2024年3月10日閲覧。
- ^ 「MySQL :: MySQL 8.0の新機能（一般公開）」 . dev.mysql.com . 2022年11月21日閲覧。
- ^ 「MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.2 ウィンドウ関数の概念と構文」。dev.mysql.com 。
- ^ 「MariaDB 10.2.0 リリースノート」 . mariadb.com . 2024年3月10日閲覧。
- ^ 「SQLite Release 3.25.0 On 2018-09-15」。www.sqlite.org。2025年2月5日閲覧。
