私がStandardSQLを使ってみて良いなと思ったこと

はじめまして。株式会社LITALICOでエンジニアをやっている兼近と申します。

この記事は「LITALICO Engineers Advent Calendar 2019」の20日目の記事です。

はじめ

弊社では、データの取得や分析などをするために BigQuery という外部サービスを利用しています。BigQueryは存じている人も多いため概要説明は省きます。

私は弊社のWebサービスである「LITALICO仕事ナビ」のエンジニアとして働いています。
そのチームの周りの人は LegacySQL を使う場面が多く StandardSQL を使って書いている人はあまりいませんでした。

最近BigQuery上でデータの集計をすることが多く、LegacySQLで長いSQL文を書いたりしていました。

そうすると、隣の席にいるひとつ下の代のエンジニアに「LagacySQLよりStandardSQLのほうがいいっすよ」とか「LegacySQL使ってるとおばさんって呼びますよ（笑）」とか言われたので、そんなに良いもんなのか、じゃあちょっとStandardSQL書いてみようかと思ったのがきっかけです。

なのでこの記事では、StandardSQLを使ってみて使いやすいと感じたので、私が感じた利点をつらつらと書いていこうと思います。

WITH句便利じゃん

よく聞いたこともあるかと思うのですが、WITH句を使って書くと読みやすさが圧倒的に違うと感じました。

複雑な分析をするために長いSQLを書いたときには、その読みやすさがより感じられます。

以下のような、日ごとのバナーのクリック数のテーブルとどのバナーから問い合わせしたかがわかるテーブルの2つがあるとします。​

click_banner_table
| date |　bannar_a_count | bannar_b_count |
| -- | -- | -- |
| 2019/12/19 | 70 | 20 | 
| 2019/12/20 | 90 | 23 |
.
.
.

banner_contact_submissions_table

| date | type |
| -- | -- |
| 2019/12/19 | バナーA |
| 2019/12/19 | バナーB |
| 2019/12/19 | バナーA |
| 2019/12/19 | バナーA |
.
.
.

そこで、それぞれのバナーがどのくらいクリックされ、そこから何件問い合わせにつながったのかを集計したいとします。​​

それぞれをLegacySQLとStandardSQLで書いてみると以下になります。

#legacySQL
SELECT
 click_banner_a.date AS date,
 banner_a_count,
 banner_a_contact_count,
 banner_b_count,
 banner_b_contact_count
FROM (
 SELECT
   date,
   banner_a_count,
   banner_b_count
 FROM
   click_banner) AS click_banner_a
LEFT JOIN (
 SELECT
   date,
   COUNT(*) AS banner_a_contact_count
 FROM
  banner_contact_submissions
 WHERE
   type = 'バナーA'
 GROUP BY
   date) AS contact_count_to_type_a_banner
ON
 contact_count_to_type_a_banner.date = click_banner_a.date
LEFT JOIN (
 SELECT
   date,
   COUNT(*) AS banner_b_contact_count
 FROM
   banner_contact_submissions
 WHERE
   type = 'バナーB'
 GROUP BY
   date) AS contact_count_to_type_b_banner
ON
  contact_count_to_type_b_banner.date = click_banner_b.date

#standardSQL
WITH
 banner_click_count AS (
 SELECT
   date,
   banner_a_count,
   banner_b_count
 FROM
   click_banner AS click_banner_a),
 contact_count_to_type_a_banner AS (
 SELECT
   date,
   COUNT(*) AS banner_a_contact_count
 FROM
   banner_contact_submissions
 WHERE
   type = 'バナーA'
 GROUP BY
   date),
contact_count_to_type_b_banner AS (
 SELECT
   date,
   COUNT(*) AS banner_b_contact_count
 FROM
   banner_contact_submissions
 WHERE
   type = 'バナーB'
 GROUP BY
   date)
SELECT
contact_count_to_type_a_banner.date AS date,
 banner_a_inquiry_count,
 banner_b_inquiry_count
FROM
 banner_click__count
JOIN
 inquiry_count_to_type_a
ON
contact_count_to_type_a_banner.date = banner_click_count.date
JOIN
 inquiry_count_to_type_b
ON
contact_count_to_type_b_banner.date = banner_click_count.date

上記のように、WITH句でかくと下部のSELECT文を見てJOIN先のテーブルにあるサブクエリの情報を確認しに行くという手順がふめるので構造的に読みやすいです。WITH句を使わないとSELECTで取得しているデータがどこか、ネストしている様々なサブクエリから順々にみたりしないと行けないため読む気になれません。

とにかくネストが重なりすぎなかったり、サブクエリに別名を付けていつでも取得できたりするため利便性も良いので、WITH句って読みやすいですね。

もっとデータの取得が複雑になったときはどこでどういうデータを取得しているのか、何がどうなっているのかが分かりづらいのですが、StandardSQLでWITH句を使っていると比較的読みやすくなります。また、クエリを書いていない人も読む気持ちになってくれやすくなります（笑）

処理速度が早い

Quickest way to faster performance?
Use StandaedSQL!!!

Query Optimization in BigQuery

パフォーマンスを高速化するにはまずStandardSQLを使えと言っているように、レスポンスが返ってくるまでが体感でわかるくらい違います、是非試してもらいたいです。

私がStandardSQLを書き始めたときにおや？と思ったように、様々なテーブルを取得したり参照するような複雑なSQLほど、StandardSQLで書いたほうがパフォーマンスが良いといえます。

COUNT(DISTINCT X) で正確なユニークカウント数を返してくれる

UU数など集計したいときにCOUNT(DISTINCT X)を使用することは多いのかなと思います。LegacySQLのCOUNT(DISTINCT X)で集計すると統計的なデータが返ります。もしもLegacySQLで正確な集計データが必要ならば、EXACT_COUNT_DISTINCT関数を使用しなければなりません。まあこれは慣れればいいのですが、私は「あ、COUNT(DISTINCT X)は統計的なデータか」と考えることが必要なのでちょっと面倒くさいなと思ってました。

DISTINCT キーワードを使用した場合、この関数は指定したフィールドの一意の値の数を返します。ただし、DISTINCT を指定したときに返される値は統計的近似値であり、正確な値であるとは限りません。

LegacySQL COUNT([DISTINCT])

ですがStandardSQLのCOUNT(DISTINCT X)で集計すると、重複しないXの正確なカウント数をデータを集計してくれます。私は結構嬉しかったです。

まとめ

今までで述べたこと以外にも、TimeZoneが使えるようになったとか色々とあると思うのですが、私が便利だと思ったことを勝手に書かせていただきました。今回、StandardSQLの利点について話しましたが、今まで書いたLegacySQLでのクエリを書き直したほうが良いぞとかそこまでは思っていません。ですが、この記事で今後StandardSQLで書いてみようかな、便利だなと思っていただければ嬉しい限りです。ここまで読んでいただいてありがとうございました。

明日は@yuyaichihashi さんの「トラブルシューティングのための思考のヒント」です。お楽しみに！