Gunosyデータ分析ブログスマートフォン用の表示で見るGunosyデータ分析ブログGunosyで働くデータエンジニアが知見を共有するブログです。採用情報2018-02-28RedshiftとBigQueryでよく使うSQLの違いTipsデータ分析部インターン生の小川です。インターンでは主に動画収集のロジック実装に取り組んでいました。Gunosyではログの管理にRedshiftとBigQueryを使用しています。サービスはAWS上で動いているものも多いので基本はRedshiftで、ログの量が多いものやアドホック分析に用いるものはBigQueryに格納しています。この2つのサービスでSQLの書き方が微妙に異なるところがあり、もどかしい経験をしたので、今回は、よく使うSQLの文法でRedshiftとBigQueryで表現が異なる所をまとめてみようと思います。BigQueryの導入についてはこちらの記事をご覧ください。SQLで記述していきます。23:00BigQueryとRedshiftで表記が異なることがわかります。現在時刻がタイムスタンプ型で返ってきますが、タイムゾーンがデフォルトのUTC(世界標準時)であるため日本時間より-9時間されています。次の例では現在時刻をJST(日本時間)で取得します。08:00BigQueryでは先ほどのCURRENT_DATETIME関数にタイムゾーンを指定することができます。Redshiftでは指定することができないため、CONVERT_TIMEZONE関数を使ってタイムゾーンをJSTに変換します。2018-01-27現在の日時を取得する際はどちらも同じ表記です。タイムゾーンがUTCなので、次の例ではJSTで取得します。2018-01-28BigQueryではタイムゾーンを指定できますが、Redshiftでは指定できません。そこで、TRUNC関数を使って現在時刻(JST)から日付を取り出してあげます。日付を取り出す関数は他にもDATE_TRUNC関数があり、これは返り値がTIMESTAMP型になる違いがあります。2018-01-27現在の日付から1日前の日付を求めています。BigQueryではDATE_SUB関数、RedshiftではDATEADD関数を使います。それぞれ引数の指定の仕方が違いますね。Redshiftには日付の減算関数が無く、DATEADD関数のINTERVALを負の整数にしてあげることで減算します。YEARに変えることで1週間前、1ヶ月前、1年前の日付を求めることができます。定期的に実行したい集計クエリは、現在の日付を軸に期間を設定すると良いでしょう。27指定する引数の順番が逆になっていることに注意です。ユーザの登録日と起動日から継続率を求める際によくこの文法を使います。str2"Redshiftでは||を使って文字列を連結することができます。CONCAT関数でも可能ですが、2つの文字列しか連結できません。3つ以上を連結させるにはCONCAT関数の引数にCONCAT関数を使えば良いのですが、||を使う方が便利です。2018-01-27Redshiftでは値と型名を::で繋ぐことで、CAST関数と同様に型変換することができます。以下のテーブルとレコードがあるとします。100click_numの中央値を求めてみます。Redshiftには中央値を返す集計関数MEDIANがありますが、BigQueryにはありません。7.00BigQueryではPERCENTILE_CONT関数を使って中央値を算出します。第2引数はパーセンテージを表しており、0を指定すると最小値、1を指定すると最大値を返します。1で1つだけ取り出しています。6.00一方でこのような式で中央値を求めようとすると、データ数が奇数の場合は正しいですが、今回のように偶数の場合には中央の2つのデータの平均を計算してくれないので注意が必要です。おわりに今回紹介した事例以外にも表現が異なる関数がたくさんあると思うので、困った時は公式リファレンスを見てみてください。他の人が書いたクエリを見てみるのもとても参考になりました。分析に興味がある人の...2018-02-16Facebookの予測ライブラリProphetを用いたトレンド抽出と変化点検知Gunosyデータ分析部アルバイトの五十嵐です。参加レポートウェブにおけるユーザエンゲージメントを計測・予測する研究について。Blogはたらくを知り、Gunosyを知る。ブロックチェーンブログはてなブログをはじめよう!gunosy_dataさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。