分析クエリを「速く・楽に・正確に」書くためにスニペットに登録すべきもの5選

この記事は eureka Engineering Advent Calendar 2017 – Qiita の3日目の記事です。
2日目は 臼井さん の「Go言語におけるエラーハンドリングを今一度振り返る」でした。

自己紹介

こんにちは、eurekaのBIチームでデータアナリストをしております大久保と申します。BIチームではPairsの施策の効果分析はもちろん、調査やレポートの作成など幅広い目的でデータの抽出を行なっています。

最近の趣味は統計の勉強と、業務改善ツール漁りです。

本記事の対象者について

今回はタイトルにあるとおり、手持ちのエディタでスニペットに登録すべきものをご紹介したいと思います。この記事の対象者は、分析クエリを書き始めたばかりで、分析クエリを少しでも速く・楽に・正確に書きたい人を想定しています。

ちなみに弊社で利用している分析環境の都合上、「分析クエリ」といった時に以下らのSQLを前提として話をします。ただ他のSQLに対してもおおよそ応用できる内容に記事はまとめたつもりです。

  • MySQL
  • BigQuery
  • PostgreSQL

目次

  • スニペットとは
  • スニペットはどう使うのか
  • スニペットに何を登録するのがオススメか
    • 1.予約語 / 関数
    • 2.DB名 / テーブル名 / カラム名 / テーブルスキーマ
    • 3.カラムに入る固定値
    • 4.マスタデータ
    • 5.頻繁に使う分析クエリやその一部
  • 最後に

スニペットとは

皆さんはお手持ちのエディタ等で「スニペット」機能を利用していますか?私は利用しています。利用しているおかげでクエリ作成効率が体感で2倍くらい上がっている気がします。

スニペットとは繰り返し登場する記述(クエリ含む)の断片、またはそれをすぐに呼び出すエディタの機能のことです。よく使用する記述をスニペットに登録しておけば、それを再び書いたり、昔自分が書いた記述を検索したりすることなく、使いたい時に正確な記述をサッと呼び出して使えます。「速く・楽に」書けるようになるのは勿論、一度スニペット登録しておけばスペルミスなども防げるので「正確に」書けるという観点も大きなメリットだと思います。

スニペットはどう使うのか

エディタにより違うので一概に言えませんが、例えば私の使うSublime Text(※1)だと以下の通りです。

手順:

以下内容のファイルを {ユーザー名}/Library/Application Support/Sublime Text 3/Packages/User/{ファイル名}.sublime-snippet として保存する。

<snippet>
    <content><![CDATA[
{文言}
]]></content>
    <tabTrigger>{文言}</tabTrigger>
    <scope>{文言}</scope>
</snippet>

※このファイルの書き方はこちらの記事などが参考になると思います
※[Tools]→[Developer]→[New Snippet…]からでも上記のファイルは生成できます


手順はこれだけです。既に楽と言えば楽ですが、私はこれを実施するだけでも手間に感じることがあります。(特に大量のスニペットを一気に生成したい時など)そこでこちらのようなpythonコードを書いてスニペットファイルを一気に生成しています。

調べてみるとAtomやVim等のエディタもそれなりに簡単に登録できそうですね。(主旨から逸れるので詳しくは説明はしませんが「エディタ名 スニペット」で検索するとすぐに詳しい記事が出てきます)

スニペットに何を登録するのがオススメか

今回はこれをテーマに記事を書きました。先に私が考える登録した方がよいものを列挙すると以下です。

  • 1.予約語 / 関数
  • 2.DB名 / テーブル名 / カラム名 / テーブルスキーマ
  • 3.カラムに入る固定値
  • 4.マスタデータ
  • 5.頻繁に使う分析クエリやその一部

以下でその理由と利用シーンについて順に説明していきます。

1.予約語 / 関数

クエリ書く上で避けて通れないこれらを登録しない手はないでしょう。予約語(※2)とは「SELECT」「FROM」等のクエリを形成するワードのこと、関数(※3)は「COUNT」「SUM」等の集計に使う関数のことです。

スニペットに登録しなくても、各エディタのパッケージ(Sublime TextならSQLTools等)やDataGrip等のIDEを使えばスニペットを作らなくても補完されるのではないかと思う方もいると思います。redashを導入していれば、redash上のクエリ補完機能を使っても良いかもしれませんね。

ただそれらだと以下3点のように不十分なケースがありますし、スニペット登録が簡単なので私はスニペット登録しております。

  • 言語やパッケージによって、補完されない関数がある 例:BigQueryのEXACT_COUNT_DISTINCT
  • スペースやインデントなどのコーディングルールを保てる
  • 癖のある使い方の関数は、補完時にその使い方も表示できる 例:DATEDIFF

スニペット使用例(関数)

snippet function

上記GIF画像で使われているスニペットファイルは以下です

<snippet>
    <content><![CDATA[
DATEDIFF(${2:[after|YYYY-mm-dd]}, ${1:[before|YYYY-mm-dd]})
${3:/*
[before],[after]には日付式(YYYY-MM-DD)もしくは日付時刻式(YYYY-MM-DD HH:MM:SS)を指定します。
日付時刻式を指定しても、時刻部分は無視されます。
*/}
]]></content>
    <tabTrigger>DATEDIFF</tabTrigger>
    <scope>source.sql</scope>
