データ分析部インターン生の小川です。インターンでは主に動画収集のロジック実装に取り組んでいました。 Gunosyではログの管理にRedshiftとBigQueryを使用しています。 サービスはAWS上で動いているものも多いので基本はRedshiftで、ログの量が多いものやアドホック分析に用いるものはBigQueryに格納しています。 この2つのサービスでSQLの書き方が微妙に異なるところがあり、もどかしい経験をしたので、今回は、よく使うSQLの文法でRedshiftとBigQueryで表現が異なる所をまとめてみようと思います。
BigQueryの導入についてはこちらの記事をご覧ください。 また、この記事ではBigQueryはStandard SQLで記述していきます。
日付・時刻関数
現在時刻(UTC)
#BigQuery SELECT CURRENT_DATETIME #Redshift SELECT SYSDATE -> 2018-01-27 23:00
BigQueryとRedshiftで表記が異なることがわかります。 現在時刻がタイムスタンプ型で返ってきますが、タイムゾーンがデフォルトのUTC(世界標準時)であるため日本時間より-9時間されています。 次の例では現在時刻をJST(日本時間)で取得します。
現在時刻(JST)
#BigQuery SELECT CURRENT_DATETIME('Asia/Tokyo') #Redshift SELECT CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE) -> 2018-01-28 08:00
BigQueryでは先ほどのCURRENT_DATETIME関数にタイムゾーンを指定することができます。 しかしRedshiftでは指定することができないため、CONVERT_TIMEZONE関数を使ってタイムゾーンをJSTに変換します。
現在の日付(UTC)
#BigQuery SELECT CURRENT_DATE #Redshift SELECT CURRENT_DATE -> 2018-01-27
現在の日時を取得する際はどちらも同じ表記です。 しかしタイムゾーンがUTCなので、次の例ではJSTで取得します。
現在の日付(JST)
#BigQuery SELECT CURRENT_DATE('Asia/Tokyo') #Redshift SELECT TRUNC(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE)) -> 2018-01-28
BigQueryではタイムゾーンを指定できますが、Redshiftでは指定できません。 そこで、TRUNC関数を使って現在時刻(JST)から日付を取り出してあげます。 日付を取り出す関数は他にもDATE_TRUNC関数があり、これは返り値がTIMESTAMP型になる違いがあります。
#Redshift SELECT DATE_TRUNC('DAY', CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE)) -> 2018-01-28 00:00:00
N日前の日付
#BigQuery SELECT DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY) #Redshift SELECT TRUNC(DATEADD(DAY, -1, TRUNC(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE)))) -> 2018-01-27
現在の日付から1日前の日付を求めています。
BigQueryではDATE_SUB関数、RedshiftではDATEADD関数を使います。
それぞれ引数の指定の仕方が違いますね。
Redshiftには日付の減算関数が無く、DATEADD関数のINTERVALを負の整数にしてあげることで減算します。
どちらもDAYをWEEK, MONTH, YEARに変えることで1週間前、1ヶ月前、1年前の日付を求めることができます。
定期的に実行したい集計クエリは、現在の日付を軸に期間を設定すると良いでしょう。
2つの日付の差
#BigQuery SELECT DATE_DIFF('2018-01-28', '2018-01-01', DAY) #Redshift SELECT DATEDIFF(DAY, '2018-01-01', '2018-01-28') -> 27
指定する引数の順番が逆になっていることに注意です。 ユーザの登録日と起動日から継続率を求める際によくこの文法を使います。
その他の関数
文字列の連結
#BigQuery SELECT CONCAT('str1', ' ', 'str2') #Redshift SELECT 'str1' || ' ' || 'str2' -> "str1 str2"
Redshiftでは||を使って文字列を連結することができます。
CONCAT関数でも可能ですが、2つの文字列しか連結できません。
3つ以上を連結させるにはCONCAT関数の引数にCONCAT関数を使えば良いのですが、||を使う方が便利です。
型変換
#BigQuery SELECT CAST(TIMESTAMP '2018-01-27 10:00:00' as DATE) #Redshift SELECT '2018-01-27 10:00:00'::date , CAST('2018-01-27 10:00:00' as DATE) -> 2018-01-27
Redshiftでは値と型名を::で繋ぐことで、CAST関数と同様に型変換することができます。
中央値
以下のテーブルとレコードがあるとします。
click_logs
| user_id | click_num |
|---|---|
| 1 | 1 |
| 2 | 6 |
| 3 | 8 |
| 4 | 100 |
click_numの中央値を求めてみます。 Redshiftには中央値を返す集計関数MEDIANがありますが、BigQueryにはありません。
#Redshift SELECT MEDIAN(click_num) as median FROM click_logs #BigQuery SELECT PERCENTILE_CONT(click_number, 0.5) OVER() as median FROM click_logs LIMIT 1 -> 7.00
BigQueryではPERCENTILE_CONT関数を使って中央値を算出します。
第2引数はパーセンテージを表しており、0を指定すると最小値、1を指定すると最大値を返します。
PERCENTILE_CONT関数はウィンドウ関数なので、LIMIT 1で1つだけ取り出しています。
#BigQuery SELECT APPROX_QUANTILES(click_number, 2)[OFFSET(1)] as median FROM click_logs -> 6.00
一方でこのような式で中央値を求めようとすると、データ数が奇数の場合は正しいですが、今回のように偶数の場合には中央の2つのデータの平均を計算してくれないので注意が必要です。
おわりに
今回紹介した事例以外にも表現が異なる関数がたくさんあると思うので、困った時は公式リファレンスを見てみてください。 また、他の人が書いたクエリを見てみるのもとても参考になりました。クエリを共有できるRedashは便利ですね！
公式リファレンス
SQL 関数リファレンス - Amazon Redshift
SQL リファレンス | BigQuery | Google Cloud Platform
