【QS】Window関数を使ってLOAD文中での集計を見通し良く記述する

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, 10
2, 30
3, 20
4, 10
5, 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関数は強力な機能なので、ぜひ使えるようにしておきましょう！