GitHub Copilotで効率的にSQLを書くコツ

Rettyプロダクトマネージャーの松田です。

プロダクトの現状把握や施策効果の分析など、さまざまなタイミングでBigQueryのSQLを書くことがあります。

Rettyでは昨年末にGitHub Copilotを導入したので、それに合わせてSQLの作成にもGitHub Copilotを使い始めました。

使いたいテーブルが偏っていたりテーブルの設計が似ているものが多く毎回同じようなクエリを書いていましたが、GitHub Copilotの導入で体感としては半分ぐらいに作業時間を短縮できたと思います。

まだまだ不十分だと感じることもありますが、現時点でも十分に活用できているので、GitHub Copilotで楽にSQLを書くときのコツをいくつかまとめてみました。

※Visual Studio Codeと組み合わせて使う前提で書いています。（導入方法が公式から出ています）

1. GitHub Copilotを使いたいSQL
2. GitHub Copilotで楽をするコツ
- 2.1 冒頭をプロンプトとして前提条件を入力する
- 2.2 テーブル名は生成できないのでタブで開いておく
3. 生成されるSQLの具体例
4. まとめ

1. GitHub Copilotを使いたいSQL

定型的なものや人力でやるとそこそこ大変だったりする場合があるSQLは、GitHub Copilotに任せると楽になります。

具体例としていくつかを挙げてみます。

1.1 定型的な条件の指定

BigQueryにはパーティションテーブルという概念があり、_PARTITIONTIME というカラムを使って日付範囲を指定します。

_PARTITIONTIMEを使うとスキャン量が減り低コストでSQLを書けるようになるのですが、毎回同じような条件を指定することになります。

「_PARTITIONTIME between '2023-11-30' and '2023-12-31'」のような条件を書きますが、もう何度同じクエリを書いたのかわからないほどです。

1.2 定型的な計算処理

SEOに関わっていることが多く、その過程でGoogle Search Consoleからエクスポートしたデータを扱うのですが、平均順位の計算でも毎回同じクエリを書いています。

公式ドキュメントにもある通り、「( sum(sum_top_position) / sum(impressions) ) + 1.0 AS avg_position」で平均順位が計算できます。

これも毎回変わらない割に () が多くてややこしいので間違いがちです。

1.3 SQLならではの複雑な処理

row_number() で特定カラムに番号を振って上からN件だけ使うようにする、一度に計算できないので複数回に分けて集計関数を実行するなど、慣れてくるとパターンとして認識できるものの、知らないと書くのが難しいSQLがあります。

この手のやつは間違えるとSQLは正しいのに想定したものが出てこないという状況になり、時間が溶けます。

2. GitHub Copilotで楽をするコツ

試行錯誤しながらではありますが、いくつか見えてきたものがあります。

こちらも具体例を挙げてみます。

2.1 冒頭をプロンプトとして前提条件を入力する

SQLの先頭にコメントで前提条件を入力しておくと、それを加味したものが生成される可能性が高くなります。

E(Extract), T(Transform), L(Load)に対応させたコメントを書いておくと効果的でした。

どの情報を使って、どのように加工し、最終的に得たい結果は何か、をコメントで書いておくと狙い通りのWHEREが生成される可能性が上がりました。

2.2 テーブル名は生成できないのでタブで開いておく

いろいろ試したのですがテーブル名まで加味して生成するのは難しく、過去に作ったSQLを残しておいて別タブで開いておくのが良いという結論に至っています。（GitHub Copilotはタブで開いているコードをもとに生成してくれます）

Rettyではある程度データ基盤が整っているので、データの抽出に複雑なJOINが必要になることは稀ですが、アプリケーションで使っているDBの形に近ければ近いほど楽ができそうです。

3. 生成されるSQLの具体例

実際にどのようなSQLを生成できるのか、簡単なものですが具体例を挙げてみます。

Google Search Consoleからエクスポートしたテーブルからデータを出すSQLを、GitHub Copilotを使って書いてみます。

エクスポートしたデータの形式は決まったもので、Google公式ドキュメントでもテーブルの説明があります。

support.google.com

3.1 最初は手動でSQLを書く

最初は手動でSQLを書いて、GitHub Copilotに生成させるための元データを作りましょう。

これをひとつのSQLファイルとして保存しておき、隣のタブで開いておきます。

-- URL, 検索クエリごとに合計表示回数、合計クリック数、平均CTR、平均掲載順位を出す
select
    , url
    , query
    , sum(impressions) as impressions
    , sum(clicks) as clicks
    , sum(clicks) / sum(impressions) as ctr,
    , ((sum(sum_position) / sum(impressions)) + 1.0) as avg_position
from `project.dataset.searchdata_url_impression`
where
    data_date between '2023-11-01' and '2023-11-30'
    and search_type = 'WEB'
    and is_anonymized_query is false
group by 1,2

3.2 生成したいSQLを日本語で指示する

次に、出したいデータについて日本語で説明し、途中までをSQLで書きます。

-- 2023年12月の平均掲載順位が上位10位以内の検索クエリを取得する
with
dataset as (
    select
        url

3.3 GitHub Copilotに続きを生成させる

1行ずつ生成させて順番に進めていくと、以下のような結果が出来上がります。「2023年12月」と指示したことによって、WHERE句の条件が11月から12月に変更されています。また、最後に「avg_position <= 10」とあり、「上位10位以内」という日本語を反映してくれていることがわかります。

-- 2023年12月の平均掲載順位が上位10位以内の検索クエリを取得する
with
dataset as (
    select
        url
        -- ここから先はGitHub Copilotが生成しています
        , query
        , sum(impressions) as impressions
        , sum(clicks) as clicks
        , sum(clicks) / sum(impressions) as ctr,
        , ((sum(sum_position) / sum(impressions)) + 1.0) as avg_position
    from `project.dataset.searchdata_url_impression`
    where
        data_date between '2023-12-01' and '2023-12-31'
        and search_type = 'WEB'
        and is_anonymized_query is false
)

select
    *
from dataset
where avg_position <= 10