Window関数とは
お疲れ様です。Deckです。
今回の記事ではWindow関数について説明します。
Window関数はSQLでは一般的な機能かつ強力な関数で、複数の行を集計した結果をそれぞれの行に返してくれます。
たとえば、以下のような使い方があります。
- 売上テーブルで、各行の売上と平均の売上を比較して差分を計算する
- 名簿テーブルで、同じ年齢のメンバーが何人いるか集計する
Window関数を使うと、これらの計算をすっきり見通し良く書くことができます。
上記の例では「平均の売上」「同じ年齢のメンバーが何人」といったように、集計が含まれている計算をするところが重要です。
使い方①
「売上テーブルで、各行の売上と平均の売上を比較して差分を計算する」方法について、実際のスクリプトを見ながら考えてみましょう。
スクリプトは以下のようになります。
トランザクション:
LOAD *,
売上 - Window(Avg(売上)) as 平均との差
;
LOAD *
inline
[
明細番号, 売上
1, 100
2, 150
3, 200
4, 300
5, 100
]
;
Window(Avg(売上)) がこのスクリプトの重要な部分です。
ウインドウ関数を使い、その中で売上の平均を集計しています。
そして 売上 - Window(Avg(売上)) とすることで、「各行の売上と平均の売上の差はいくらか？」という集計を行うことができます。
このスクリプト例を実行した結果は以下になります。
ここでは平均の売上は170であり、「平均との差」で正しく集計が行われていることがわかります。
使い方②
次に、「名簿テーブルで、同じ年齢のメンバーが何人いるか集計する」方法について使い方を見てみましょう。
スクリプトは以下のようになります。
名簿:LOAD *, Window(Count(会員番号), 年齢) as 同じ年齢の人数;LOAD *Inline[会員番号, 年齢1, 102, 303, 204, 105, 20];
Window(Count(会員番号), 年齢) のように、ここではWindow関数の第2引数として年齢を集計軸に指定しました。
この指定により、同じ年齢の会員番号の人数をカウントすることができます。
スクリプト例の実行結果は以下になります。
データでは「10歳が2人」「20歳が2人」「30歳が1人」なので、年齢別の集計ができています！
注意事項
このように大変便利なWindow関数ですが、いくつかの注意事項があります。
ここではヘルプに記載されている項目を引用します。
- Window はリソースを大量に消費する機能であり、特にメモリの消費量が大きくなります。
- Window は Qlik Sense Mobile には対応していません。
- チャートの数式は Windowをサポートしていません。
- Window 関数を他の Window 関数内にネストすることはできません。
- Window は集計関数内では使用できません。
- Window はテーブル全体をスキャンできる必要があります。
- スライディング ウィンドウ機能を使用する場合、WRank()、RecNo()、RowNo() は Window と一緒に使用できません。
特にこの中で重要なのは「Window はテーブル全体をスキャンできる必要があります。」というところでしょう。
Where句などでデータを絞った場合、正しく集計が行われない場合があるということです。
このような場合は、先行LOADなどであらかじめ絞ってあげた状態のデータを渡してあげるなどの工夫が必要です。
Window関数は強力な機能なので、ぜひ使えるようにしておきましょう！
