唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryのStandard SQLの便利な機能とLegacy SQLからの変更点をいくつか紹介

勉強会で発表したので内容を適当にまとめてブログ記事にしておく

いつの間にかリファレンスが日本語化されているので、全部一読したい人はそちらを読んだほうがよいかも
SQL Reference  |  BigQuery Documentation  |  Google Cloud Platform
ちなみに日本語版のリファレンスだと最近の変更点が載っていない場合があるので、最新の情報を知りたい場合は英語版を見た方がよいです(URLに?hl=enを足してアクセスすればよい(?))

以下目次

個人的に便利になったと思うのはJOINの条件制限の緩和、WITH、サブクエリを使える場所の制限の緩和、タイムゾーンが使えるようになったことです

BigQueryで使える2つのSQL

BigQueryでテーブルのデータを取得するときには以下の2つのSQLが使えます

Legacy SQL
元々BigQueryで使えたSQLで、Legacyという名前ですが今でもデフォルトはこれです
Standard SQL
SQL標準の仕様に沿っているらしい新しく使えるようになったSQLです。Standard SQLを使いたいときはオプションを指定したり、Webのコンソールの場合は1行目に#standardSQL的な行を書けばよいです

f:id:sucrose:20170625235506p:plain

Standard SQLを使うべき?

Standard SQLはLegacy SQLと比べるとこんな人に向いています

  • サブクエリやJOINを駆使した複雑なクエリを書く
  • 他のDBでSQLに触ったことがあるがBigQueryのLegacy SQLには触ったことがない
  • ドキュメントが比較的詳細な方を使いたい(Legacy SQLではLIKEやOFFSETが使えますがドキュメントには説明を見つけられませんでした)
  • 新しい機能を使うのが好き

また以下のQiitaの記事によると「Standard SQLの方が高速で、新機能も追加されていく」ということらしいです
qiita.com

便利な機能や変更点

以下適当に列挙していきます
スライドからコピペしてきたので、ものによっては動かないかも(?)

テーブル名の参照

普通のSQLっぽくなりました
[]``は省略できるので囲まずにプロジェクト名も省略すれば両方に対応できます

Legacy SQL Standard SQL
[プロジェクト名:データセット名.テーブル名] `プロジェクト名.データセット名.テーブル名`

COUNT(DISTINCT value)

