【SQL】Window関数を使ってみた

NI+C マーケソリューションチームです：）

今回のTech Blogでは、新入社員が学習と備忘も兼ねて、Window関数を用いてデータを操作してみようと思います！

まずはじめに

Window関数ってそもそも何なのでしょうか？また何ができるのでしょうか・・・？

Window関数とは？

データベースを使って、リアルタイム（オンラインで）にデータ分析を行うための処理のことで、2000年代中盤くらいからトレンドになっている古くからあるデータベース技術の中でも、比較的新しい技術です。

なお、OracleやSQL Serverでは、「分析関数」と呼んでいたりします。

（出典元URL:http://www.code-magagine.com/?p=4892）

こちら先輩社員の方に教えていただきました。データベース技術の中でも新しいものなんですね。

また、別名でOLAP関数とも呼ばれているそうです。

Window関数で何ができる？

（Google CloudのBigQueryの公式説明が、一番分かりやすく感じたので引用しました。）

Window関数は分析関数とも呼ばれ、行のグループに対して値を計算して、各行に対して 1 つの結果を返します。これは、行のグループに対して 1 つの結果を返す集計関数とは異なります。

Window関数には OVER 句が含まれています。この句は、評価対象の行を囲む行のWindowを定義します。各行について、選択された行のWindowを入力として使用してWindow関数の結果が計算され、場合によっては集計が行われます。

Window関数を使用すると、移動平均の計算、項目のランク付け、累積合計の計算、その他の分析を行うことができます。

（出典元URL:https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja）

早速比較してみましょう

一つ目の文章について、例えばグループの平均をとりたいとき、集計関数でGROUP BYを使っても同じことができるようですが・・・

文章だけだとイメージが湧きづらいですね。

実際のデータを用いてSQLを書いて、両者を比較してみましょう！

（このブログでは筆者が所属するチームの製品である、TreasureDataの環境を使っています。Window関数機能はバッチリ搭載しております。）

今回は2022年秋ドラマの視聴率のデータを使ってみます。

サンプルデータとして、第一話視聴率のテーブル【drama_rating】↓

さらに、day_idがそれぞれどの曜日に該当するかのテーブル【weeks_data】↓

を用意しました。

【drama_rating】テーブルの、day_idカラムをグループだと仮定して、曜日ごとの平均をとっていきます。

試しに第一話の平均視聴率を計算してみましょう。集計関数を使う場合のSQLは↓のようになります。

day_idと、第一話の平均視聴率をAVERAGE_1として抽出し、GROUP BYでday_idごとにグループ分けします。

結果はこちら↓

第一話について、曜日ごとの平均視聴率が出せました！しかしday_idごとの表示だと、少し分かりづらいですね・・・

day_idがどの曜日に紐づいているかは、【weeks_data】テーブルを見れば分かりますが、

【weeks_data】テーブルにあるday_nameカラム（曜日名）を表示させつつ、

上記で取得したAVERAGE_1カラム（第一話の平均視聴率）を見たいといった場合、どうすればいいのでしょうか？

集計関数では、テーブル結合という方法があります。WITH句を使ってテーブル結合してみましょう。

SQLはこのような感じになります。↓

WITH句でAVERAGESという仮テーブルを作り、そこに一つ前のSQL文を入れます。

そしてSELECT文以降に、仮テーブルのカラムと結合したいテーブルのカラムを書きます。

AVERAGESという仮テーブルに対し【weeks_data】テーブルを、それぞれのday_idをキーに内部結合します。

結果はこうなります。↓

これで曜日名と曜日ごとの一話の平均視聴率を表示させることができました！

では、この情報を【drama_rating】テーブルに付与するにはどうすればいいのでしょうか？

先ほどのSQLにJOINを重ねて【drama_rating】テーブルも結合してみます。↓

このように、それぞれのドラマが、どのグループに属しているか、グループの平均視聴率はいくらかを出すことができました。

しかし、仮テーブルを含め３つテーブルを結合しているので、SQLが少し長くなってしまいました・・・。

ここでWindow関数の出番です！

おさらいですが、Window関数はグループの中で指定された範囲を集計し、集計結果を各行に戻すことができます。

OVER句で、【drama_rating】テーブルのday_idを範囲指定し、平均を算出しているのがミソです。

Window関数を使うと、仮テーブルを作らなくて良いので、２つのテーブル結合で済みます。クエリもスッキリしていますね！

結果はこちら↓

同じ結果を得られました！

今回のブログでは、Window関数を使って曜日ごとの第一話平均視聴率の平均をとり、各行に返す作業を行ってみました！

また今回、TreasureDataでのSQLを使った技術ブログでしたが、製品紹介についてのブログもご紹介させていただければと思います！

その他、Treasure Data CDP についての記事はこちら↓

セグメント作成について↓↓
Treasure Data CDP ＜第1弾＞Audience Studio の機能でセグメント作成してみた！！

Activationについて↓↓
Treasure Data CDP ＜第2弾＞Audience Studio の機能 Activation を使ってみた！

Predictive Scoring について↓↓
Treasure Data CDP ＜第3弾＞Predictive Scoring のご紹介

データのインポートについて↓↓
Treasure Data CDP ＜第4弾＞Treasure Data にデータをインポートしてみた

SQLを使ったデータの抽出方法について↓↓
Treasure Data CDP＜第5弾＞SQL を使ってデータ抽出してみた！

Treasure Workflowについて（前編）↓↓
Treasure Data CDP＜第6弾＞Treasure Workflow とは（前編）

Treasure Workflowについて（後編）↓↓
Treasure Data CDP＜第7弾＞Treaure Workflow とは（後編）

ジャーニーオーケストレーションについて↓↓
Treasure Data CDP＜第8弾＞新機能 ジャーニーオーケストレーション ご紹介

読んでいただきありがとうございました。ではまた、お会いしましょう！