執筆者：Hakky AI
BigQuery｜ウィンドウ関数でデータ分析を効率化する方法
- ウィンドウ関数は、データ分析を効率化する強力なツールであり、集計やランキングを容易にする。
PARTITION BY句でデータ分割、OVER句で範囲や順序を指定し、柔軟なデータ分析を実現。- サブクエリや自己結合を代替し、クエリを簡潔化、パフォーマンス改善に貢献する。
はじめに
BigQueryのウィンドウ関数は、データ分析を効率化する強力なツールです。この記事では、ウィンドウ関数の基本的な概念から構文、実際のクエリ例、パフォーマンス改善のヒントまでを徹底的に解説します。
PARTITION BY句やOVER句といった重要な要素を理解し、データ分析のスキルを向上させましょう。ウィンドウ関数を使いこなすことで、より高度なデータ分析が可能になります。ぜひこの記事を参考に、BigQueryのウィンドウ関数を活用してください。
BigQueryウィンドウ関数とは？基本と概念
このセクションでは、BigQueryにおけるウィンドウ関数の基本的な定義、種類、そしてその概念について解説します。集約関数との違いや、ウィンドウ関数を使用するメリットについても詳しく説明します。
ウィンドウ関数の定義と種類
ウィンドウ関数は、BigQueryでデータ分析を行う際に非常に強力なツールです。特定の範囲（ウィンドウ）内での集計や計算を、元の行を保持したまま行うことができます。
主なウィンドウ関数には、RANK、ROW_NUMBER、LAG、LEADなどがあります。
- RANK関数は、データセット内の順位を決定するために使用され、同じ値を持つ行には同じランクが割り当てられます。
- ROW_NUMBER関数は、結果セット内の各行に一意の連番を割り当てます。
- LAG関数は、現在の行から指定されたオフセット前の行の値を取得し、LEAD関数は、現在の行から指定されたオフセット後の行の値を取得します。
例えば、SELECT product_name, price, RANK() OVER (ORDER BY price DESC) AS price_rank FROM productsというクエリでは、製品の価格に基づいて順位付けを行います。
また、SELECT sale_date, amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_amount FROM salesというクエリでは、前日の売上額を取得できます。これらの関数を組み合わせることで、複雑なデータ分析を効率的に実行できます。
PARTITION BY句の役割と効果
[PARTITION BY](https://book.st-hakky.com/data-analysis/bigquery-how-to-use-partition)句は、ウィンドウ関数を適用する前にデータセットを複数のパーティションに分割するために使用されます。この句を使用することで、データは指定された列の値に基づいてグループ化され、各パーティション内で独立してウィンドウ関数が適用されます。
例えば、顧客ごとの購入履歴を分析する場合、PARTITION BY customer_idを使用することで、各顧客のデータが個別のパーティションに分割され、それぞれの顧客に対して個別に集計やランキングを行うことができます。
具体的には、SELECT customer_id, order_date, order_amount, SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_order_amount FROM ordersというクエリでは、顧客ごとの注文日ごとの累積注文金額を計算できます。
PARTITION BY句を使用しない場合、ウィンドウ関数はデータセット全体に対して適用されるため、顧客ごとの分析は困難になります。このように、PARTITION BY句は、データ分析の範囲を制御し、より詳細な分析を可能にするために重要な役割を果たします。
OVER句の構造とwindow_frame_clause
OVER句は、ウィンドウ関数の動作範囲と順序を定義するために使用されます。OVER句は、PARTITION BY句とORDER BY句を組み合わせて使用することが一般的です。
PARTITION BY句は、前述の通り、データセットを複数のパーティションに分割し、各パーティション内で独立してウィンドウ関数を適用します。ORDER BY句は、各パーティション内のデータの順序を指定します。
window_frame_clauseは、ウィンドウ関数の集計範囲をさらに詳細に指定するために使用されます。例えば、ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGという指定は、現在の行の前後の1行を含む範囲を集計対象とすることを意味します。
具体的には、SELECT order_date, order_amount, AVG(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average FROM ordersというクエリでは、注文日ごとの移動平均を計算できます。
window_frame_clauseを使用することで、特定の期間や範囲に限定した集計を行うことができ、より柔軟なデータ分析が可能になります。
ウィンドウ関数の構文と記述方法
ここでは、BigQueryにおけるウィンドウ関数の構文と記述方法について、基本的なSQL構文からOVER句、window_frame_clauseの指定方法までを詳しく解説します。
基本的なSQL構文
ウィンドウ関数は、標準SQLの構文に組み込んで使用します。基本的な構文は、SELECT句内で関数を呼び出し、OVER句でパーティションや順序を指定する形です。例えば、SELECT function_name(argument) OVER (PARTITION BY column1 ORDER BY column2) FROM table_name WHERE condition のように記述します。FROM句でデータソースとなるテーブルを指定し、WHERE句で条件を絞り込むことも可能です。
ウィンドウ関数は、集約関数と異なり、GROUP BY句を必要としません。各行に対して計算が行われるため、詳細なデータ分析に適しています。例えば、各顧客の購入履歴に対して、購入金額の累積を計算するクエリは以下のようになります。
SELECT customer_id, purchase_date, purchase_amount,
SUM(purchase_amount) OVER (PARTITION BY customer_id ORDER BY purchase_date)
FROM purchase_history
WHERE purchase_date BETWEEN '2023-01-01' AND '2023-12-31';
このクエリでは、customer_id ごとに purchase_date の順に購入金額を集計しています。ウィンドウ関数を使用することで、各行のコンテキスト内で集計値を算出できます。また、WHERE句を利用して、特定の期間のデータに絞り込むことで、より詳細な分析が可能です。
OVER句の書き方
OVER句は、ウィンドウ関数の動作を定義する上で非常に重要です。PARTITION BY句を使用すると、データを特定の列の値に基づいてグループ化できます。例えば、OVER (PARTITION BY category) と記述すると、category 列の値が同じ行が同じパーティションに属します。ORDER BY句を使用すると、各パーティション内の行の順序を指定できます。例えば、OVER (PARTITION BY category ORDER BY sales DESC) と記述すると、category ごとに sales の降順で行が並べられます。
複数のパーティションやソート条件を指定することも可能です。例えば、OVER (PARTITION BY region, category ORDER BY sales DESC, date ASC) と記述すると、region と category の組み合わせごとにパーティションが作成され、sales の降順、date の昇順でソートされます。以下に具体的なクエリ例を示します。
SELECT product_name, region, category, sales,
RANK() OVER (PARTITION BY region, category ORDER BY sales DESC) AS rank
FROM sales_data
WHERE date = CURRENT_DATE();
このクエリでは、各地域とカテゴリにおける製品の売上ランキングを計算しています。PARTITION BY と ORDER BY を組み合わせることで、より複雑なデータ分析が可能になります。
window_frame_clauseの指定方法
window_frame_clauseは、ウィンドウ関数の計算対象となる行の範囲をさらに詳細に指定するために使用されます。ROWSとRANGEは、それぞれ行数と値の範囲に基づいてフレームを定義します。ROWS BETWEENを使用すると、物理的な行数に基づいて範囲を指定できます。例えば、ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING は、現在の行の前後の1行を含めた3行を計算対象とします。
RANGE BETWEENを使用すると、値の範囲に基づいて範囲を指定できます。例えば、RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING は、現在の行の値から前後100の範囲内の行を計算対象とします。フレームの開始位置と終了位置は、UNBOUNDED PRECEDING（パーティションの最初から）、CURRENT ROW（現在の行）、UNBOUNDED FOLLOWING（パーティションの最後まで）などのキーワードで指定できます。以下に具体的なクエリ例を示します。
SELECT date, sales,
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average
FROM daily_sales
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';
このクエリでは、日ごとの売上データに対して、過去7日間の移動平均を計算しています。
実践！ウィンドウ関数クエリ例
このセクションでは、BigQueryのウィンドウ関数を用いた実践的なクエリ例を紹介します。RANK関数による順位付け、LAG関数による前の行の値の取得、そして集計関数との組み合わせによる応用例を通じて、ウィンドウ関数の具体的な活用方法を解説します。
これらの例を参考に、様々な分析ニーズに対応できるクエリを作成してみましょう。
RANK関数で順位付け
BigQueryのウィンドウ関数であるRANK関数は、データセット内のレコードに順位を付ける際に非常に有効です。特に、特定の条件に基づいてグループ内でランキングを算出する際にその力を発揮します。
例えば、店舗ごとの売上ランキングを算出するケースを考えてみましょう。以下のクエリは、store_idごとに売上salesのランキングを計算します。RANK() OVER (PARTITION BY store_id ORDER BY sales DESC)という構文を使用することで、各店舗内での売上順位が算出されます。
SELECT
store_id,
sales,
RANK() OVER (PARTITION BY store_id ORDER BY sales DESC) AS sales_rank
FROM
project_id.dataset.sales_data;
このクエリを実行すると、各店舗の売上高とそれに対応するランキングが表示されます。RANK関数は同順位が発生した場合、同じ順位を付与し、次の順位をスキップします。例えば、1位が2つある場合、次の順位は3位となります。
同順位の処理方法として、DENSE_RANK関数やROW_NUMBER関数も存在します。DENSE_RANK関数は、同順位の場合でも順位をスキップせずに連番を付与します。一方、ROW_NUMBER関数は、同順位であっても一意の順位を付与します。これらの関数を適切に使い分けることで、より詳細なランキング分析が可能になります。
LAG関数で前の行の値を取得
LAG関数は、BigQueryのウィンドウ関数の一つで、現在の行から見て前の行のデータを参照する際に利用します。特に、時系列データ分析において、前日比や前回値の算出に非常に役立ちます。
例えば、日ごとの株価データを用いて、前日の株価との差を計算するケースを考えてみましょう。以下のクエリは、stock_dateごとに株価priceのLAGを計算し、前日比を算出します。
SELECT
stock_date,
price,
LAG(price, 1, 0) OVER (ORDER BY stock_date) AS previous_price,
price - LAG(price, 1, 0) OVER (ORDER BY stock_date) AS price_difference
FROM
project_id.dataset.stock_prices;
このクエリでは、LAG(price, 1, 0) OVER (ORDER BY stock_date)という構文を使用しています。ここで、1は前の行からどれだけ遡るかを示し、0は最初の行にLAGが存在しない場合に返すデフォルト値を指定しています。
このクエリを実行することで、各日の株価、前日の株価、そしてその差額を一覧で確認できます。LAG関数を活用することで、時系列データのトレンドや変化をより詳細に分析することが可能になります。
集計関数と組み合わせた応用例
BigQueryのウィンドウ関数は、SUMやAVGなどの集計関数と組み合わせることで、より高度なデータ分析を実現できます。特に、移動平均や累積和の算出は、ビジネスにおけるトレンド分析や予測に不可欠です。
例えば、日ごとの売上データを用いて、7日間の移動平均を算出するケースを考えてみましょう。以下のクエリは、sale_dateごとに売上salesの7日移動平均を計算します。
SELECT
sale_date,
sales,
AVG(sales) OVER (ORDER BY sale_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average
FROM
project_id.dataset.daily_sales;
このクエリでは、AVG(sales) OVER (ORDER BY sale_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)という構文を使用しています。ROWS BETWEEN 6 PRECEDING AND CURRENT ROWは、現在の行とその前の6行を含むウィンドウフレームを指定しています。これにより、各日の売上に対して、過去7日間の平均が算出されます。
また、累積和を算出する場合は、SUM関数を使用します。例えば、
SUM(sales) OVER (ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
という構文を使用することで、データの開始時点からの累積和を計算できます。これらの応用例を通じて、ウィンドウ関数と集計関数を組み合わせることで、より深いデータ分析が可能になることがわかります。
パフォーマンス改善のためのウィンドウ関数活用
BigQueryにおけるウィンドウ関数の活用は、クエリのパフォーマンスを大幅に向上させるための重要な手段です。特に、サブクエリや自己結合を多用する複雑なクエリにおいて、ウィンドウ関数はコードの簡潔化と実行速度の改善に貢献します。
サブクエリのウィンドウ関数による代替
サブクエリは、SQLクエリ内で別のクエリを実行する手法であり、複雑なデータ操作を実現するために広く利用されます。しかし、サブクエリは実行計画が複雑になりやすく、特に大規模データセットを扱うBigQueryにおいては、パフォーマンスのボトルネックとなることがあります。ウィンドウ関数を用いることで、サブクエリを書き換えることが可能です。
たとえば、各カテゴリ内で最も売れた商品の情報を取得するケースを考えてみましょう。サブクエリを使用した場合、カテゴリごとに最大売上を計算し、その結果と元のテーブルを結合する必要があります。一方、ウィンドウ関数を使うと、RANK()関数とPARTITION BY句を組み合わせることで、各カテゴリ内の順位を計算し、順位が1位の商品を抽出できます。これにより、クエリが大幅に簡略化され、実行速度が向上します。
具体的には、以下のクエリはサブクエリを使った例です。
SELECT
category,
product,
sales
FROM
(SELECT
category,
product,
sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank
FROM
sales_table)
WHERE rank = 1;
このクエリをウィンドウ関数で代替することで、クエリの可読性が向上し、BigQueryのクエリ最適化エンジンがより効率的に処理できるようになります。
自己結合のウィンドウ関数による代替
自己結合は、同一テーブル内でレコードを結合する操作であり、時間経過に伴うデータの変化や、階層構造を持つデータを扱う際に頻繁に用いられます。しかし、自己結合は計算コストが高く、特に大規模なデータセットではクエリの実行時間が著しく長くなることがあります。ウィンドウ関数を使用することで、自己結合を回避し、より効率的なクエリを実現できます。
例えば、顧客の購買履歴データがあり、各顧客の前回の購入からの経過日数を計算したい場合を考えます。自己結合を用いると、各購入レコードに対して、同じ顧客の直前の購入レコードを結合する必要があります。一方、ウィンドウ関数LAG()を使用すると、PARTITION BY句で顧客ごとにデータを分割し、ORDER BY句で購入日時の順に並べることで、各購入レコードに対して直前の購入日時を簡単に取得できます。これにより、自己結合を行うことなく、必要な情報を効率的に取得できます。
具体的なクエリ例は以下の通りです。
SELECT
customer_id,
purchase_date,
LAG(purchase_date, 1, NULL) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS previous_purchase_date,
DATE_DIFF(purchase_date, LAG(purchase_date, 1, NULL) OVER (PARTITION BY customer_id ORDER BY purchase_date), DAY) AS days_since_last_purchase
FROM
purchase_history;
このクエリでは、LAG()関数を使って各顧客の直前の購入日を取得し、DATE_DIFF()関数で前回の購入からの経過日数を計算しています。自己結合を使用するよりも、クエリが簡潔になり、実行時間も短縮されます。
ウィンドウ関数利用時の注意点
ウィンドウ関数は非常に強力なツールですが、利用時にはいくつかの注意点があります。特に、データ量が非常に多い場合、ウィンドウ関数の計算コストが無視できなくなることがあります。
このような場合、BigQueryのクエリ最適化機能を活用し、適切なインデックスを使用することが重要です。具体的には、PARTITION BY句で使用するカラムや、ORDER BY句で使用するカラムにインデックスを設定することで、クエリのパフォーマンスを大幅に改善できます。
また、ウィンドウ関数内で使用する集約関数もパフォーマンスに影響を与える可能性があります。例えば、COUNT(DISTINCT column)のような関数は、大量のデータを処理する際に計算コストが高くなることがあります。このような場合は、近似集約関数であるAPPROX_COUNT_DISTINCT(column)を使用することを検討してください。近似集約関数は、正確な値を返すわけではありませんが、計算コストが低く、大規模データセットに対して高速に処理できます。
さらに、ウィンドウ関数を使用する際には、クエリの実行プランを注意深く確認し、ボトルネックとなっている箇所を特定することが重要です。BigQueryのクエリ実行プランは、クエリの実行順序や使用されるリソースを表示するため、パフォーマンス改善のヒントを得るのに役立ちます。
ウィンドウ関数利用時の注意点とトラブルシューティング
BigQueryでウィンドウ関数を利用する際には、構文エラーやパフォーマンス問題、データ型変換のエラーなど、いくつかの注意点があります。これらの問題に対するトラブルシューティングの方法を解説します。
構文エラーの特定と修正
BigQueryでウィンドウ関数を使用する際、構文エラーはよく発生します。ここでは、一般的な構文エラーの事例とその修正方法について解説します。まず、OVER句の書き間違いによるエラーがあります。例えば、SELECT SUM(revenue) as revenue_total OVER (ORDER BY amount DESC) FROM sample_data;というクエリは、Invalid OVER clauseというエラーメッセージを表示します。
これは、OVER句に必要な関数名が不足しているためです。正しくは、SELECT SUM(revenue) OVER (ORDER BY amount DESC) AS revenue_total FROM sample_data;のように、関数名をSUM(revenue)として指定する必要があります。
次に、PARTITION BY句の指定ミスによるエラーがあります。例えば、SELECT dept_code, SUM(sales_amt) AS DEPT_AMT FROM sample_data WHERE dept_code = 'D001' GROUP BY sales_amt;というクエリは、Syntax error on line x: Unexpected keyword GROUPというエラーメッセージを表示します。
これは、PARTITION BY句をGROUP BY句と混同していることが原因です。ウィンドウ関数にはPARTITION BY句が含まれ、GROUP BY句は集約後の結果に適用されます。正しくは、SELECT dept_code, SUM(sales_amt) OVER(PARTITION BY dept_code) AS DEPT_AMT FROM sample_data;のように記述します。
また、集計関数の誤用によるエラーも存在します。例えば、SELECT SUM(user) as total_users FROM sample_data;というクエリは、Invalid function referenceというエラーメッセージを表示します。集計関数が適用される前に、必要なGROUP BY句を追加する必要があります。
エラーメッセージを注意深く読み、構文の誤りを特定し、修正することが重要です。
パフォーマンス問題の診断と改善
BigQueryでウィンドウ関数を使用する際、データ量が多い場合にパフォーマンスが劣化することがあります。大規模なデータセットを処理する場合、ウィンドウ関数のコストが増大し、クエリの実行時間が長くなることがあります。例えば、大規模なデータセットを扱うクエリの場合、実行時間が2時間以上かかることがあります。
これは、大規模なデータセットをそのまま処理することで要する計算リソースが多く、結果としてパフォーマンスが低下したことを示しています。この問題を改善するためには、データセットを小規模なセットに分割し、それぞれに分けてウィンドウ関数を適用することが有効です。
また、データのパーティショニングが不適切な場合もパフォーマンスが低下します。データが適切にパーティションドされていないと、特定のデータ要素を効率的に取得することが困難になります。例えば、売上データを地域ごとに集約したい場合でも、適切なパーティショニングがされていないと、地域ごとのデータを効率的に取得できないため、処理時間が長くなります。
この問題を解決するためには、データを適切にパーティションドすることで、特定のデータ要素を効率的に取得することが可能になります。
さらに、BigQueryのシステムテーブル報告を使い、ジョブの可視化を行うことで、パフォーマンスの悪い箇所を発見することができます。クエリ結果の実行グラフを利用してパフォーマンスの悪い部分を診断し、改善策を実施することが推奨されます。
データ型変換のエラー
BigQueryでウィンドウ関数を使用する際、データ型の不一致によるエラーが発生することがあります。例えば、文字列型のデータを数値型の関数に適用しようとした場合や、異なるデータ型のカラム間で計算を行おうとした場合にエラーが発生します。
このようなエラーを解決するためには、CAST関数やSAFE_CAST関数を使用して、データ型を適切に変換する必要があります。CAST関数は、データ型を強制的に変換しますが、変換できない場合にはエラーが発生します。一方、SAFE_CAST関数は、データ型を変換できない場合にはNULL値を返します。
例えば、SELECT CAST(sales_amount AS INT64) FROM sales_data;というクエリは、sales_amountカラムの値を整数型に変換します。もし、sales_amountカラムに変換できない値が含まれている場合、エラーが発生します。
このような場合には、SELECT SAFE_CAST(sales_amount AS INT64) FROM sales_data;というクエリを使用することで、エラーを回避することができます。
また、データ型変換を行う際には、変換後のデータ型が適切であるかを確認することが重要です。例えば、小数点以下の値を持つ数値を整数型に変換すると、小数点以下の値が切り捨てられるため、意図しない結果になることがあります。
データ型変換を行う際には、データの特性を理解し、適切なデータ型を選択することが重要です。
おわりに
この記事では、BigQueryのウィンドウ関数の基本から応用までを解説しました。ウィンドウ関数を使いこなすことで、より高度なデータ分析が可能になります。
もしデータ活用にお困りでしたら、Hakkyのデータ活用支援サービスをご検討ください。お客様のデータ分析を効率化し、データドリブンな意思決定をサポートします。