</snippet>

DATEDIFF関数以外にも以下の予約語 / 関数群等はスニペットにしたことで作業が特に楽になったと感じるので登録をオススメします。

BETWEEN {x} AND {y}

CASE WHEN {x} THEN {y} ELSE {z} END

INTERVAL {x} HOUR

ORDER BY {x} ASC

OVER(PARTITION BY {x} ORDER BY {y} ASC)

2.DB名 / テーブル名 / カラム名 / テーブルスキーマ

先に触れておきますと、SQLToolsやDataGrip, redashを使ってDBに接続すると、エディタ上で「DB名 / テーブル名 / カラム名 / テーブルスキーマ」の全て(又はいずれか)が補完されます。なのでこれらをスニペット登録しなくてもいいという意見を持たれる読者の方もいらっしゃるかと思います。

分析環境がMySQLやPostgreSQLのみで構成されているなら、それで全く問題ないと思います。むしろスキーマやカラム名の変更が起こるたびにスニペットの登録内容の変更をするくらいなら、そちらの方が効率がいいと思います。

ただ弊社の場合すでにお話したようにBigQueryも分析環境として使っているので、それを踏まえるとスニペット登録が必要になってくるケースがあります。(※4)

スニペット使用例(テーブルスキーマ)

snippet table schema

3.カラムに入る固定値

テーブルのカラムによっては、固定値が入ることがあると思います。例えば device というカラムがあるとき pc_browse, smartphone_browse, ios_app, android_app のいずれかが入るなど。長い固定値になるとスペルミスなどもありえるのでスニペット化してしまいましょう。

4.マスタデータ

例えば都道府県データを管理するとき、マスタテーブルにidと都道府県名等を持たせて、別テーブルからidを参照するなどよくあるテーブル構成だと思います。そうした時にどのidが何県だったか?を思い出すたびに毎回マスタテーブルを参照しに行くのは、私は面倒だと感じます。よって以下のようなスニペットを作ってしまいましょう。

スニペット使用例(マスタデータ)

snippet master data

マスタデータ補完用スニペットファイルを1つ1つ作るのは大変(都道府県であれば47ファイルも作る必要があります!)なので、こちらで行っているようにCSVファイルを読み込んで複数のスニペットファイルを同時生成するなどの工夫は必要でしょう。

5.頻繁に使う分析クエリやその一部

分析クエリと言っても様々あると思いますが、似たようなクエリを書くことは割りとよくあるのではないでしょうか。例えば私がよく書くクエリに「日毎の●●を取得する」というものがあり、以下をスニペットに登録しています。

スニペット使用例(頻繁に使う分析クエリやその一部)

snippet helpful query

最後に

以上、分析クエリを書く際にスニペットに登録すべきと思うものとして以下5種類を紹介してきましたが、いかがでしたでしょうか?

  • 1.予約語 / 関数
  • 2.DB名 / テーブル名 / カラム名 / テーブルスキーマ
  • 3.カラムに入る固定値
  • 4.マスタデータ
  • 5.頻繁に使う分析クエリやその一部

紹介したのは5種類だけでしたが、普段頻繁に入力するものについては何でもスニペットに登録してしまうのがいいでしょう。5種類というのはあくまで私が考える分類の仕方で、実際はもっと色々登録していいと思います。(こういうのも登録しておくと便利だよという声、大大大歓迎です)

またスニペット化は分析クエリを書く際に限らず、コードを書く時もそうですし、企画書のような文書を書く時も同様でしょう。普段の繰り返し業務に使う時間は少しでも減らして、よりクリエイティブな仕事に割く時間を確保するのが、自分の仕事の価値を上げるために重要だと日々感じています。同様のことを考えている方にとって参考になる部分が、本記事内に少しでもあれば幸いに思います。

明日はPairs Product UXチーム 竹内さんの 「社内システムでのClojureScript+re-frame活用事例」 です。お楽しみに!


※1. 使用環境は Mac OS X , Versionは 3.0, Build 3143

※2. 例えばMySQL5.6の予約語はこちらです

※3. 例えばMySQL5.6の関数はこちらです

※4. 調べたところ、2017/11/26現在ではBigQueryの「DB名・テーブル名・カラム名・テーブルスキーマ」を補完してくれるエディタのパッケージやIDEはありませんでした。オススメがある方はぜひ教えてください。
ちなみにBigQueryのコンソール上からなら「DB名・テーブル名・カラム名」は「tab」押下で補完してくれますね。ただ テーブルスキーマ、カラムに入る固定値、マスタデータ、頻繁に使う分析クエリやその一部 の補完ができないので、BigQueryのコンソール上でクエリを書くことは今のところ私は少ないです。

  • このエントリーをはてなブックマークに追加

エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください!皆様のエントリーをお待ちしております!

Recommend

PairsのBlue-Green-Deployment事情とゴールデンイメージの生成高速化(失敗)について

Go言語とGoogle Cloud Vision APIで画像認識