Legacy SQL Standard SQL
EXACT_COUNT_DISTINCT(value) COUNT(DISTINCT value

Legacy SQLのCOUNT(DISTINCT value)は統計的な予測値を返すので、正確な値が欲しいときはEXACT_COUNT_DISTINCT(value)を使わないといけないという紛らわしさがあったのですがStandard SQLで解消されました

SELECT テーブル名.*

JOINしたときにSELECT テーブル名.*で特定のテーブル名のカラムだけ取ってこれます

SELECT * EXCEPT (カラム名)

SELECT * EXCEPT (カラム名)で特定のカラムだけ除外してその他のカラムを取得できます

複数のREPEATED型のカラムを持つテーブルのSELECT *

またSELECT *をするテーブルがREPEATED型(ARRAY型)のカラムを複数持っているとLegacy SQLではエラーになってクエリを実行できなかったのですが、Standard SQLでは実行できるようになりました

SELECTのカラム名などの後FROMの前の,の禁止

使いづらくなった点としてはStandard SQLではSELECTのカラム名などの後FROMの前に,があるとエラーになるようになってしまいました

JOINしたテーブルのカラムをSELECTした時に出力されるカラム名の変更

またSELECT テーブル名.dateのようなカラムをSELECTした場合、Legacy SQLでは結果としてテーブル名_dateというカラム名に解釈されていたのですが、Standard SQLではdateと解釈されてしまうようになったので、同名のカラムを複数SELECTする場合には明示的に別のエイリアスを指定しないといけなくなりました

JOINの条件にだいたいなんでも書けるようになった

Legacy SQLだと以下のようにカラム名の一致の条件しか書けなかった

  • ON テーブルの別名1.id = テーブルの別名2.id

Standard SQLだとだいたいなんでも書けるようになっています

  • ON テーブルの別名1.id = テーブルの別名2.id
  • ON テーブル名1.id = テーブル名2.id
  • USING (id)
  • ON テーブルの別名1.id < テーブルの別名2.id
  • ON テーブル名1.id + 100 = テーブル名2.id

複数のテーブルをUNION ALLするときの書き方

Legacy SQLだとテーブル名を,でつなげるだけで書けます

  • FROM テーブル1, テーブル2

Standard SQLだとちゃんとUNION ALLを書かないといけなくなってしまいました

  • FROM (SELECT * FROM テーブル1 UNION ALL SELECT * FROM テーブル2)

日付ごとに分けられているテーブルを日付の範囲を指定して取ってくる

BigQueryではテーブルを日付ごとに分けるのがよく行われている。テーブル名は「テーブル名の先頭部分+日付」の形式にするのが多い
Legacy SQLだとTABLE_DATE_RANGE関数などを使う
FROM TABLE_DATE_RANGE([テーブル名の先頭部分], TIMESTAMP('2017-06-23'),TIMESTAMP('2017-06-24'))
Standard SQLだと*で指定した部分が_TABLE_SUFFIXに入るのでWHEREで条件を指定できる
FROM `テーブル名のプレフィクス*` WHERE _TABLE_SUFFIX BETWEEN '20170623' AND '20170624'

WITH

Standard SQLではWITHを使ってサブクエリに名前を付けて使いまわすことができます
これを使うことで複雑なクエリの場合に、何層もサブクエリを使っても多少わかりやすくなったり、共通部分をWITHでまとめたりできます
ちなみにBigQueryのWITHでは引数を与えたり再帰はできないです

↓あまり意味のないWITHを使ったクエリの例

#standardSQL
WITH data AS (SELECT 1 AS x, 2 AS y UNION ALL SELECT 3 AS x, 4 AS y)

SELECT
  SUM(x) AS sum_x,
  SUM(y) AS sum_y
FROM data

サブクエリ

Legacy SQLではFROMの中にしかサブクエリが書けませんでしたが、Standard SQLではいろいろなところに書けるようになりました
そのおかげでORDER BY RAND()みたいなこともできるようになってます
更にサブクエリが外側の値に依存している相関サブクエリも書けるようになりました

日付の型

Legacy SQLではTIMESTAMP型しかなかったのですが、Standard SQLではTIME型、DATETIME型、DATE型が加わりました
TIMESTAMP型はUTCの標準時の日時を表していて、その他はローカルの日時を表しています

タイムゾーン

Standard SQLではタイムゾーンが使えるようになったので以下のように書けるようになりました(Legacy SQLだと日本時間との間の変換で9時間ずらすことを考えないといけなかった)

  • TIMESTAMP('2017-06-24 00:00:00', 'Asia/Tokyo')
  • TIMESTAMP('2017-06-24 00:00:00+09')

日時などを取り出すときも以下のようにタイムゾーンを指定すれば9時間ずらすことをあまり考えずに日時を扱えます

  • EXTRACT(MONTH FROM timestamp AT TIME ZONE 'Asia/Tokyo')
  • DATE(timestamp, 'Asia/Tokyo')
  • STRING(timestamp, 'Asia/Tokyo')

キャスト

Legacy SQLでは以下のような感じでした

  • FROAT('2.0')
  • INTEGER(1.5)

Standard SQLではCAST関数などを使います

  • CAST('2.0' AS FLOAT64)
  • CAST(1.5 AS INT64)

ちなみにStandard SQLの浮動小数のキャストCAST(1.5 AS INT64)は切り捨てではなく四捨五入されるので注意(-1.5の場合は-2)

UDF

このブログで何回か触れていますが、JavaScriptやSQLで関数を定義して呼べるので便利です
Legacy SQLでもStandard SQLでも使えますが、Standard SQLになってちょっと使いやすくなりました
BigQueryで配列に添字をつける(複数の配列をzipする) - 唯物是真 @Scaled_Wurm
BigQueryで単語分割がしたい - 唯物是真 @Scaled_Wurm
BigQueryのWebコンソールでJSONを整形して出力(pretty print) - 唯物是真 @Scaled_Wurm

新規ユーザーのLTVを既存ユーザーの全体の解約率の逆数で計算するのは不適切?

LTV(lifetime value)という顧客(ユーザー)が将来的に使う金額を予測しようという話があります
前に以下の記事でも書きましたが、月額課金制のサービスだと粗い推定として解約率を一定とみなして解約率の逆数を平均継続期間としてLTVを計算したりしています
sucrose.hatenablog.com

LTVがわかれば、例えばLTVが5000円のユーザーを獲得するのにコストとして3000円をかけるとユーザーを一人獲得するごとに2000円ずつ利益が出る、と言った計算ができます

新規ユーザーのLTVを既存ユーザー全体の解約率の逆数で計算しているのを見かけて「新規ユーザーの解約率って既存ユーザー全体の解約率とは全然合わないのでは?」と思ったので簡単に計算してみました(先月以前に契約した解約率の低いユーザーが残っているので新規ユーザーの解約率よりも全体の解約率は下がるはず?)

シミュレーション

設定

極端な例として、毎月以下の200人のユーザーが新規に月額1000円の課金制のサービスに契約するとしてシミュレーションしてみます

  • 月ごとの解約率が10%のユーザー100人
  • 月ごとの解約率が50%のユーザー100人

解約は必ず月末に発生し、ある月に契約したユーザーの解約は契約したその月から発生するとします

月ごとの解約率が10%の新規ユーザーの解約率の逆数でLTVを求めると\(1000 \times \frac{1}{0.1} = 10000\)
月ごとの解約率が50%の新規ユーザーの解約率の逆数でLTVを求めると\(1000 \times \frac{1}{0.5} = 2000\)
上記の2群100人ずつの平均のLTVは\(\frac{10000 \times 100 + 2000 \times 100}{200} = 6000\)となります

ちなみに上の方にも貼った以下の記事に書いたように全体の解約率の逆数からLTVを計算すると個々のユーザーの解約率の逆数から計算したLTVの平均とは全然違う値になるので注意が必要かもしれません(?)

今回の場合、解約率の平均は\(\frac{0.1 \times 100 + 0.5 \times 100}{200} = 0.3\)なので、解約率の逆数でLTVを求めると\(1000 \times \frac{1}{0.3} \approx 3333\)

結果

上記の条件でシミュレーションを動かして、月ごとのユーザーの人数、解約率、LTVについて計算してグラフを書いてみました(60ヶ月まで10000回の平均)
解約率(churn rate?)の定義は解約したユーザー数を分母で割るというものですが、分母となるユーザー数は月の途中で変わってしまうので、分母を単純に月末にいたユーザー数にしたり、月初と月末のユーザー数の平均にしたりいろいろなバリエーションがあります
計算が楽なので、今回は月末にいたユーザー数(その月に解約したユーザーも含む)を分母とします

グラフ

ユーザー数は月が経過すると、解約率ごとに一定の値に収束しているのがわかる(その月に解約した人数は含んでいません)
f:id:sucrose:20170624201653p:plain

解約率は初月の新規ユーザーしかいないときの値からどんどん下がっていってある値に収束している
f:id:sucrose:20170624202339p:plain

全体の解約率から計算したLTVと各ユーザーの解約率から計算したLTVの平均もグラフにした
どちらも初月の新規ユーザーしかいないときの値よりも増加している
f:id:sucrose:20170624222719p:plain

以上のように毎月の新規ユーザーのLTVを固定してシミュレーションしても、ユーザー全体のLTVはその値とは違ってくる
おもしろいことに、収束したときの全体の解約率から計算したLTVと、新規ユーザーのそれぞれの解約率の逆数を使って計算したLTVの平均は一致しているように見える

数式的な話

ある月に残っているユーザー数は、1ヶ月目のユーザー、2ヶ月目のユーザー、……となるので、解約率と毎月契約する人数を一定とした場合、幾何級数の総和でだいたいの値が計算できます
つまり解約率\(p\)のユーザーが1ヶ月目からnヶ月目までいるときのそれらのユーザーの人数の総和は元の人数の\((1-p)\frac{1 - (1-p)^n}{1-(1-p)}\)倍となり、\(n\)が無限大になったときに収束するのは\(\frac{1 - p}{p}\)倍となります
試しに\(p=\frac{1}{10}\)を入れると\(\frac{1 - 0.1}{0.1}=9\)倍となり、上のシミュレーションの結果と一致します

この結果を使うと解約率やLTVの収束する値も計算できます

収束したときの解約率は以下のようになる
$$p = \frac{\mathrm{解約率が0.1のユーザー数} \times 0.1 + \mathrm{解約率が0.5のユーザー数 \times 0.5}}{\mathrm{解約率が0.1のユーザー数} + \mathrm{解約率が0.5のユーザー数}}$$ 今回使った解約率の定義では上のユーザー数の式とは違ってその月に解約した人数も含める必要があるので初項は最初の人数の\(1-p\)倍ではなく\(1\)倍になる。すなわち\(n\)が極大になったときに収束するユーザー数は元の\(\frac{1}{p}\)倍である
この結果を使うと以下のようになりシミュレーションの結果とほぼ一致する
$$\frac{\frac{1}{0.1} \times 100 \times 0.1 + \frac{1}{0.5} \times 100 \times 0.5}{\frac{1}{0.1} \times 100 + \frac{1}{0.5} \times 100} = \frac{100 + 100}{\frac{1}{0.1} \times 100 + \frac{1}{0.5}\times 100} \approx 0.17$$ ちなみに上の式は毎月の新規ユーザーごとの解約率の調和平均の形になっている

LTVは解約率の逆数に月ごとの金額をかければいいので収束したときのLTVの計算は上の結果を使えばよく、以下のようになる
$$1000 \times \frac{\frac{1}{0.1} \times 100 + \frac{1}{0.5}\times 100}{100 + 100} = 6000$$

この数値は新規ユーザーだけ見たときの、ユーザーごとの解約率の逆数を使って計算したLTVの平均と一致している
$$\frac{1000 \times \frac{1}{0.1} \times 100 + 1000 \times \frac{1}{0.5} \times 100}{100 + 100} = 6000$$
もう一度グラフをみると、青い線の最初と緑の線の最後が一致しているのがわかる
f:id:sucrose:20170624222719p:plain

この性質が群が増えても同じような感じになるのか確かめるために解約率0.1から1.0までの10個のグループに対してシミュレーションを行いました
(グループ数以外は同様の条件で、毎月100人ずつ新規ユーザーを追加しています)
すると同様に収束した全体の解約率から計算したLTVと新規ユーザーの個々のLTVの平均がほぼ一致する結果が得られました(数式的にそうなっているので当然の結果かも)
f:id:sucrose:20170624224414p:plain
f:id:sucrose:20170624224437p:plain

まとめ

というわけで当然といえば当然ですが、シミュレーションで新規ユーザーの解約率が一定でも全体の解約率は新規ユーザーの解約率とは全然違う値になることがわかりました
つまり全体の解約率からLTVを計算しても新規ユーザーのLTVの推定としてはあまりよくないのかもと思うのですが、毎月同質の新規ユーザーが来るという仮定のもとでは収束するまで時間が経てば全体の解約率から計算したLTVと新規ユーザーのそれぞれのLTVを平均したものが同じ結果になるので、本当に求めたいものなのかはよくわかりませんが推定としてはそれなりによいものが出てくるのかもしれません(?)(そもそも解約率などが一定という仮定がどうなのかという話はさておき)

LTVの計算や統計についてそれほど詳しいわけではないので、誤りや知っておいた方がよい情報などあればコメントしていただけると嬉しいです

参考

Customer lifetime value - Wikipedia
Wikipediaを見るともう少し頭を使った(?)LTVの計算がいろいろあるみたいです

ソースコード

# coding: utf-8
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
from future_builtins import *

import random
import numpy as np
import seaborn as sns
import matplotlib.font_manager

#確率の異なるn個の群のユーザーを仮定
#それぞれ毎月per_month人ごと増えていく
prob = [0.1, 0.5]
per_month = [100, 100]
#prob = [0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]
#per_month = [100] * len(prob)

profit = 1000

stats_user = []
stats_ratio = []
stats_ltv = []

for i in xrange(1000):
    stats_user.append([])
    stats_ratio.append([])
    stats_ltv.append([])
    
    users = [0] * len(prob)
    
    for j in xrange(60):
        denominator = sum(users) + sum(per_month)
        
        for u in xrange(len(users)):
            users[u] += per_month[u]
        
        decrease = [0] * len(users)
        
        for u in xrange(len(users)):
            for v in xrange(users[u]):
                if random.random() < prob[u]:
                    decrease[u] += 1
        
        for u in xrange(len(users)):
            users[u] -= decrease[u]
        
        stats_user[i].append([sum(users)] + users)
        
        ratio = sum(decrease) / denominator
        stats_ratio[i].append(ratio)
        
        ltv = []
        for u in xrange(len(users)):
            ltv.append(profit / prob[u])
        average_ltv = 0
        for u in xrange(len(users)):
            average_ltv += ltv[u] * (users[u] + decrease[u])
        average_ltv /= (sum(users) + sum(decrease))
        stats_ltv[i].append([average_ltv, profit / ratio])

stats_user = np.array(stats_user)
print(stats_user.mean(axis=0))
stats_ratio = np.array(stats_ratio)
print(stats_ratio.mean(axis=0))
stats_ltv = np.array(stats_ltv)
print(stats_ltv.mean(axis=0))

prop = matplotlib.font_manager.FontProperties(fname=r'C:\Windows\Fonts\meiryo.ttc', size=12)

sns.plt.title('月ごとのユーザー数', fontproperties=prop)
sns.plt.xlabel('月', fontproperties=prop)
sns.plt.ylabel('ユーザー数', fontproperties=prop)
sns.plt.plot(stats_user.mean(axis=0))
sns.plt.legend(['合計'] + ['解約率$p={}$のユーザー'.format(p) for p in prob], prop=prop)
sns.plt.show()

sns.plt.title('月ごとの全体の解約率', fontproperties=prop)
sns.plt.xlabel('月', fontproperties=prop)
sns.plt.ylabel('解約率', fontproperties=prop)
sns.plt.plot(stats_ratio.mean(axis=0))
sns.plt.show()

sns.plt.title('月ごとのLTV', fontproperties=prop)
sns.plt.xlabel('月', fontproperties=prop)
sns.plt.ylabel('LTV', fontproperties=prop)
sns.plt.ylim(0, 10000)
sns.plt.plot(stats_ltv.mean(axis=0))
sns.plt.legend(['ユーザーそれぞれの解約率の逆数で計算したLTVの平均', '全体の解約率の逆数を使って計算したLTV'], prop=prop)
sns.plt.show()