はじめに
本記事では、SQLでの分析の幅を広げる機能として注目されている、分析関数(ウィンドウ関数)の概要をご案内します。分析関数では、レコードのグルーピングを行いながら、レコードの集約をせずに1行ずつ処理を行えます。
本記事に記載しているSQLは、以下のリンクから取得できますので、ぜひ手を動かしながら分析関数の動きを確認してみてください。
sampleコマンド
分析関数と集計関数の違い
分析関数には、AVG・SUM・MAX・MINなど集計関数と同名の関数がいくつもあります。集計関数と大きく異なるのは、入力と出力の関係です。
集計関数は、グループごとに、レコードの集約を行った1行の結果を返します。
分析関数では、レコードの集約はせず、入力と同じ数のレコードを返します。
集計関数の処理結果のイメージ
※sales_sum列は、ある小売の店舗A1の売上合計を意味します。
分析関数の処理結果のイメージ
※sales_sum列は、その行までのsales列の値を集計して表示しています。(売上累計)
分析関数の構文
|
1 2 3 |
分析関数(引数) OVER( [ PARTITION BY句 ] [ ORDER BY句 [ フレーム句 ] ] ) |
OVER句
OVER句 表の分割の基準にするカラムや、ソートの基準にするカラムを決めます。OVER句は以下の句から構成されます。
・PARTITION BY句
・ORDER BY句
・フレーム句
次項から、OVER句を構成する各句について、以下の分析関数を使ったSQLの構文とその結果について、処理のイメージとともに紹介します。
店舗(store_id)ごとに、日付(dete)ごとの売上(sales)と、その日までの累計売上(sum_sales)を表示させるSQLです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
dbadmin=> SELECT dbadmin-> store_id dbadmin-> ,date dbadmin-> ,sales dbadmin-> ,SUM(sales) OVER( PARTITION BY store_id ORDER BY date ) AS sum_sales dbadmin-> FROM tbl_sales dbadmin-> ORDER BY date dbadmin-> ; store_id | date | sales | sum_sales ----------+------------+-------+----------- B1 | 2019-01-01 | 1514 | 1514 B1 | 2019-01-02 | 1496 | 3010 B1 | 2019-01-04 | 812 | 3822 A1 | 2019-01-09 | 2253 | 2253 A1 | 2019-01-10 | 1244 | 3497 A1 | 2019-01-11 | 1424 | 4921 C1 | 2019-01-19 | 1867 | 1867 C1 | 2019-01-21 | 2079 | 3946 C1 | 2019-01-22 | 1254 | 5200 (9 rows) |
上記のSQLの元々のデータセットは以下です。
このデータセットから、どのように処理が行われていくのか順を追って見ていきます。
PARTITION BY句
PARTITION BY句に指定したカラム(store_id)をもとに、レコードを分割します。
ORDER BY句
PARTITION BY句の指定により分割されたデータセットの中で、ORDER BY句に指定したカラム(今回はdate)を基準に、レコードをソートします。レコードがソートされ、パーティションで区切られた範囲のことを「ウィンドウ」と呼びます。データの「範囲」のようなものを意味するとご理解ください。
分析関数の処理
分析関数の処理は、ウィンドウごとに、一番上のデータから一行ずつ行われます。
①～⑨の順番で処理し、各行ごとにその日付までの売上額の累計として、sum_sales列の値を出しています。
集計を行っていないsales列の値と、集計処理を行ったsum_sales列の値を合わせて表示できている点にご注目ください。
フレーム句の処理
前項までの内容が、分析関数の基本的な処理の流れです。さらにフレーム句を使うことで、ウィンドウの中でさらに細かい指定が可能です。
具体的には、「現在の処理の対象行を基準とした、分析関数の処理範囲の調節」ができます。
フレーム句を使って、ウィンドウ内で指定した範囲のことを「ウィンドウフレーム」と呼びます。
フレーム句の構文は以下です。
|
1 |
{RANGE|ROWS} { BETWEEN 処理の開始地点 AND 処理の終了地点 | 処理の開始地点 } |
フレーム句には以下のパラメータを指定できます。
| パラメータ名 | 内容 |
|---|---|
| ROWS | RANGE | ROWSには、行数を指定して処理の範囲とします。 例．3行前からカレント行 RANGE では値の範囲を指定します。 例．3日前から今日 |
| BETWEEN 処理の開始地点 AND 処理の終了地点 | ウィンドウの最初と最後の行を指定し、それぞれには以下を指定できます。 UNBOUNDED {PRECEDING | FOLLOWING} CURRENT ROW 任意の数値 {PRECEDING | FOLLOWING} 詳細は後述します。 |
| UNBOUNDED PRECEDING | ウィンドウフレームを、現在のウィンドウの最初の行まで広げます。 |
| 処理の開始地点 | ROWSあるいはRANGEの引数として"処理の開始地点"のみを指定する場合、処理の終了地点は現在行となります。 この時、"処理の開始地点"には、現在行以前を指定する必要があります。 |
| UNBOUNDED FOLLOWING | ウィンドウフレームを現在のウィンドウの最後の行まで広げます。 |
| CURRENT ROW | カレント行をウィンドウの始点または終点に指定します。 |
| 任意の数値 { PRECEDING | FOLLOWING } | 数値を指定して、処理範囲を指定できます。 ROWSとRANGEのどちらを指定しているかで、「任意の数値」が物理的なオフセットを指すか、論理的なオフセットを指すかが変わります。 |
いくつか指定例をご案内します。
N行前のレコードから現在行を処理範囲とする場合
行単位で、分析関数の処理範囲を区切りたい場合は「ROWS BETWEEN ‘数値’」と指定します。次のように書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT store_id ,date ,sales ,SUM(sales) OVER( PARTITION BY store_id ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS sum_sales FROM tbl_sales ORDER BY date ; |
以下の結果が表示されます。
「ROWS BETWEEN 1 PRECEDING AND CURRENT ROW」という記述が、「1行前(1 PRECEDING)から現在行(CURRENT ROW)を範囲とする」という意味になります。
1日前の日付のレコードから現在行を処理範囲とする場合
行ではなく、値の範囲で処理範囲を指定したい場合は「ROWS」の代わりに「RANGE」を指定します。たとえば、「直近二日分の売上累計を出したい」という場合です。
一つ前のクエリを「RANGE」の指定に書き換えると以下のようになります。
「★」が先ほどの例からフレーム句を書き換えた部分です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT store_id ,date ,sales ,SUM(sales) OVER( PARTITION BY store_id ORDER BY date RANGE BETWEEN '1 DAYS' PRECEDING AND CURRENT ROW ★ ) AS sum_sales FROM tbl_sales ORDER BY date ; |
先ほどと結果がどう変わったかにご注目ください。
「RANGE BETWEEN ‘1 DAYS’ PRECEDING AND CURRENT ROW」は、「一日前の日付のレコードから(‘1 DAYS’ PRECEDING)、現在行(CURRENT ROW)までを処理対象とする」ことを意味します。
そのため、一日前のデータが欠落している③と⑧の処理は、sum_salesの値が現在行のsalesのみになっていることにご注目ください。
ウィンドウに名前を付ける
あるウィンドウに対して複数の分析関数を使用できます。ただし、分析関数ごとにウィンドウの定義をしていると、クエリの記述が非常に煩雑になります。
その際、ウィンドウに名前をつけて、分析関数ごとにウィンドウを定義する手間を省くことが可能です。
一つのウィンドウに対して、複数の観点で分析を行いたい場合に便利です。
ウィンドウに名前を付けるには、WINDOW句を使用します。
構文は以下です。
|
1 |
WINDOW ウィンドウ名 AS (ウィンドウ定義) |
指定位置は、ORDER BY句の前です。
使用例
複数の分析関数を使用する際、ウィンドウ名を付けない場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
dbadmin=> SELECT dbadmin-> store_id dbadmin-> ,date dbadmin-> ,sales dbadmin-> ,SUM(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales dbadmin-> ,MAX(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales dbadmin-> ,MIN(sales) OVER(PARTITION BY store_id ORDER BY date) AS sum_sales dbadmin-> ,AVG(sales) OVER(PARTITION BY store_id ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_sales dbadmin-> FROM tbl_sales dbadmin-> ORDER BY date dbadmin-> ; store_id | date | sales | sum_sales | sum_sales | sum_sales | avg_sales ----------+------------+-------+-----------+-----------+-----------+----------- B1 | 2019-01-01 | 1514 | 1514 | 1514 | 1514 | 1514 B1 | 2019-01-02 | 1496 | 3010 | 1514 | 1496 | 1505 B1 | 2019-01-04 | 812 | 3822 | 1514 | 812 | 1154 A1 | 2019-01-09 | 2253 | 2253 | 2253 | 2253 | 2253 A1 | 2019-01-10 | 1244 | 3497 | 2253 | 1244 | 1748.5 A1 | 2019-01-11 | 1424 | 4921 | 2253 | 1244 | 1334 C1 | 2019-01-19 | 1867 | 1867 | 1867 | 1867 | 1867 C1 | 2019-01-21 | 2079 | 3946 | 2079 | 1867 | 1973 C1 | 2019-01-22 | 1254 | 5200 | 2079 | 1254 | 1666.5 (9 rows) |
ウィンドウ名を付けた場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
dbadmin=> SELECT dbadmin-> store_id dbadmin-> ,date dbadmin-> ,sales dbadmin-> ,SUM(sales) OVER(win_store_id) AS sum_sales dbadmin-> ,MAX(sales) OVER(win_store_id) AS sum_sales dbadmin-> ,MIN(sales) OVER(win_store_id) AS sum_sales dbadmin-> ,AVG(sales) OVER(win_store_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_sales dbadmin-> FROM tbl_sales dbadmin-> WINDOW win_store_id AS (PARTITION BY store_id dbadmin(> ORDER BY date) dbadmin-> ORDER BY date dbadmin-> ; store_id | date | sales | sum_sales | sum_sales | sum_sales | avg_sales ----------+------------+-------+-----------+-----------+-----------+----------- B1 | 2019-01-01 | 1514 | 1514 | 1514 | 1514 | 1514 B1 | 2019-01-02 | 1496 | 3010 | 1514 | 1496 | 1505 B1 | 2019-01-04 | 812 | 3822 | 1514 | 812 | 1154 A1 | 2019-01-09 | 2253 | 2253 | 2253 | 2253 | 2253 A1 | 2019-01-10 | 1244 | 3497 | 2253 | 1244 | 1748.5 A1 | 2019-01-11 | 1424 | 4921 | 2253 | 1244 | 1334 C1 | 2019-01-19 | 1867 | 1867 | 1867 | 1867 | 1867 C1 | 2019-01-21 | 2079 | 3946 | 2079 | 1867 | 1973 C1 | 2019-01-22 | 1254 | 5200 | 2079 | 1254 | 1666.5 (9 rows) |
各分析関数のOVER句の中には、ウィンドウ名を指定している部分にご注目ください。
※フレーム句は、分析関数ごとに指定します。
注意事項
以下にSQL実行時の処理順序を記載します。分析関数は、ORDER BY句(※)より前に処理されます。
※分析関数のORDER BYではなく、SELECT結果のソート順序を指定するORDER BYを指す
1．結合処理
2．WHERE句
3．GROUP BY句
4．SELECT句
5．HAVING句
6．分析関数 ★
7．ORDER BY句
これを踏まえ、以下の点にご注意ください。
・WHERE句やHAVING句で、分析関数の処理結果を使っての絞り込みは行えません。
→必要であれば、サブクエリーに指定します。
・分析関数で処理したい範囲が決まっていれば、WHERE句の絞り込みで処理対象を削減しておくことでクエリのコストが下げられます。
まとめ
分析関数は、新しい機能というわけではなく、既存の3つの機能を組み合わせたものです。・集合の分割(PARTITION BY)
・レコードの並び替え(ORDER BY)
・行レベルでの範囲選択(ウィンドウフレーム)
そのため、はじめは少し複雑に感じるかもしれませんが、理解すればSQLによる表現の幅をぐっと広がる便利な関数です。
Verticaには、本記事で使用したもの以外にもたくさんの分析関数が用意されています。また別の記事で詳しくご案内させていただきます。
もしご興味があれば、Verticaの公式マニュアルもぜひご確認ください。
参考情報
Analytic Functionshttps://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/AnalyticFunctions.htm
Analytic Query Examples
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/SQLAnalytics/AnalyticQueryExamples.htm
